Auf Grund einer Anfrage des geschätzten Kollegen Johannes Curio (e | w), die sich um Sperren von Objekten in einem HEAP drehte, beschäftigt sich dieser Artikel mit dem Sperrverhalten von Microsoft SQL Server, da die grundsätzliche Frage war, ob Microsoft SQL Server in einem HEAP jede Datenseite / jeden Datensatz nach dem Scannen sofort wieder freigibt. Die Antwort – wie meistens bei Microsoft SQL Server – … “It depends”. Dieser Artikel beschreibt die unterschiedlichen Sperrverhalten bei SELECT-Statements unter Berücksichtigung der verschiedenen ISO-Isolationsstufen in einem HEAP und in einem Clustered Index.

Testumgebung

Alle Tests verwenden einen HEAP, der mit 1.000 Datensätzen gefüllt wird. Zwei Fragen sollen mit den nachfolgenden Tests beantwortet werden:

• Sperrt Microsoft SQL Server bei einem SELECT in einem HEAP jeden Datensatz?
• Wird nach dem Lesen einer Ressource unmittelbar eine Freigabe der Ressource ausgelöst?

-- Erstellen der Demotabelle
CREATE TABLE dbo.Customer
(
    Id INT NOT NULL IDENTITY(1,1),
    Name CHAR(100) NOT NULL,
    Ort CHAR(100) NOT NULL
);
GO

-- Füllen der Demotabelle mit 1.000 Datensätzen
DECLARE @i INT = 1;
WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.Customer (Name, Ort)
    VALUES ('Kunde ' + CAST(@i AS VARCHAR(10)), 'Frankfurt am Main');

    SET @i += 1;
END
GO

Die Tabelle hat 1.000 Datensätze und besitzt keinen Index. Für die Tests mit unterschiedlichen ISO-Isolationsstufen reicht dieses einfache Modell aus.

Hinweis(e)

Der Fokus dieses Artikels liegt in dem Sperrverhalten bei SELECT-Statements unter Berücksichtigung verschiedener ISO-Isolationsstufen; viele andere – aber ebenso wichtige – Details können nur bedingt in dieser Tiefe beschrieben werden, da sie sonst den Rahmen dieses Artikels sprengen würden; es werden jedoch weiterführende Links zu den entsprechenden Themen / Objekten genannt oder aber die Fachausdrücke unmittelbar mit Links versehen! Ein wichtiger Link sei bereits vorab genannt – die unterschiedlichen Sperren, die von Microsoft SQL Server verwendet werden können können unter “Kompatibilität von Sperren” genauer studiert werden. Dieser Artikel beleuchtet nicht die Sperr-Möglichkeiten auf Statement-Level (ROWLOCK / TABLOCK, …) sondern ausschließlich das automatische Sperrverhalten von Microsoft SQL Server unter den verschiedenen ISO-Isolationsstufen.

Protokollierung mit Microsoft SQL Server Profiler

In Zeiten von “Extended Events” ist der SQL Server Profiler sicherlich nicht mehr das zeitgemäße Mittel; aber für die benötigte Protokollierung ist Microsoft SQL Server Profiler vollkommen ausreichend und er bietet ideale lesbare Ergebnisse.

Warum eine dedizierte Protokollierung?

Das Problem von Shared Locks besteht bei einigen ISO-Isolationsstufen darin, dass man sie nicht “sichtbar” machen kann, wenn man eine explizite Transaktion beginnt, ein SELECT ausführt und diese Transaktion geöffnet lässt. Eine Kontrolle der gesetzten Sperren ist schwierig und nur während der Ausführung des Befehls in einer SQL Server Profiler-Sitzung oder mit Extended Events zu kontrollieren.

Konfiguration der Microsoft SQL Server Profiler Sitzung

Während der Ausführung der SQL-Abfragen wird aufgezeichnet, wann die Ausführung des Befehls beginnt [SQL:StmtStarting] und wann die Ausführung beendet wird [SQL:StmtCompleted]. Gleichwohl muss aufgezeichnet werden, wann eine Objektsperre gesetzt wird [Lock:Acquired] und wann eine gesetzte Sperre wieder aufgehoben wird [Lock:Released]. Die zu jeder Aktion aufzuzeichnenden Informationen erstrecken sich vom Sperrmodus [Mode], der auf das Objekt [ObjectID] gesetzt wird bis auf die Ressource, die in [TextData] angezeigt wird. Der Sperrtyp wird in der Spalte [Type] angezeigt. Um unnötige Aktionen aufzuzeichnen, empfiehlt es sich, auf [SPID] einen Filter zu setzen, um ausschließlich Aktionen der Verbindungen zu filtern, in der die Abfragen ausgeführt werden. Wer mehr über die Konfigurationsmöglichkeiten des Microsoft SQL Server Profilers wissen möchte, wird hier fündig.

Alternative Ausgabe mittels TRACEFLAGS

Die Ausgabe von Sperren (ähnlich wie in der Aufzeichnung mit dem Profiler oder Extended Events) kann man unmittelbar in SQL Server Management Studio ausgeben lassen; natürlich sind es auch hier wieder Traceflags, die solche Dinge ermöglichen, wie der folgende Code zeigt:

/* Ausgabe von Sperrinformationen in SSMS */
DBCC TRACEON(-1, 3604, 1200) WITH NO_INFOMSGS;
GO

TF 3604 leitet Ergebnisse nicht in das Fehlerprotokoll des Microsoft SQL Server sondern in die Ausgabe von SQL Server Management Studio und TF 1200 aktiviert die Ausgabe von Sperrinformationen. Bitte darauf achten, dass TF 1200 nicht dokumentiert ist und somit auch nicht in einem Produktivumfeld eingesetzt werden sollte. Weiterhin gilt es zu beachten, dass – je nach Anzahl der Sperren – eine sehr große Datenmenge an den Client zurückgeliefert werden kann! Ein weiterer – nicht zu unterschätzender – Punkt, der ganz besonders bei Produktionssystemen beachtet werden sollte; es handelt sich um einen GLOBALEN Traceflag. Die Aktivierung gilt also nicht nur für die aktuelle Session sondern für alle Sessions, die auf dem Microsoft SQL Server ausgeführt werden.

Isolationsstufe “READ COMMITTED”

Die Isolationsstufe “READ COMMITTED” ist die Standard-Isolationsstufe für Datenbanken in Microsoft SQL Server. In der Isolationsstufe READ COMMITTED werden ausschließlich Datensätze gelesen, die VOR dem Lesevorgang mittels COMMIT in der Datenbank gespeichert worden sind. Durch dieses Verfahren werden “Dirty Reads” vermieden. Im Microsoft SQL Server Profiler werden bei Ausführung der hoch selektiven Abfrage (es wird 1 Datensatz ausgegeben!) folgende Sperrverhalten aufgezeichnet (Auszug):

