Search code examples
sql-serversql-server-2008-r2ado.netdatabase-deadlocks

A Tale of Two Schemas: One Deadlocks, One Does Not

Background

I have an application that periodically receives large amounts of data from an external source (happens to be an XML file) and inserts that data into a database. During this operation, nothing else is accessing the database. Since there is a large amount of data, I use a configurable number of threads to perform the insertion.

The application uses Entity Framework. However for this particular operation, due to performance considerations, ADO.Net is used to execute a stored procedure to insert data into a header table (Answers) and a detail table (ListAnswerSelections).

The project pre-dates EF Migrations. The original DbContext implements IDatabaseInitializer to create views, indices, etc. Works like a charm. I just reimplemented our DbContext to make proper use of EF Migrations. That is where stuff gets strange.

The Problem

When the bulk loading operation runs with a single thread against a fresh schema created with the reimplemented DbContext, it works fine. When I use 2 or more threads, however, I get a high rate of deadlocks on the primary key index of ListAnswerSelections (detail table). The deadlock diagram looks like:

Lock Diagram

If I use the original context and create a separate, new schema with it, the bulk loading operation runs deadlock-free with 8 threads writing to the database.

In both cases, a new schema is created for each test run and identical XML files are imported. The tests have been run several times against each schema, with the same result each time.

When I use the Schema Compare tools in Visual Studio 2012 to find differences between the schemas created by the original vs. the reimplemented DbContext, I see zero differences for either of the two tables involved in this operation.

The Code

The C# code that calls the stored procedure looks like:

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.uspInsertAnswer";
// Add appropriate parameters
int resultCount = cmd.ExecuteNonQuery();

The stored procedure itself looks like:

CREATE PROCEDURE [dbo].[uspInsertAnswer] @defId INT, @partId INT, @weight FLOAT, @questionId INT, @listValues tvpInt32List READONLY AS
    DECLARE @type INT
    DECLARE @id INT

    BEGIN TRY   
        INSERT INTO dbo.Answers VALUES(@questionId, @partId, NULL, 0, @weight, GETDATE(), NULL, @partId, @defId, @type)

        INSERT INTO dbo.ListAnswerSelections SELECT n, '', GETDATE(), @questionId, @partId FROM @listValues
    END TRY
    BEGIN CATCH
        -- Error Handling
    END CATCH

The tables involved look like (some non-indexed columns removed for brevity):

CREATE TABLE [dbo].[Answers](
    [RelatedQuestionId] [int] NOT NULL,
    [RelatedParticipantId] [int] NOT NULL,
    [Text] [nvarchar](max) NULL,
    [Response_ParticipantId] [int] NULL,
    [Response_DefinitionId] [int] NULL,
    [Type] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.Answers] PRIMARY KEY CLUSTERED 
(
    [RelatedQuestionId] ASC,
    [RelatedParticipantId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[ListAnswerSelections](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [NumericValue] [int] NOT NULL,
    [ListAnswer_RelatedQuestionId] [int] NOT NULL,
    [ListAnswer_RelatedParticipantId] [int] NOT NULL,
 CONSTRAINT [PK_dbo.ListAnswerSelections] PRIMARY KEY CLUSTERED 
(
    [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]

My Question

What could possibly be different between the two schemas (and not shown in the VS Schema Compare Tool) that would cause one of them to experience frequent deadlocks with 2 (or more) threads writing, while the other experiences zero deadlocks with 8 threads writing?

Update: Deadlock XML

<deadlock-list>
 <deadlock victim="process53ace08">
  <process-list>
   <process id="process53ace08" taskpriority="0" logused="1360" waitresource="KEY: 11:72057594041663488 (2c3c53413efb)" waittime="7106" ownerId="6158342" transactionname="user_transaction" lasttranstarted="2014-08-25T18:29:49.570" XDES="0xbf3cf950" lockMode="RangeS-S" schedulerid="7" kpid="2808" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-08-25T18:30:01.240" lastbatchcompleted="2014-08-25T18:30:01.237" lastattention="2014-08-25T18:22:27.293" clientapp=".Net SqlClient Data Provider" hostname="CRUNCHBOX" hostpid="10164" loginname="Crunchbox\Eric" isolationlevel="read committed (2)" xactid="6158342" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="Survey_DEV_BAT.dbo.uspInsertAnswer" line="17" stmtstart="1088" stmtend="1332" sqlhandle="0x03000b004e241b1505a42b0192a300000100000000000000">
INSERT INTO dbo.Answers VALUES(@questionId, @partId, NULL, 0, @weight, GETDATE(), NULL, @partId, @defId, @type)     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 11 Object Id = 354100302]    </inputbuf>
   </process>
   <process id="process534ee08" taskpriority="0" logused="1360" waitresource="KEY: 11:72057594041663488 (354416591f4b)" waittime="4983" ownerId="6158339" transactionname="user_transaction" lasttranstarted="2014-08-25T18:29:49.570" XDES="0xc1d01950" lockMode="RangeS-S" schedulerid="4" kpid="13824" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-08-25T18:30:01.237" lastbatchcompleted="2014-08-25T18:30:01.237" clientapp=".Net SqlClient Data Provider" hostname="CRUNCHBOX" hostpid="10164" loginname="Crunchbox\Eric" isolationlevel="read committed (2)" xactid="6158339" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="Survey_DEV_BAT.dbo.uspInsertAnswer" line="17" stmtstart="1088" stmtend="1332" sqlhandle="0x03000b004e241b1505a42b0192a300000100000000000000">
INSERT INTO dbo.Answers VALUES(@questionId, @partId, NULL, 0, @weight, GETDATE(), NULL, @partId, @defId, @type)     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 11 Object Id = 354100302]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594041663488" dbid="11" objectname="Survey_DEV_BAT.dbo.ListAnswerSelections" indexname="PK_dbo.ListAnswerSelections" id="lock8be36880" mode="RangeX-X" associatedObjectId="72057594041663488">
    <owner-list>
     <owner id="process534ee08" mode="RangeX-X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process53ace08" mode="RangeS-S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594041663488" dbid="11" objectname="Survey_DEV_BAT.dbo.ListAnswerSelections" indexname="PK_dbo.ListAnswerSelections" id="lock8544da00" mode="X" associatedObjectId="72057594041663488">
    <owner-list>
     <owner id="process53ace08" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process534ee08" mode="RangeS-S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

UPDATE: Foreign key constraint

ALTER TABLE [dbo].[ListAnswerSelections]  WITH NOCHECK ADD  CONSTRAINT [FK_dbo.ListAnswerSelections_dbo.Answers_ListAnswer_RelatedQuestionId_ListAnswer_RelatedParticipantId] FOREIGN KEY([ListAnswer_RelatedQuestionId], [ListAnswer_RelatedParticipantId])
REFERENCES [dbo].[Answers] ([RelatedQuestionId], [RelatedParticipantId])
ON DELETE CASCADE
Solution

  • Post the deadlock graph. Not the picture of it, the actual deadlock graph XML. The picture is like 1% of the info in the XML. For instance the XML would answer whether you have a lock hash collision.

    Range locks. Why? This implies serializable isolation and nothing in your description justifies it. Use read committed.

    Unrelated to the deadlock, but if you truly care about performance then use bulk insert. As in true bulk insert. Use SqlBulkCopy which uses the true bulk insert API, not ordinary INSERT statement. Only the bulk insert API can achieve minimal logging and only the bulk insert API can do streaming of data while inserting.

    Certain operations internally use serializable isolation level. Foreign key constraint and indexed views maintenance are two examples of such, see Conor vs. Isolation Level Upgrade on UPDATE/DELETE Cascading RI:

    This same condition applies to indexed view maintenance

    Capturing the execution plans of the two cases would immediately point out the issue.