SQL server – Deadlock auf Schlüsselaktualisierung

SQL server 2014 Express.

Ich habe mein Problem wie folgt vereinfacht:

CREATE TABLE [dbo].[foo]( [fooid] [numeric](10, 0) IDENTITY(1,1) NOT NULL, [fooval] [nvarchar](4), CONSTRAINT [foo_PK] PRIMARY KEY CLUSTERED ( [fooid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [dbo].[foo] ([fooval]) VALUES (1) GO INSERT INTO [dbo].[foo] ([fooval]) VALUES (2) GO CREATE TABLE [dbo].[bar]( [barid] [numeric](10, 0) IDENTITY(1,1) NOT NULL, [barval] [nvarchar](4), CONSTRAINT [bar_PK] PRIMARY KEY CLUSTERED ( [barid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [dbo].[bar] ([barval]) VALUES (1) GO INSERT INTO [dbo].[bar] ([barval]) VALUES (2) GO 

Also habe ich zwei einfache Tabellen mit einem geclusterten Primärschlüssel auf fooid und barid.

Ich laufe die folgenden zwei Abfragen in zwei Debugger.

Erste Abfrage:

 BEGIN TRAN UPDATE dbo.foo SET fooval = 1 WHERE fooid = 1 UPDATE dbo.bar SET barval = 1 WHERE barval = 1 COMMIT 

Zweite Abfrage:

 BEGIN TRAN UPDATE dbo.bar SET barval = 2 WHERE barid = 2 UPDATE dbo.foo SET fooval = 2 WHERE fooval = 2 COMMIT 

Während des Debugging, führen ich erste Aktualisierung der Abfrage 1, dann erste Aktualisierung der Abfrage 2, dann zweite Aktualisierung der Abfrage 1 und schließlich zweite Aktualisierung der Abfrage 2.

Dies führt zu einem Deadlock. Ich laufe Snapshot Isolation Ebene Read Committed.

Die Grafik zeigt:

 <deadlock-list> <deadlock victim="process2f3ed64e8"> <process-list> <process id="process2f3ed64e8" taskpriority="0" logused="288" waitresource="KEY: 5:72057607973896192 (227b7397de24)" waittime="2067" ownerId="1978563" transactionname="user_transaction" lasttranstarted="2015-08-24T16:24:57.280" XDES="0x2e2ff23b0" lockMode="U" schedulerid="1" kpid="9892" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-08-24T16:24:56.997" lastbatchcompleted="2015-08-24T16:24:56.993" lastattention="1900-01-01T00:00:00.993" clientapp="Microsoft SQL server Management Studio - Abfrage" hostname="VSL53439" hostpid="9124" loginname="x" isolationlevel="read committed (2)" xactid="1978563" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="6" stmtstart="38" stmtend="146" sqlhandle="0x02000000118b7210fc35334336b07155dea42e1470abe8dd0000000000000000000000000000000000000000"> unknown </frame> <frame procname="adhoc" line="6" stmtstart="336" stmtend="426" sqlhandle="0x02000000bf0a381fd6fec29b6ed330f87409b4e8c47d26f10000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> BEGIN TRAN UPDATE dbo.bar SET barval = 2 WHERE barid = 2 UPDATE dbo.foo SET fooval = 2 WHERE fooval = 2 COMMIT </inputbuf> </process> <process id="process2e01b5088" taskpriority="0" logused="432" waitresource="KEY: 5:72057607973830656 (c939eba47c7b)" waittime="2970" ownerId="1978502" transactionname="user_transaction" lasttranstarted="2015-08-24T16:24:54.100" XDES="0x2df783000" lockMode="U" schedulerid="5" kpid="1928" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-08-24T16:24:53.730" lastbatchcompleted="2015-08-24T16:24:53.730" lastattention="1900-01-01T00:00:00.730" clientapp="Microsoft SQL server Management Studio - Abfrage" hostname="VSL53439" hostpid="4348" loginname="x" isolationlevel="read committed (2)" xactid="1978502" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="adhoc" line="6" stmtstart="38" stmtend="146" sqlhandle="0x02000000f8c0c134764c79fe77f7cda514cc62eaf1a50cc80000000000000000000000000000000000000000"> unknown </frame> <frame procname="adhoc" line="6" stmtstart="336" stmtend="426" sqlhandle="0x020000005c75f728d068a9d6386669fb7b8e315b3e484d640000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> BEGIN TRAN UPDATE dbo.foo SET fooval = 1 WHERE fooid = 1 UPDATE dbo.bar SET barval = 1 WHERE barval = 1 COMMIT </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057607973896192" dbid="5" objectname="dbdevelop.dbo.foo" indexname="foo_PK" id="lock2ea279880" mode="X" associatedObjectId="72057607973896192"> <owner-list> <owner id="process2e01b5088" mode="X"/> </owner-list> <waiter-list> <waiter id="process2f3ed64e8" mode="U" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057607973830656" dbid="5" objectname="dbdevelop.dbo.bar" indexname="bar_PK" id="lock2eb0e6500" mode="X" associatedObjectId="72057607973830656"> <owner-list> <owner id="process2f3ed64e8" mode="X"/> </owner-list> <waiter-list> <waiter id="process2e01b5088" mode="U" requestType="wait"/> </waiter-list> </keylock> </resource-list> </deadlock> </deadlock-list> 

Wenn ich einen Blick in das Schloss erwerbe, sehe ich, dass die folgenden Schlösser gemacht wurden

  1. erworben – IX – OBJECT
  2. erworben – IX – SEITE
  3. erworben – X – KEY
  4. erworben – X – EXTENT
  5. freigegeben – X – EXTENT
  6. erworben – U – EXTENT
  7. erworben – X – PAGE
  8. freigegeben – U – EXTENT
  9. freigegeben – X – PAGE
  10. freigegeben – 0 – KEY
  11. freigegeben – 0 – SEITE

So wird alles freigegeben, mit exception des OBJECT von Anfang an, was der primäre Schlüsselindex zu sein scheint. Ich denke, es wird gehalten werden, bis das Commit der Transaktion erfolgt und nicht sofort veröffentlicht wird. Und das scheint zu einem Deadlock zu führen.

Können Sie mir bitte folgende Fragen beantworten:

  1. Bin ich richtig, dass die geclusterte Primärschlüssel-Indexsperre gehalten wird, bis das Commit?
  2. Bin ich richtig, dass dies blockiert alle anderen gleichzeitigen Update versucht zu warten?
  3. Wenn ja, warum wird der ganze Index bei der Aktualisierung mit einem gegebenen Primärschlüssel in der where-Klausel gesperrt? Dies würde bedeuten, dass jedes Update auf einem Primärschlüssel wo Klausel die ganze Tabelle für die Transaktion sperren wird. Ich kann das nicht glauben.
  4. Ist die beste Lösung, um einen Index auf fooval und barval hinzuzufügen?
  5. Wird ein SQL server in seinem Verhalten von einem SQL server ausdrücken?

Crossing Updates ist ein Rezept für Deadlocks. Unabhängig von Indizes, Arten von Indizes etc etc .. Immer versuchen, Tabellen in der gleichen Reihenfolge zu aktualisieren. Habe gesagt, dass unabhängig von dem Index, wenn die data auf der gleichen Seite sind, dann haben Sie ein Sperrszenario und weil Sie in einer kreuzigen Weise aktualisieren, wird einer Ihrer Befehle als Deadlock ausgewählt.

1.Ja
2.Ja
3.Dies ist kompliziert zu beantworten, und es gibt wunderbare Erklärungen im Internet, aber was Sie verstehen sollten, ist, dass unabhängig von Indizes, Schlösser passieren werden, und passieren oft, aber Deadlocks sind wegen einer schlechten Strategie.
4.Irehelevant
5.Ja in bestimmten Dingen aber nicht für diese Situation.