Execute statement

Every statement has an associated connection object, a statement represents a single ‘compiled’ SQL statement. There’re two methods to execute SQLStatement instance in AIR, one execute a single statement each time, another use .begin() .commit() to execute multiple statements, as a transaction. For SQLite engine, they both transaction, the single statement operates in autocommit mode by default.

SQL statement execution process: Statement+SQL -> Compilation -> Byte Code (with parameters) -> execution -> Finalization

Use parameters for statements is highly recommended. One important benefit Adobe might not documented: SQLite takes care of escaping the string values binding to parameters, helping avoid syntax errors and SQL injection attacks, i.e, it will convert ‘ to ”.

Transaction and Locks

• Multiple read transactions (SELECT), one single write transaction (INSERT/UPDATE/DELETE) at a time.
• When write transaction is writing (exclusive locked), no new transactions can connect.
• Write transaction will wait for any running read transactions to finish before writing.

Each transaction (or SQLStatement execution in autocommit mode) transit from one state to another state.

A default SELECT statement run in path as followed: Unlocked -> Pending -> Shared -> Unlocked

Two SELECT statements grouped with .begin() and .commit() in a single transaction:

Unlocked -> Pending -> Shared -> Unlocked

Two SELECT statements run in turn:

Unlocked -> Pending -> Shared -> Unlocked -> Pending -> Shared -> Unlocked

A write transaction (i.e. UPDATE) by default (autocommit mode):

Unlocked -> Pending -> Shared -> Reserved -> Pending -> Exclusive locked(commit) -> Unlocked

A write transaction with ‘BEGIN IMMEDIATE’:

Reserved -> Pending -> Exclusive locked(commit) -> Unlocked

The ‘Reserved’ state is a tricky state that may a place to improve performance. It actually store modifications in a memory cache, and create a rollback journal file for possible crash recovery. In this state the write transaction get real work done without affecting other read transactions. That’s how ‘multiple reader, one writer’ works.

After the write transaction get it work done, it begins trying get a Pending lock to move to Exclusive commit state, where (Pending) no more new read/write transactions (they have to wait in queue), the write transaction need to wait any other transactions still working to finish. Only after anything else cleared, this transaction enter into Exclusive state.

The ‘Exclusive’ state is to flush the modifications from cache to database file.

SQLConnection has a property cacheSize that affect write transaction. In ‘Reserved’ state if page cache fills up, it have to move to ‘Exclusive’ state before finish its own task. The bigger cacheSize the more modified pages can store and minimize the time to be used in ‘Exclusive’ state. (sqlite_analyzer can dump detailed statistics helpful for adjusting cacheSize)