About Implicit Transactions

Implicit mode is the default transaction mode in NuoDB SQL. This is governed by the default setting of the NuoDB SQL AUTOCOMMIT runtime parameter, which is ON. The default behavior is that each statement starts its own transaction and is automatically committed. The START TRANSACTION and COMMIT/ROLLBACK statements are implied for each SQL statement executed. Each SQL statement is a separate transaction.

You can execute the 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 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 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 SQL statement.

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

In the following example, an implicit transaction is started with the first SQL statement. The transaction remains open until a COMMIT or ROLLBACK is issued. All 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

When the transaction ends with either a COMMIT or ROLLBACK statement, the next executed statement will implicitly start another transaction. This transaction spans all 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