SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock help


Deadlock help

Author
Message
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24870 Visits: 6519
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>





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Ian Scarlett
Ian Scarlett
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5376 Visits: 7144
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...
http://blogs.msdn.com/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx



anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24870 Visits: 6519
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Ian Scarlett
Ian Scarlett
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5376 Visits: 7144
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.



anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24870 Visits: 6519
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Ian Scarlett
Ian Scarlett
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5376 Visits: 7144
The Select is waiting on the Insert, but, it's a deadlock... so the Insert is also waiting on the Select.



anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24870 Visits: 6519
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228015 Visits: 46339
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228015 Visits: 46339
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24870 Visits: 6519
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search