About Implicit Transactions

Implicit mode is the default transaction mode in NuoDB SQL. This is governed by the default setting of the NuoD SQL AUTOCOMMIT runtime parameter, which is ON. The default behavior is that each Data Manipulation Language (DML) statement starts its own transaction and is automatically committed or rolled back, depending on whether the DML SQL statement executes successfully (the transaction is committed) or fails (the transaction is rolledback). The START TRANSACTION and COMMIT/ROLLBACK statements are implied for each DML SQL statement executed. Each DML SQL statement is a separate transaction.

You can specify the NuoDB SQL SET statement to toggle the setting of the AUTOCOMMIT parameter on and off. Alternatively, you can use NuoDB driver APIs to do this.

In the following example, with the default behavior of AUTOCOMMIT set to ON, each DML SQL statement starts with an implied START TRANSACTION and finishes with an implied COMMIT. Therefore, the ROLLBACK that executes after the second INSERT statement has no effect. Note that the SET AUTOCOMMIT ON statement is not required since it is the default. It is shown here to clarify the behavior.

DROP TABLE IF EXISTS testtab;
CREATE TABLE testtab (col1 INTEGER, col2 STRING);
SET AUTOCOMMIT ON;
INSERT INTO testtab VALUES (1,'row1');
INSERT INTO testtab VALUES(2,'row2');
ROLLBACK;
SELECT * FROM testtab;

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

Suppose you set the AUTOCOMMIT parameter to OFF. Now the behavior is that the first DML SQL statement starts an implied transaction that remains in effect until a COMMIT or a ROLLBACK statement is executed. After that, a new transaction automatically starts with the next DML SQL statement.

If the connection terminates while a transaction is open, all pending DML SQL statements are rolled back.

In the following example, an implicit transaction is started with the first DML SQL statement. The transaction remains open until a COMMIT or ROLLBACK is issued. All DML SQL statements within the transaction are treated the same. That is, they are all either committed or rolled back. There are three implicit transactions in this example.

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

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

With the AUTOCOMMIT parameter set to OFF, when the transaction ends with either a COMMIT or ROLLBACK statement, there is an implied START TRANSACTION statement that starts a new transaction. This transaction spans all DML statements until the next COMMIT or ROLLBACK statement.

In NuoDB SQL, the AUTOCOMMIT parameter is reset for every new connection to the default value of ON:

SELECT autocommitflags FROM system.connections;

  AUTOCOMMITFLAGS  
 ---------------- 

        1