Das Ergebnis zeigt den Beginn der Aufzeichnung mit dem Absetzen des eigentlichen SQL Befehls. Anschließend wird ein “Intent Shared Lock (IS)” auf die Tabelle gesetzt. Ein IS-Lock wird von Microsoft SQL Server IMMER gesetzt, um eine – mögliche – Lock-Eskalation durchführen zu können. Mit einer IS-Sperre signalisiert Microsoft SQL Server, dass diese Objekte nicht durch andere konkurrierende / inkompatible Sperren blockiert werden können.
In der Isolationsstufe “READ COMMITTED” werden gesetzte Shared Locks unmittelbar nach dem Scan der Ressource wieder freigegeben, damit andere Operationen die Datenseiten verwenden können. Im Isolationsmodus “READ COMMITTED” werden KEINE Datensätze gesperrt! Alle Sperren werden auf Ebene einer Datenseite durchgeführt. Sobald Microsoft SQL Server die Datenseite durchsucht hat, wird die nächste Datenseite durchsucht. Zuvor wird die mit einem Shared Lock versehene Datenseite wieder freigegeben.
Die Sperre auf einer Datenseite – statt einer Datenzeile – macht in einem HEAP Sinn, da kein Index verwendet werden kann. Sofern Microsoft SQL Server nicht gezielt durch Indexe die geforderten Daten anfordern kann, muss die vollständige Datenseite nach passenden Datensätzen durchsucht werden. Durch das Sperren ganzer Datenseiten werden die Ressourcen (RAM / CPU) des Microsoft SQL Server geschont. Eine Zeilensperre ist aber auch in einer indexierten Tabelle nicht erforderlich, da keine Änderungen an den Datenzeilen durchgeführt werden müssen. Nur in den restriktiveren ISO-Isolationsstufen müssen u. U. Zeilensperren verwendet werden (wie die nachfolgenden Beispiele zeigen werden).

Schemastabilität während der Ausführung von Abfragen

Nachdem alle Datenseiten eingelesen wurden, wird eine SCH-S Sperre zur Sicherung der Schemastabilität für die Tabelle selbst gesetzt. Diese Sperre ist für Microsoft SQL Server wichtig, damit Objekte während der Ausführung der Abfrage keinen Strukturänderungen unterzogen werden können. Aus diesem Grund wird VOR der Ausführung der Abfrage eine Sperre zur Schemastabilität gesetzt! Diese besondere Sperre wird ausschließlich bei HEAPS angewendet.

Isolationsstufe “READ UNCOMMITTED”

Die Isolationsstufe “READ UNCOMMITTED” ist nicht zu empfehlen, da sie sogenannte “Dirty Reads” zulässt. Dirty Reads bedeuten, dass ein SQL Befehl auch Daten von Datenseiten liest, die zwar schon geändert aber noch nicht bestätigt wurden (Die Daten wurden innerhalb einer noch nicht abgeschlossenen Transaktion geändert). Wie verhält es sich nun mit den Objektsperren, wenn ein SELECT-Befehl für einen HEAP abgesetzt wird. Die folgende Abbildung zeigt die gesetzten Sperren bei Verwendung der zweiten Abfrage mit der ISO-Isolationsstufe “READ UNCOMMITTED”.

