Syntax:
BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] [ TRANSACTION [ name ]] END | COMMIT | ROLLBACK [ TRANSACTION [ name ]]
GDBase supports transactions with rollback and atomic commit.
The optional transaction name is ignored. GDBase does not allow nested transactions.
No changes can be made to a table except within a transaction. Any command that changes the table (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 roll back if the table is closed or if an error occurs and the ROLLBACK conflict resolution algorithm is specified. See the documentation on the ON CONFLICT clause for additional information about the ROLLBACK conflict resolution algorithm.
In GDBase transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE:
DEFERRED means that no locks are acquired on the table until the table is first accessed. Thus with a deferred transaction, the BEGIN statement itself does nothing. Locks are not acquired until the first read or write operation. The first read operation against a table 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 table after the BEGIN on the current thread has executed.
The default behavior for GDBase is a deferred transaction.
IMMEDIATE guarantees that no other thread or process will be able to write to the table or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the table, however.
EXCLUSIVE guarantees that no other thread or process will be able to read or write the table until the transaction is complete.
The COMMIT command does not actually perform a commit until all pending SQL commands finish. Thus if two or more SELECT statements are in the middle of processing and a COMMIT is executed, the commit will not actually occur until all SELECT statements finish. The connection executing the SELECT statement can not change the selected table.
An attempt to execute COMMIT might result in an error message. This indicates that another thread or process has a read lock on the table that prevents the table 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.