Post reply

Deadlock on two update statments

  • 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

  • April 3, 2008 at 10:49 am

    #797953

    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.

  • April 3, 2008 at 11:57 am

    #797995

    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".

  • April 3, 2008 at 12:04 pm

    #797998

    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.

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

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