Interessant ist bei diesem Ergebnis, dass – und das gilt ausschließlich für HEAPS – ein Shared Lock auf den “Index” selbst gesetzt wird (12 – HOBT). “HOBT” steht für HeapOrBTree und die Tabelle in unserem Beispiel ist eine Tabelle ohne Indexe (HEAP). Die generelle “SCH-S” Sperre muss gesetzt werden, um Modifikationen an der Tabelle selbst zu verhindern. Dieses Verhalten wird sehr gut in den BOL beschrieben: “Alle Abfragen, auch solche mit READUNCOMMITTED- und NOLOCK-Hinweisen, aktivieren bei der Kompilierung und Ausführung Sperren des Typs Sch-S (Schemastabilität)”.
Die [BULK_OPERATION]-S-Sperre auf den “Index” wird ausschließlich auf HEAPS angewendet und verhindert das Lesen von unformatierten – neuen – Datenseiten, die während des Lesevorgangs durch neue / geänderte Datensatze (FORWARDED RECORDS) generiert werden können. Grundsätzlich unterliegt die Belegung von Speicher durch neue Datensätze in einem HEAP anderen Regeln als in einem Clustered Index (das genaue Verfahren kann man in dem folgenden TECHNET-Artikel hier nachlesen (englisch).

Isolationsstufe “REPEATABLE READ”

Die Isolationsstufe “REPEATABLE READ” gehört zu den restriktiveren Isolationsstufen. “REPEATABLE READ” definiert, dass Anweisungen keine Daten lesen können, die geändert wurden, für die jedoch noch kein Commit von anderen Transaktionen ausgeführt wurde (wie READ COMMITTED) jedoch können darüber hinaus von der aktuellen Transaktion gelesene Daten erst nach Abschluss der aktuellen Transaktion von anderen Transaktionen geändert werden. Um die Ergebnisse besser erklären zu können, wird mit dem nachfolgenden Skript zunächst die exakte Position des Datensatzes mit der Id = 10 ermittelt:

SELECT sys.fn_physlocformatter(%%physloc%%) AS Position, *
FROM dbo.Customer WHERE Id = CAST(10 AS int);

Im gezeigten Beispiel befindet sich der Datensatz mit der Id = 10 auf der Datenseite 163 in Slot = 9. Diese Position wird für die Untersuchung der von Microsoft SQL Server gesetzten Sperren in den engeren Fokus rücken.

Die Auswertung der Aufzeichnung mit Microsoft SQL Server Profiler zeigt, dass zunächst eine IS-Sperre auf die Tabelle selbst gesetzt wird. Anschließend wird sofort eine IS-Sperre auf die erste Datenseite (163) gesetzt. Das nachfolgende Verhalten unterscheidet sich deutlich von den zuvor beschriebenen Isolationsstufen. Tatsächlich wird JEDE Datenzeile zunächst mit einer S-Sperre versehen, um sie nach der Prüfung sofort wieder freizugeben. Dieses Verhalten gilt jedoch nicht für die Datenzeile, die sich auf der Datenseite 163 in Slot = 9 befindet! Die obige Abbildung zeigt, dass ein Shared Lock auf die Datenzeile (RID) angewendet wird, der aber nach der Prüfung nicht wieder freigegeben wird. Die nächste Abbildung der Ergebnisse des Microsoft SQL Server Profilers zeigt, wann eine Freigabe der Datenzeile erfolgt – nachdem die Ausführung der Abfrage abgeschlossen und die Transaktion beendet ist!

Dieses Ergebnis ist schlüssig, wenn man die Vorgehensweise von Sperren bei REPEATABLE READ kennt. Der besondere Unterschied von REPEATABLE READ zu den bisher beschriebenen Isolationsstufen besteht darin, dass bei Microsoft SQL Server in dieser Isolationsstufe sichergestellt, dass ein Datensatz, der innerhalb einer Transaktion gelesen wird, auch bei einem erneuten Lesen innerhalb der gleichen Transaktion identische Daten besitzen muss. Würde Microsoft SQL Server nach dem Lesen des Datensatzes mit der ID = 10 den Datensatz wieder freigeben, wäre folgende Situation möglich:

• Transaktion 1 setzt eine Sperre auf den Datensatz ID = 10, liest den Datensatz und hebt die Sperre wieder auf
• Transaktion 2 bearbeitet nach der Freigabe den Datensatz mit der ID = 10 und ändert z. B. den Ort
• Transaktion 1 setzt erneut eine Sperre auf den Datensatz ID = 10 und liest den Datensatz erneut ein.

Diesmal wären jedoch nicht mehr die ursprünglichen Werte des ersten Lesevorgangs vorhanden sondern durch Transaktion 2 geänderten Daten. Damit solche Änderungen nicht vorkommen können, können Datensätze während der gesamten Transaktion gesperrt werden, die zuvor beschriebenen Isolationsstufen können diese Abgrenzung nicht leisten!

Isolationsstufe “SERIALIZABLE”

Die Isolationsstufe “SERIALIZABLE” ist die restriktivste ISO-Isolationsstufe, die mit Microsoft SQL Server angewendet werden kann. In der ISO-Isolationsstufe “SERIALIZABLE” werden Bereichssperren in den Schlüsselwertbereichen eingerichtet. Dadurch wird verhindert, dass andere Transaktionen Zeilen aktualisieren oder einfügen, die den von der aktuellen Transaktion ausgeführten Anweisungen entsprechen würden. Wie restriktiv die Sperren gesetzt werden, zeigt die nachfolgende Abbildung. Für die Demonstration wird erneut eine Abfrage auf ID = 10 ausgeführt, die lediglich einen Datensatz zurückliefern wird.

Obwohl nur ein Datensatz benötigt wird, muss ein Table Scan durchgeführt werden. Es müssen – wie schon zuvor geschehen – ALLE Datensatze durchsucht werden. Es wird die Tabelle als vollständiger Wertebereich durchsucht! Basierend auf der Tatsache, dass in einem HEAP nicht explizite Schlüsselwerte gesperrt werden können (Ausnahme REPEATABLE READ), wird die vollständige Tabelle gesperrt. Während einer Transaktion in der ISO-Isolationsstufe SERIALIZABLE können in einen HEAP keine weiteren Datensätze eingetragen werden!

Verhalten von Shared Locks in indizierten Tabellen

Das Sperrverhalten von SELECT-Statements ändert sich beim Zugriff auf Indexe, sofern ein INDEX SEEK angewendet werden kann. In diesem Fall werden – je nach Selektivität – nur die Datenseiten / Datenzeilen gesperrt, die durch den INDEX-SEEK gezielt verwendet werden können. Zunächst wird die Tabelle mit einem Clustered Index auf [ID] versehen. Zusätzlich wird ein Index für den Ort angelegt.

CREATE UNIQUE CLUSTERED INDEX ix_Customer_Id ON dbo.Customer (Id);
CREATE INDEX ix_Customer_Ort ON dbo.Customer (Ort) INCLUDE (Name);

Die nachfolgenden SQL-Statements mit ihren jeweiligen Aufzeichnungen im Profiler zeigen, wie sich das Sperrverhalten ändert. Die Ausführungen werden aber nur kurz angerissen und bei Besonderheiten vertieft.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM dbo.Customer WHERE ID = CAST(10 AS INT) ;
GO

Unterschied zum Sperrverhalten in einem HEAP ist die ausschließliche Sperre der Datenseite, in der sich der betreffende Datensatz befindet.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM dbo.Customer WHERE Ort = 'Erzhausen';
GO

Da es sich nicht mehr um einen HEAP handelt, ist ausschließlich eine SCH-S Sperre erforderlich, um Änderungen an den strukturellen Daten (Metadaten) der Tabelle zu verhindern (Schemastabilität).

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM dbo.Customer WHERE Id = CAST(10 AS INT);
GO

Bei “REPEATABLE READ” muss sichergestellt sein, dass der Datensatz, den Microsoft SQL Server einliest auch in weiteren Lesevorgängen innerhalb der gleichen Transaktion unverändert ist. Aus diesem Grund wird eine Zeilensperre durchgeführt. Der Wert in [TextData] entspricht dem KeyHashValues des Datensatzes auf der Datenseite.

DBCC TRACEON (3604);
DBCC PAGE ('demo_db', 1, 1480, 3) WITH TABLERESULTS;

Mit dem obigen Befehl kann der Inhalt der Betroffenen Datenseite (1480) sichtbar gemacht werden. Für die bessere Lesbarkeit wird die Ausgabe als Tabelle forciert.

Die letzte Abfrage weicht etwas ab, um eine Bereichssperre zu demonstrieren, die nur in der Isolationsstufe “SERIALIZABLE READ” auftreten kann.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM dbo.Customer WHERE Id BETWEEN 10 AND 20;
GO

Eine Bereichssperre muss von Microsoft SQL Server gesetzt werden, um zu verhindern, dass zwischen den gesperrten Datensätzen weitere Datensätze eingefügt werden können. Würde in einer anderen Transaktion versucht werden, einen neuen Datensatz mit einer ID zwischen 10 und 20 einzutragen, wird die Transaktion so lange gesperrt, bis die von Microsoft SQL Server in der aktuellen Transaktion gehaltenen Bereichssperren wieder freigegeben werden.

Herzlichen Dank fürs Lesen!