About Explicit Transactions

NuoDB SQL supports explicitly defining transactions with the use of START TRANSACTION and COMMIT/ROLLBACK statements. An explicit transaction block begins when a START TRANSACTION statement is issued and ends when either a COMMIT or ROLLBACK statement is issued.

Explicit transaction mode suspends the AUTOCOMMIT parameter setting for the connection. In other words, in an explicit transaction block, NuoDB SQL ignores the AUTOCOMMIT parameter setting. The AUTOCOMMIT parameter setting has no effect inside an explicit transaction block. The benefit of this is that the default AUTOCOMMIT parameter setting can be overridden. Several SQL statements can be grouped together and can be committed or rolled back in one operation. For example:

Each explicit transaction block is open until a COMMIT or ROLLBACK statement is issued. After a COMMIT or ROLLBACK statement is executed, transaction behavior is based on the setting of the AUTOCOMMIT parameter until another START TRANSACTION statement is issued.

The following example illustrates four transaction blocks. The DELETE statement is an implicit transaction and is automatically committed because the AUTOCOMMIT parameter is set to ON. The START TRANSACTION statement starts an explicit transaction block, which suspends the AUTOCOMMIT behavior. The explicit transaction block ends when the COMMIT statement is issued. The next START TRANSACTION statement begins a new explicit transaction block, which again suspends the AUTOCOMMIT behavior. This explicit transaction blocks ends when the ROLLBACK statement is issued, which rolls back all of the statements executed inside this explicit transaction block. The last INSERT statement is an implicit transaction that is automatically committed because the AUTOCOMMIT parameter is set to ON. The last ROLLBACK statement has no effect.

In an explicit transaction block, you can define save points to exercise additional transaction control. See SAVEPOINT and RELEASE SAVEPOINT.