1. @@TRANCOUNT and savepoints
In this lesson, you will understand @@TRANCOUNT and savepoints.
2. @@TRANCOUNT
@@TRANCOUNT returns the number of BEGIN TRAN statements that are active in your current connection. It returns a value greater than zero if you are in an open transaction, and zero if you are not.
It can be modified by the BEGIN TRAN statement, which increases the @@TRANCOUNT value by 1. The COMMIT TRAN statement decreases the @@TRANCOUNT value by 1. The ROLLBACK TRAN statement decrements the @@TRANCOUNT to 0, except if there is a savepoint.
Let's see an example of @@TRANCOUNT in a nested transaction.
3. Nested transactions
In this example, we can see a nested transaction. There is a BEGIN TRAN after another BEGIN TRAN, before COMMIT TRAN and ROLLBACK TRAN.
Initially, the @@TRANCOUNT value is 0, as there is no open transaction yet. After that, a BEGIN TRAN statement is executed, incrementing the @@TRANCOUNT value by 1. Then, a DELETE statement deletes every row from the transactions table. After that, another transaction is open, incrementing the value of @@TRANCOUNT by 1. The next statement deletes every row from the accounts table. Then, a COMMIT TRAN statement is executed, decrementing the value of @@TRANCOUNT by 1. If the value of @@TRANCOUNT is 1 before executing the COMMIT TRAN statement, the current transaction is closed, and the changes are confirmed. However, if its value is greater than 1, like in this example where it is 2, the COMMIT TRAN statement decrements @@TRANCOUNT by 1 but doesn't commit the deletion of the table. The transaction is still active. Finally, ROLLBACK TRAN decreases the value of @@TRANCOUNT to 0 and reverts all the uncommitted statements, including the deletion of the accounts table.
4. Nested transactions
Let's check the content of transactions and accounts. We see that the rows of these tables were not deleted.
5. Nested transactions
Let's change the previous example using COMMIT TRAN instead of ROLLBACK TRAN in the penultimate statement.
The last COMMIT TRAN statement will be executed when the @@TRANCOUNT value is 1, so it will commit the deletion of the tables.
6. Nested transactions
If we check the content of transactions and accounts, we can see that the rows of these tables were deleted.
7. @@TRANCOUNT in a TRY...CATCH construct
We can use @@TRANCOUNT in a TRY...CATCH construct to determine whether any transactions are still open, by checking if the @@TRANCOUNT value is greater than 0. If there are any open transactions, we can commit or rollback the changes.
8. Savepoints
Let's study savepoints.
Savepoints are markers we can set within a transaction. They allow us to rollback to the different savepoints we set.
Savepoints start with the words SAVE TRAN or TRANSACTION followed by the name.
9. Savepoints
Let's see an example that uses savepoints.
This code opens a new transaction, and after that, marks the savepoint1. Then, the data of a new customer is inserted. Next, another savepoint, savepoint2, is marked, and the data of another customer is inserted. After that, ROLLBACK TRAN savepoint2 rollbacks the changes between the declaration of savepoint2 and the rollback of savepoint2. In other words, ROLLBACK TRAN rollbacks the insertion of the customer 'Zack Roberts'. ROLLBACK TRAN savepoint1, rollbacks the changes between savepoint1 and savepoint2, it is the data of 'Mark Davis'. Then, a new savepoint, savepoint3 is marked. Then the code inserts the data of another customer. Finally, the COMMIT TRAN statement is executed.
If we execute this code, we can see that only the last insert statement took place, because savepoint1 and savepoint2 were rolled back.
10. Savepoints
If we select the value of @@TRANCOUNT after every savepoint rollback, we can see that these statements don't decrease the value of @@TRANCOUNT. In this example, we see that the outputs of both are 1.
11. Let's practice!
Let's practice!