Transaction Behavior in SQL Stored Procedures

In NuoDB SQL, by default, all DML 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 DML 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 DML SQL statement in a stored procedure fails, all previously, successfully executed DML 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 DML SQL statements in the stored procedure.

If stored procedures are nested, all DML SQL statements executed in the called stored procedure wait to be committed until the calling stored procedure successfully finishes execution.

To override the default behavior of a stored procedure being one transaction block, use the AUTOCOMMIT parameter along with the AUTOCOMMIT_SP_MODE parameter. The following table shows the transaction behavior provided by the settings of these parameters:

AUTOCOMMIT
Parameter
Setting

AUTOCOMMIT_SP_MODE Parameter Setting

Transaction Behavior in Stored Procedure

ON OFF
This is the default setting.
Provides the default behavior described above. That is, all DML SQL statements in a stored procedure execute as a single transaction block.
ON ON Each DML SQL statement successfully executed in a stored procedure automatically commits during execution of the stored procedure. If failed execution of a DML SQL statement causes a stored procedure to abort, any previously committed DML SQL statements remain committed.
OFF OFF
This is the default setting.
Provides the default behavior. All DML SQL statements in a stored procedure execute as a single transaction block. When the AUTOCOMMIT parameter is set to OFF then the setting of the AUTOCOMMIT_SP_MODE parameter is ignored. (This is the default setting.)
OFF ON

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

SELECT autocommitflags,autocommitspmode FROM system.connections;
 AUTOCOMMITFLAGS  AUTOCOMMITSPMODE  
 ---------------- ----------------- 

        1               FALSE       

AUTOCOMMITFLAGS: Indicates the autocommit behavior for the connection. This is governed by the NuoDB SQL AUTOCOMMIT parameter setting. The default value is ON. See SET and CONNECTIONS System Table Description.

AUTOCOMMITSPMODE: Indicates the autocommit behavior in a stored procedure. This is governed by the NuoDB SQL AUTOCOMMIT_SP_MODE paramete setting or by the autoCommitSPMode connection property. The default value is OFF. See SET, Connection Properties, and CONNECTIONS System Table Description.

As you can see, the AUTOCOMMIT parameter setting controls the behavior provided by the AUTOCOMMIT_SP_MODE parameter setting. Consequently, to override the default transaction behavior in a stored procedure, set the AUTOCOMMIT_SP_MODE parameter to ON and toggle the setting of the AUTOCOMMIT parameter between ON and OFF.

The following example illustrates the default transaction behavior in a stored procedure. The AUTOCOMMIT_SP_MODE parameter is set to OFF by default. Toggling the setting of the AUTOCOMMIT parameter has no effect on the transaction behavior of the stored procedure. The DML 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

Suppose that the AUTOCOMMIT_SP_MODE parameter is set to ON for the connection. You can now use the AUTOCOMMIT parameter setting to control the transaction behavior in stored procedures.

In the following example, when the AUTOCOMMIT parameter is set to OFF, the AUTOCOMMIT_SP_MODE setting is ignored. Therefore, you would see the default behavior when executing the insert_testtab() stored procedure. All DML SQL statements execute in one transaction block. All DML SQL statements are committed or rolled back, depending on the successful execution of all DML SQL statements. When the AUTOCOMMIT parameter is set to ON, you would see each DML 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 DML SQL statement. A DML SQL statement that does not execute successfully still causes the stored procedure to abort. However, all DML SQL statements previously, successfully executed and committed remain committed. An error that would cause the stored procedure to abort rolls back only the current DML SQL statement.

SET AUTOCOMMIT_SP_MODE ON;
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   

There is an alternative approach to setting the AUTOCOMMIT_SP_MODE parameter to ON and toggling the setting of the AUTOCOMMIT parameter. 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 settings of the AUTOCOMMIT and AUTOCOMMIT_SP_MODE parameters. Multiple transaction blocks can be executed inside a stored procedure.

By controlling transaction autocommit behavior in nested stored procedures, you can enable DML SQL statements executed in one called stored procedure to be committed, while DML 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, autocommitspmode FROM system.connections;
 AUTOCOMMITFLAGS  AUTOCOMMITSPMODE  
 ---------------- ----------------- 
        1               FALSE        

In the following example, the AUTOCOMMIT parameter is set to its default of ON and the AUTOCOMMIT_SP_MODE parameter is also set to ON. However, you can suspend the behavior provided by these settings by using an explicit transaction. The stored procedure controls the commit or roll back of executed DML SQL statements by using explicit transaction blocks.

DROP PROCEDURE if exists insert_testtab;
DROP TABLE if exists testtab;
CREATE TABLE testtab (col1 INT);
SET DELIMITER @
CREATE PROCEDURE insert_testtab( IN i_var INT)
  AS
    VAR l_cnt INT = 0;
    WHILE ( l_cnt <= i_var )
      START TRANSACTION;
        INSERT INTO testtab VALUES (l_cnt);
        IF ( l_cnt = 2 or l_cnt = 4 )
          ROLLBACK;
        ELSE
          COMMIT;
        END_IF;
        l_cnt = l_cnt + 1;
    END_WHILE;
END_PROCEDURE ;
@
SET DELIMITER ;
CALL insert_testtab(5);
SELECT * FROM testtab;

  COL1
  ----- ­­­­­
   0
   1
   3
   5