October 4, 2014 at 4:15 am
I'm getting deadlocks on the following Select statements which use an UPDLOCK hint, this is a third party application so I don't have control over the SQL.
SELECT "timestamp","ID","Scan Result ID","Text","Style Code","Element Type","Element Part","Address","New Line After","Created By","Created Date Time","Modified By","Modified Date Time"
FROM "XXXX_TEST"."dbo"."XXX$NC Scan Result Element" WITH(UPDLOCK) WHERE ("Scan Result ID"=@1)
ORDER BY "ID" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)
I've attached the dead lock graph to this post.
Primary Key is on the ID field,
And a unique nonclustered index is on (Scan Result ID and ID)
CREATE UNIQUE NONCLUSTERED INDEX [IX_XXXXX]
(
[Scan Result ID] ASC,
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
If I removed page locks on this index do you think it may help with the Deadlocks ? and just left Row locks on?
Here is the deadlock XML
<deadlock>
<victim-list>
<victimProcess id="process7d44c7498" />
</victim-list>
<process-list>
<process id="process7d44c7498" taskpriority="0" logused="2820" waitresource="KEY: 6:72057601762525184 (fa2a0c4b4768)" waittime="622" ownerId="3500501" transactionname="user_transaction" lasttranstarted="2014-10-03T18:20:34.713" XDES="0x8329ccd28" lockMode="U" schedulerid="4" kpid="4212" status="suspended" spid="84" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-10-03T18:20:34.733" lastbatchcompleted="2014-10-03T18:20:34.733" lastattention="2014-10-03T18:20:34.707" clientapp="XXXXXNAV" hostname="XXXXX" hostpid="5356" loginname="XXXXXLogin" isolationlevel="repeatable read (3)" xactid="3500501" currentdb="9" lockTimeout="10000" clientoption1="671156320" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x02000000d10b781e362296d34dff56de1e6507ff2a10bb7c0000000000000000000000000000000000000000">
SELECT TOP (@0) "timestamp","ID","Scan Result ID","Text","Style Code","Element Type","Element Part","Address","New Line After","Created By","Created Date Time","Modified By","Modified Date Time" FROM "DB_TEST"."dbo"."Scan Result Element" WITH(UPDLOCK) WHERE ("Scan Result ID"=@1) ORDER BY "ID" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50) </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int)SELECT TOP (@0) "timestamp","ID","Scan Result ID","Text","Style Code","Element Type","Element Part","Address","New Line After","Created By","Created Date Time","Modified By","Modified Date Time" FROM "DB_TEST"."dbo"."Scan Result Element" WITH(UPDLOCK) WHERE ("Scan Result ID"=@1) ORDER BY "ID" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50) </inputbuf>
</process>
<process id="process7d44c7868" taskpriority="0" logused="36308" waitresource="KEY: 6:72057601762525184 (65ddcb539052)" waittime="1401" ownerId="3501076" transactionname="user_transaction" lasttranstarted="2014-10-03T18:20:40.830" XDES="0x80bf0d6a8" lockMode="U" schedulerid="4" kpid="6876" status="suspended" spid="83" sbid="3" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-10-03T18:20:40.930" lastbatchcompleted="2014-10-03T18:20:40.930" lastattention="2014-10-03T18:20:23.940" clientapp="XXXXXNAV" hostname="XXXXX" hostpid="5356" loginname="XXXXXLogin" isolationlevel="repeatable read (3)" xactid="3501076" currentdb="9" lockTimeout="10000" clientoption1="671156320" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x02000000d10b781e362296d34dff56de1e6507ff2a10bb7c0000000000000000000000000000000000000000">
SELECT TOP (@0) "timestamp","ID","Scan Result ID","Text","Style Code","Element Type","Element Part","Address","New Line After","Created By","Created Date Time","Modified By","Modified Date Time" FROM "DB_TEST"."dbo"."Scan Result Element" WITH(UPDLOCK) WHERE ("Scan Result ID"=@1) ORDER BY "ID" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50) </frame>
</executionStack>
<inputbuf>
(@0 int,@1 int)SELECT TOP (@0) "timestamp","ID","Scan Result ID","Text","Style Code","Element Type","Element Part","Address","New Line After","Created By","Created Date Time","Modified By","Modified Date Time" FROM "DB_TEST"."dbo"."Scan Result Element" WITH(UPDLOCK) WHERE ("Scan Result ID"=@1) ORDER BY "ID" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50) </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057601762525184" dbid="9" objectname="DB_TEST.dbo.Scan Result Element" indexname="Scan Result Element$0" id="lock87f6e4400" mode="X" associatedObjectId="72057601762525184">
<owner-list>
<owner id="process7d44c7868" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process7d44c7498" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057601762525184" dbid="9" objectname="DB_TEST.dbo.Scan Result Element" indexname="Scan Result Element$0" id="lock7eaa1ab00" mode="U" associatedObjectId="72057601762525184">
<owner-list>
<owner id="process7d44c7498" mode="U" />
</owner-list>
<waiter-list>
<waiter id="process7d44c7868" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
Could I maybe add a covering index with items in the select?
Thanks
B
October 4, 2014 at 6:55 am
I created a covering index and included all columns being returned in the select statement, this has stopped the Deadlocks. It doesn't feel right just including all columns in the index though? (index is going to be massive) however this table is constantly being hit for these columns.
October 5, 2014 at 4:56 pm
I would look how indexes for this table are being used for a while. If it looks like this is index is used for most of the queries I would change clustered index to scanid, id and add unique index for id primary key. This will save some space.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply