Wozu brauche ich Kenntnisse über Locking und Blocking?

Als Datenbankentwickler oder -administrator kann man bei einfach gestrickten Datenbanken mit wenigen Usern oft mit Grundkenntnissen über SQL Server und T-SQL auskommen. Mit zunehmender Größe und Komplexität der Datenbanken und insbesondere mit steigender Zahl paralleler Zugriffe wird jedoch Hintergrundwissen über die internen Abläufe in der Engine immer wichtiger. Langsam laufende Queries, Dateninkonsistenzen und Deadlocks sind oft schwierig zu reproduzieren, weil sie nur unter Last und bei parallelen Zugriffen auftreten.
In solchen Fällen hilft das Wissen über Locking und Blocking bei der statischen und dynamischen Codeanalyse, beim Setzen von Lock-Hints, bei der Wahl des Transaktionslevels sowie von Datenbank- und Indexoptionen.

Locktypen

Hier zunächst eine Übersicht über die Namen der wichtigsten Locktypen:

• (S)hared Locks: Werden von Leseoperationen verwendet und verhindern Schreibzugriffe auf die Ressource. Mehrere S-Locks können gleichzeitig gesetzt sein.
• (U)pdate Locks: Sind eine Vorstufe für Schreibzugriffe. Können auch gesetzt werden, wenn schon ein S-Lock existiert und verhindern Schreibzugriffe und weitere U-Locks.
• (X)clusive Locks: Vor einem Schreibzugriff muss grundsätzlich ein X-Lock gesetzt werden. Kein anderes Lock darf auf der Ressource vorhanden sein.

Eine vollständige Übersicht gibt es hier.
Locks werden intern in einer Tabelle gespeichert, die neben dem Locktyp die Art der gelockten Ressource beschreibt sowie ggf. einen Identifier der Ressource. So enthalten z.B. Tablelocks die ObjectId der Tabelle, Pagelocks die PageId.
Rowlocks auf einzelne Daten- oder Indexzeilen können auf zwei Arten realisiert werden: Falls es um einen Indexeintrag (Row) geht, ist der Locktyp “KEY” und der Identifier ist ein 6-Byte Hash des Indexkeys. Falls eine Tabelle keinen Clustered Index hat, wird statt dieses Keyhashes die Record-Id (RID) verwendet.

Basisbeispiele

SELECT

Als Ausgangsbeispiel soll ein einfaches SELECT auf einen Clustered Index dienen, z.B. auf die Person-Tabelle aus der AdventureWorks-DB:
SELECT LastName FROM Person.Person WHERE BusinessEntityID = 1
Die produzierten Locks kann man schön mit dem SQL-Profiler sehen. Dazu nimmt man das TSQL-Locks-Template als Basis, nimmt die Lock:Acquired und Lock:Released Events dazu und schränkt die SPID auf die gewünschte Session ein. Die interessanten Spalten sind EventClass, Mode, Type, ObjectID und TextData. Damit man nicht die ganzen Schema-Stability-Locks für den Compile-Vorgang mitbekommt, sollte man das Statement vorher schon einmal ausgeführt haben.
Als Ergebnis bekommt man hier drei Locks:

• Zunächst ein IS(Intent Shared)-Lock auf die ganze Tabelle. Ein Intent-Lock ist ein Hinweis für SQL-Server, dass auf niedrigeren Ebenen weitere Locking-Aktivität stattfindet. In diesem Fall verhindert es z.B. effizient, dass eine andere Connection sich ein X-Lock auf die ganze Tabelle holt.
• Ein weiteres IS-Lock auf die Page mit dem fraglichen Datensatz. Die beiden übergeordneten Pages in der Clustered Index-Hierarchie (Root und Intermediate Level) werden nicht gelockt.
• Schließlich ein S-Lock auf die Row mit dem Datensatz, genauer gesagt auf den Hash des Cluster-Keys, wie oben beschrieben.

Anschließend werden die Locks in umgekehrter Reihenfolge wieder freigegeben.
Modifiziert man die Query mit WITH (PAGLOCK), entfällt der S-Lock auf die Row, stattdessen wird ein S-Lock auf die Page genommen. Analog wird mit WITH (TABLOCK) nur noch ein Lock erzeugt, ein S-Lock auf die ganze Tabelle.

UPDATE

Als nächstes Beispiel sehen wir uns ein einfaches UPDATE an:
UPDATE Person.Person SET Title='Mr.' WHERE BusinessEntityID = 1
Auch hier werden drei Locks gesetzt, in der gleichen Reihenfolge und auf die gleichen Objekte wie oben, nur werden jetzt statt IS/S-Locks IX/X-Locks verwendet, da es sich um einen Schreibzugriff handelt. IX/X-Locks werden grundsätzlich bis zum Ende der Transaktion gehalten, während andere Locks je nach Transaktionslevel schon früher wieder freigegeben werden. Mehr dazu bei der Beschreibung der Transaktionen.

Konsistenzgarantien

Was ist der Zweck dieser Locks? Warum genau diese, nicht mehr und nicht weniger? Der Default-Transaktionslevel, der hier verwendet wurde, ist “READ COMMITED”. Er garantiert, dass keine Daten anderer Transaktionen gelesen werden, die zwar bereits physisch geschrieben, aber noch nicht committed wurden. Wenn etwas die Daten in der Person-Tabelle verändern will, setzt SQL Server vorher zwingend ein X-Lock. Wohin dieses Lock gesetzt wird, ist aber situationsabhängig, es kann entweder auf die Row, die Page oder die Tabelle gesetzt werden. Die drei Locks der Leseoperation stellen sicher, dass keiner dieser Fälle zum Zeitpunkt des Lesens vorliegt. Damit wird die Garantie der “READ COMMITED”-Transaktion erfüllt.

Blocking

Falls ein Lock aufgrund eines bereits gesetzten, inkompatiblen Locks nicht gesetzt werden kann, kommt es zum sog. Blocking. D.h. die Transaktion wartet bis das gewünschte Lock verfügbar wird. Alle anderen Lock-Requests auf diese Ressource werden dann in eine Warteschlange eingereiht, selbst wenn sie mit dem gerade gesetzten Lock kompatibel wären!
Der Sinn hinter dieser Maßnahme ist das Verhindern von “lock starvation”. Angenommen, auf unserer BusinessEntityID 1 findet eine Menge Leseaktivität statt und jetzt möchte eine Transaktion ein Update machen. Es befinden sich also mehrere (kompatible) S-Locks auf der Row, dann kommt ein X-Lock-Request vom Update daher, der warten muss, bis die vorhandenen S-Locks freigegeben sind. Während der Wartezeit kommen weitere Lese-Requests. Diese könnten im Prinzip ausgeführt werden, aber dann wartet das X-Lock bis zum Sankt-Nimmerleinstag (na gut, bis zum Transaktions-Timeout).

Deadlocks

Durch das Blockingverhalten kann es auch zu Deadlocks kommen, im einfachsten Fall wenn Transaktion A auf eine Ressource wartet, die von Transaktion B gelockt ist, Transaktion B aber ihrerseits auf eine von A gelockte Resource wartet. Der SQL Server erkennt die meisten dieser Situationen und lässt eine der Transaktionen fehlschlagen (und zwar die laut Queryplan kostengünstigere).

Lock Escalation

Ein weiteres Phänomen kann auftreten, wenn eine Transaktion sehr viele Locks setzt, etwa ein Update für viele Datensätze einer großen Tabelle. Hier würden standardmäßig viele Row- oder Pagelocks gesetzt, was jedoch viel Speicher und CPU verbraucht. Daher versucht der SQL Server nach Erreichen bestimmter Schwellwerte die ganzen “kleinen” Locks durch ein Tablelock zu ersetzen. Falls das nicht funktioniert, geht es zunächst mit Row/Page-Locks weiter, in Abständen wird aber weiterhin versucht, ein Tablelock zu bekommen. Mehr zu den Schwellwerten und was bei partitionierten Tabellen geschieht hier
Es ist bei größeren Datenbanken generell eine gute Idee, Lock Escalations via Performance Counter, bei auftretenden Problemen auch per Profiler oder Extended Events zu monitoren. Gleiches gilt für Deadlocks.

Einflussmöglichkeiten auf das Lockingverhalten

Neben dem Transaktionslevel (dazu gleich mehr) gibt es diverse Möglichkeiten, Einfluss auf das Locking zu nehmen. Beinflussen lassen sich Granularität, Zeitraum und Modus der gesetzten Locks. Wenn z.B. ein UPDATE sehr viele (genauer geht es nicht, es kommt immer auf den Einzelfall an) Datensätze einer großen (dito) Tabelle ändert, sollte man überlegen, ob man nicht gleich einen TABLOCKX-Hint auf das Statement setzt, statt auf die Lock-Escalation zu warten.
Prinzipiell unterscheiden lassen sich noch Hints auf DML-Ebene (z.B. UPDLOCK, ROWLOCK, HOLDLOCK etc.) und für DDL-Statements (z.B. bei CREATE INDEX mit ALLOW_ROW_LOCKS und ALLOW_PAGE_LOCKS).

Transaktionen

Die wichtigste Einflussmöglichkeit auf das Locking ist der Transaktionslevel. Hier eine kurze Zusammenfassung der verschiedenen Verhaltensweisen:

• READ_UNCOMMITED: Setzt keine IS- oder S-Locks. Wenig Blocking, kann aber inkosistente (uncommitted) Daten lesen.
• READ_COMMITED: Standardlevel. Setzt IS/S-Locks auf Lesevorgänge, gibt sie nach jedem Lesevorgang sofort wieder frei. Geringes Blocking.
• REPEATABLE_READ: Setzt die gleichen Locks wie READ_COMMITED, hält sie aber bis zum Ende der Transaktion. Potentiell starkes Blocking bei langen Transaktionen. Achtung Fallstrick: Dieser Level bedeutet nicht, dass ein mehrfach ausgeführtes SELECT immer dasselbe Ergebnis liefert! Es werden zwar gelesene Datensätze vor Updates geschützt. Inserts oder Updates, die weitere Datensätze in den WHERE-Bereich fallen lassen, können aber konkurrierend durchgeführt werden und verändern somit das Ergebnis bei einer erneuten Abfrage.
• SERIALIZABLE: Neben Locks auf einzelne Datensätze werden bei Bereichsabfragen hier noch zusätzlich Key-Range-Locks gesetzt, um genau die oben beschriebene Falle aus REPEATABLE_READ zu verhindern. Das hat potentiell weitreichende Konsequenzen, insbesondere wenn nicht indizierte Spalten in WHERE-Bedingungen auftauchen. Um zu verhindern, dass ein anderes Insert/Update/Delete das WHERE-Ergebnis beeinflusst, muss dann auf jede(!) Zeile der Tabelle ein Range-Lock gesetzt werden. Lock Escalation, here we come…
• SNAPSHOT: Dieser interessante Level garantiert eine wiederholte Lesbarkeit, ähnlich wie SERIALIZABLE, benutzt dafür aber keine IS/S-Locks, d.h. Modifikationen durch andere Transaktionen werden nicht verhindert! Klingt gut, ist aber nicht kostenlos, da es eine Vorbereitung der Datenbank benötigt (ALLOW_SNAPSHOT_ISOLATION ON). Kurz gesagt wird hiermit eine Versionierung der Rows eingeführt, was die Belastung der tempdb beträchtlich erhöhen kann und auch zusätzlichen Platz in den Indices belegt.
• RCSI (Read Committed Snapshot Isolation): Hier handelt es sich nicht um einen Transaktionslevel, sondern um eine Datenbankoption (nicht zu verwechseln mit ALLOW_SNAPSHOT_ISOLATION), die das Locking-Verhalten von READ_COMMITTED-Transaktionen modifiziert. Diese setzen jetzt keine IS/S-Locks mehr, verhindern folglich auch keine parallelen Updates, stattdessen wird auch hier der Version-Store in der tempdb genutzt. Das Lock-Verhalten der anderen Transaktionslevel wird nicht beeinflusst!

Beispiele

Nach der ganzen Theorie hier noch ein paar praktische Anwendungen.

NOLOCK

Im administrativen Alltag muss sich zuweilen einen groben Überblick über Tabelleninhalte verschaffen, ohne dass man aber Rücksicht auf Konsistenz nehmen muss. Hier bietet sich an, bei den betreffenden SELECTs den WITH (NOLOCK) Hint zu verwenden, um keine laufenden Transaktionen zu blockieren, bzw. selbst blockiert zu werden. Oder man stellt gleich zu Beginn seiner Session READ_UNCOMMITTED ein.

UPDLOCK

Zuweilen kommt es vor, dass man einen gelesenen Datensatz nicht gleich modifiziert, sondern erst später in einer Transaktion. Falls die Modifikation abhängig von dem Leseergebnis ist, muss man verhindern, dass der Datensatz zwischen Lesen und Update modifiziert wird. REPEATABLE_READ würde das leisten, kann jedoch Deadlocks produzieren, falls der Datensatz von mehreren Sessions aktualisiert wird. Außerdem werden in diesem Modus potentiell unnötige Leselocks gesetzt. Beide Probleme erschlägt man, indem man bei READ_COMMITTED bleibt und stattdessen in dem lesenden SELECT sowohl UPDLOCK als auch HOLDLOCK verwendet. Ersteres verhindert die Deadlocks, indem ein paralleler Zugriff geblockt wird, das HOLDLOCK hält das Lock bis zum Ende der Transaktion, es würde auf READ_COMMITTED sonst sofort wieder freigegeben.
Die beschriebene Deadlock Problematik kann übrigens sogar bei SERIALIZABLE auftreten, auch in diesem Fall schafft das UPDLOCK aber Abhilfe.

READPAST

Zuweilen kommt es vor, dass man einzelne Datensätze “verbrauchend” aus einer Tabelle lesen möchte, im einfachsten Fall so:
DELETE TOP (1) FROM MyTable OUTPUT DELETED.Id INTO @MyIdTableVar
Das DELETE setzt mindestens ein X-Lock auf den betreffenden Datensatz. Soll dieser Vorgang mit mehreren Transaktionen parallel durchgeführt werden, blockiert das X-Lock den Lesevorgang auf der Tabelle, es tritt also ein ungewollter Serialisierungseffekt ein. Dieser lässt sich durch den READPAST-Hint verhindern:
DELETE TOP (1) FROM MyTable WITH (READPAST) OUTPUT DELETED.Id INTO @MyIdTableVar
Dieser zuweilen sehr nützliche Hinweis bewirkt, dass Lesevorgänge nicht durch bestehende Locks geblockt werden, die betreffenden Datensätze werden einfach übergangen. In diesem Beispiel lässt sich die Parallelität der Ausführung damit massiv erhöhen.

Fazit

Mit Kenntnissen über das Locking- und Blockingverhalten des SQL-Servers lassen sich viele Probleme, insbesondere Deadlocks, bereits im Vorfeld entschärfen ohne dabei zu viel Performance zu opfern. Wenn es doch einmal zu ungewollten Blocksituationen oder Deadlocks kommt, können diese Kenntnisse die Problemanalyse und -lösung beträchtlich beschleunigen.
P.S.: Da ich plane, weitere Artikel zu den SQL Server Interna zu schreiben, würde ich mich über Feedback zu der gewünschten “Tiefe” freuen. Bin ich hier z.B. “zu tief” in die Details gegangen, oder darf es noch etwas mehr sein?