Mittwoch, 25. Juni 2014

Sperrverhalten von Shared Locks…

Auf Grund einer Anfrage des von mir sehr geschätzten Kollegen Johannes Curio (e | w), die sich um Sperren von Objekten in einem HEAP drehte, habe ich mich etwas intensiver mit dem Sperrverhalten von Microsoft SQL Server beschäftigt, 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
 
-- Aktualisierung von 10 Datensätzen für hohe Selektivität
UPDATE dbo.Customer
SET    Ort = 'Erzhausen'
WHERE  Id % 1000 = 0;
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

Ich höre bereits die Aufschreie der Anhänger von “Extended Events” aber für die benötigte Protokollierung ist Microsoft SQL Server Profiler vollkommen ausreichend und er bietet mir verbesserte lesbare Ergebnisse (für diesen Blockeintrag).

Warum eine dedizierte Protokollierung?

Das Problem von Shared Locks – bei einigen ISO-Isolationsstufen – besteht darin, dass man sie nicht “sichtbar” machen kann, indem man eine dedizierte Transaktion beginnt, ein SELECT ausführt und diese Transaktion geöffnet lässt. Eine Kontrolle der gesetzten Sperren ist daher sehr 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


SQL_PROFILER_SETTINGS_01

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 die zuvor 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 als auch auf die Ressource, die in [TextData] angezeigt wird. Der Sperrtype 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 nachfolgenden 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 auch 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:


DBCC TRACEON(-1, 3604,1200) WITH NO_INFOMSGS;
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! Grundsätzlich wird die Ausführung einer Abfragen bei aktiviertem TF deutlich beeinflusst! 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.

Testabfragen

Für die Tests werden zwei Abfragetypen verwendet, die wiederkehrend unter einer jeweils anderen ISO-Isolationsstufe ausgeführt werden. Dabei handelt es sich jeweils um Abfragen auf die ID als auch auf das Attribut [Ort] in der Tabelle [dbo].[Customer]. Mit der Abfrage auf die [ID] soll eine Auswahl mit hoher Selektivität untersucht werden, während eine Abfrage auf das Attribut [Ort] mit einer sehr hohen Datenmenge (99%) arbeitet.
Allen verwendeten Abfragen ist gemein, dass sie vor der Ausführung die zu überprüfende Isolationsstufe explizit für die Session setzen. Weitere Details zum Setzen von manuellen Isolationsstufen finden sich hier.

Abfrage mit hoher Selektivität

Grundsätzlich stellt sich in einem Heap nicht die Frage nach “Selektivität”, da immer ein Table Scan durchgeführt werden muss (schließlich kann nicht gezielt über einen Index gesucht werden). Aber es gibt im Rahmen von Sperren interessante Merkmale, die bei entsprechender Selektivität auffallend sind. Ein Abfrage auf eine explizite ID ist hoch selektiv, da die ID – unabhängig davon, ob es sich um einen Heap handelt – immer nur einen Datensatz zurückliefert. Die CAST-Funktion wird verwendet, um sicherzustellen, dass keine implizite Konvertierung (wie hier beschrieben) durchgeführt wird.



SELECT * FROM dbo.Customer WHERE Id = CAST(10 AS int);

GO

Abfrage mit niedriger Selektivität

Abfragen mit niedriger Selektivität können sehr große Datenmengen liefern, wenn die Auswahl auf einen Wert fällt, der sehr häufig vorkommt. In der Testumgebung wird im [Ort] sehr häufig “Frankfurt am Main” verwendet (90%). Abfragen auf das Attribut [Ort] mit der Einschränkung “Frankfurt am Main” werden immer 9.990 Datensätze (99%) liefern; nur 1 % der Datensätze hat einen anderen Wert (“Erzhausen”) in diesem Attribut.



SELECT * FROM dbo.Customer WHERE Ort = 'Frankfurt am Main';

GO

Isolationsstufe “READ COMMITTED”

Die Isolationsstufe “READ COMMITTED” ist die Standardeinstellung für Datenbanken in Microsoft SQL Server. READ COMMITTED bedeutet, dass ausschließlich Datensätze gelesen werden, 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):

