Nested Transaction, SavePoint and error handling in sql server
In my earlier article, we saw how to us transactions in SQL server. In this article, we will see the use of nested transactions in SQL server. Nested transaction will be useless without error handling and savepoints. So, we would also look at error handling and savepoint in this article.
Why Nested Transactions?
Nested transactions are one of the important features of SQL. As the name suggests, nested transaction means a transaction within transaction. Nested transactions let you split your large database operations into smaller chunk of operations. Operation is issued with BEGIN TRAN command. You can determine the number of active transactions using @@TRANCOUNT automatic variable. 0 means you are at first level. 1 means you are at first nested transactions, 2 mean second nested transactions and so on. Data is not committed till the first outermost transaction is successful. So, even after committing the inner transaction, if the outer transaction fails, then the data committed by the inner transaction will also be rolled back. Rollback operation, on the other hand, works at all level. Irrespective of whether you use rollback at the first level or the 10th level, all the operations will be rolled back.
Count of @@TRANCOUNT automatic variable increases by 1 for every transaction. Commit operation decreases @@TRANCOUNT by one. ROLLBACK reduces the count directly to 0.
Above figure explains how the commit and rollback statement works. Commit only commit the local transactions while rollback undo changes done by all the transactions. Thus nested transactions implicates that data is not committed until the last commit statement is executed. Even if you use ten commit statements in nested transactions, they are of no significance till the last commit is successful.
Savepoint in SQL server
Savepoint is one more important feature of SQL. Using save point, you can rollback portions of nested transactions. Savepoint is like a marker you can return to incase you want to revert the changes done after that point. If is like rolling the transactions up to the point where the data is consistent. Savepoint are declared using via the SAVE TRAN statement. @@TRANCOUNT automatic variable stays unaffected by the use of SAVE TRAN statement.
SAVE TRAN SP2
Error Handling in SQL server
Error handling is also very important in Sql server. Whenever an error occurs in a database operation, best thing is to rollback the entire transactions else if the error is not severe, you can return the control back to the transaction. @@ERROR automatic variable is used to track and implement error handling in SQL. This variable stores the error id of the last error statement executed by the client. This variable stores 0 if no error is encountered. You should check the value of @@ERROR after triggering any statement which you think might result in error. Check the value of @@ERROR immediately after your DML statement. If it is greater than zero than it means some error occurred. @@ERROR value is updated after every successful command. So you need to check the value of @@ERROR immediately after running your DML command.
Following piece of code shows how to use error @@ERROR variable
IF (@@ERROR <> 0) BEGIN
PRINT Some error occurred...'
ROLLBACK TRAN
RETURN 1
END
COMMIT TRAN
Raise your own custom Error
You can also raise your own custom error message using RAISERROR statement. This statement has three parameters.
First is your custom error message with optional format designators.
Second parameter defines the severity of error which ranges from 0 to 25. User can set severity level from 0 to 18. 0 to 10 means a warning whereas 11 to 18 mean errors. Level 19 to 25 are used by only members of sysadmin role. Level 19 to 25 mandates the use of LOG option. Severity levels from 20 to 25 are extremely fatal for database. Fatal error terminates the client connections and logs the error in error log and application log.
Third parameter can be any value between 1 through 127. This parameter is used to pass additional user defined code to be carried by the exception. Normally, it is not required and quite often is set to 1.
RAISERROR ('Errors found, please fix these errors and retry', 1, 2) WITH SETERROR
RETURN
Demo:
In below chunk of code, I begin a transaction by updating a user last name. I check for error. If error then raises custom error else create a savepoint. Then I begin a nested transaction which updates another user last name. Again, I check for error, if error raise it else create second savepoint. Then I rollback to first save point.Now, if I check the state of my userinfo table then I notice that result of my first update statement is persisted while the second update has been rolled back. Next On rolling back the entire transaction, my first update statement is also rolled back.
BEGIN TRAN main
print 'start of Transaction main:'+cast(@@TRANCOUNT as varchar(5))
-- No of Transactions: 1
update UserInfo set LastName='Singh' where UserId=1
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in updating records of UserInfo.', 16, 1)
RETURN
END
SAVE TRAN SP1 -- Create savepoint
print 'After first savepoint:'+cast(@@TRANCOUNT as varchar(5))
-- No of Transactions: 1
BEGIN TRAN first
print 'start of nested first transaction:'+cast(@@TRANCOUNT as varchar(5))
-- No of Transactions: 2
update UserInfo set LastName='Mandal' where UserId=3
IF @@ERROR <> 0
BEGIN
-- Rollback the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error in deleting records of UserInfo.', 14, 1)
RETURN
END
SAVE TRAN SP2 -- Create second savepoint
print