Un conte de deux schémas: un deadlocks, un ne pas

Context

J'ai une application qui reçoit périodiquement de grandes quantités de données d'une source externe (il s'agit d'un file XML) et insère ces données dans une database. Pendant cette opération, rien d'autre n'accède à la database. Comme il y a une grande quantité de données, j'utilise un nombre configurable de threads pour effectuer l'insertion.

L'application utilise Entity Framework. Toutefois, pour cette opération particulière, en raison de considérations de performances, ADO.Net est utilisé pour exécuter une procédure stockée pour insert des données dans une table d'en-tête (Réponses) et une table de détails (ListAnswerSelections).

Le projet est antérieur à EF Migrations. Le DbContext d'origine implémente IDatabaseInitializer pour créer des vues, des index, etc. Fonctionne comme un charme. Je viens de réimplémenter notre DbContext pour utiliser correctement les migrations EF. C'est là que les choses deviennent étranges.

Le problème

Lorsque l'opération de chargement en bloc s'exécute avec un seul thread contre un nouveau schéma créé avec le DbContext réimplémenté, cela fonctionne correctement. Lorsque j'utilise 2 threads ou plus, cependant, j'obtiens un taux élevé d'interblocages sur l'index de key primaire de ListAnswerSelections (table de détails). Le diagramme de blocage ressemble à ceci:

Verrouiller le diagramme

Si j'utilise le context d'origine et crée un nouveau schéma séparé, l'opération de chargement en bloc s'exécute sans interblocage avec 8 threads qui écrivent dans la database.

Dans les deux cas, un nouveau schéma est créé pour chaque exécution de test et des files XML identiques sont importés. Les tests ont été exécutés plusieurs fois contre chaque schéma, avec le même résultat à chaque fois.

Lorsque j'utilise les outils de comparaison de schéma dans Visual Studio 2012 pour find des différences entre les schémas créés par le DbContext original et le DbContext réimplémenté, je vois des différences nulles pour l'une des deux tables impliquées dans cette opération.

Le code

Le code C # qui appelle la procédure stockée ressemble à:

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

La procédure stockée elle-même ressemble à:

 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 

Les tables impliquées ressemblent (certaines colonnes non indexées ont été supprimées par souci de concision):

 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] 

Ma question

Qu'est-ce qui pourrait être différent entre les deux schémas (et non montré dans VS Schema Compare Tool) qui provoquerait l'un d'entre eux à rencontrer de fréquents deadlocks avec 2 (ou plus) threads, alors que l'autre connaîtrait zéro deadlocks avec 8 threads?

Mise à jour: 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: contrainte de key étrangère

 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 

Afficher le graphique de blocage. Pas l'image de celui-ci, le graphique de l'impasse réelle XML. L'image est comme 1% de l'information dans le XML. Par exemple le XML répondrait si vous avez une collision de hachage de verrou .

Verrous de plage. Pourquoi? Cela implique un isolement sérialisable et rien dans votre description ne le justifie. Utilisez read committed.

Sans rapport avec l'impasse, mais si vous vous souciez vraiment de la performance, utilisez un encart en vrac. Comme dans l'insert en vrac véritable . Utilisez SqlBulkCopy qui utilise l' API d'insertion en bloc véritable et non l'instruction INSERT ordinaire. Seule l'API d'insertion en bloc peut réaliser une journalisation minimale et seule l'API d'insertion en bloc peut effectuer la diffusion de données lors de l'insertion.

Certaines opérations utilisent en interne le niveau d'isolation sérialisable. La contrainte de key étrangère et la maintenance des vues indexées en sont deux exemples, voir Conor vs. Isolation Level Upgrade sur UPDATE / DELETE RI en cascade :

Cette même condition s'applique à la maintenance de vue indexée

La capture des plans d'exécution des deux cas signalerait immédiatement le problème.