Microsoft® SQL Server® 2012 Bible

1052

Part VIII: Performance Tuning and Optimization

Xact_Abort

A common SQL Server myth is that all error conditions roll back a transaction. Unless

there’s a try-catch error handling in place, a runtime error aborts only the statement caus-

ing the error. The batch continues and the transaction is completed even though an error

occurred.

Turning on Xact_Abort solves some of these problems by ensuring that any runtime level

error fails the entire batch and not just the statement that failed. So, Xact_Abort is a

good thing and should often be set in code. Xact_Abort also triggers the try-catch code

and sends execution into the catch block.

Nested Transactions

Nested transactions are a bit of a misnomer. Nested transactions behave as one large trans-

action: Changes made in one transaction can be read in a nested transaction, They do not

behave as isolated transactions where actions of the nested transaction can be committed

independently of a parent transaction.

When transactions are nested, a commit only marks the current nested transaction level as

complete. However, and this is the important part, a Rollback at any level of nesting rolls

back all pending and committed transactions that occurred inside the outermost transac-

tion. The @@TranCount statement indicates the current nesting level. A commit when the

trancount > 1 has no effect except to reduce trancount by 1. Only when the trancount is 1

are the actions within all levels of the nested transaction committed to disk. To prove this

behavior, the next code sample examines the @@TranCount global variable, which returns

the current transaction nesting level:

SELECT @@TRANCOUNT; -- 0

BEGIN TRAN;

SELECT @@TRANCOUNT; -- 1

BEGIN TRAN;

SELECT @@TRANCOUNT; -- 2

BEGIN TRAN;

SELECT @@TRANCOUNT; -- 3

ROLLBACK; -- undoes all nested transactions

SELECT @@TRANCOUNT; -- 0

Results:

0

1

2

c47.indd 1052c47.indd 1052 7/31/2012 10:23:27 AM7/31/2012 10:23:27 AM

http://www.it-ebooks.info