Open topic with navigation
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
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
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
AUTOCOMMIT parameter set to
OFF, when the transaction ends with either a
ROLLBACK statement, there is an implied
START TRANSACTION statement that starts a new transaction. This transaction spans all DML statements until the next
In NuoDB SQL, the
AUTOCOMMIT parameter is reset for every new connection to the default value of
SELECT autocommitflags FROM system.connections;AUTOCOMMITFLAGS ---------------- 1