Transaction Behavior in SQL Stored Procedures

In NuoDB SQL, by default, all SQL statements in a stored procedure execute as a single transaction block. The implicit transaction starts when the stored procedure begins executing and ends when the stored procedure completes executing. All SQL statements executed in the stored procedure are either committed or rolled back, depending on the success or failure of the execution of the stored procedure.

By default, if a single SQL statement in a stored procedure fails, all previously, successfully executed SQL statements are rolled back and the stored procedure execution is aborted. The only exception to this default behavior is with the use of the TRY…​CATCH statement (see SQL Procedural Conditional Statements), which allows for handling errors and continuing execution of SQL statements in the stored procedure.

Nested stored procedures are executed in the transaction context of the outermost stored procedure.

The following table shows the transaction behavior:

AUTOCOMMIT Transaction Behavior in Stored Procedure

ON

Provides the default behavior described above. That is, all SQL statements in a stored procedure execute as a single transaction block.

ON

Each SQL statement successfully executed in a stored procedure automatically commits during execution of the stored procedure. If failed execution of a SQL statement causes a stored procedure to abort, any previously committed SQL statements remain committed.

OFF

The entire stored procedure is executed in the context of the transaction in which it was called.

OFF

To learn what these per-connection settings are, query SYSTEM.CONNECTIONS. See CONNECTIONS System Table Description. For example:

SELECT autocommitflags FROM system.connections;
 AUTOCOMMITFLAGS
 ----------------

        1

AUTOCOMMITFLAGS: Indicates the autocommit behavior for the connection. This is governed by the AUTOCOMMIT parameter. The default value is ON.

The following example illustrates the default transaction behavior in a stored procedure. Toggling the setting of the AUTOCOMMIT parameter has no effect on the transaction behavior of the stored procedure. The SQL statements in the stored procedure will always execute as one transaction block.

DROP PROCEDURE IF EXISTS insert_testtab;
DROP TABLE testtab;
CREATE TABLE testtab (col1 INTEGER);
SET DELIMITER @
CREATE PROCEDURE insert_testtab()
 AS
     INSERT INTO testtab (col1) VALUES (1);
     INSERT INTO testtab (col1) VALUES (2);
     INSERT INTO testtab (col1) VALUES('hello');
     INSERT INTO testtab (col1) VALUES (3);
END_PROCEDURE ;
@
SET DELIMITER ;

SET AUTOCOMMIT OFF;
CALL insert_testtab;
Procedure TEST.INSERT_TESTTAB, unable to convert string "hello" into type "integer"
SELECT * FROM testtab;
-- no records selected

SET AUTOCOMMIT ON;
CALL insert_testtab;
Procedure TEST.INSERT_TESTTAB, unable to convert string "hello" into type "integer"
SELECT * FROM testtab;
-- no records selected

In the following example, the AUTOCOMMIT parameter is set to OFF. Therefore, you would see the default behavior when executing the insert_testtab() stored procedure. All SQL statements execute in one transaction block. All SQL statements are committed or rolled back, depending on the successful execution of all SQL statements. When the AUTOCOMMIT parameter is set to ON, you would see each SQL statement inside the insert_testtab() stored procedure execute as an individual implicit transaction. Each statement would be committed or rolled back based on the successful execution of each SQL statement. A SQL statement that does not execute successfully still causes the stored procedure to abort. However, all SQL statements previously, successfully executed and committed remain committed. An error that would cause the stored procedure to abort rolls back only the current SQL statement.

SET AUTOCOMMIT OFF;
CALL insert_testtab;
Procedure TEST.INSERT_TESTTAB, unable to convert string "hello" into type "integer"
SELECT * FROM testtab;
-- no records selected

SET AUTOCOMMIT ON;
CALL insert_testtab;
SET AUTOCOMMIT ON;
CALL insert_testtab; unable to convert string "hello" into type "integer"
SELECT * FROM testtab;

 COL1
 -----
   1
   2

You can use explicit transactions inside stored procedures. Similar to outside a stored procedure, the use of explicit transactions using START TRANSACTION and COMMIT/ROLLBACK statements inside a stored procedure suspends the behavior that would be provided by the setting of the AUTOCOMMIT parameter. Multiple transaction blocks can be executed inside a stored procedure.

By controlling transaction autocommit behavior in nested stored procedures, you can enable SQL statements executed in one called stored procedure to be committed, while SQL statements executed in another called stored procedure can be rolled back.

SAVEPOINT and RELEASE SAVEPOINT statements can also be used in a stored procedure to control the transaction behavior and error handling. See SAVEPOINT, RELEASE SAVEPOINT.

To illustrate this behavior, both settings, as shown below, are set to their default settings.

SELECT autocommitflags FROM system.connections;
 AUTOCOMMITFLAGS
 ----------------
        1