Nesting Transactions

Advantage Concepts

  Previous topic Next topic  

Transactions can be nested within other transactions. When nesting begin transaction and commit transaction statements, the outermost pair actually begin and commit the transaction. The inner pairs just keep track of the nesting level. Advantage Database Server does not commit the transaction until the commit transaction that matches the outermost begin transaction is issued. Normally, this transaction nesting occurs as stored procedures or triggers that contain begin/commit pairs call each other.

 

The ::conn.TransactionCount global variable keeps track of the current nesting level for transactions. An initial implicit or explicit begin transaction sets ::conn.TransactionCount to 1. Each subsequent begin transaction increments ::conn.TransactionCount, and a commit transaction decrements it. Firing a trigger also increments ::conn.TransactionCount, and the transaction begins with the statement that causes the trigger to fire. Nested transactions are not committed unless ::conn.TransactionCount equals 0.

 

For example, the following nested groups of statements are not committed by Advantage Database Server until the final commit transaction:

 

BEGIN TRANSACTION;
  SELECT ::conn.TransactionCount FROM system.iota;
  /* ::conn.TransactionCount = 1 */
 
  BEGIN TRANSACTION;
      SELECT ::conn.TransactionCount FROM system.iota;
      /* ::conn.TransactionCount = 2 */
 
      BEGIN TRANSACTION;
          SELECT ::conn.TransactionCount FROM system.iota;
          /* ::conn.TransactionCount = 3 */
      COMMIT WORK;
 
  COMMIT WORK;
 
COMMIT WORK;
 
SELECT ::conn.TransactionCount FROM system.iota;
/* ::conn.TransactionCount = 0 */

 
A nested rollback transaction statement without a savepoint name will roll back to the outermost begin transaction statement and cancel the transaction.