Microsoft® SQL Server® 2012 Bible

1053

Chapter 47: Managing Transactions, Locking, and Blocking

47

3

0

If the code might have nested transactions, it’s a good idea to examine @@TranCount (or

XactState()) because attempting to commit or rollback a transaction if no pending

transactions exist can raise a 3902 or 3903 error with a 16 severity code to the client.

Implicit Transactions

Although SQL Server requires an explicit begin transaction to initiate a logical trans-

action, this behavior can be modifi ed so that every DML statement begins a transaction

if one is not already started (so you don’t end up with lots of nested transactions). This

means that after a SQL DML command is issued, a commit or rollbackis required.

To demo implicit transactions, the following code alone will not commit the update:

USE AdventureWorks2012

SET Implicit_Transactions ON;

UPDATE HumanResources.Department

SET Name = 'Department of Redundant Departments'

WHERE DepartmentID = 2;

Viewing the @@TranCount global variable does indeed show that there’s one pending

transaction level awaiting a commit or rollback:

SELECT @@TRANCOUNT;

Result:

1

Adding a commit transaction to the end of the batch commits the transaction, and the

update is fi nalized:

COMMIT TRANSACTION;

Multiple DML commands or batches occur within a single logical transaction, so it doesn’t

create a bunch of nested transactions.

Turning off implicit transactions affects only future batches. It does not commit any pending transactions. Be mindful

if implicit transactions are turned on because if you do not explicitly commit the transaction, it remains open, poten-

tially blocking other operations.

SET Implicit_Transactions OFF;

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

http://www.it-ebooks.info