July 26, 2011 at 9:47 am
Please see the attached deadlock graph from SQL 2008 -- I have tried interpreting this but I am relatively new at it.
It seems that both statements are trying to update different rows in the same table so I am not really sure what the issue was but I did notice that there was no index on the table for the "sessionkey" field. Will adding the index help to resolve the deadlocks? (there are about 25K rows in the table).
Is there anything else I should be doing?
Thanks in advance
Tony
<deadlock-list>
<deadlock>
<victim-list>
<victimProcess id="process47a0988" />
</victim-list>
<process-list>
<process id="process47a0988" taskpriority="0" logused="200" waitresource="RID: 15:1:118809:20" waittime="57195" ownerId="50828074" transactionname="UPDATE" lasttranstarted="2011-07-25T09:31:40.340" XDES="0x7dc89f970" lockMode="U" schedulerid="6" kpid="4276" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-07-25T09:31:40.340" lastbatchcompleted="2011-07-25T09:21:40.340" clientapp="LBMXApplications" hostname="DHWB7V91" hostpid="112" loginname="LBMX" isolationlevel="read committed (2)" xactid="50828074" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="" line="1" stmtstart="26" sqlhandle="0x02000000b371eb23aca414bf7ed181daa0a00931a13edd2a" />
<frame procname="" line="1" sqlhandle="0x02000000a1b5901d0ada7102fe21d39e68b9af39b79d4eba" />
</executionStack>
<inputbuf>
update Config_UserAuditLog set LastAccessed = current_timestamp ,LogoutDate = null where SessionKey = 21670 </inputbuf>
</process>
<process id="process3d93948" taskpriority="0" logused="883576" waitresource="RID: 15:1:118809:17" waittime="13157" ownerId="50600963" transactionname="implicit_transaction" lasttranstarted="2011-07-25T09:19:44.127" XDES="0x7ae5ff260" lockMode="U" schedulerid="3" kpid="4368" status="suspended" spid="298" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-07-25T09:35:31.853" lastbatchcompleted="2011-07-25T09:25:31.840" lastattention="2011-07-25T09:18:06.547" clientapp="LBMXApplications" hostname="DDP6KPD1" hostpid="2140" loginname="LBMX" isolationlevel="read committed (2)" xactid="50600963" currentdb="15" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="" line="1" stmtstart="26" sqlhandle="0x02000000b371eb23aca414bf7ed181daa0a00931a13edd2a" />
<frame procname="" line="1" sqlhandle="0x0200000093ff7e00afa5b79f2ee586570bf65c3431f5be46" />
</executionStack>
<inputbuf>
update Config_UserAuditLog set LastAccessed = current_timestamp ,LogoutDate = null where SessionKey = 21690 </inputbuf>
</process>
</process-list>
<resource-list>
<ridlock fileid="1" pageid="118809" dbid="15" objectname="" id="lock139a3a200" mode="X" associatedObjectId="121910177103872">
<owner-list>
<owner id="process3d93948" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process47a0988" mode="U" requestType="wait" />
</waiter-list>
</ridlock>
<ridlock fileid="1" pageid="118809" dbid="15" objectname="" id="lockb5001800" mode="X" associatedObjectId="121910177103872">
<owner-list>
<owner id="process47a0988" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process3d93948" mode="U" requestType="wait" />
</waiter-list>
</ridlock>
</resource-list>
</deadlock>
</deadlock-list>
July 26, 2011 at 10:56 am
I would definitely recommend an index on session key. Without that, SQL's having to scan the table to find the rows for update.
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
July 26, 2011 at 11:00 am
That is what I thought -- is there a good chance this will resolve the deadlock?
Tony
July 26, 2011 at 11:03 am
I would think so, probably.
If sessionkey is unique, make it a unique index.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply