AIR and SQLite transaction issues

Nice thing about SQLite in Adobe AIR are transactions, you start them with begin method of SQLConnection instance and then finish with rollback or commit methods. Problem occurs when you start transaction with default arguments and then try to stop it without any changes being made.

In that case suggested in help documentation solution:

            // If a transaction is happening, roll it back
            if (conn.inTransaction)
            {
                conn.addEventListener(SQLEvent.ROLLBACK, rollbackHandler);
                conn.rollback();
            }

will never be executed and then subsequent call to begin will result with following error:


[SQLErrorEvent type="error" bubbles=false cancelable=false error=SQLError: 'Error #3115: SQL Error.', details:'cannot start a transaction within a transaction', operation:'begin', detailID:'2509']


To avoid this you can call begin with the “option” argument set to any value other then SQLTransactionLockType.DEFERRED.
By doing this you have explicitly set connection to “inTransaction” state and above code will work.

* SQLTransactionLockType.DEFERRED indicates that a lock is not acquired until the first read or write operation.
* SQLTransactionLockType.EXCLUSIVE indicates that a lock is acquired as soon as possible, and no other SQLConnection instance can read from or write to the database.
* SQLTransactionLockType.IMMEDIATE indicates that a lock is acquired as soon as possible, in which other SQLConnection instances can read from but can’t write to the database.

The default value (null) is equivalent to SQLTransactionLockType.DEFERRED.
adobe help