1051
Chapter 47: Managing Transactions, Locking, and Blocking
47
COMMIT TRANSACTION;
END TRY
BEGIN CATCH;
ROLLBACK TRANSACTION;
RAISERROR('Inventory Transaction Error', 16, 1);
RETURN;
END CATCH;If you’re not familiar with Try...Catch, the improved error handling code introduced in SQL Server
2005, it’s covered in Chapter 16, “Programming with TSQL.”If all goes as expected, both updates are executed, the transaction is committed, and the try
block completes execution. However, if either update operation fails, execution immediately
transfers down to the catch block, the commit is never executed, and the catch block’s
rollback transaction will undo any work that had been done within the transaction.Xact_State()
Every user connection is in one of three possible transaction states, which may be queried
using the Xact_State() function, introduced in SQL Server 2005:■ (^1) : Active, healthy transaction.
■ 0 : No transaction.
■ (^) –1: Uncommittable transaction: You can begin a transaction, experience an error,
and not commit that transaction. (Consider the consistency part of ACID.) In prior
versions of SQL server, these were called doomed transactions.
Typically, the error handling catch block tests the XactState() function to see if the
transaction can be committed or must be rolled back. The next catch block checks Xact
State() and decides if it can commit or rollback the transaction (using the catch block
from the previous example):
BEGIN CATCH
IF Xact_State() = 1 –- there's an active committable transaction
COMMIT TRAN;
IF Xact_State() = -1 –- there's an uncommittable transaction
BEGIN
ROLLBACK TRANSACTION;
RAISERROR('Inventory Transaction Error', 16, 1);
END
END CATCH;
Although the XactState() function is normally used within the error handling catch
block, it’s not restricted to the catch block and may be called at any time to see if the code
is in a transaction.
c47.indd 1051c47.indd 1051 7/31/2012 10:23:27 AM7/31/2012 10:23:27 AM
http://www.it-ebooks.info