@@TRANCOUNT and savepoints

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

@@TRANCOUNT

Number of BEGIN TRAN statements that are active in your current connection.

Returns:

greater than 0 -> open transaction
0 -> no open transaction

Modified by:

BEGIN TRAN -> @@TRANCOUNT + 1
COMMIT TRAN -> @@TRANCOUNT - 1
ROLLBACK TRAN -> @@TRANCOUNT = 0 (except with savepoint_name)

Nested transactions

SELECT @@TRANCOUNT AS '@@TRANCOUNT value';
BEGIN TRAN;
    SELECT @@TRANCOUNT AS '@@TRANCOUNT value'; 
    DELETE transactions;
    BEGIN TRAN; 
        SELECT @@TRANCOUNT AS '@@TRANCOUNT value'; 
        DELETE accounts; 
    -- If @@TRANCOUNT > 1 it doesn't commit!
    COMMIT TRAN;
    SELECT @@TRANCOUNT AS '@@TRANCOUNT value'; 
ROLLBACK TRAN;
SELECT @@TRANCOUNT AS '@@TRANCOUNT value';

| @@TRANCOUNT value |
|-------------------|
| 0                 |

| @@TRANCOUNT value |
|-------------------|
| 1                 |

| @@TRANCOUNT value |
|-------------------|
| 2                 |

| @@TRANCOUNT value |
|-------------------|
| 1                 |

| @@TRANCOUNT value |
|-------------------|
| 0                 |

Nested transactions

SELECT * FROM transactions

| transaction_id | account_id | amount   | transaction_date       |
|----------------|------------|----------|------------------------|
| 1              | 1          | -100,00  | 2019-03-18 19:12:36.81 |
| 2              | 2          | 100,00   | 2019-01-18 19:12:36.91 |
| ...            | ...        | ...      | ...                    |

SELECT * FROM accounts

| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1          | 55555555551234567890 | 1           | 25000,00        |
| 2          | 55555555559876543210 | 1           | 200,00          |
| ...        | ...                  | ...         | ...             |

Nested transactions

SELECT @@TRANCOUNT AS '@@TRANCOUNT value';
BEGIN TRAN;
    SELECT @@TRANCOUNT AS '@@TRANCOUNT value'; 
    DELETE transactions;
        BEGIN TRAN; 
            SELECT @@TRANCOUNT AS '@@TRANCOUNT value'; 
            DELETE accounts;
        COMMIT TRAN;
        SELECT @@TRANCOUNT AS '@@TRANCOUNT value'; 
COMMIT TRAN;
SELECT @@TRANCOUNT AS '@@TRANCOUNT value';

Nested transactions

SELECT * FROM transactions

| transaction_id | account_id | amount   | transaction_date       |
|----------------|------------|----------|------------------------|

SELECT * FROM accounts

| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|

@@TRANCOUNT in a TRY...CATCH construct

BEGIN TRY  
    BEGIN TRAN;
        UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
        INSERT INTO transactions VALUES (1, -100, GETDATE());

        UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
        INSERT INTO transactions VALUES (5, 100, GETDATE());
    IF (@@TRANCOUNT > 0)
        COMMIT TRAN;    
END TRY
BEGIN CATCH  
    IF (@@TRANCOUNT > 0)
        ROLLBACK TRAN;
END CATCH

Savepoints

Markers within a transaction
Allow to rollback to the savepoints

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }  
[ ; ]

Savepoints

BEGIN TRAN;
    SAVE TRAN savepoint1;
    INSERT INTO customers VALUES ('Mark', 'Davis', '[email protected]', '555909090');

    SAVE TRAN savepoint2;
    INSERT INTO customers VALUES ('Zack', 'Roberts', '[email protected]', '555919191');

    ROLLBACK TRAN savepoint2;
    ROLLBACK TRAN savepoint1;

    SAVE TRAN savepoint3;
    INSERT INTO customers VALUES ('Jeremy', 'Johnsson', '[email protected]', '555929292');
COMMIT TRAN;

| customer_id | first_name | last_name | email                   | phone     |
|-------------|------------|-----------|-------------------------|-----------|
| 13          | Jeremy     | Johnsson  | [email protected] | 555929292 |

Savepoints

BEGIN TRAN
    ...
    ROLLBACK TRAN savepoint2;
    SELECT @@TRANCOUNT AS '@@TRANCOUNT value';

    ROLLBACK TRAN savepoint1;
    SELECT @@TRANCOUNT AS '@@TRANCOUNT value';
    ...
COMMIT TRAN;

| @@TRANCOUNT value |
|-------------------|
| 1                 |

| @@TRANCOUNT value |
|-------------------|
| 1                 |

Let's practice!

Transactions and Error Handling in SQL Server