More About Transactions and Composite Transact-SQL Constructs
(Page 1 of 6 )
Last week, we talked about scripting and began to examine transactions. This week, we finish our discussion of transactions. This article, the third of a three-part series, is excerpted from chapter five of the book
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281).
Named Transactions
Transaction statements can be named. The name must be a valid SQL Server identifier (that is, no more than 128 characters), but SQL Server will read only the first 32 characters:
Begin Tran[saction][transaction_name|@transaction_name_variable] Commit Tran[saction][transaction_name|@transaction_name_variable] Rollback [Tran[saction][transaction_name|@transaction_name_variable]]
I know that this sounds like a perfect tool for resolving some issues with nested transactions. Unfortunately, in nested transactions, only the names of outer transactions are recorded by SQL Server. If you try to roll back any of the inner transactions, errors occur. The following listing is an academic demonstration of such an attempt:
BEGIN TRANSACTION t1
BEGIN TRANSACTION t2
ROLLBACK TRANSACTION t2
ROLLBACK TRANSACTION t1
SQL Server will return an error:
Server: Msg 6401, Level 16, State 1, Line 3 Cannot roll back t2. No transaction or savepoint of that name was found.
TIP
You can see that you need to know the name of the outer transaction that has called all other stored procedures/transactions. This is not a practical requirement, especially when your stored procedure will be called from more than one stored procedure. Therefore, I recommend that you do not use transaction names.
Next: Savepoints >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter five of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
