May 14, 2012 at 3:47 am
Hello,
I am new to working out a deadlock and would really appreciate some help with it and hopefully learn something new. Firstly, its SQL 2008 R2, I have enabled Traceflag 1222 and run profiler, I have saved it into an XML and have the results below.
I also have a deadlock graph, but unfortunatly I cannot make head nor tail of it, as I say this is my first time so any help would be great. I have of course checked my friend Google, but the info is a little too scattered and general for someone with very limited knowledge, so I hope to take something away with me from this. The actual lock lasts for something like 650ms and is happening roughly three times a day, it is not happening when backups are occurring so I can rule that out.
<deadlock-list>
<deadlock victim="process516ddc8">
<process-list>
<process id="process516ddc8" taskpriority="0" logused="0" waitresource="PAGE: 10:1:11488" waittime="3793" ownerId="932000888" transactionname="UPDATE" lasttranstarted="2012-05-10T14:16:04.743" XDES="0x9d820080" lockMode="U" schedulerid="15" kpid="14356" status="suspended" spid="145" sbid="0" ecid="9" priority="0" trancount="0" lastbatchstarted="2012-05-10T14:16:04.743" lastbatchcompleted="2012-05-10T14:16:04.743" hostname="APPLICATION_SERVER" hostpid="8512" isolationlevel="read committed (2)" xactid="932000888" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x02000000331c8d0ee0f30d633f7771498aad10ec93f1ebdb">
UPDATE [Session] set [Status] = @1 WHERE [SessionID]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x020000004e26190a749a389a572515c1bd1dad6824ab95a4">
update Session set Status = 2 where SessionID = 2513479 </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process5153288" taskpriority="0" logused="0" waitresource="PAGE: 10:1:12168" waittime="3792" ownerId="932000888" transactionname="UPDATE" lasttranstarted="2012-05-10T14:16:04.743" XDES="0x800ef7e0" lockMode="U" schedulerid="14" kpid="6936" status="suspended" spid="145" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2012-05-10T14:16:04.743" lastbatchcompleted="2012-05-10T14:16:04.743" hostname="APPLICATION_SERVER" hostpid="8512" isolationlevel="read committed (2)" xactid="932000888" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x02000000331c8d0ee0f30d633f7771498aad10ec93f1ebdb">
UPDATE [Session] set [Status] = @1 WHERE [SessionID]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x020000004e26190a749a389a572515c1bd1dad6824ab95a4">
update Session set Status = 2 where SessionID = 2513479 </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process8d8e08" taskpriority="0" logused="200" waitresource="PAGE: 10:1:10176" waittime="3832" ownerId="932000890" transactionname="UPDATE" lasttranstarted="2012-05-10T14:16:04.747" XDES="0x800057e0" lockMode="U" schedulerid="4" kpid="11476" status="suspended" spid="79" sbid="0" ecid="5" priority="0" trancount="0" lastbatchstarted="2012-05-10T14:16:04.747" lastbatchcompleted="2012-05-10T14:16:04.743" hostname="APPLICATION_SERVER" hostpid="7612" isolationlevel="read committed (2)" xactid="932000890" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x02000000331c8d0ee0f30d633f7771498aad10ec93f1ebdb">
UPDATE [Session] set [Status] = @1 WHERE [SessionID]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x02000000b2d2302a6c3e36709bbd886c2257754c508e70cb">
update Session set Status = 2 where SessionID = 2513478 </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process5152bc8" taskpriority="0" logused="200" waitresource="PAGE: 10:1:9472" waittime="3792" ownerId="932000890" transactionname="UPDATE" lasttranstarted="2012-05-10T14:16:04.747" XDES="0x129b5fb60" lockMode="U" schedulerid="14" kpid="13856" status="suspended" spid="79" sbid="0" ecid="13" priority="0" trancount="0" lastbatchstarted="2012-05-10T14:16:04.747" lastbatchcompleted="2012-05-10T14:16:04.743" hostname="APPLICATION_SERVER" hostpid="7612" isolationlevel="read committed (2)" xactid="932000890" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x02000000331c8d0ee0f30d633f7771498aad10ec93f1ebdb">
UPDATE [Session] set [Status] = @1 WHERE [SessionID]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x02000000b2d2302a6c3e36709bbd886c2257754c508e70cb">
update Session set Status = 2 where SessionID = 2513478 </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process38bdc8" taskpriority="0" logused="200" waitresource="PAGE: 10:1:10832" waittime="3823" ownerId="932000890" transactionname="UPDATE" lasttranstarted="2012-05-10T14:16:04.747" XDES="0x132ad9620" lockMode="U" schedulerid="1" kpid="14272" status="suspended" spid="79" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2012-05-10T14:16:04.747" lastbatchcompleted="2012-05-10T14:16:04.743" hostname="APPLICATION_SERVER" hostpid="7612" isolationlevel="read committed (2)" xactid="932000890" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x02000000331c8d0ee0f30d633f7771498aad10ec93f1ebdb">
UPDATE [Session] set [Status] = @1 WHERE [SessionID]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x02000000b2d2302a6c3e36709bbd886c2257754c508e70cb">
update Session set Status = 2 where SessionID = 2513478 </frame>
</executionStack>
<inputbuf>
</inputbuf>
</process>
<process id="process8f34c8" taskpriority="0" logused="10000" waittime="3147" schedulerid="5" kpid="7952" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-05-10T14:16:04.747" lastbatchcompleted="2012-05-10T14:16:04.743" hostname="APPLICATION_SERVER" hostpid="7612" loginname="USER_NAME" isolationlevel="read committed (2)" xactid="932000890" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="30" sqlhandle="0x02000000331c8d0ee0f30d633f7771498aad10ec93f1ebdb">
UPDATE [Session] set [Status] = @1 WHERE [SessionID]=@2 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x02000000b2d2302a6c3e36709bbd886c2257754c508e70cb">
update Session set Status = 2 where SessionID = 2513478 </frame>
</executionStack>
<inputbuf>
update Session set Status = 2 where SessionID = 2513478 </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="11488" dbid="10" objectname="NAME_OF_DATABASE.dbo.Session" id="lockd0627900" mode="U" associatedObjectId="131665637736448">
<owner-list>
<owner id="process8f34c8" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process516ddc8" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="12168" dbid="10" objectname="NAME_OF_DATABASE.dbo.Session" id="lockd031b980" mode="U" associatedObjectId="131665637736448">
<owner-list>
<owner id="process8f34c8" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process5153288" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="10176" dbid="10" objectname="NAME_OF_DATABASE.dbo.Session" id="lockcb28b500" mode="U" associatedObjectId="131665637736448">
<owner-list>
<owner id="process5153288" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process8d8e08" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="9472" dbid="10" objectname="NAME_OF_DATABASE.dbo.Session" id="lock1c72c2400" mode="U" associatedObjectId="131665637736448">
<owner-list>
<owner id="process516ddc8" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process5152bc8" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="10832" dbid="10" objectname="NAME_OF_DATABASE.dbo.Session" id="lock17f24d380" mode="U" associatedObjectId="131665637736448">
<owner-list>
<owner id="process516ddc8" mode="U"/>
</owner-list>
<waiter-list>
<waiter id="process38bdc8" mode="U" requestType="wait"/>
</waiter-list>
</pagelock>
<exchangeEvent id="Piped1558480" WaitType="e_waitPipeGetRow" nodeId="2">
<owner-list>
<owner id="process38bdc8"/>
<owner id="process8d8e08"/>
<owner id="process5152bc8"/>
</owner-list>
<waiter-list>
<waiter id="process8f34c8"/>
</waiter-list>
</exchangeEvent>
</resource-list>
</deadlock>
</deadlock-list>
Thank you in advance for any help or advice.
Kind Regards,
D.
May 14, 2012 at 6:00 am
Could you upload the deadlock graph as well?
It's important to understand what a deadlock actually is in diagnosing these issues - it's NOT the same as locking or blocking. It specifically means that 2 or more resources have held and requested an unresolvable cycle of locks. For e.g. Process 1 is in a transaction and has locked Row 1 in Table A, Process 2 is in a transaction and has locked Row 2 in Table A. If process 1 then requests a lock on Row 2 and process 2 requests a lock on Row 1, that is completely unresolvable as neither process can continue without the other being rolled back.
It's worth a read of the following article which has a good explanation of deadlocks:
http://msdn.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx
Ultimately it comes down to application design, although the probability of deadlocks can be reduced by improving overall transaction times/optimising performance
May 14, 2012 at 6:07 am
You've got this query:
UPDATE [Session] set [Status] = 2 WHERE [SessionID]=2513479
That seems to be going into a deadlock with a very similar query:
UPDATE [Session] set [Status] = 2 WHERE [SessionID]=2513478
So, just looking at the queries as they are, you're going to have hard time determining the cause of the deadlock. What you need to determine is, what occurred before these statements? A deadlock takes place when one process is holding a lock that another process needs, and vice versa. One of them is chosen as the victim and it's transaction is rolled back. To fix it, you need to know all the statements that are part of these transactions because, in all likelihood, one of them is calling the tables in a different order than the other.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2012 at 7:25 am
Hello,
Thank you both so much for getting back, I have attached the dead lock graph as requested. Sorry, it comes out a bit small.
Regards,
D.
May 14, 2012 at 7:32 am
Ah, I didn't spot the Exchange Event the first time through. This is a parallelism deadlock. Best solution in this case is to eliminate the parallelism. As a starting point, what's the Threshold for Parallelism on your sever? If it's the default of 5, I'd suggest bumping it up to something like 40 and then see how things go from there. If you still get this deadlock, you might want to set the Max Degree of Parallelism for this query to 1 using the MAXDOP query hint.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2012 at 7:37 am
Ah, I was just reading about that in the link that Howard sent through, my server is indeed set to 5, so I shall change it, before I do does this change require a restart of the engine or anything?
Regards,
D.
May 14, 2012 at 7:43 am
I wonder why you're getting parallelism here for such a simple query... Is sessionID the clustered index of the Session table? Is SessionID unique?
May 14, 2012 at 7:49 am
Duran (5/14/2012)
Ah, I was just reading about that in the link that Howard sent through, my server is indeed set to 5, so I shall change it, before I do does this change require a restart of the engine or anything?Regards,
D.
Nope, no restart required for that one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2012 at 7:50 am
Hello Howard,
The session table has no indexes. Its always functioned ok in the past, this problem appears to have come out of nowhere. I'm not sure how to find out the answers to your questions I'm afraid.
Regards,
D.
May 14, 2012 at 7:54 am
OK, if it's a heap with no indexes, I'd fix the design rather than change parallelism settings.
You can find out if Session ID is unique at any given time by running a query like:
Select SessionID, count(*) from Session
Group by SessionID
Having count(*) > 1
If this returns no rows, it's unique, otherwise it's not. However, you need to talk to the application developers ultimately to see if there are occasions where this won't be true. It sounds like from the naming conventions/normal use of this type of table that it's likely to be unique and these problems will go away with a clustered PK on SessionID, but test and speak to the application developers first.
May 14, 2012 at 7:55 am
Duran (5/14/2012)
Hello Howard,The session table has no indexes. Its always functioned ok in the past, this problem appears to have come out of nowhere. I'm not sure how to find out the answers to your questions I'm afraid.
Regards,
D.
Whoa! I'd get indexes on there. At least a clustered index. There are all sorts of tests and results from various sources (Bingle away) that show that a good clustered index improves INSERT performance (let alone, SELECT, UPDATE, DELETE). Unless you have a tested, proven reason why a table should not have a clustered index, they all should have one.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2012 at 7:58 am
HowardW (5/14/2012)
OK, if it's a heap with no indexes, I'd fix the design rather than change parallelism settings.You can find out if Session ID is unique at any given time by running a query like:
Select SessionID, count(*) from SessionGroup by SessionID
Having count(*) > 1
If this returns no rows, it's unique, otherwise it's not. However, you need to talk to the application developers ultimately to see if there are occasions where this won't be true. It sounds like from the naming conventions/normal use of this type of table that it's likely to be unique and these problems will go away with a clustered PK on SessionID, but test and speak to the application developers first.
I'd still change the parallelism settings. The default value of 5 is so utterly useless it's hard to fathom why Microsoft continues to leave it in place. It may or may not help in this situation, but it'll certainly help in others.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 14, 2012 at 8:02 am
Hello,
The query did indeed return 0 rows, so I will add a clustered index and see what happens. I will also change the parallelism as well, I will leave a gap in those activites and see if one or the other makes the difference. I will come back with the results to complete the thread.
In the meantime, I realise your time is precious so thank you very much for giving some to my issue, I'll let you know how I get on.
Kind Regards,
D.
May 14, 2012 at 8:21 am
Duran (5/14/2012)
The query did indeed return 0 rows, so I will add a clustered index and see what happens. I will also change the parallelism as well, I will leave a gap in those activites and see if one or the other makes the difference. I will come back with the results to complete the thread.In the meantime, I realise your time is precious so thank you very much for giving some to my issue, I'll let you know how I get on.
No problem.
I think that either approach is likely to stop the problem occurring. As Grant says, the parallelism setting is a general performance setting you should change anyway (at least for OLTP systems), but the root of this problem is that your table is a heap and your updates are excessively costly, so adding a clustered unique index (I'd recommend making it the PK as well) is going to vastly improve the overall performance of updates to this table.
Bear in mind that parallelism is kicking in because this simple update is estimated to take over 5 seconds, as it has to scan through the whole table for each update. With an appropriate index, this should be milliseconds at most.
May 16, 2012 at 7:15 am
Hello,
Just to complete the tread, the increase to the cost of parallelism from the default 5 to 40 did in fact solve my issue. I will still be putting an index into the table in question though for further performance enhancement.
Thanks again for all your help, much appreciated.
Regards,
D.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply