Handling SQL Server Errors in Nested Procedures : Page 2
Transact-SQL error handling can become very complex when you have to trap errors in nested stored procedures that use transactions. The complexity arises from restrictions on how stored procedures can use transactions and from the asymmetry between the Transact-SQL COMMIT and ROLLBACK statements.
by Ron Talmage
Jun 20, 2003
Page 2 of 3
If you encapsulate any of your operations in database transactions, some errors will abort a transaction while others will not. When working with transactions, consider the following questions:
Does the error abort the transaction?
What type of transaction is it?
Is the XACT_ABORT setting on?
When SQL Server aborts a transaction, it also aborts the current and calling batches or stored procedures. SQL Server has three types of transactions: Autocommit, Explicit, and Implicit.
Autocommit: All data-modification statements such as INSERT, UPDATE, and DELETE occur in a transaction. If you do not explicitly declare a transaction, or if you use an implicit transaction, SQL Server automatically uses a transaction for those commands. That is the autocommit mode. SQL Server ensures these data-modification commands either completely succeed or completely fail. If you update a million rows, and SQL Server cannot complete the update, it will not leave the database only partially updated. This keeps the database in a consistent state and assures the atomicity of the transaction. That is, all the steps of a transaction as a group must complete, or everything gets rolled back.
The number of possible error messages is very large; over 3,800 error messages are stored in the master database's sysmessages system table. Unfortunately, only a small number of the error messages are documented in Books Online.
Explicit: You can explicitly begin a Transact-SQL transaction with BEGIN TRANSACTION, optionally label it, and end the transaction with either COMMIT TRANSACTION or ROLLBACK TRANSACTIONbut not both. A COMMIT statement instructs SQL Server to commit your changes, while a ROLLBACK statement results in all changes being removed. SQL Server can do this because it first writes all data changes to the transaction log before it changes any actual database data. If SQL Server needs to restore any data to its original state because of an error or a ROLLBACK, it can recover that data from the transaction log.
When you explicitly begin a transaction, the @@TRANCOUNT system function count increases from 0 to 1; when you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0. As you see, the behavior of COMMIT and ROLLBACK is not symmetric. If you nest transactions, COMMIT always decreases the nesting level by one, as you can see illustrated in Figure 1. The ROLLBACK command, on the other hand, rolls back the entire transaction, illustrated in Figure 2. This asymmetry between COMMIT and ROLLBACK is the key to handling errors in nested transactions.