SQL Server Performance

Deadlocking with Snapshot Isolation

Discussion in 'SQL Server 2005 General Developer Questions' started by g1tech, May 24, 2007.

  1. g1tech New Member

    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 />&lt;deadlock-list&gt;<br /> &lt;deadlock victim="process6d8d48"&gt;<br /> &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />rocess-list&gt;<br /> &lt<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"&gt;<br /> &lt;executionStack&gt;<br /> &lt;frame procname="adhoc" line="1" stmtstart="44" sqlhandle="0x02000000397c51333abea55884ec9049be4adfa5efcdd2b9"&gt;<br />DELETE FROM SubInspections WHERE Id = @p0 &lt;/frame&gt;<br /> &lt;frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"&gt;<br />unknown &lt;/frame&gt;<br /> &lt;/executionStack&gt;<br /> &lt;inputbuf&gt;<br />(@p0 uniqueidentifier)DELETE FROM SubInspections WHERE Id = @p0 &lt;/inputbuf&gt;<br /> &lt;/process&gt;<br /> &lt<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"&gt;<br /> &lt;executionStack&gt;<br /> &lt;frame procname="adhoc" line="1" stmtstart="44" sqlhandle="0x02000000397c51333abea55884ec9049be4adfa5efcdd2b9"&gt;<br />DELETE FROM SubInspections WHERE Id = @p0 &lt;/frame&gt;<br /> &lt;frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"&gt;<br />unknown &lt;/frame&gt;<br /> &lt;/executionStack&gt;<br /> &lt;inputbuf&gt;<br />(@p0 uniqueidentifier)DELETE FROM SubInspections WHERE Id = @p0 &lt;/inputbuf&gt;<br /> &lt;/process&gt;<br /> &lt;/process-list&gt;<br /> &lt;resource-list&gt;<br /> &lt<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"&gt;<br /> &lt;owner-list&gt;<br /> &lt;owner id="process6d8d48" mode="IX"/&gt;<br /> &lt;/owner-list&gt;<br /> &lt;waiter-list&gt;<br /> &lt;waiter id="process8ccf28" mode="S" requestType="wait"/&gt;<br /> &lt;/waiter-list&gt;<br /> &lt;/pagelock&gt;<br /> &lt<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"&gt;<br /> &lt;owner-list&gt;<br /> &lt;owner id="process8ccf28" mode="SIX"/&gt;<br /> &lt;/owner-list&gt;<br /> &lt;waiter-list&gt;<br /> &lt;waiter id="process6d8d48" mode="S" requestType="wait"/&gt;<br /> &lt;/waiter-list&gt;<br /> &lt;/pagelock&gt;<br /> &lt;/resource-list&gt;<br /> &lt;/deadlock&gt;<br />&lt;/deadlock-list&gt;<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 />
  2. Adriaan New Member

    Can you give us more details of SubInspections - PK definition, any indexes ...
  3. satya Moderator

    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.
  4. g1tech New Member

    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.
  5. g1tech New Member

    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.

    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.
  6. MohammedU New Member

    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.
  7. Haywood New Member

    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.


    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
  8. g1tech New Member

    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
  9. satya Moderator

    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


    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.

    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.
  10. Adriaan New Member

    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.

Share This Page