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
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
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
ROLLBACK statement, the next executed statement will implicitly start another transaction. This transaction spans all 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