Versteckte Exceptions in T-SQL fangen

Kürzlich bin ich auf stackoverflow auf die Frage gestoßen, wie man in T-SQL "versteckte" Exceptions abfangen kann. Ich habe dazu eine Lösung mit Extended Events vorgeschlagen, die ich hier noch etwas ausführlicher beschreibe.

Ausgangslage

In T-SQL können Anweisungen manchmal mehrere Exceptions auslösen. Mit dem TRY/CATCH-Mechanismus von T-SQL lässt sich jedoch normalerweise nur die letzte dieser Exceptions begutachten, die leider nicht immer die informativste ist. Als Beispiel kann ein einfaches BACKUP dienen:

BEGIN TRY
  BACKUP DATABASE Test TO DISK = 'C:	est.bak'
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH

Falls das SQL Server Dienstkonto keinen Zugriff auf C: hat (was der Fall sein sollte) oder dort zufällig schon eine auf Read-Only gesetzte Datei "test.bak" liegt, bekommt man die Ausgabe

3013 BACKUP DATABASE is terminating abnormally.

Das ist zwar zutreffend, aber wenig hilfreich bei der Fehlersuche.

Deutlich erhellender ist die Ausführung des reinen BACKUP-Codes im Management Studio. Dies produziert folgende Meldungen

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device 'C: est.bak'. Operating system error 5(Zugriff verweigert).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Es gibt also mindestens einen hilfreichen Exception-Text, aber wie kommt man von T-SQL aus dort heran?

Extended Events

Die Lösung in diesem Falle lautet "Extended Events". Extended Events (EE) sind der mit SQL Server 2008 eingeführte Nachfolger von SQL Traces, deren bekannteste Anwendung wohl der SQL Profiler sein dürfte. EE bieten aber höhere Flexibilität, mehr Performance und mehr Details aus dem Inneren der Engine. Weitere allgemeine Details gibt es u.a. hier, ich möchte mich jedoch auf den konkreten Anwendungsfall beschränken.

Um EE zu fangen, braucht man eine sog. "Event Session". Diese läuft global auf der SQL Server Instanz, man muss sie also nur einmal einrichten. Für den hier angepeilten Zweck sollte das SQL dafür etwa so aussehen:

CREATE EVENT SESSION ErrorCatcher ON SERVER 
  ADD EVENT sqlserver.error_reported( 
   ACTION(sqlserver.session_id,sqlserver.sql_text) 
     WHERE (severity >= 16)) 
  ADD TARGET package0.ring_buffer(SET max_events_limit=(50)) 
  WITH (STARTUP_STATE=ON, MAX_DISPATCH_LATENCY = 1 SECONDS)

Hierdurch wird eine Session namens ErrorCatcher eingerichtet. Sie lauscht auf den Event error_reported aus dem sqlserver-Event-Package und fängt neben den Basisdaten dieses Events zusätzlich noch die session_id und den auslösenden sql_text ein. Es werden alle Fehler mit severity kleiner als 16 ignoriert und die gefangenen Events werden in einen Ringpuffer im Hauptspeicher geschrieben, der immer die letzten 50 Events hält. Wird der SQL Server gestartet, startet sich auch die Session mit und gefangene Events sollten nach spätestens einer Sekunde in den Ringpuffer geschrieben werden – vorher werden sie ggf. noch vom Eventsystem gepuffert, eine der Performance steigernden Maßnahmen.

Generiert habe ich diesen Code übrigens mit Hilfe des Extended Event Wizards, den es ab dem Management Studio 2012 unter "Management" gibt.

Die frisch generierte Session ist noch nicht gestartet – das STARTUP_STATE=ON bezieht sich nur auf das Verhalten beim Instanz-Neustart. Los geht es mit

ALTER EVENT SESSION ErrorCatcher ON SERVER STATE = START

Führt man nun das fehlerhafte BACKUP noch einmal aus, verhält sich zunächst alles wie gehabt. Im Hintergrund wurden nun aber die geworfenen Exceptions aufgezeichnet. Ein XML mit dem gesamten aktuellen Inhalt des Ringpuffers erhält man mit

SELECT CAST(xet.target_data AS xml) 
FROM sys.dm_xe_session_targets AS xet 
  INNER JOIN sys.dm_xe_sessions AS xe 
    ON (xe.address = xet.event_session_address) 
WHERE xe.name = 'ErrorCatcher' 

Das ist noch etwas unhandlich, man kann es aber mit XQuery bequem auf ein Tabellenformat herunterbrechen. In der Gesamtheit sieht das Backup-Statement samt Fehleranalyse dann wie folgt aus:

DECLARE @startzeit datetime2 = GETUTCDATE() 

BEGIN TRY 
  BACKUP DATABASE Test TO DISK = 'C:	est.bak' WITH INIT 
END TRY 
BEGIN CATCH 
  WAITFOR DELAY '00:00:01' -- Wait for MAX_DISPATCH_LATENCY 

  DECLARE @target_data XML 
  SELECT @target_data = CAST(xet.target_data AS xml) 
  FROM sys.dm_xe_session_targets AS xet 
    INNER JOIN sys.dm_xe_sessions AS xe 
      ON (xe.address = xet.event_session_address) 
  WHERE xe.name = 'ErrorCatcher' 

  SELECT 
    n.value('(@timestamp)[1]', 'datetime2') 
      AS [timestamp], 
    n.value('(data[@name="error_number"]/value)[1]', 'int') 
      AS Error,
    n.value('(data[@name="message"]/value)[1]', 'nvarchar(max)') 
      AS Message
  FROM @target_data.nodes('RingBufferTarget/event') AS q(n) 
  WHERE 
    @@SPID = n.value('(action[@name="session_id"]/value)[1]', 'int') 
    AND n.value('(@timestamp)[1]', 'datetime2') >= @startzeit 
END CATCH

Man beachte, dass die Basiswerte des Events aus den data-Elementen kommen, während die über die ACTION-Klausel der Session zusätzlich eingefangenen Werte sich in den ansonsten gleich aufgebauten action-Elementen finden.

Da die Eventsession dauernd und serverweit Fehlermeldungen einfängt, ist in dem finalen WHERE noch eine Einschränkung auf die aktuelle Session-ID gesetzt, sowie eine zeitliche Einschränkung, damit man keine alten Events zu sehen bekommt.

Das Ergebnis dieses Codes sieht dann so aus:

Timestamp    Error    Message
2013-12-03 20:44:38.0440000 18204    BackupDiskFile::CreateMedia: Backup device ‘C: est.bak’ failed to create. Operating system error 5(Zugriff verweigert).
2013-12-03 20:44:38.0440000 3201 Cannot open backup device ‘C: est.bak’. Operating system error 5(Zugriff verweigert).
2013-12-03 20:44:38.0440000 3041    BACKUP failed to complete the command BACKUP DATABASE Test. Check the backup application log for detailed messages.
2013-12-03 20:44:38.0440000 3201    Cannot open backup device ‘C: est.bak’. Operating system error 5(Zugriff verweigert).
2013-12-03 20:44:38.0450000 3013    BACKUP DATABASE is terminating abnormally.

Wie man sieht, gibt es intern gleich eine ganze Error-Kaskade, aber man kann sich ja über die Fehlernummern die passenden herausfischen. Es gehen jedenfalls keine Meldungen mehr verloren 🙂

Alternative Einstellungen

Um keine dauernd mitlaufende Session zu haben, bzw. um auf einem vielbeschäftigten Server nicht zu viele Events zu bekommen, sind noch verschiedene Varianten denkbar.

  • Auf einem Server mit vielen Datenbanken kann man noch eine Event-WHERE-Bedingung auf die Datenbank(-ID) setzen und ggf. mehrere Sessions für verschiedene Datenbanken einrichten.
  • Der Ringpuffer kann auf vergrößert werden, wobei das nicht ganz unproblematisch ist, weil man bei jeder Abfrage immer den gesamten Inhalt zurückbekommt.
  • Bei längerlaufenden Operationen wie dem BACKUP im Beispiel kommt es auf ein paar Millisekunden nicht an. Man könnte daher auch direkt im Code ein passgenaues CREATE SESSION mit einem WHERE (session_id = @@SPID) machen und würde so der "Verschmutzung" des Puffers durch Fehler anderer User entgehen. Allerdings braucht man für die Ausführung des Codes dann ALTER SERVER STATE-Rechte.
Fazit

Extended Events sind flexible, mächtige und nützliche Diagnosewerkzeuge. Es ist ein wenig wie mit der Powershell: Man hat anfangs eine gewisse Lernkurve, weil das Thema nicht intuitiv zugänglich ist, hat dann aber nach etwas Übung jeweils ein sehr mächtiges Werkzeug an der Hand.

Lokale TempDb im SQL Server Cluster

Eines meiner Lieblingsfeatures im SQL Server 2012 ist die offizielle Unterstützung der TempDb auf lokalen Laufwerken im Clusterbetrieb (AlwaysOn Failover Cluster).

Dadurch lässt sich selbst bei moderater TempDb-Belastung ein schneller und preiswerter Performancegewinn erzielen.

Clusterlaufwerke

Im Enterprisebereich kommen oft SQL Server Failovercluster zum Einsatz, um hohe Verfügbarkeit und bei Geoclustern auch K-Fall Sicherheit zu erreichen. SQL Server Cluster setzen allerdings bisher geclusterte Laufwerke voraus, also Datenspeicher, die entweder von allen Clusternodes gemeinsam genutzt oder für das Betriebssystem transparent synchron repliziert werden.

Der Grund hierfür ist, dass bei einem Schwenk einer SQL Server Instanz von einem Clusternode auf einen anderen diese neue Instanz dort exakt denselben Zustand ihrer Datenbankdateien vorfinden muss wie vor dem Schwenk. Sonst kann der Erhalt der Datenbankkonsistenz bei einem Schwenk nicht gewährleistet werden.

TempDb

Die Ausnahme von dieser Regel war schon immer die TempDb. Diese wird grundsätzlich bei jedem Instanzstart (also auch bei Clusterschwenks) neu initialisiert und braucht daher nicht persistiert werden. Da diese Datenbank oft auch stark belastet wird (z.B. durch Sortier-, Hash- und Spool-Operationen oder durch eingeschalteten Version-Store), haben findige DBAs Wege gefunden, die TempDb auf lokale Laufwerke auszulagern. Der große Haken an diesem Vorgehen war, dass dieses Vorgehen von Microsoft nicht supported wurde. Das hat nun mit SQL Server 2012 ein Ende.

Die TempDb braucht bestmögliche Performance und ist meist nicht besonders groß. Das sind ideale Voraussetzungen für den Einsatz von Solid-State-Disks (SSD). Selbst über einen lokalen SATA-Port angebunden bringen diese Laufwerke aufgrund der hohen IOPS Zahlen bereits einen beträchtlichen Performanceschub. Dieser kann durch den Einsatz von PCI-Express-Lösungen bei Bedarf noch einmal um eine Größenordnung gesteigert werden.

Eine lokale Anbindung der TempDb entlastet weiterhin auch den jeweiligen HBA zum Clusterlaufwerk, sodass mehr Bandbreite für die Logs und Datenbankdateien zur Verfügung steht.

Einrichtung

In SQL Server 2012 kann man bei der Einrichtung des ersten Clusternodes die Pfade zu den Basisdatenbanken angeben. Der Installer prüft, ob es sich dabei um Clusterlaufwerke handelt. Falls das TempDb-Laufwerk kein Clusterlaufwerk ist, wird jetzt nur eine Warnung ausgegeben. Diese besagt, dass auf den anderen Nodes der hier angegebene lokale Pfad ebenfalls existieren muss (und das der SQL Server Service dort Read/Write Rechte hat). Ansonsten würde ein Clusterschwenk fehlschlagen.

2817_localtempdb4[1]

Bei der Konfiguration mit dem lokalen Laufwerk kann man sich sogar überlegen, ob man auf RAID-Redundanz verzichtet. Persistente Daten werden dort nicht gespeichert und eine Redundanz ist durch das zusätzliche lokale Laufwerk auf den anderen Clusternodes gegeben. Wenn hardwareseitig nichts dagegen spricht, kann aber auch das lokale Laufwerk per RAID-1 Mirror gegen Ausfall abgesichert werden. Im Falle von SSDs ist dann allerdings auf ausreichende Performance des RAID-Controllers zu achten. Ältere Controller sind mit der hohen Performance einer SSD oft schlicht überfordert und bremsen diese aus oder produzieren schlimmstenfalls sogar Fehler. Und selbst aktuell erhältliche Controller garantieren kein optimales Ergebnis zumal auch die SSD-Performance rapide weiter wächst.

Fazit

Die Möglichkeit, die TempDb auf lokalen Laufwerken zu betreiben, ist ein sehr sinnvolles Feature, das nach Möglichkeit ausgenutzt werden sollte. Selbst wenn die TempDb aktuell kaum belastet wird, kann sich das beim nächsten Queryplan schon ändern. Wann immer möglich sollte dafür eine dedizierte SSD zum Einsatz kommen.

SQL Server Locking und Blocking

div class=”articleAbstract”>SQL Server Locking und Blocking – wie funktioniert es, wie kann ich es beeinflussen und warum muss ich das eigentlich wissen?
Disclaimer: Das ist ein komplexes Thema und obwohl ich bei weitem nicht alle Feinheiten erkläre, wird es ein längerer Artikel, also anschnallen und ein wenig Zeit mitbringen 🙂

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?