Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deadlock help Expand / Collapse
Author
Message
Posted Thursday, April 5, 2012 1:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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
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

Post #1278601
Posted Thursday, April 5, 2012 5:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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




Post #1278685
Posted Thursday, April 5, 2012 5:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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
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

Post #1278715
Posted Thursday, April 5, 2012 6:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
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.


Post #1278724
Posted Thursday, April 5, 2012 6:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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
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

Post #1278728
Posted Thursday, April 5, 2012 6:26 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
The Select is waiting on the Insert, but, it's a deadlock... so the Insert is also waiting on the Select.


Post #1278733
Posted Thursday, April 5, 2012 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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
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

Post #1278736
Posted Thursday, April 5, 2012 6:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 40,154, Visits: 36,541
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 2008, MVP
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

Post #1278740
Posted Thursday, April 5, 2012 6:36 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 40,154, Visits: 36,541
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 2008, MVP
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

Post #1278743
Posted Thursday, April 5, 2012 6:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:42 AM
Points: 5,216, Visits: 5,111
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
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

Post #1278751
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse