SQL Server Performance Forum – Threads Archive
Deadlocking with Snapshot Isolation
Hi<br /><br />We’re encountering some major concurrency issues in one of our applications, a C# 2.0 Winforms application with a SQL Server 2005 back end. We’re using Snapshot Isolation mode to ensure non blocking reads and update consistency across transactions. We’ve managed to replicate the problem and have captured the deadlock events using SQL Server 2005 Profiler as XML (shown below):<br /><br /><deadlock-list><br /> <deadlock victim="process6d8d48"><br /> <<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rocess-list><br /> <<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rocess id="process6d8d48" taskpriority="0" logused="442880" waitresource="PAGE: 5:1:55177" waittime="130234" ownerId="3142" transactionname="user_transaction" lasttranstarted="2007-05-23T15:25:00.540" XDES="0x38f4c78" lockMode="S" schedulerid="1" kpid="976" status="suspended" spid="53" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2007-05-23T15:26:48.400" lastbatchcompleted="2007-05-23T15:26:48.400" clientapp=".Net SqlClient Data Provider" hostname="RTOPT400" hostpid="7396" loginname="sa" isolationlevel="snapshot (5)" xactid="3142" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"><br /> <executionStack><br /> <frame procname="adhoc" line="1" stmtstart="44" sqlhandle="0x02000000397c51333abea55884ec9049be4adfa5efcdd2b9"><br />DELETE FROM SubInspections WHERE Id = @p0 </frame><br /> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"><br />unknown </frame><br /> </executionStack><br /> <inputbuf><br />(@p0 uniqueidentifier)DELETE FROM SubInspections WHERE Id = @p0 </inputbuf><br /> </process><br /> <<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rocess id="process8ccf28" taskpriority="0" logused="1084448" waitresource="PAGE: 5:1:62531" waittime="1890" ownerId="3152" transactionname="user_transaction" lasttranstarted="2007-05-23T15:25:05.730" XDES="0x3971a40" lockMode="S" schedulerid="2" kpid="3024" status="suspended" spid="52" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2007-05-23T15:28:56.760" lastbatchcompleted="2007-05-23T15:28:56.760" clientapp=".Net SqlClient Data Provider" hostname="RTTAB2" hostpid="1868" loginname="sa" isolationlevel="snapshot (5)" xactid="3152" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"><br /> <executionStack><br /> <frame procname="adhoc" line="1" stmtstart="44" sqlhandle="0x02000000397c51333abea55884ec9049be4adfa5efcdd2b9"><br />DELETE FROM SubInspections WHERE Id = @p0 </frame><br /> <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"><br />unknown </frame><br /> </executionStack><br /> <inputbuf><br />(@p0 uniqueidentifier)DELETE FROM SubInspections WHERE Id = @p0 </inputbuf><br /> </process><br /> </process-list><br /> <resource-list><br /> <<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />agelock fileid="1" pageid="62531" dbid="5" objectname="SISServer.dbo.ServiceSchedules" id="lock360ca40" mode="IX" associatedObjectId="72057594043105280"><br /> <owner-list><br /> <owner id="process6d8d48" mode="IX"/><br /> </owner-list><br /> <waiter-list><br /> <waiter id="process8ccf28" mode="S" requestType="wait"/><br /> </waiter-list><br /> </pagelock><br /> <<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />agelock fileid="1" pageid="55177" dbid="5" objectname="SISServer.dbo.ServiceSchedules" id="lock737eec0" mode="SIX" associatedObjectId="72057594043105280"><br /> <owner-list><br /> <owner id="process8ccf28" mode="SIX"/><br /> </owner-list><br /> <waiter-list><br /> <waiter id="process6d8d48" mode="S" requestType="wait"/><br /> </waiter-list><br /> </pagelock><br /> </resource-list><br /> </deadlock><br /></deadlock-list><br /><br />We are updating/deleting hundreds of rows across several tables during each transaction, but in our test case the rows are always different accross the individual transactions. For this reason, and because we are using snapshot isolation (i.e. row versioning instead of locks), I do not understand how a deadlock situation can arise.<br /><br />I’d be grateful if anyone can suggest why the deadlocking is occurring and how we may minimize or eliminate it altogether?<br /><br />Thanks in advance<br />Can you give us more details of SubInspections – PK definition, any indexes …
Have you checked for tempdb contention during this time? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
quote:Originally posted by Adriaan
Can you give us more details of SubInspections – PK definition, any indexes …
Thanks for getting back to me Adriaan The SubInspections table has a PK consisting of a single column of type varchar(36) and is a clustered index. The SubInspections table has a 1..N relationship with another table called ServiceSchedules, which also has a PK consisting of a single column of type varchar(36) and is also a clustered index. The ServiceSchedules table has a 1..N relationship with another table, ServiceScheduleSubComponents which currently has no PK/index. In terms of our domain model, each SubInspection has a collection of ServiceSchedules, which each have a collection of ServiceScheduleSubComponents.
quote:Originally posted by satya
Have you checked for tempdb contention during this time?
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi Satya Thanks for the reply…what sort of thing should we be looking at to establish whether there are any contention issues in tempdb? We’ve been monitoring tempdb free space, version store size and version generation/cleanup rates while the deadlocking occurs but have seen nothing untoward.
I don’t think snapshot isolation level will eliminate all dead locks specially when you are doing updates/deletes…. When you use snapshot isolation level reads will not block writes and writes will not block reads…
In your case both spids are writing I believe..
http://msdn2.microsoft.com/en-us/library/ms189050.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
quote:Originally posted by MohammedU
I don’t think snapshot isolation level will eliminate all dead locks specially when you are doing updates/deletes….
When you use snapshot isolation level reads will not block writes and writes will not block reads…
In your case both spids are writing I believe..
http://msdn2.microsoft.com/en-us/library/ms189050.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
In your case both spids are writing I believe..
http://msdn2.microsoft.com/en-us/library/ms189050.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Agreed, and doubly so after reading the first installment at: http://sqlblog.com/blogs/hugo_kornelis/archive/2006/07/21/Snapshot-and-integrity-part-1.aspx
Thanks for your help guys. Managed to eventually get everything working in snapshot isolation mode the way we wanted by removing the foreign keys from the tables in question. Our application business logic maintains referential integrity so we can live without it in the database. We can now support multiple concurrent transactions performing updates/deletes with no deadlocking whatsoever. Not sure if this is the only possible solution but would be interested to hear your views. Cheers Stewart
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Due to the changes in the usage of TEMPDB in SQL 2005, it is essential. Workers & processes use tempdb like any other database, any worker can issue I/O to and from tempdb as needed.
quote:Originally posted by g1tech
Hi Satya Thanks for the reply…what sort of thing should we be looking at to establish whether there are any contention issues in tempdb? We’ve been monitoring tempdb free space, version store size and version generation/cleanup rates while the deadlocking occurs but have seen nothing untoward.
quote:Originally posted by satya
Have you checked for tempdb contention during this time?
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Hi Satya Thanks for the reply…what sort of thing should we be looking at to establish whether there are any contention issues in tempdb? We’ve been monitoring tempdb free space, version store size and version generation/cleanup rates while the deadlocking occurs but have seen nothing untoward.
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
quote:The ServiceSchedules table has a 1..N relationship with another table, ServiceScheduleSubComponents which currently has no PK/index
Then add an identity column to ServiceScheduleSubComponents, and add a PK (clustered) on this column.
I presume that there is an index on the foreign key column on ServiceScheduleSubComponents that refers to ServiceSchedules?
Note that in SQL Server, FK constraints do not automatically get an index to support them … (common mistake when you’re used to MS Access).
If ServiceScheduleSubComponents already has an index for the FK, then rebuild it after adding the clustered PK. You might consider adding the other columns that make up the natural key to this index.
Also check if the application does a lot of lookups for filtering on other columns in ServiceScheduleSubComponents, and add indexes to support.
]]>