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 DML SQL statements can be grouped together and can be committed or rolled back in one operation. For example:

SET AUTOCOMMIT ON;
DELETE FROM testtab;
ROLLBACK;
START TRANSACTION;
-- AUTOCOMMIT is suspended
INSERT INTO testtab VALUES (1,'row1');
INSERT INTO testtab VALUES (2,'row2');
COMMIT;
START TRANSACTION;
INSERT INTO testtab VALUES (3,'row3');
INSERT INTO testtab VALUES (4,'row4');
ROLLBACK;
SELECT * FROM testtab;

 COL1  COL2  
 ----- ----- 
   1   row1  
   2   row2  

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 DML SQL 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 DML SQL statements executed inside this explicit transaction block. The last DML SQL 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.

SET AUTOCOMMIT ON;
DELETE FROM testtab;
START TRANSACTION;
INSERT INTO testtab VALUES (1,'row1');
INSERT INTO testtab VALUES (2,'row2');
COMMIT;
START TRANSACTION;
INSERT INTO testtab VALUES (3,'row3');
INSERT INTO testtab VALUES (4,'row4');
ROLLBACK;
INSERT INTO testtab VALUES (5,'row5');
ROLLBACK;
SELECT * FROM testtab;

 COL1  COL2  
 ----- ----- 

   1   row1  
   2   row2  
   5   row5  

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