TRANSACTION

Synopsis:

 BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [TRANSACTION [name]] END 
  [TRANSACTION [name]] COMMIT [TRANSACTION [name]] 
    ROLLBACK [TRANSACTION [name]]

Description:

The TRANSACTION keyword is used in SQL commands to indicate transactions. QDB supports transactions with rollback and atomic commit. The optional transaction name is ignored. QDB currently doesn't allow nested transactions.

Any changes to the database must be made within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed at the conclusion of the command.

Transactions can be started manually using the BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command. But a transaction will also execute ROLLBACK if the database is closed or if an error occurs and a ROLLBACK conflict-resolution algorithm is specified. See the documentation on the ON CONFLICT clause for more information about the ROLLBACK conflict-resolution algorithm.

In QDB, transactions can be deferred, immediate, or exclusive. Deferred means that no locks are acquired on the database until it is first accessed. Thus, with a deferred transaction, the BEGIN statement doesn't lock anything—locks are not acquired until the first read or write operation. The first read operation creates a SHARED lock and the first write operation creates a RESERVED lock. Because the acquisition of locks is deferred until they are needed, it is possible that another thread or process could create a separate transaction and write to the database after the BEGIN on the current thread has executed.

With an immediate transaction, RESERVED locks are acquired on all databases as soon as the BEGIN statement is executed, without waiting for the database to be used. After a BEGIN IMMEDIATE statement, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue reading from the database, however.

An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE statement, you are guaranteed that no other thread or process will be able to read from or write to the database until the transaction is complete.

Note: The default behavior for QDB is to use deferred transactions.

The COMMIT command doesn't actually perform a commit until all pending SQL commands finish. Thus, if two or more SELECT statements are actively processing and a COMMIT is executed, the commit will not actually occur until all the SELECT statements finish.

The SHARED, RESERVED, and EXCLUSIVE locks behave as follows:

SHARED: The database may be read but not written. Any number of threads or processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active.
RESERVED: A process or thread is planning on writing to the database file in the future but is currently just reading from the file. Only one RESERVED lock may be active at one time, though multiple SHARED locks can coexist with a single RESERVED lock.
EXCLUSIVE: An EXCLUSIVE lock is needed to write to the database file. Only one EXCLUSIVE lock is allowed on the file and no other locks of any kind are allowed to coexist with an EXCLUSIVE lock. To maximize concurrency, QDB works to minimize the amount of time that EXCLUSIVE locks are held.

Returns:

An attempt to execute COMMIT might result in an SQLITE_BUSY return code. This indicates that another thread or process has a read lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear.