16. Mär 2017
Lesedauer 10 Min.
Die sechs Gesichter der Deadlocks
SQL Server Concurrency, Teil 3
Wie Sie verhindern, dass sich zwei Transaktionen gegenseitig blockieren.
Schon in den beiden vorangegangenen Teilen dieser Miniserie [1] [2] haben Sie erfahren, dass der SQL Server gleichzeitig arbeitende Benutzer abhängig vom gewählten Transaction Isolation Level voneinander isoliert. Dafür verwendet er diese Locks:
- Für schreibende Transaktionen werden immer exklusive Locks angefordert.
- Lesende Transaktionen können abhängig vom gewählten Transaction Isolation Level Shared Locks anfordern.
Deadlock-Handling im SQL Server
Das Nette am SQL Server ist, dass er Deadlock-Situationen selbstständig auflösen kann. Das erledigt ein Hintergrundprozess namens Deadlock Monitor, der alle paar Sekunden prüft, ob Deadlocks vorhanden sind.Hat er eine Deadlocking-Situation identifiziert, macht er die günstigste Transaktion rückgängig. Dadurch werden die angeforderten Locks wieder freigegeben und die blockierte Transaktion kann mit ihrer Arbeit fortfahren. Die günstigste Transaktion ist dabei diejenige, welche die wenigsten Daten ins Transaktions-Log geschrieben hat. Bei einem Deadlock zwischen einer lesenden und einer schreibenden Transaktion wird deshalb immer die lesende Transaktion zurückgerollt, da diese keine Daten ins Transaktions-Log schreibt. Die vom Deadlock Monitor zurückgerollte Transaktion wird auch als Deadlock Victim bezeichnet.Die als Deadlock Victim ausgewählte Transaktion erhält die Fehlernummer 1205. Tritt diese Meldung auf, sollte der Datenzugriffscode der Anwendung die Transaktion einfach noch einmal ausführen. Da der Deadlock seitens des SQL Server beim Ausgeben der Fehlernummer bereits aufgelöst wurde, sollte beim nächsten Versuch kein Deadlock mehr auftreten. Allerdings sollte der Code nicht unendlich lange versuchen, eine mit dem Fehler 1205 quittierte Transaktion erneut auszuführen, sondern die Versuche nach mehrmaligem Fehlschlagen beenden und den Benutzer informieren, dass es Probleme beim Zugriff auf die Datenbank gibt.Ein Deadlock ist aber eine Situation, die Ihre Anwendung in den meisten Fällen ohne Benutzerintervention lösen kann – solange Ihr Datenzugriffscode stets das Auftreten der Fehlernummer 1205 überprüft und angemessen darauf reagiert.Deadlock-Troubleshooting
Das Bordwerkzeug des SQL Server enthält drei Technologien zur Analyse und Behandlung von Deadlocking-Situationen:- Trace Flag 1222
- SQL Server Profiler
- Extended Events
Generell empfehle ich den Einsatz von Extended Events für den Umgang mit Deadlocks. Da es sich beim SQL Server Profiler bereits um eine von Microsoft abgekündigte Funktion (deprecated) handelt, wird sie in einer der nächsten Versionen der Datenbank nicht mehr enthalten sein. Das Trace Flag 1222 passt nicht mehr so recht in die Zeit, da hier lediglich XML-Informationen über den aufgetretenen Deadlock ins SQL-Server-Log geschrieben werden (Bild 1).
Die leistungsfähigste Möglichkeit zum Troubleshooting bei Deadlocks bieten die Extended Events, da der SQL Server hier eine Vielzahl unterschiedlicher Informationen in Form eines XML-Dokuments zurückliefert. Bild 2 zeigt einen Ausschnitt daraus. Es handelt sich dabei um den sogenannten Deadlock-Graphen, der die folgenden XML-Knoten auf oberster Ebene beinhaltet:
- <process-list>
- <resource-list>
Cycle Deadlock
Wie oben schon angedeutet, gibt es verschiedene Arten von Deadlocks. Die einfachste davon ist ein Cycle Deadlock. Er ensteht, wenn auf Ressourcen in unterschiedlicher Reihenfolge zugegriffen wird. Sehen Sie sich dazu Listing 1 und 2 an. Wie zu erkennen ist, greifen die beiden Sessions auf dieselben beiden Tabellen zu, aber in unterschiedlicher Reihenfolge: Die erste Session greift auf Tabelle T1 und dann auf Tabelle T2 zu, die zweite Session greift auf Tabelle T2 und danach auf Tabelle T1 zu.Listing 1: Cycle Deadlock – Session 1
<span class="hljs-keyword">USE</span> TempDB <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> T1 <br/>( <br/> C1 <span class="hljs-built_in">INT</span> <br/>) <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> T1 <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>) <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> T2 <br/>( <br/> C1 <span class="hljs-built_in">INT</span> <br/>) <br/> <br/><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> T2 <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>) <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> T1 <br/><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> T2 <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">BEGIN</span> <span class="hljs-keyword">TRANSACTION</span> <br/> <br/><span class="hljs-keyword">UPDATE</span> T1 <span class="hljs-keyword">SET</span> C1 = <span class="hljs-number">2</span> <br/> <br/><span class="hljs-keyword">UPDATE</span> T2 <span class="hljs-keyword">SET</span> C1 = <span class="hljs-number">2</span> <br/> <br/><span class="hljs-comment">-- COMMIT TRANSACTION </span><br/><span class="hljs-comment"> </span><br/><span class="hljs-comment">DROP TABLE T1 </span><br/><span class="hljs-comment">DROP TABLE T2 </span><br/><span class="hljs-comment">GO </span>
Wenn Sie die Statements Schritt für Schritt abwechselnd für die beiden Transaktionen durchführen, werden Sie in einen Deadlock laufen, der ein paar Sekunden später durch den Deadlock Monitor erkannt und schlussendlich durch das Zurückrollen der günstigsten Transaktion aufgelöst wird.
Listing 2: Cycle Deadlock – Session 2
<span class="hljs-keyword">USE</span> TempDB <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">BEGIN</span> <span class="hljs-keyword">TRANSACTION</span> <br/> <br/><span class="hljs-keyword">UPDATE</span> T2 <span class="hljs-keyword">SET</span> C1 = <span class="hljs-number">1</span> <br/> <br/><span class="hljs-keyword">UPDATE</span> T1 <span class="hljs-keyword">SET</span> C1 = <span class="hljs-number">1</span> <br/> <br/><span class="hljs-keyword">COMMIT</span> <span class="hljs-keyword">TRANSACTION</span> <br/><span class="hljs-keyword">GO</span>
Dieser zyklische Deadlock lässt sich ganz einfach vermeiden: Es genügt, wenn Sie auf Tabellen immer in der gleichen Reihenfolge zugreifen. Ich habe schon mehrfach mit Kunden zusammengearbeitet, deren Entwickler angehalten waren, auf Tabellen immer in alphabetischer Reihenfolge (auf Basis der Namensgebung) zuzugreifen, um Cycle Deadlocks zu vermeiden.
Bookmark Lookup Deadlocks
Ich habe schon einige Male in der dotnetpro über Bookmark Lookups geschrieben – beispielsweise in [3] – und berichtet, dass diese hinsichtlich Performance und Indizierungsstrategie nicht immer das beste Mittel sind. Ein weiterer negativer Seiteneffekt, der sich aus Bookmark Lookups ergeben kann, ist die Tatsache, dass Sie hier ganz leicht in Deadlocks laufen, wenn Sie parallele schreibende Transaktionen auf derselben Tabelle ausführen. Sehen Sie sich dazu Listing 3 und 4 an.Listing 3: Bookmark Lookup Deadlock – Session 1
<span class="hljs-keyword">USE</span> <span class="hljs-keyword">master</span> <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">DATABASE</span> BookmarkLookupDL <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">USE</span> BookmarkLookupDL <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> Deadlock ( <br/> Col1 <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> PRIMARY <span class="hljs-keyword">KEY</span> CLUSTERED, <br/> Col2 <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>, <br/> Col3 <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <br/>) <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">CREATE</span> NONCLUSTERED <span class="hljs-keyword">INDEX</span> idx_Col3 <span class="hljs-keyword">ON</span> Deadlock(Col3) <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> Deadlock <span class="hljs-keyword">VALUES</span> (<span class="hljs-number">1</span>, <span class="hljs-number">1</span>, <span class="hljs-number">1</span>) <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Deadlock <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">SET</span> <span class="hljs-keyword">TRANSACTION</span> <span class="hljs-keyword">ISOLATION</span> <span class="hljs-keyword">LEVEL</span> REPEATABLE <span class="hljs-keyword">READ</span> <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">WHILE</span> (<span class="hljs-number">1</span> = <span class="hljs-number">1</span>) <br/><span class="hljs-keyword">BEGIN</span> <br/> <span class="hljs-keyword">UPDATE</span> Deadlock <br/> <span class="hljs-keyword">SET</span> Col1 = Col1 + <span class="hljs-number">1</span> <br/> <span class="hljs-keyword">WHERE</span> Col3 = <span class="hljs-number">1</span> <br/><span class="hljs-keyword">END</span> <br/><span class="hljs-keyword">GO</span>
Die Listings sind nicht komplex. In Listing 3 wird in einer Endlosschleife der Clustered Key der Tabelle aktualisiert. Da der Clustered Key auch als logischer Zeiger im Non-Clustered Index vorhanden ist, muss der SQL Server zunächst den Clustered Index und danach auch den Non-Clustered Index aktualisieren. Für das Aktualisieren beider Indizes sind jeweils exklusive Locks erforderlich.
Listing 4: Bookmark Lookup Deadlock – Session 2
<span class="hljs-keyword">USE</span> BookmarkLookupDL <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">SET</span> <span class="hljs-keyword">TRANSACTION</span> <span class="hljs-keyword">ISOLATION</span> <span class="hljs-keyword">LEVEL</span> REPEATABLE <span class="hljs-keyword">READ</span> <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">WHILE</span> (<span class="hljs-number">1</span> = <span class="hljs-number">1</span>) <br/><span class="hljs-keyword">BEGIN</span> <br/> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Deadlock <span class="hljs-keyword">WITH</span> (<span class="hljs-keyword">INDEX</span>(idx_Col3)) <br/> -– The hint <span class="hljs-keyword">is</span> necessary <span class="hljs-keyword">to</span> overcome the Tipping <br/> <span class="hljs-comment">-- Point to produce a Bookmark Lookup </span><br/><span class="hljs-comment"> WHERE Col3 = 1 </span><br/><span class="hljs-comment">END </span><br/><span class="hljs-comment">GO </span>
Der Code in Listing 4 greift per Bookmark Lookup auf dieselbe Tabelle zu: im ersten Schritt lesend auf den Non-Clustered Index, im zweiten Schritt lesend auf den Clustered Index. Der Zugriff auf die beteiligten Ressourcen erfolgt auch hier nicht in der gleichen Reihenfolge, wodurch ein Deadlock ganz einfach provoziert werden kann!Der Transaction Isolation Level wurde in beiden Transaktionen auf Repeatable Read gesetzt, damit der Deadlock einfacher zu reproduzieren ist. In einer Produktivumgebung mit entsprechendem Workload lässt sich dieser Deadlock auch im Standard-Isolation-Level Read Committed reproduzieren. Bild 3 veranschaulicht diese Problematik.
Wie kann ein solcher Deadlock vermieden werden? Das Problem entsteht ja dadurch, dass der SQL Server intern in der falschen Reihenfolge auf die Ressourcen zugreift. Einfaches Umschreiben der Abfragen ist somit keine Option.Sie können den Deadlock jedoch verhindern, indem Sie den Bookmark Lookup ganz vermeiden – ein Covering Non-Clustered Index hilft dabei! Wird der lesende Zugriff auf den Clustered Index vermieden, lässt sich der Deadlock lösen. Listing 5 zeigt den dazu erforderlichen Index.
Listing 5: Covering Non-Clustered Index
-- Creates a Covering Non-Clustered Index <br/>CREATE NONCLUSTERED INDEX idx_Col3 ON Deadlock(<span class="hljs-name">Col3</span>) <br/>INCLUDE (<span class="hljs-name">Col1</span>, Col2) <br/>WITH (<span class="hljs-name">DROP_EXISTING</span> = ON) <br/>GO
Bookmark Lookup Deadlocks sind folglich ein weiterer Grund, warum Sie Bookmark Lookups wirklich nur in Spezialfällen in Kauf nehmen sollten.
Deadlocks aufgrund fehlender Indizes
Auch durch fehlende Indizes können Deadlocking-Situationen entstehen. Stellen Sie sich vor, Sie haben eine Heap Table (eine Tabelle ohne Clustered Index) und zusätzlich ist für diese Tabelle kein einziger Non-Clustered Index für den Datenzugriff definiert. In diesem Fall gibt es nur eine Möglichkeit, auf diese Tabelle zuzugreifen – nämlich über einen Table Scan Operator im Ausführungsplan.Listing 6: Fehlende Indizes – Session 1
USE master <br/>GO<br/>-- <span class="hljs-keyword">Create</span> a <span class="hljs-keyword">new</span> database <br/><span class="hljs-keyword">CREATE</span> DATABASE DeadlockingDemo <br/>GO <br/> <br/>-- Use it <br/>USE DeadlockingDemo <br/>GO <br/> <br/>-- <span class="hljs-keyword">Create</span> a table without any indexes <br/><span class="hljs-keyword">CREATE</span> TABLE Table1 ( <br/> Column1 INT, Column2 INT ) <br/>GO <br/> <br/>-- Insert a few records <br/>INSERT <span class="hljs-keyword">INTO</span> Table1 VALUES (<span class="hljs-number">1</span>, <span class="hljs-number">1</span>) <br/>INSERT <span class="hljs-keyword">INTO</span> Table1 VALUES (<span class="hljs-number">2</span>, <span class="hljs-number">2</span>) <br/>INSERT <span class="hljs-keyword">INTO</span> Table1 VALUES (<span class="hljs-number">3</span>, <span class="hljs-number">3</span>) <br/>INSERT <span class="hljs-keyword">INTO</span> Table1 VALUES (<span class="hljs-number">4</span>, <span class="hljs-number">4</span>) <br/>GO <br/> <br/>-- <span class="hljs-keyword">Create</span> a table without any indexes <br/><span class="hljs-keyword">CREATE</span> TABLE Table2 ( <br/> Column1 INT, Column2 INT ) <br/>GO <br/>-- Insert a few records <br/>INSERT <span class="hljs-keyword">INTO</span> Table2 VALUES (<span class="hljs-number">1</span>, <span class="hljs-number">1</span>) <br/>INSERT <span class="hljs-keyword">INTO</span> Table2 VALUES (<span class="hljs-number">2</span>, <span class="hljs-number">2</span>) <br/>INSERT <span class="hljs-keyword">INTO</span> Table2 VALUES (<span class="hljs-number">3</span>, <span class="hljs-number">3</span>) <br/>INSERT <span class="hljs-keyword">INTO</span> Table2 VALUES (<span class="hljs-number">4</span>, <span class="hljs-number">4</span>) <br/>GO <br/> <br/><span class="hljs-keyword">BEGIN</span> TRANSACTION<br/> -- Acquires an Exclusive Lock <span class="hljs-keyword">on</span> the row <br/> UPDATE Table1 <span class="hljs-keyword">SET</span> Column1 = <span class="hljs-number">3</span> <span class="hljs-keyword">WHERE</span> Column2 = <span class="hljs-number">1</span> <br/> -- Execute the query <span class="hljs-keyword">from</span> Session <span class="hljs-number">2</span>... <br/> -- ...<br/><br/> -- This query now requests a Shared Lock, but gets <br/> -- blocked, because the other session/transaction <br/> -- <span class="hljs-keyword">has</span> an Exclusive Lock <span class="hljs-keyword">on</span> one row, that <span class="hljs-keyword">is</span> <br/> -- currently updated <br/> <span class="hljs-keyword">SELECT</span> Column1 <span class="hljs-keyword">FROM</span> Table2 <br/> --<span class="hljs-keyword">WITH</span> (<span class="hljs-keyword">INDEX</span> = idx_Column2) <br/> -- The <span class="hljs-keyword">index</span> hint <span class="hljs-keyword">is</span> necessary, because <span class="hljs-keyword">in</span> this <br/> -- tiny table, SQL Server will just scan the whole <br/> -- table... <br/> <span class="hljs-keyword">WHERE</span> Column2 = <span class="hljs-number">3</span> <br/> <br/>ROLLBACK TRANSACTION <br/>GO
Dadurch müssen Sie bei jedem Zugriff auf die Tabelle die komplette Tabelle lesen (und für jeden Datensatz einen Shared Lock anfordern), auch wenn Sie nur an einem spezifischen Datensatz interessiert sind. Sehen Sie sich dazu Listing 6 und 7 näher an. Sie werden erkennen, dass hier auf die beiden beteiligten Tabellen wieder in unterschiedlicher Reihenfolge zugegriffen wird.
Listing 7: Fehlende Indizes – Session 2
-- Use the previous created database <br/>USE DeadlockingDemo <br/>GO <br/> <br/><span class="hljs-keyword">BEGIN</span> TRANSACTION <br/> -- Acquires an Exclusive Lock <span class="hljs-keyword">on</span> the row <br/> UPDATE Table2 <span class="hljs-keyword">SET</span> Column1 = <span class="hljs-number">5</span> <span class="hljs-keyword">WHERE</span> Column2 = <span class="hljs-number">2</span> <br/> <br/> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.dm_tran_locks <br/> <span class="hljs-keyword">WHERE</span> request_session_id = @@SPID <br/><br/> -- <span class="hljs-keyword">Continue</span> <span class="hljs-keyword">with</span> the query <span class="hljs-keyword">from</span> Session <span class="hljs-number">2</span>... <br/> -- ... <br/> <br/> -- This query now requests a Shared Lock, but<br/> -- gets blocked, because the other session/<br/> -- transaction <span class="hljs-keyword">has</span> an Exclusive Lock <span class="hljs-keyword">on</span> one row, <br/> -- that <span class="hljs-keyword">is</span> currently updated <br/> <span class="hljs-keyword">SELECT</span> Column1 <span class="hljs-keyword">FROM</span> Table1 <br/> -- <span class="hljs-keyword">WITH</span> (<span class="hljs-keyword">INDEX</span> = idx_Column2) -- The <span class="hljs-keyword">index</span> hint <br/> -- <span class="hljs-keyword">is</span> necessary, because <span class="hljs-keyword">in</span> this tiny table SQL <br/> -- Server will just scan the whole table... <br/> <span class="hljs-keyword">WHERE</span> Column2 = <span class="hljs-number">4</span> <br/> <br/>ROLLBACK TRANSACTION <br/>GO
Dadurch entsteht, wie gehabt, ein Deadlock zwischen den beiden Transaktionen. Diesen Deadlock vermeiden Sie, indem Sie zusätzliche Non-Clustered Indizes für beide Tabellen definieren. Durch den Einsatz eines Non-Clustered Index kann der SQL Server über eine Seek-Operation die gewünschten Datensätze im Leaf Level des Non-Clustered Index finden, ohne auf die eigentliche Tabelle über einen Table Scan Operator zugreifen zu müssen. Und dadurch wird der Deadlock eliminiert. Listing 8 zeigt die dazu erforderlichen Non-Clustered Indizes.
Listing 8: Non-Clustered Indizes
-- <span class="hljs-keyword">Create</span> <span class="hljs-keyword">new</span> indexes so that SQL Server <span class="hljs-keyword">has</span> alter<br/>-- native access paths <span class="hljs-keyword">to</span> the data. The previous <br/>-- <span class="hljs-number">2</span> <span class="hljs-keyword">SELECT</span> statements can be now done through the <br/>-- Nonclustered <span class="hljs-keyword">Index</span> without acquiring a Shared <br/>-- Lock <span class="hljs-keyword">on</span> the table itself (which currently holds <br/>-- an Exclusive Lock <span class="hljs-keyword">from</span> the UPDATE statement). <br/><span class="hljs-keyword">CREATE</span> NONCLUSTERED <span class="hljs-keyword">INDEX</span> idx_Column2 <span class="hljs-keyword">ON</span> Table1(Column2) <br/><br/><span class="hljs-keyword">CREATE</span> NONCLUSTERED <span class="hljs-keyword">INDEX</span> idx_Column2 <span class="hljs-keyword">ON</span> Table2(Column2) <br/>GO Deadlocks beim Zugriff auf unterschiedliche Datenbereiche
Greifen Sie auf unterschiedliche Datenbereiche in unterschiedlicher Reihenfolge zu, droht Ihnen ebenfalls ein Deadlock. Hier ein konkretes Beispiel: Listing 9 und 10 zeigen wieder zwei Transaktionen, die sich gegenseitig blockieren können. Hier handelt es sich wieder um einen klassischen Cycle Deadlock, da auf unterschiedliche Datenbereiche in unterschiedlicher Reihenfolge zugegriffen wird. Auch dieser Deadlock lässt sich vermeiden, wenn Sie auf die Datenbereiche in identischer Reihenfolge zugreifen.Listing 9: Deadlock – Session 1
USE AdventureWorks2014 <br/>GO <br/><br/>-- Causes a deadlock when we access data <span class="hljs-keyword">in</span> the <br/>-- wrong <span class="hljs-keyword">order</span> <br/><span class="hljs-keyword">BEGIN</span> TRANSACTION <br/> -- <span class="hljs-number">1</span>st range <span class="hljs-keyword">of</span> data <br/> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Person.Person <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120208'</span> <br/> <br/> UPDATE Person.Person <br/> <span class="hljs-keyword">SET</span> FirstName = <span class="hljs-string">'...'</span> <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120208'</span> <br/> -- Switch <span class="hljs-keyword">to</span> session <span class="hljs-number">2</span> <br/> -- ... <br/> -- <span class="hljs-number">2</span>nd range <span class="hljs-keyword">of</span> data <br/> -- This statement will cause a deadlock! <br/> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Person.Person <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120209'</span> <br/> <br/> UPDATE Person.Person <br/> <span class="hljs-keyword">SET</span> FirstName = <span class="hljs-string">'...'</span> <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120209'</span> <br/> <br/>ROLLBACK <br/>GO Deadlocks bei Repeatable Read
Der Transaction Isolation Level Repetable Read ist ebenfalls sehr anfällig für Deadlocking-Situationen, da hier bekanntlich (siehe [1] [2]) die Shared Locks bis zum Ende der Transaktion gehalten werden, damit Repeatable Reads gewährleistet werden können. Sehen Sie sich dazu Listing 11 an.Listing 10: Deadlock – Session 2
USE AdventureWorks2014 <br/>GO <br/>-- Causes a deadlock when we access data <span class="hljs-keyword">in</span> the <br/>-- wrong <span class="hljs-keyword">order</span> <br/><span class="hljs-keyword">BEGIN</span> TRANSACTION <br/> -- <span class="hljs-number">2</span>nd range <span class="hljs-keyword">of</span> data <br/> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Person.Person <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120209'</span> <br/> <br/> UPDATE Person.Person <br/> <span class="hljs-keyword">SET</span> FirstName = <span class="hljs-string">'...'</span> <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120209'</span> <br/> -- Switch <span class="hljs-keyword">to</span> session <span class="hljs-number">1</span> <br/> -- ... <br/> -- <span class="hljs-number">1</span>st range <span class="hljs-keyword">of</span> data <br/> -- This statement will cause a deadlock! <br/> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Person.Person <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120208'</span> <br/> <br/> UPDATE Person.Person <br/> <span class="hljs-keyword">SET</span> FirstName = <span class="hljs-string">'...'</span> <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120208'</span> <br/> <br/>ROLLBACK <br/>GO
Führen Sie diese Transaktion in zwei Sessions gleichzeitig aus, können Sie auch hier in einen Deadlock laufen. Das Problem dabei ist, dass keine der beiden Transaktionen den Exclusive Lock für das UPDATE-Statement anfordern kann, da bereits die andere Transaktion einen inkompatiblen Shared Lock auf den Datensatz angefordert hat. Dadurch blockieren beide UPDATE-Statements – Deadlock!
Listing 11: Deadlock in Repeatable Read – Session 1
<span class="hljs-keyword">USE</span> AdventureWorks2014 <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">SET</span> <span class="hljs-keyword">TRANSACTION</span> <span class="hljs-keyword">ISOLATION</span> <span class="hljs-keyword">LEVEL</span> REPEATABLE <span class="hljs-keyword">READ</span> <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">BEGIN</span> <span class="hljs-keyword">TRANSACTION</span> <br/> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Person.Person <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120208'</span> <br/> <span class="hljs-comment">-- Switch to session 2 </span><br/><span class="hljs-comment"> -- ... </span><br/><span class="hljs-comment"> </span><br/><span class="hljs-comment"> -- This statement will cause a deadlock! </span><br/><span class="hljs-comment"> UPDATE Person.Person </span><br/><span class="hljs-comment"> SET FirstName = '...' </span><br/><span class="hljs-comment"> WHERE ModifiedDate = '20120208' </span><br/><span class="hljs-comment"> </span><br/><span class="hljs-comment">ROLLBACK </span><br/><span class="hljs-comment">GO </span>
Diesen Deadlock lösen Sie auf, indem Sie zum Beispiel beim SELECT-Statement explizit einen Update Lock anfordern. Dieser ist kompatibel mit einem Shared Lock, aber inkompatibel mit sich selbst, und auch inkompatibel mit einem Exclusive Lock. Dadurch kann nur das erste SELECT-Statement ausgeführt werden. Das SELECT-Statement der anderen Session wird blockieren, da der Update Lock nicht angefordert werden kann.
Listing 12: Deadlock in Repeatable Read – Session 2
<span class="hljs-keyword">USE</span> AdventureWorks2014 <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">SET</span> <span class="hljs-keyword">TRANSACTION</span> <span class="hljs-keyword">ISOLATION</span> <span class="hljs-keyword">LEVEL</span> REPEATABLE <span class="hljs-keyword">READ</span> <br/><span class="hljs-keyword">GO</span> <br/> <br/><span class="hljs-keyword">BEGIN</span> <span class="hljs-keyword">TRANSACTION</span> <br/> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> Person.Person <br/> <span class="hljs-keyword">WHERE</span> ModifiedDate = <span class="hljs-string">'20120208'</span> <span class="hljs-keyword">WITH</span> (UPDLOCK) <br/> <span class="hljs-comment">-- Switch to session 1 </span><br/><span class="hljs-comment"> -- ... </span><br/><span class="hljs-comment"> </span><br/><span class="hljs-comment"> -- This statement will cause a deadlock! </span><br/><span class="hljs-comment"> UPDATE Person.Person </span><br/><span class="hljs-comment"> SET FirstName = '...' </span><br/><span class="hljs-comment"> WHERE ModifiedDate = '20120208' </span><br/><span class="hljs-comment"> </span><br/><span class="hljs-comment">ROLLBACK </span><br/><span class="hljs-comment">GO </span>
Dank dieser Vorgehensweise serialisiert der SQL Server beide Transaktionen und führt sie der Reihe nach aus. Die Performance sinkt dadurch zwar, aber als Gegenleistung haben Sie keinen Deadlock mehr, vergleiche Listing 12.
Intra-Parallelism Deadlocks
Ein äußerst gemeiner Deadlock im SQL Server ist der Intra-Parallelism Deadlock . Das Gemeine daran ist, dass es sich um einen Bug im SQL Server handelt, der bewusst nicht gefixt wird. Microsoft hat sich für diese Vorgehensweise entschieden, da die Behebung des Bugs zu risikoreich wäre. Wichtig ist für Sie zu erkennen, wann Sie es mit einem Intra-Parallelism Deadlock zu tun haben. Der Graph eines solchen Deadlocks verrät, dass es sich immer um die gleiche Session ID handelt, die Abfrage folglich selbst in einen Deadlock gelaufen ist. Dies ist möglich, weil ein Ausführungsplan eines solchen Deadlocks immer mit mehreren Worker-Threads umgesetzt wird, die schlussendlich gegenseitig aufeinander warten und dadurch den Deadlock verursachen.Trotzdem lässt sich ein solcher Deadlock vermeiden, denn ein Intra-Parallelism Deadlock tritt ausschließlich in parallelen Ausführungsplänen auf. Sorgen Sie also dafür, dass solche Abfragen seriell mit nur einem Worker-Thread ausgeführt werden. Das klappt auf zweierlei Arten:- Anpassen der Indizierungsstrategie, damit sichergestellt werden kann, dass sich die Gesamtkosten des Ausführungsplanes unterhalb des Cost Threshold for Parallelism [4] bewegen.
- Verwenden des Query Hints MAXDOP 1, da Sie dadurch einen seriellen Ausführungsplan erzwingen.
Read Committed Snapshot Isolation
In der vorigen Ausgabe der dotnetpro haben Sie Optimistic Concurrency und die beiden neuen Transaction Isolation Level Read Committed Snapshot Isolation und Snapshot Isolation kennengelernt [2].Die Grundidee hinter Optimistic Concurrency ist, dass lesende Vorgänge – also SELECT-Statements – keine Shared Locks mehr anfordern. Und dieses Verhalten kann Ihnen ebenfalls beim Eliminieren von Deadlocks helfen.Wie Sie auf den vorangegangenen Seiten gelesen haben, treten Deadlocks sehr oft zwischen lesenden und schreibenden Transaktionen auf. Wenn Sie bei lesenden Transaktionen die Shared Locks eliminieren, verhindern Sie zugleich die Deadlocks zwischen diesen beiden Datenzugriffsarten.Registrieren Sie daher sehr viele Deadlocks zwischen lesenden und schreibenden Transaktionen und haben auch keine direkte Möglichkeit, diese zu beheben, schalten Sie einfach mal Read Committed Snapshot Isolation für die betreffende Datenbank ein – das kann Wunder bewirken.Fazit
Die wichtigste Nachricht dieses Artikels: Der SQL Server kann über seinen Deadlock Monitor Blockaden selbstständig erkennen und auflösen. Generell gilt, dass Sie möglichst immer in derselben Reihenfolge auf Tabellen und Daten zugreifen sollten. Dies verhindert viele Deadlocks bereits im Vorfeld. Kann diese Regel nicht befolgt werden, kann auch das Aktivieren von Read Committed Snapshot Isolation sehr viele Deadlocks beseitigen.Fussnoten
- Klaus Aschenbrenner, SQL Server Concurrency, Teil 1, Pessimistic Concurrency, dotnetpro 2/2017, Seite 84 ff., http://www.dotnetpro.de/A1702SQLServer
- Klaus Aschenbrenner, SQL Server Concurrency, Teil 2, Optimistic Concurrency, dotnetpro 3/2017, Seite 80 ff., http://www.dotnetpro.de/A1703SQLServer
- Klaus Aschenbrenner, Nachschlagen mit Köpfchen, dotnetpro 3/2015, Seite 94 ff.,, http://www.dotnetpro.de/A1503TSQL
- Cost Threshold for Parallelism,, http://www.dotnetpro.de/SL1704SQLServer1
