Microsoft® SQL Server® 2012 Bible

1065

Chapter 47: Managing Transactions, Locking, and Blocking

47

SET @retry = 1

WHILE @retry = 1

BEGIN

BEGIN TRY

SET @retry = 0

BEGIN TRANSACTION

UPDATE HumanResources.Department

SET Name = 'Accounting'

WHERE DepartmentID = 2;

UPDATE HumanResources.Department

SET Name = 'Development'

WHERE DepartmentID = 1;

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF ERROR_NUMBER() = 1205

BEGIN

PRINT ERROR_MESSAGE()

SET @retry = 1

END

ROLLBACK TRANSACTION

END CATCH

END

Instead of letting SQL Server decide which transaction is the “deadlock victim,” you can

take control and assign priorities to your T-SQL code. The transaction with the lowest dead-

lock priority will be rolled back fi rst. The priorities with the same priority fall back to the

rollback cost to determine which to roll back. To set the priority, issue the following com-

mand before the beginning of the transaction:

SET DEADLOCK_PRIORITY LOW

The setting actually allows for a range of values from –10 to 10, or normal (0), low (–5),

and high (5).

The System_Health Session

Starting in SQL Server 2008, an Extended Event session named the system_health session captures

important diagnostic information for a given SQL Server instance, which includes deadlock informa-

tion. Extended Events is greatly enhanced for SQL Server 2012. See Chapter 40, “Extended Events,”

for more information about the system_health session.

c47.indd 1065c47.indd 1065 7/31/2012 10:23:30 AM7/31/2012 10:23:30 AM

http://www.it-ebooks.info