February 29, 2012 at 1:25 am
Hi,
I'm getting deadlock and using the profiler it is giving me information on where the dead lock is happening. What I'm keen to understand is what the following means?
waitresource="KEY: 5:72057594047561728 (d100894c8261)"
I'm sure the 5 is the basebase ID.
With the 72057594047561728 I believe this to be a pointer to a table in my example:
SELECT OBJECT_SCHEMA_NAME([object_id]),OBJECT_NAME([object_id])
FROM sys.partitions
WHERE partition_id = 72057594047561728;
The latter outputs a table name. Note the table concerned has a clustered index and other non clustered indexes.
However what does KEY mean?
What does the d100894c8261 mean?
And what level of lock is attempting to be acquired (i.e. row, page, table)?
The deadlock details are below:
- <EVENT_INSTANCE>
<EventType>DEADLOCK_GRAPH</EventType>
<PostTime>2012-02-29T07:37:28.350</PostTime>
<SPID>20</SPID>
- <TextData>
- <deadlock-list>
- <deadlock victim="processad0868">
- <process-list>
- <process id="processabb588" taskpriority="0" logused="740" waitresource="KEY: 5:72057594047561728 (d100894c8261)" waittime="3447" ownerId="26188186" transactionname="user_transaction" lasttranstarted="2012-02-29T07:37:24.857" XDES="0x9939b280" lockMode="U" schedulerid="3" kpid="59140" status="suspended" spid="69" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-02-29T07:37:24.857" lastbatchcompleted="2012-02-29T07:37:24.857" clientapp=".Net SqlClient Data Provider" hostname="01125DEVCON2" hostpid="15084" loginname="HEADOFFICE\Davids" isolationlevel="read committed (2)" xactid="26188186" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
- <executionStack>
<frame procname="DataStore.dbo.uspDSNET_DIM_FileInfo_Add" line="125" stmtstart="11052" stmtend="11804" sqlhandle="0x03000500b00df52234a5770006a000000100000000000000">UPDATE dbo.[tbDSNET_DIM_File_IndexValues_DataState] SET [ActionRequired] = 1 /*Purge (delete) data.*/, [IsDataSearchable] = 0, [ActionInvokedBy] = @ActionInvokedBy WHERE [DataItemType] = 3 /*file*/ AND [idDataItem] = @idFile AND [ActionRequired] IN ( 2/*off line, injection in progress*/, 3 /*no further action*/) ;</frame>
</executionStack>
<inputbuf>Proc [Database Id = 5 Object Id = 586485168]</inputbuf>
</process>
- <process id="processad0868" taskpriority="0" logused="740" waitresource="KEY: 5:72057594047561728 (d2006a4b0def)" waittime="3447" ownerId="26188188" transactionname="user_transaction" lasttranstarted="2012-02-29T07:37:24.857" XDES="0xa3e0c9a0" lockMode="U" schedulerid="4" kpid="10676" status="suspended" spid="68" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2012-02-29T07:37:24.857" lastbatchcompleted="2012-02-29T07:37:24.857" clientapp=".Net SqlClient Data Provider" hostname="01125DEVCON2" hostpid="15084" loginname="HEADOFFICE\Davids" isolationlevel="read committed (2)" xactid="26188188" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
- <executionStack>
<frame procname="DataStore.dbo.uspDSNET_DIM_FileInfo_Add" line="125" stmtstart="11052" stmtend="11804" sqlhandle="0x03000500b00df52234a5770006a000000100000000000000">UPDATE dbo.[tbDSNET_DIM_File_IndexValues_DataState] SET [ActionRequired] = 1 /*Purge (delete) data.*/, [IsDataSearchable] = 0, [ActionInvokedBy] = @ActionInvokedBy WHERE [DataItemType] = 3 /*file*/ AND [idDataItem] = @idFile AND [ActionRequired] IN ( 2/*off line, injection in progress*/, 3 /*no further action*/) ;</frame>
</executionStack>
<inputbuf>Proc [Database Id = 5 Object Id = 586485168]</inputbuf>
</process>
</process-list>
- <resource-list>
- <keylock hobtid="72057594047561728" dbid="5" objectname="DataStore.dbo.tbDSNET_DIM_File_IndexValues_DataState" indexname="PK_tbDSNET_DIM_File_IndexValues_DataState" id="locka6ba6800" mode="X" associatedObjectId="72057594047561728">
- <owner-list>
<owner id="processad0868" mode="X" />
</owner-list>
- <waiter-list>
<waiter id="processabb588" mode="U" requestType="wait" />
</waiter-list>
</keylock>
- <keylock hobtid="72057594047561728" dbid="5" objectname="DataStore.dbo.tbDSNET_DIM_File_IndexValues_DataState" indexname="PK_tbDSNET_DIM_File_IndexValues_DataState" id="locka964da00" mode="X" associatedObjectId="72057594047561728">
- <owner-list>
<owner id="processabb588" mode="X" />
</owner-list>
- <waiter-list>
<waiter id="processad0868" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
<TransactionID />
<LoginName>sa</LoginName>
<StartTime>2012-02-29T07:37:28.310</StartTime>
<ServerName>01125DEVCON2</ServerName>
<LoginSid>AQ==</LoginSid>
<EventSequence>1847556</EventSequence>
<IsSystem>1</IsSystem>
<SessionLoginName />
</EVENT_INSTANCE>
All help very appreciated.
Thanks
David.
February 29, 2012 at 1:51 am
KEY is the type of lock being taken, it's a key lock, so a lock on an index row
5 is the database id
72057594047561728 is the partition id, a pointer to an index on a table. Modify your query as such:
SELECT OBJECT_SCHEMA_NAME(p.object_id),OBJECT_NAME(p.object_id), i.name
FROM sys.partitions p inner join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
WHERE partition_id = 72057594047561728;
d100894c8261 is the hash of the key value being locked. Not much use, you can figure out which row it refers to, but there's usually little reason
To figure out why its deadlocking I'd need to see the definition of DataStore.dbo.uspDSNET_DIM_FileInfo_Add and the structure and indexes on DataStore.dbo.tbDSNET_DIM_File_IndexValues_DataState
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
February 29, 2012 at 2:57 am
Hi Gail Shaw and thanks for the information.
I've executed your SQL statement and this shows that against table tbDSNET_DIM_File_IndexValues_DataState the index PK_tbDSNET_DIM_File_IndexValues_DataState is being used. This is also confirmed in the deadlock graph above.
The deadlock graph above shows that both processes involved in the deadlock, is that with each process it is the same stored procedure and SQL statement. However what I don't understand is that the rows that require locking "should" be different, so since key row locks are being performed no contention should exist! But the reality is different.
With the structures, I'll post them next.
Note I can reproduce the deadlock, so if required I can collect other additional information that may help.
Thanks
David.
February 29, 2012 at 2:58 am
STORED PROCEDURE [dbo].[uspDSNET_DIM_FileInfo_Add]
February 29, 2012 at 2:59 am
TABLE [dbo].[tbDSNET_DIM_File_IndexValues_DataState]
February 29, 2012 at 3:01 am
Indexes against table [dbo].[tbDSNET_DIM_File_IndexValues_DataState]
February 29, 2012 at 6:00 am
I think I can resolve the deadlock issue by adding an index.
As shown in the above deadlock graph, the deadlock occurs against two processes, whilst each process is performing the following SQL:
UPDATE dbo.[tbDSNET_DIM_File_IndexValues_DataState]
SET [ActionRequired] = 1
[IsDataSearchable] = 0,
[ActionInvokedBy] = @ActionInvokedBy
WHERE [DataItemType] = 3
AND [idDataItem] = @idFile
AND [ActionRequired] IN ( 2, 3)
With the above SQL no index exists that completely covers the where SQL statement conditions; at the moment.
If I add a new index that covers all the SQL where conditions; the deadlock issue disappears (so far).
With no covering index, an index seek is performed against a non-clustered index and then a lookup is performed against the clustered index; within a nested loop. This I believe should not cause an issue as the rows being used in both indexes should not be locked by other process.
Can anyone please explain why adding a index that covers the WHERE conditions resolves this?
Thanks
February 29, 2012 at 6:11 am
Yup, that's pretty much what I would have suggested.
Appropriate index means less reads so less locks and less duration to the locks to be held.
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
February 29, 2012 at 9:56 am
The deadlock issue is resolved:-).
The issue of deadlock has been resolved by looking at not only the deadlock trace, but also the actual execution plan.
The deadlock trace pointed the direction of the SQL statements of where the deadlock was occurring; however it was only by reviewing the execution plan of the SQL statement(s) that the deadlock reason was discovered.
Reviewing the SQL UPDATE statement execution plan, showed that the UPDATE statement was correctly broken down into various steps; however one of the steps was an index scan! A suitable index does exist, however I think that because so little data is in the table, SQL Server decided a table scan would be better!
Deadlock was occurring because two different processes were correctly holding on to exclusive row locks against different rows in the index as part of a previous operation within their respective transactions, and then later within their respective transactions they both attempted an index scan as part of the SQL UPDATE statement. The latter index scans resulted in deadlock as each process required the other process to unlock the locked row(s).
To resolve the issue, the SQL UPDATE statement was changed to have a query hint that specified a certain index should be used. This resulted in the SQL UPDATE statement having a different execution plan in which an index scan was not done, but instead a seek. The seek avoided the deadlock as the seek only required access to the rows that the process is using.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply