Explicit Transactions and Rollback Modes

With the use of explicit transactions in NuoDB SQL, all successfully executed SQL statements contained in a NuoDB SQL transaction block are either committed or rolled back with an explicit COMMIT or ROLLBACK statement. If one or more of the SQL statements executed in a transaction block abort, those SQL statements are automatically rolled back and the transaction processing continues until an explicit COMMIT or ROLLBACK statement is issued. Only the successfully executed SQL statements are committed.

However, when one or more of these SQL statements are executed in a stored procedure, and that stored procedure contains at least one SQL statement that aborts due to a runtime error, the default behavior is that all SQL statements executed in the stored procedure are automatically rolled back. Execution of the stored procedure is aborted and processing control is returned to the NuoDB SQL transaction block to continue processing the current transaction until either a COMMIT or ROLLBACK statement is explicitly issued.

You can override this default behavior by setting the NuoDB SQL ROLLBACK MODE parameter (see SET) or by specifying the , rollbackMode connection property (see Connection Properties). Changing rollback mode controls only the behavior of SQL statements executed in a stored procedure.

The rollback mode setting is stored in the ROLLBACKMODE column of SYSTEM.CONNECTIONS. For example:

SELECT rollbackmode FROM system.connections;

 ROLLBACKMODE
 -------------

   procedure

You can change the setting of the ROLLBACK MODE parameter in a stored procedure. If you do, then when that stored procedure finishes executing the value of the ROLLBACK MODE parameter reverts to what it was before execution of the stored procedure started.

The valid values for the NuoDB SQL ROLLBACK MODE parameter are:

  • PROCEDURE

  • When a transaction is started and SQL statements are executed both individually and from within a stored procedure, if the stored procedure aborts due to a runtime error, all SQL statements executed in that stored procedure are automatically rolled back, including the successfully executed SQL statements. However, the individual SQL statements executed in the transaction block, and outside the stored procedure, remain in effect and available to be explicitly committed or rolled back.

  • TRANSACTION

  • When a transaction is started and SQL statements are executed both individually and from within a stored procedure, if the stored procedure aborts due to a runtime error, not only are the SQL statements executed in the stored procedure rolled back, but all SQL statements executed in the transaction block are automatically rolled back. This includes even the successfully executed SQL statements.

  • OFF

  • When a transaction is started and SQL statements are executed both individually and from within a stored procedure, if the stored procedure encounters a runtime error, it rolls back the SQL statement that encountered the error and continues executing all other SQL statements in the stored procedure. When control is returned to the transaction block, only the SQL statements that successfully executed, both individually as well as inside the stored procedure are available for committing. A permissions violation at runtime is considered an error and causes the transaction block to terminate.

PROCEDURE ROLLBACK MODE Example

In this example, the ROLLBACK MODE parameter is set to PROCEDURE. When the stored procedure encounters a runtime error, the procedure aborts and rolls back all SQL statements that executed in the stored procedure. The individual SQL statements that executed as part of the transaction block, both before and after the call to the stored procedure, are available to be committed.

DROP PROCEDURE IF EXISTS insert_testtab;
DROP TABLE IF EXISTS testtab;
CREATE TABLE testtab (col1 INT PRIMARY KEY,col2 STRING);
SET DELIMITER @
CREATE PROCEDURE insert_testtab (IN i_insert_cnt INTEGER)
AS
  VAR l_while_cnt INTEGER = 1;
  WHILE ( l_while_cnt <= i_insert_cnt )
     INSERT INTO testtab VALUES (l_while_cnt,'sp_insert');
     l_while_cnt = l_while_cnt + 1;
  END_WHILE;
END_PROCEDURE;
@
SET DELIMITER ;
SET ROLLBACK MODE PROCEDURE;
START TRANSACTION;
INSERT INTO testtab VALUES (3,'insert');
CALL insert_testtab(5);
Procedure TEST.INSERT_TESTTAB, duplicate value in unique index TESTTAB..PRIMARY_KEY, key = '3'

SELECT * FROM testtab;
 COL1   COL2
 ----- ------
   3   insert

INSERT INTO testtab VALUES (4,'insert');
COMMIT;
SELECT * FROM testtab;
 COL1   COL2
 ----- ------
   3   insert
   4   insert

TRANSACTION ROLLBACK MODE Example

In this example, the ROLLBACK MODE parameter is set to TRANSACTION. When the stored procedure encounters a runtime error, the procedure aborts and rolls back all SQL statements that executed as part of the transaction block, both the SQL statements in the stored procedure and the individual SQL statements that executed before the call to the stored procedure. Only SQL statements that execute as part of the transaction block and after the rollback are available to be committed.

DROP PROCEDURE IF EXISTS insert_testtab;
DROP TABLE IF EXISTS testtab;
CREATE TABLE testtab (col1 INT PRIMARY KEY,col2 STRING);
SET DELIMITER @
CREATE PROCEDURE insert_testtab (IN i_insert_cnt INTEGER)
AS
  VAR l_while_cnt INTEGER = 1;
  WHILE ( l_while_cnt <= i_insert_cnt )
     INSERT INTO testtab VALUES (l_while_cnt,'sp_insert');
     l_while_cnt = l_while_cnt + 1;
  END_WHILE;
END_PROCEDURE;
@
SET DELIMITER ;
SET ROLLBACK MODE TRANSACTION;
START TRANSACTION;
INSERT INTO testtab VALUES (3,'insert');
CALL insert_testtab(5);
Procedure TEST.INSERT_TESTTAB, duplicate value in unique index TESTTAB..PRIMARY_KEY, key = '3'

SELECT * FROM testtab;
-- no records are returned

INSERT INTO testtab VALUES (4,'insert');
COMMIT;
SELECT * FROM testtab;
 COL1    COL2
 ----- ---------
   4   sp_insert

ROLLBACK MODE OFF Example

In this example, the ROLLBACK MODE parameter is set to OFF. The stored procedure encounters a runtime error but continues to execute all other SQL statements in the stored procedure. All successfully executed SQL statements executed as part of the transaction block are available to be committed.

DROP PROCEDURE IF EXISTS insert_testtab;
DROP TABLE IF EXISTS testtab;
CREATE TABLE testtab (col1 INT PRIMARY KEY,col2 STRING);
SET DELIMITER @
CREATE PROCEDURE insert_testtab (IN i_insert_cnt INTEGER)
AS
  VAR l_while_cnt INTEGER = 1;
  WHILE ( l_while_cnt <= i_insert_cnt )
     INSERT INTO testtab VALUES (l_while_cnt,'sp_insert');
     l_while_cnt = l_while_cnt + 1;
  END_WHILE;
END_PROCEDURE;
@
SET DELIMITER ;
SET ROLLBACK MODE OFF;
START TRANSACTION;
INSERT INTO testtab VALUES (3,'insert');
CALL insert_testtab(5);
COMMIT;
SELECT * FROM testtab;
 COL1    COL2
 ----- ---------
   3   insert
   1   sp_insert
   2   sp_insert
   4   sp_insert
   5   sp_insert