Transactions are the basis of concurrency control, providing strong consistency and preserving the ACID guarantees: Atomicity, Consistency, Isolation and Durability. A transaction is a sequence of SQL statements that is committed or rolled back as a whole, in a single operation. The intermediate states between the statements of a transaction are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from being completed, then none of its statements affect the database at all.

An active transaction ends either by successful commit, or rollback (abort). Rollback can happen either by the client request, or by the Regatta system itself if it encounters an error or if it detects that the transaction entered a deadlock with other transactions.

The transaction control commands aren’t regular SQL statements. They are executed by calling specific API on the Regatta client drivers. The only exception is the SET TRANSACTION command, which can be executed as a statement, provided that it’s the first statement in the transaction. The specification of that command is detailed below.

The Regatta client driver has an AUTOCOMMIT mode which can be turned ON/OFF via the client driver API. The AUTOCOMMIT mode defines how transaction control is managed:

  • When AUTOCOMMIT mode is set to ON, the driver receives the statements and automatically starts a transaction for each statement. Once the statement’s execution is complete, the driver automatically commits the transaction. This mode doesn’t support multi-statement transactions.
  • When AUTOCOMMIT mode is set to OFF, each transaction has to be started and committed (or rolled back) explicitly by using the driver API.

By default, the Regatta client driver AUTOCOMMIT mode is set to ON.

Important note: Passing through transaction control commands (BEGIN/COMMIT/ROLLBACK) as strings through the regular SQL statement API would result in an error.

​
Transaction control and DDL

DDL commands don’t conform to the standard transaction ACID rules, therefore, they cannot be executed as part of a transaction like other SQL commands. Technically, they are executed in single-statement pseudo transactions to retain the basic protocol that encapsulates statements within transactions. But those transactions don’t provide the isolation guarantee among transactions.

A DDL statement must be the first statement in a transaction, and it causes an immediate commit of the transaction after its completion, even if AUTOCOMMIT mode is set to OFF.

Other concurrent transactions are not isolated from changes made by DDLs, and they may be exposed to changes made by a DDL even before the DDL is completed and even if they run in snapshot-based isolation mode.

For example, a statement that performs a full scan on a table and a DROP TABLE is issued concurrently to the scan execution, may either complete successfully or fail depending on the timing of execution (even if the scan is snapshot-based). However, Regatta guarantees that if it succeeds, it won’t return partial results.