Checking the state of transactions  Example of a transaction

Chapter 20: Transactions: Maintaining Data Consistency and Recovery

Nested transactions

You can nest transactions within other transactions. When you nest 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. Adaptive 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 @@trancount global variable keeps track of the current nesting level for transactions. An initial implicit or explicit begin transaction sets @@trancount to 1. Each subsequent begin transaction increments @@trancount, and a commit transaction decrements it. Firing a trigger also increments @@trancount, and the transaction begins with the statement that causes the trigger to fire. Nested transactions are not committed unless @@trancount equals 0.

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

begin tran
    select @@trancount
    /* @@trancount = 1 */ 
    begin tran
        select @@trancount
        /* @@trancount = 2 */ 
        begin tran
            select @@trancount
            /* @@trancount = 3 */
        commit tran 
    commit tran 
commit tran 
select @@trancount
/* @@ trancount = 0 */

When you nest a rollback transaction statement without including a transaction or savepoint name, it rolls back to the outermost begin transaction statement and cancels the transaction.





Copyright © 2005. Sybase Inc. All rights reserved. Example of a transaction

View this book as PDF