Explicit Transactions and Rollback Modes

With the use of explicit transactions in NuoDB SQL, all successfully executed DML 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 DML SQL statements executed in a transaction block abort, those DML SQL statements are automatically rolled back and the transaction processing continues until an explicit COMMIT or ROLLBACK statement is issued. Only the successfully executed DML SQL statements are committed.

However, when one or more of these DML SQL statements are executed in a stored procedure, and that stored procedure contains at least one DML SQL statement that aborts due to a runtime error, the default behavior is that all DML 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 DML 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 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 DML SQL statements that executed in the stored procedure. The individual DML 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 DML SQL statements that executed as part of the transaction block, both the DML SQL statements in the stored procedure and the individual DML SQL statements that executed before the call to the stored procedure. Only DML 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 DML SQL statements in the stored procedure. All successfully executed DML 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