Here is another question I got in this week. The question is all about rolling back transactions, whether we need to write a code with ROLLBACK TRAN for rolling back or can SQL Server automatically rollback the transaction if XACT_ABORT is on.
Many believe that once the transaction is handled using TRY/CATCH block, rolling back is guaranteed when an error or unexpected issue is thrown. Most of the cases, this is true, when something goes wrong, execution jumps to Catch block and execute the rollback tran statement. But what if execution is unable to jump to Catch block? Can it happen? Yes, it is possible. Assume that logic is implemented with a stored procedure and it is executed by an application. While it is being executed, if something happens at the application-end, application disconnects from the database. This stops continuing the code and executing the rollback tran. Not only that, this results the transaction remain in the database, keeping all the locked placed without releasing.
This type of issues can be solved using XACT_ABORT statement. All we have to do is, add SET XACT_ABORT ON inside the stored procedure because XACT_ABORT makes sure all transactions are rolled back and the batch is aborted when an error occured. For more info, read : https://msdn.microsoft.com/en-us/library/ms188792.aspx
Does this mean that we do not need to have TRY/CATCH block and handling errors? No, we still need them because it is the way of capturing errors and handling them.
Here is an example. This creates a table and stored procedure for inserting records.
Code 1
USE tempdb; GO DROP TABLE dbo.Account CREATE TABLE dbo.Account ( AccountId INT NOT NULL PRIMARY KEY, Name NVARCHAR (50) NOT NULL, Balance Money NOT NULL ) GO CREATE PROCEDURE dbo.InsertAccount AS BEGIN BEGIN TRY BEGIN TRAN INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000) -- This throws an error INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Duplicate', 10000) INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000) COMMIT TRAN END TRY BEGIN CATCH SELECT ERROR_MESSAGE() ROLLBACK TRAN END CATCH END GO
Now let's open a new connection and execute the stored procedure.
Code 2
USE tempdb; GO EXEC dbo.InsertAccount; SELECT * FROM dbo.Account;
Here is the result.
As expected it throws an error. if you check for active transactions using below code, you should NOT see the transaction we started as it has been handled.
Code 3
Now let's make a small modification to the stored procedure and execute the code in a different way. Insert WAITFOR statement in between first and second INSERT statements like below and alter the procedure.
Code 4
Now open a new connection and execute Code 2 again. Open another connection immediately and execute Code 3 too. You should see following record with Code 3.
Go back to Code 2 immediately and stop execution. You need make sure you do this within 20 seconds. Once you have stopped the code, go back to Code 3 and execute it again. You should see the transaction which is still exist.
Same can happen with an application too. We stopped the application but still the transaction is exist with the database, locking some resources. Let's make another modification to the stored procedure and test the same. Add following code to the procedure and recreate it.
Code 5
Now do the same thing, open a new connection, execute the Code 2, and with a different connection, execute the Code 3, go back to Code 2, stop it and execute Code 3 again. You should NOT see the transaction now because it has been taken care by XACT_ABORT.
Code 3
SELECT est.transaction_id as TransactionID, tas.name as [Transaction Name], DB_NAME(tds.database_id) as DatabaseName FROM sys.dm_tran_active_transactions tas INNER JOIN sys.dm_tran_database_transactions tds ON tas.transaction_id = tds.transaction_id INNER JOIN sys.dm_tran_session_transactions est ON est.transaction_id=tas.transaction_id WHERE est.is_user_transaction = 1 AND tas.transaction_state = 2 AND tds.database_transaction_begin_time IS NOT NULL
Now let's make a small modification to the stored procedure and execute the code in a different way. Insert WAITFOR statement in between first and second INSERT statements like below and alter the procedure.
Code 4
INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000) WAITFOR DELAY '00:00:20'; INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Duplicate', 10000) INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000)
Now open a new connection and execute Code 2 again. Open another connection immediately and execute Code 3 too. You should see following record with Code 3.
Go back to Code 2 immediately and stop execution. You need make sure you do this within 20 seconds. Once you have stopped the code, go back to Code 3 and execute it again. You should see the transaction which is still exist.
Same can happen with an application too. We stopped the application but still the transaction is exist with the database, locking some resources. Let's make another modification to the stored procedure and test the same. Add following code to the procedure and recreate it.
Code 5
ALTER PROCEDURE dbo.InsertAccount AS BEGIN SET XACT_ABORT ON BEGIN TRY BEGIN TRAN
Now do the same thing, open a new connection, execute the Code 2, and with a different connection, execute the Code 3, go back to Code 2, stop it and execute Code 3 again. You should NOT see the transaction now because it has been taken care by XACT_ABORT.
No comments:
Post a Comment