Deadlocks while updating rows in table

Question

Post reply

Deadlocks while updating rows in table

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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)
Intro to Tally Tables and Functions