SQL Server-Deadlock für dieselbe Tabelle

Nach ein bisschen mehr Suchen und Testen bin ich ziemlich zuversichtlich, dass ich die richtige Antwort auf meine eigene Frage geben kann.

Ich muss Martin Smith danken, der mich in die richtige Richtung gelenkt hat, indem er darauf hingewiesen hat, dass die Wartezeiten unterschiedlich sind.

Wie Martin in seinem Kommentar schrieb, sind die Wartezeiten: 11: 290100074: 0 und 11: 290100074: 5. Nach der Suche stellt sich heraus, dass Sql Server eine Funktion namens Partitionierung sperren .

Dieser Artikel sagt unter anderem:

  

Nur NL-, SCH-S-, IS-, IU- und IX-Sperrmodi werden in einem einzigen Modus erfasst   Partition.

Was in meinem Fall passiert, ist, dass spid 155 eine gemeinsame Sperre auf eine Zeile oder Seite setzt und dafür eine beabsichtigte gemeinsame Sperre auf das Objekt setzt, und bei der Sperrpartitionsfunktion befindet sich dies auf der Partitions-ID 5.

Gleichzeitig muss spid 124 das gesamte Objekt mit einer exklusiven Sperre sperren und muss daher X-Sperre auf allen Partitionen setzen.

  

Gemeinsame (S), exklusive (X) und andere Sperren in anderen Modi als NL,   SCH-S, IS, IU und IX müssen auf allen Partitionen erworben werden, die mit beginnen   Partitions-ID 0 und folgende in der Partitions-ID-Reihenfolge.

Wenn es bei der Partitions-ID 5 ankommt, wird gesagt, dass die Spid 155 eine IS-Sperre hält und sie warten muss, bis diese Sperre freigegeben wird.

Wenn spid 124 auf die IS-Sperre wartet, wird die Sperreskalation ausgeführt spid 155 und fordert eine gemeinsame Sperre für die Tabelle an. Das heißt, es muss S-Sperre auf alle Partitionen ab ID 0 setzen. Aber sofort auf ID 0 trifft es die Wand, weil Spid 124 bereits eine exklusive Sperre für diese Partition enthält. Und da hast du die Ursache für den Stillstand.

Ich kann nicht 100% garantieren, das ist die genaue Antwort, aber ich bin mir ziemlich sicher, dass ich es bin, wenn nicht 100% richtig, zumindest nahe an der Antwort.

Die Lösung? Gut. Die Sperrpartitionsfunktion kann nicht deaktiviert werden, andererseits können Sie Sperreskalation mit steuern verschiedene Transaktionsstufen und auch verschiedene Optionen in der alter table-Anweisung.

Ich werde weiter untersuchen, warum die Abfrage die Eskalation blockiert, weil ich glaube, dass die Lösung in meinem speziellen Fall darin besteht, die Abfrage irgendwie zu optimieren, damit sie nicht eskaliert. Zumindest werde ich das versuchen, bevor ich die oben erwähnten Werkzeuge benutze.

Ich hoffe, diese Antwort hilft anderen mit ähnlichen Problemen.

Es ist nicht immer wahr, dass "der übliche Deadlock ist, wenn zwei oder mehr Sessions Sperren auf verschiedenen Ressourcen halten und auf einander warten" - auch gibt es Conversion-Deadlocks. Selbst wenn zwei Prozesse auf nur einer Ressource miteinander konkurrieren, können sie dennoch in einem Conversion-Deadlock auftreten was ich hier beschrieben habe.

Obwohl das bekannteste Deadlock-Szenario zwei Verbindungen enthält, die zwei Tabellen in unterschiedlicher Reihenfolge modifizieren, gibt es auch andere Deadlock-Szenarien, die nur eine Tabelle umfassen. Außerdem muss in einigen Szenarien jede Verbindung nur eine Anweisung ausgeben, und es genügt, einen Deadlock zu erhalten. In einigen Szenarios muss auch nur eine Verbindung exklusive Sperren ändern oder erwerben - Der andere darf nur Daten lesen und nur gemeinsame Sperren erhalten und sich dennoch in einem Deadlock festhalten.

Noch eine Sache: Beantworten Sie diesen Kommentar "keiner der Abfragen läuft in einer Transaktion" - jede DML-Anweisung wird immer in einer Transaktion ausgeführt, und DML bedeutet auch selects. Alle im Deadlock enthaltenen Befehle werden im Kontext einer Transaktion ausgeführt. Folgen Sie dem zweiten Link und führen Sie die Repro-Skripts aus - Sie werden es selbst sehen.

Wie auch immer, ich würde einfach die Auswahl unter Snapshot-Isolation ausführen - das würde verhindern, dass dieser spezielle Deadlock (wenn nur eine Verbindung gelesen wird) passiert.

Es passiert, weil Ihre Lock-Strategie zu einfach ist und Sie in einen Fall geraten, in dem das kommt und Sie verletzt.

Wie in: Wenn Sie mit einer ausreichend hohen Sperrstufe auswählen, erhalten Sie eine Lesesperre und können nicht auf eine Schreibsperre aktualisieren, während eine andere Lesesperre vorhanden ist, wenn zwei Apps das tun ... Sie können genau Thagt Verhalten haben (Prozess 1 wird gelesen, 2 erhält Lesesperre, 1 möchte auf Schreibsperre aktualisieren (wartet), 2 möchte auf Schreibsperre aktualisieren - Deadlock.

In Ihrem speziellen Fall scheinen die Reads Lesesperren zu setzen, während der Upsert einen zur Aktualisierung veranlasst, aber dann auf das für die Einfügung erforderliche Schloss bläst (und ja, Sie können eine Sperre haben, die ein einfügen).

  

Dieser Fehler tritt häufig in unserer Anwendung auf und einige Sperren müssen als erste angefordert werden   und warum wird die zweite Sperre akzeptiert, wenn bereits eine Sperre für die gesamte Tabelle besteht?

Anfänger Design Problem. Das Problem tritt auf, weil einige Sperren gemeinsam genutzt werden (hauptsächlich Lesesperren), wodurch andere Lesesperren eingerichtet werden können. WENN du das erlaubst. Ich würde vorschlagen, dafür zu sorgen, dass der Deadlock nicht auftritt. Entweder lassen die Lesevorgänge keine Sperre (mit NOLOCK) oder erhalten viel früher richtig Schreibsperren.