April 5, 2012 at 1:17 am
Hi All
Having a few problems with deadlocks around a particular table in the DB (deadlock graph below)
Just wondering if anyone can shed any light on this
Table def
CREATE TABLE [dbo].[PSX_PUBLICATION_SITE_ITEM](
[REFERENCE_ID] [bigint] NOT NULL,
[VERSION] [int] NOT NULL,
[SITE_ID] [bigint] NOT NULL,
[CONTEXT_ID] [int] NOT NULL,
[UNPUBLISH_INFO] [image] NULL,
PRIMARY KEY CLUSTERED
(
[REFERENCE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Index Def
CREATE NONCLUSTERED INDEX [IX_SITE_ITEMS] ON [dbo].[PSX_PUBLICATION_SITE_ITEM]
(
[SITE_ID] ASC,
[CONTEXT_ID] ASC
)
Index fragmentation below 30% for both indexes
Deadlock Graph
<TextData>
<deadlock-list>
<deadlock victim="processa2db88">
<process-list>
<process id="processa2db88" taskpriority="0" logused="0" waitresource="PAGE: 5:1:6824253" waittime="10933" ownerId="1493304634"
transactionname="implicit_transaction" lasttranstarted="2012-04-04T15:19:42.193" XDES="0x20cf52730" lockMode="S" schedulerid="3"
kpid="68992" status="suspended" spid="81" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2012-04-04T15:19:44.810"
lastbatchcompleted="2012-04-04T15:19:44.807" clientapp="jTDS" hostname="hostname" hostpid="123" isolationlevel="read committed (2)"
xactid="1493304634" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000007ecd772bc607c482a24c941ad99dd40ef170ae1e">
select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_, pssiteitem0_.SITE_ID as SITE3_37_,
pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_ from database.dbo.PSX_PUBLICATION_SITE_ITEM
pssiteitem0_, database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_
where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0
and pssiteitem0_.CONTEXT_ID= @P1 and pspubitem2_.STATUS=pspubitem2_.OPERATION
</frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process21a958748" taskpriority="0" logused="10000" waittime="10430" schedulerid="2" kpid="67784" status="suspended" spid="81"
sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-04-04T15:19:44.810" lastbatchcompleted="2012-04-04T15:19:44.807" clientapp="jTDS"
hostname="hostname" hostpid="123" loginname="LOGIN" isolationlevel="read committed (2)" xactid="1493304634" currentdb="5"
lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="40" sqlhandle="0x020000007ecd772bc607c482a24c941ad99dd40ef170ae1e">
select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_, pssiteitem0_.SITE_ID as SITE3_37_,
pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_ from database.dbo.PSX_PUBLICATION_SITE_ITEM pssiteitem0_,
database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_ where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID
and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0 and pssiteitem0_.CONTEXT_ID= @P1
and pspubitem2_.STATUS=pspubitem2_.OPERATION
</frame>
</executionStack>
<inputbuf>
(@P0 bigint,@P1 int)select pssiteitem0_.REFERENCE_ID as REFERENCE1_37_, pssiteitem0_.CONTEXT_ID as CONTEXT2_37_,
pssiteitem0_.SITE_ID as SITE3_37_, pssiteitem0_.UNPUBLISH_INFO as UNPUBLISH4_37_, pssiteitem0_.VERSION as VERSION37_
from database.dbo.PSX_PUBLICATION_SITE_ITEM pssiteitem0_, database.dbo.RXSITES pssite1_, database.dbo.PSX_PUBLICATION_DOC pspubitem2_
where pssiteitem0_.REFERENCE_ID=pspubitem2_.REFERENCE_ID and pssiteitem0_.SITE_ID=pssite1_.SITEID and pssiteitem0_.SITE_ID= @P0
and pssiteitem0_.CONTEXT_ID= @P1 and pspubitem2_.STATUS=pspubitem2_.OPERATION
</inputbuf>
</process>
<process id="process4e48bc8" taskpriority="0" logused="98960" waitresource="PAGE: 5:1:6968174" waittime="24" ownerId="1493312850"
transactionname="implicit_transaction" lasttranstarted="2012-04-04T15:19:52.987" XDES="0x26163eb60" lockMode="IX" schedulerid="8" kpid="63104"
status="suspended" spid="90" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-04-04T15:20:06.870"
lastbatchcompleted="2012-04-04T15:20:06.863" clientapp="jTDS" hostname="hostname" hostpid="123" loginname="LOGIN"
isolationlevel="read committed (2)" xactid="1493312850" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="118" sqlhandle="0x020000003e76203748e31becf98a2dd2b3c36d3f9d5e5ae2">
insert into database.dbo.PSX_PUBLICATION_SITE_ITEM (CONTEXT_ID, SITE_ID, UNPUBLISH_INFO, VERSION, REFERENCE_ID) values
( @P0 , @P1 , @P2 , @P3 , @P4 )
</frame>
</executionStack>
<inputbuf>
(@P0 int,@P1 bigint,@P2 varbinary(8000),@P3 int,@P4 bigint)insert into database.dbo.PSX_PUBLICATION_SITE_ITEM
(CONTEXT_ID, SITE_ID, UNPUBLISH_INFO, VERSION, REFERENCE_ID) values ( @P0 , @P1 , @P2 , @P3 , @P4 )
</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="6824253" dbid="5" objectname="database.dbo.PSX_PUBLICATION_SITE_ITEM" id="lock1b208e400" mode="IX"
associatedObjectId="72057594180927488">
<owner-list>
<owner id="process4e48bc8" mode="IX">
</owner>
</owner-list>
<waiter-list>
<waiter id="processa2db88" mode="S" requestType="wait"></waiter>
</waiter-list>
</pagelock>
<exchangeEvent id="Pipe225726400" WaitType="e_waitPipeGetRow" nodeId="0">
<owner-list>
<owner id="processa2db88"></owner>
</owner-list>
<waiter-list>
<waiter id="process21a958748"></waiter>
</waiter-list>
</exchangeEvent>
<pagelock fileid="1" pageid="6968174" dbid="5" objectname="database.dbo.PSX_PUBLICATION_SITE_ITEM" id="locke1d49980" mode="S"
associatedObjectId="72057594180927488">
<owner-list>
<owner id="process21a958748" mode="S"></owner>
</owner-list>
<waiter-list>
<waiter id="process4e48bc8" mode="IX" requestType="wait"></waiter>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
April 5, 2012 at 5:05 am
This could be down to some Intra-Query parallelism.
Although your deadlock graph shows 3 processes, there are only 2 spids involved, and one of the waits involves a parallel exchange event.
Have a look at this blog...
April 5, 2012 at 5:58 am
Thanks Ian, we would have to look at a plan guide as the app is a 3rd party content management system and cannot be changed.
Now this is all new to me, so what statement would you put in the plan guide, the insert or the select or would you create two guides?
The rest is pretty self explanitory, Scope = SQL, Hints = OPTION (MAXDOP 1), Batch = NULL, Params = NULL
Thanks
Ant
April 5, 2012 at 6:09 am
It's all pretty new to me too, but the query that has the parallel waits is the SELECT statement, so that would seem the obvious plan to do first.
April 5, 2012 at 6:19 am
That was my thinking as well, but then I thought that the select is waiting on the insert so shouldnt I optimise the insert first.
Pretty sure will find out once I created a plan guide of the select.
April 5, 2012 at 6:26 am
The Select is waiting on the Insert, but, it's a deadlock... so the Insert is also waiting on the Select.
April 5, 2012 at 6:28 am
yep, sorry, been one of them days, had my head in MDX all day.
I just thought to myself, why dont you see if the sql handle and plan handle are still in the DMV's to which they are, its the select which is paralellisming, so will add the plan guide for that statement.
April 5, 2012 at 6:32 am
It's not an intra-query parallelism deadlock. There's more than one process involved and the resources are locks in 2 cases. For it to be an intra-query parallelism deadlock there would be only one spid and ALL the resources would be parallelism-related.
This is a normal deadlock in which one process is running in parallel.
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
April 5, 2012 at 6:36 am
Easiest solution (since this is a reader-writer deadlock) would be snapshot or read committed smapshot isolation.
Can you investigate and see what these two pages belong to (can see the table, not the index)
1:6968174
1:6824253
Also can you post all indexes on PSX_PUBLICATION_SITE_ITEM
I suspect this is a key-lookup related deadlock.
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
April 5, 2012 at 6:49 am
Hi Gail
There are only 2 indexes, the Primary key and the extra index provided above in the create table and index definition snippets.
I have attached the two plans for each statement.
Could you advise how to check what object the page is allocated to?
Thanks
Ant
April 5, 2012 at 6:57 am
Figured it out, enable trace flag 3604 then use dbcc page.
boths pages have MetaData: ObjectId = 789577851 which relates to PSX_PUBLICATION_SITE_ITEM in sys.objects
April 5, 2012 at 7:08 am
consider adding STATUS column to the non-clustered index, either as part of the composite or as included column. (which way would depend on several things but you could try it either way).
The probability of survival is inversely proportional to the angle of arrival.
April 5, 2012 at 8:22 am
anthony.green (4/5/2012)
Figured it out, enable trace flag 3604 then use dbcc page.boths pages have MetaData: ObjectId = 789577851 which relates to PSX_PUBLICATION_SITE_ITEM in sys.objects
I know the object, what I don't know and need to know is the 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
April 5, 2012 at 8:26 am
Sorry Gail, the indexid for both pages is 1, which is PK__PSX_PUBLICATION___300424B4
April 5, 2012 at 8:31 am
Well the select is doing a clustered index scan, which is not efficient.
Does anything ever use the ReferenceID column to filter or join on?
Is that the only select in the transactions (it's an implicit transaction)
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply