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 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

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