SQL_PROFILER_RESULTS_01
Das Ergebnis zeigt den Beginn der Aufzeichnung mit dem Absetzen des eigentlichen SQL Befehls. Anschließend wird ein “Intent Shared Lock” auf die Tabelle selbst 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.
Die nachfolgenden 16 Zeilen beantworten bereits die ursprüngliche Frage. “Gibt Microsoft SQL Server nach dem Scannen einer Ressource die Sperre wieder auf?”. Ja – 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. Auch die zweite Frage, die sich unmittelbar an die erste anschließt: “Führt Microsoft SQL Server Zeilensperren durch?” kann mit diesem einfachen Test belegt werden. Im Isolationsmodus “READ COMMITTED” werden KEINE Datensätze gesperrt! Alle Sperren werden auf Ebene einer Datenseite durchgeführt. Sobald Microsoft SQL Server die Datenseite nach dem Kriterium 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 Indexes die geforderten Daten anfordern kann (SEEK), muss immer die vollständige Datenseite nach passenden Datensätzen durchsucht werden (SCAN). 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).
SQL_PROFILER_RESULTS_02

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 relevant, 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”

Wie bereits zu Beginn erwähnt, verhält sich Microsoft SQL Server bei der Verwendung von Sperren unter der Verwendung von verschiedenen Isolationsstufen unterschiedlich. Die Isolationsstufe “READ UNCOMMITTED” ist in der Regel 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”.
SQL_PROFILER_RESULTS_03
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 Datensätze/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 habe ich detailliert in einem TECHNET-Artikel hier beschrieben (englisch)). Immer wieder liest man im Internet, dass READ UNCOMMITTED / NOLOCK keine Sperren verwendet. Das obige Beispiel demonstriert eindeutig, dass es sich dabei um eine Falschaussage handelt!

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);

PHYSICAL_LOCATION_01
Im konkrete 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.
SQL_PROFILER_RESULTS_04
Die Auswertung der Aufzeichnung mit dem 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 erheblich 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 zeigen, wann eine Freigabe der Datenzeile erfolgt – nachdem die Ausführung der Abfrage abgeschlossen und die Transaktion beendet ist!
SQL_PROFILER_RESULTS_05
Dieses Ergebnis ist absolut schlüssig, wenn man die Vorgehensweise der Sperren bei REPEATABLE READ genauer 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 sind 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, sollen die nachfolgenden Abbildungen und Erläuterungen demonstrieren.
Für die Demonstration wird erneut eine Abfrage auf ID = 10 ausgeführt, die lediglich einen Datensatz zurückliefern wird.
SQL_PROFILER_RESULTS_06
Immer mit dem Hintergedanken, dass es sich bei der Tabelle [dbo].[Customer] um einen HEAP handelt, wird das Ergebnis schnell einleuchten. Obwohl nur ein Datensatz benötigt wird, muss ein vollständiger Table Scan durchgeführt werden. Es müssen also – 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. Die Ergebnisse können sehr leicht selbst überprüft werden. 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

SQL_PROFILER_RESULTS_07
Einziger 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

SQL_PROFILER_RESULTS_08
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

SQL_PROFILER_RESULTS_09
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.

DBCC_PAGE_01
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

SQL_PROFILER_RESULTS_10
Eine Bereichssperren 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 z. B. 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.

Zusammenfassung

Erst einmal herzlichen Dank an Johannes Curio für die interessante Frage; zum einen hat sie mir Material für einen neuen Blogeintrag geliefert und zum anderen – wie meistens – musste ich mich wieder etwas intensiver mit Themen auseinandersetzen, die weit über die normale Verwendung hinausgehen, aber auf jeden Fall wieder viele Dinge in einem – einleuchtenden – neuen Licht zeigen.

Herzlichen Dank fürs Lesen!

1 Kommentar :

  1. Hallo Uwe,

    Danke für den Ausführlichen Artikel.

    Wäre es nicht auch interssant zu sehen, wie sich das Locking durch Snapshot-Isolation verändert?

    AntwortenLöschen