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