Deadlock on two update statments

Question

Deadlock on two update statments

bob mazzo

SSCommitted

Points: 1684
More actions
April 3, 2008 at 10:13 am

#188689

Here are the two statements that the Sql 05 Tracer reported a deadlock on.
Why am I deadlocked on two different records ? Please see that the pkey values are different.
Update ORDER_FX SET update_by = 'JB', update_date = '04/02/2008 03:55:21 PM', BEEP_COUNT = 3.00000000
WHERE PKey = 183112
Update ORDER_FX SET update_by = 'JT', update_date = '04/02/2008 03:55:21 PM', STATUS = 'G' , COLD = ' ', DISP_FIRST = ' ', ORD_STAT = ' ', BEEP_COUNT = 0.00000000
WHERE PKey = 183206
Is this a case of table lock escalation ?
Thanks,
Bob

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

In order to achieve a deadlock, you must have at least two locks taken on one of the connections. Lock escalation cannot do this - if that were the case it would have simply allowed the first connection to complete it's action first.

You either have other statements in your transactions, you are updating a view, or you have a trigger on the table you are updating.

bob mazzo SSCommitted Points: 1684 More actions · Answer 2

Bingo! I have a trigger on that Order_fx table.

But I don't understand your comment "...two locks taken on one of the connections".

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

To get a deadlock, you have to have a connection lock at least two resources in a single transaction. It is mostly a timing issue.

Here is a pretty typical example:

2 Connections A and B

2 Records R1 and R2 (they could be in the same or different tables)

A takes a shared lock on R1

B takes a shared lock on R2

A takes an exclusive lock on R2 - so not it has to wait for B to release it's lock

B takes an exclusive lock on R1 - so it now has to wait for A to release it's lock

This is now a deadlock because neither can finish without the other completing. This situation is impossible if you have not locked at least two resources with one of the connections because you must take a lock and be waiting on another lock. Lock escalation would take a table or page lock all at once, so you cannot get this just from simple lock escalation with two connections updating a single record in the same table.