Post reply

Deadlocks while updating rows in table

  • November 28, 2008 at 2:15 pm

    #904982

    From what I can see, this is what happened.

    spid 60, running from Microsoft SQL Server 2005 JDBC Driver in a user-defined transaction takes out an exclusive lock on the table EMPMSQL.SS.EMPLOYEES, index = IDX_EMP, keyhash = 01016c1436f9. It then requests an update lock on the table EMPMSQL.SS.EMPLOYEES, index = IDX_EMP, keyhash = f400557bd7f9, and has to wait.

    spid 70, unknown client, is in an implicit transaction and takes out an exclusive lock on the table EMPMSQL.SS.EMPLOYEES, index = IDX_EMP, keyhash = f400557bd7f9. It then trequests an update lock on vthe table EMPMSQL.SS.EMPLOYEES, index = IDX_EMP, keyhash = 01016c1436f9 and has to wait.

    It's now holding a resource that the other process wants and wanting a resource the other process has, resulting in a deadlock.

    While the Sections table appears in the input buffer, there's no reference to it at all in the resource list, which is a list of all resources involved in the deadlock. It looks like the only table that's actually involved in the deadlock is the employees table.

    Two things to check. (Do you have access to the source code of the java app?)

    Firstly, is there a trigger on the Sections table and, if so, what does it do?

    Second, can you investigate through the client app and see where the user-defined transaction begins (and where that implicit transaction begins) and what tables are modified before the app gets to the point of updating the sections table

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 28, 2008 at 2:29 pm

    #904989

    Adi Cohn (11/28/2008)

    Might be wrong here but it seems that those 2 statements caused the deadlock:

    UPDATE SS.SECTIONS WITH(ROWLOCK) SET SECTNO=160 WHERE ROOMNO=401

    UPDATE SS.SECTIONS WITH(ROWLOCK) SET SECTNO=101 WHERE ROOMNO=601

    If you look at the resource list (the last part of the deadlock graph printed by 1222), all of the resources involved in the deadlock are listed, along with who owns the locks and who wants the locks.

    resource-list

    keylock hobtid=72057594044547072 dbid=20 objectname=EMPMSQL.SS.EMPLOYEES

    indexname=IDX_EMP id=lock3688cc0 mode=X associatedObjectId=72057594044547072

    owner-list

    owner id=process73b978 mode=X

    waiter-list

    waiter id=process73ba68 mode=U requestType=wait

    keylock hobtid=hobtid=72057594044547072 dbid=20 objectname=EMPMSQL.SS.EMPLOYEES

    indexname=IDX_EMP id=lock3689480 mode=X associatedObjectId=72057594044547072

    owner-list

    owner id=process73ba68 mode=X

    waiter-list

    waiter id=process73b978 mode=U requestType=wait

    The only resources are key locks on the employees table.

    The resource section of the 1204 deadlock graph confirms that.

    KEY: 20:hobtid=72057594044547072 (01016c1436f9) CleanCnt:3 Mode:X Flags: 0x0

    KEY: 20:hobtid=72057594044547072 (f400557bd7f9) CleanCnt:2 Mode:X Flags: 0x0

    The hobitID (as given in the key) matches to the one listed in the resource list, ie the employees table

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 28, 2008 at 4:46 pm

    #905009

    TNV (11/26/2008)

    Hi Kumar,

    Basically I am new to this SQL Server stuff...

    I am using MSSQL Server 2005 Express. I turned on trace by using DBCC TRACEON (1024). But in the errorlog, I couldn't see any info on deadlocks.

    And where can I find the SQL Profiler. I couldn't see any SQL Profiler in the Installation folders.

    Thanks,

    TNV

    If memory serves correctly... that's the wrong trace for deadlock logging. I believe that trace flag 1204 is the correct trace.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 28, 2008 at 4:48 pm

    #905010

    Sorry... didn't see Gail's post on 1222...

    I also share her concern on setting a deadlock priority... it's like asking a Mother which baby someone can kill. The best thing to do is to find the cause and fix it. I'll look back through this thread to find some code, but the initial post has no chance of being resolved without seeing the actual code that does the update.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 16 through 18 (of 18 total)

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