- Transaction Properties
- Transaction Types
- Transaction Syntax
- Transaction Tricks
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Transaction Tricks
As you can tell from the syntax, the transaction names are optional. On the other hand, naming your transactions does help make the code easier to read, and more self-documenting. Transaction names are particularly helpful if you use nested transactions inside your stored procedures.
Be sure to pair up all of your BEGIN TRANSACTION statements with COMMIT and ROLLBACK statements. SQL Server will let you compile a stored procedure without corresponding BEGIN ... COMMIT statements, but the code will break at runtime.
There are a couple of "gotchas" with the transaction syntax that are bound to get you in trouble if you're not careful. The trouble stems from the fact that COMMIT and ROLLBACK don't behave as you'd expect from the first glance.
Although you can specify the transaction names with ROLLBACK, this command really couldn't care less—it rolls back all open transactions for your connection, unless you specify a savepoint. ROLLBACK works fine if you specify the name of the outermost transaction with it. If you specify the name of any other transaction, ROLLBACK will fail. For instance, examine the results of the following sample query:
SET NOCOUNT ON
BEGIN TRANSACTION add_50_percent_to_qty
UPDATE sales
SET Qty = Qty * 1.5
WHERE Qty < 20
BEGIN TRAN net_60_for_less_than_20
UPDATE sales
SET payterms = 'NET 60'
WHERE qty < 20
ROLLBACK TRANSACTION net_60_for_less_than_20
Results:
Server: Msg 6401, Level 16, State 1, Line 12 Cannot roll back net_60_for_less_than_20. No transaction or savepoint of that name was found.
The same set of statements with "ROLLBACK add_50_percent_to_qty" or simply ROLLBACK works fine—it rolls back both transactions.
The COMMIT command also doesn't behave as you'd expect: It commits only the innermost transaction, leaving the rest of the transactions in force. To avoid such pitfalls, you should check for the number of open transactions after using COMMIT or ROLLBACK statements. This is accomplished by examining the value of the @@TRANCOUNT global variable. If @@TRANCOUNT shows anything other than zero after your program's execution, then you're in trouble. Consider the following example:
SET NOCOUNT ON
BEGIN TRANSACTION add_50_percent_to_qty
UPDATE sales
SET Qty = Qty * 1.5
WHERE Qty < 20
BEGIN TRAN net_60_for_less_than_20
UPDATE sales
SET payterms = 'NET 60'
WHERE qty < 20
COMMIT TRANSACTION net_60_for_less_than_20
This code runs without generating any errors, but checking the global variable @@TRANCOUNT returns 1, which means that there is one open transaction on the current connection.
Unlike ROLLBACK, COMMIT takes care of only one open transaction at a time. If we execute the code snippet above and examine the current activity window from SQL Server Enterprise Manager, we'll see that our connection has acquired a number of exclusive locks. We'll have to execute another COMMIT or ROLLBACK statement to release these locks.
In general, try to avoid nested transactions, if possible. If you must use nested transactions, then be sure to check @@TRANCOUNT to ensure all transactions are either committed or rolled back.
Error checking is important, regardless of whether you use transactions or not. However, if you're using transactions you're also potentially acquiring and releasing exclusive locks, so checking @@ERROR value is a must. SQL Server will return an error if one occurs within a transaction, but it won't clean up the open transactions for you. That's why you should check the value of @@ERROR after each DML statement and roll back the transaction if @@ERROR is other than zero.