Microsoft® SQL Server® 2012 Bible

1063

Chapter 47: Managing Transactions, Locking, and Blocking

47

Transaction 1 now has an exclusive lock on BusinessEntityID 101. Executing Step 2 in

the second window:

USE AdventureWorks2012

BEGIN TRANSACTION

--Step 2

UPDATE Production.Product

SET Name = 'DeadLock Repair Kit'

WHERE ProductNumber = 'FR-R38R-44'

UPDATE Person.Person

SET FirstName = 'Tim'

WHERE BusinessEntityID = 101

COMMIT TRANSACTION

Transaction 2 gains an exclusive lock on ProductNumber “FR-R38R-44” and then tries

to grab an exclusive lock on BusinessEntityID 101 , but transaction 1 already has it

locked.

It’s not a deadlock yet, because although transaction 2 waits for transaction 1, transac-

tion 1 is not waiting for transaction 2. At this point, if transaction 1 fi nished its work and

issued a commit transaction, the data resource would be freed; transaction 2 could get

its lock on the contact row and be on its way as well.

The trouble begins when transaction 1 tries to update ProductNumber “FR-R38R-44.” It

can’t get an exclusive lock because transaction 2 already has an exclusive lock. So when

this code is executed

-- Transaction 1

-- Step 3

UPDATE Production.Product

SET Name = 'DeadLock Identification Tester'

WHERE ProductNumber = 'FR-R38R-44'

Transaction 1 returns the following friendly error message in a few seconds:

Msg 1205, Level 13, State 51, Line 2

Transaction (Process ID 52) was deadlocked on lock resources with

another process and has been chosen as the deadlock victim.

Rerun the transaction.

The deadlock can also be viewed using SQL Server Profi ler (as shown in Figure 47-6).

Transaction 2 completes as if there’s no problem.

c47.indd 1063c47.indd 1063 7/31/2012 10:23:29 AM7/31/2012 10:23:29 AM

http://www.it-ebooks.info