NOLOCK ist ein NO GO!

Immer wieder treffe ich auf Applikationen, die inflationär die Option WITH (NOLOCK) verwenden. Bitte hört damit auf. NOLOCK bedeutet nicht, dass ohne Sperren gearbeitet wird. Vielmehr können durch die Verwendung mehr Probleme auftreten, als vermeintlich gelöst werden. Warum das so ist, beschreibt dieser Artikel.

Was bedeutet NOLOCK?

Die Option NOLOCK wird von Entwicklern mit den folgenden Begründungen verteidigt:

• Der Leseprozess verwendet keine Sperren
• Der Leseprozess ignoriert Sperren, die von anderen Prozessen gesetzt wurden
• Eine Abfrage mit NOLOCK wird schneller ausgeführt

Die Option NOLOCK entspricht der Isolationsstufe „READ UNCOMMITTED„. Bei dieser Isolationsstufe ignorieren Leseoperationen jegliche Sperren, die von anderen Prozessen gesetzt werden!

SET TRAN ISOLATION LEVEL READ UNCOMMITTED;
GO

SELECT * FROM dbo.Customer;
GO

SET TRAN ISOLATION LEVEL READ COMMITTED;
GO

Der obige Code verhält sich identisch zu den folgenden SQL-Abfragevarianten:

SELECT * FROM dbo.Customer WITH (NOLOCK);
GO
SELECT * FROM dbo.Customer WITH (READUNCOMMITTED);
GO

Somit ist die oben genannte zweite Annahme richtig; falsch hingegen ist die Aussage, dass NOLOCK (alt. READ UNCOMMITTED) selbst keine Sperren setzt! Dieses Verhalten wird weiter unten thematisiert.

Warum die Verwendung von NOLOCK?

Wenn SQL Ausführungen entdeckt werden, die für jede Tabelle im Code den NOLOCK-Hinweis verwenden, frage ich zunächst den Kunden, warum diese Option gewählt wurde. Mit sehr großer Sicherheit wird argumentiert, dass man so mögliche Sperren von anderen Prozessen ignoriert und somit die Abfrage ohne Unterbrechungen ausgeführt werden kann. Treibend bei dieser Entscheidung ist, dass man den Benutzer nicht lange warten lassen möchte und die Datenqualität zweitranging ist. Vom Standpunkt eines Entwicklers vollständig nachvollziehbar; aber was bedeutet dieser „Vorteil“ für Microsoft SQL Server?

Testumgebung

Mit Hilfe der nachfolgenden Testumgebung, die aus einer Schulungsdatenbank erstellt wird, soll gezeigt werden, wie Microsoft SQL Server sich verhält, wenn Operationen mit NOLOCK ausgeführt werden.

/* Create a demo database for the demo */
CREATE DATABASE demo_db;
ALTER DATABASE demo_db SET RECOVERY SIMPLE;
ALTER AUTHORIZATION ON DATABASE::demo_db TO sa;
GO

USE demo_db;
GO

/*
	Insert 800,000 records into the table!
	The source database can be found here:
	https://www.db-berater.de/downloads/ERP_DEMO_2012.BAK

	SQL Server will execute this statement in parallel!
*/
SELECT	*
INTO	dbo.Customer
FROM	ERP_Demo.dbo.Customer
WHERE	c_custkey % 2 = 0;
GO

/*
    Primary Key Implementation as Clustered Index
*/
ALTER TABLE dbo.Customer
ADD CONSTRAINT pk_Customer PRIMARY KEY CLUSTERED (c_custkey);
GO

Eine besondere Beachtung bei der Ausführung des obigen Codes gilt der Erstellung der Tabelle [dbo].[Customer] aus einer gefilterten Datenmenge aus der Schulungsdatenbank. Da die Quelle ein Heap ist, parallelisiert Microsoft SQL Server die Abfrage, wenn der Kostenschwellwert niedriger ist als die kalkulierten Ausführungskosten! Im Ergebnis werden ca. 800.000 Datensätze in die neue Tabelle eingetragen.

Abfrageverhalten ohne Verwendung von NOLOCK

Werden Abfragen ohne Tabellenweise ausgeführt, werden sie standardmäßig in der Isolationsstufe READ COMMITTED ausgeführt.

Read Committed ist eine Isolationsebene in Datenbanken, die sicherstellt, dass Transaktionen nur dann auf Daten zugreifen, wenn sie festgeschrieben sind und sichergestellt ist, dass sie nicht von anderen Transaktionen modifiziert werden. Dies bedeutet, dass während einer Transaktion, die in der Read Committed-Isolation ausgeführt wird, keine anderen Transaktionen die Daten, auf die die erste Transaktion zugreift, ändern dürfen.

/*
	Query runs with READ COMMITTED Isolation Level
*/
SELECT * FROM dbo.Customer;

Das Ergebnis ist unspektakulär. Es werden die gespeicherten Datensätze angezeigt. Hierbei werden die Daten – wie erwartet – in der Reihenfolge des Indexschlüssels (c_custkey) ausgeben. Jedoch gibt es absolut KEINE Garantie, dass die Daten sortiert ausgegeben werden, wenn nicht explizit ORDER BY verwendet wird!

Wenn eine Abfrage ausgeführt wird, kann Microsoft SQL Server sich für zwei Strategien entscheiden:

• Abruf der Daten gem. der Speicherung im B-Tree des Indexes
• Abruf der Daten gem. der Speicherung der Daten in IAM Seiten

Wenn die Abfrage die Daten gem. der gespeicherten Informationen des B-Trees eines Indexes abruft, beginnt der Leseprozess im Root-Knoten um in den ersten Leaf-Knoten zu gelangen, ab dem die Daten gelesen werden müssen.

Wenn bei diesem Leseprozess die Datenseiten gemäß ihrer logischen Reihenfolge gelesen werden, werden die Daten – wie oben gezeigt – sortiert ausgegeben.

Abfrageverhalten mit NOLOCK / READ UNCOMMITTED

Wird der Abfragehinweis NOLOCK oder READUNCOMMITTED verwendet, ändert sich das Verhalten vollständig.

Read uncommitted ist eine Isolationsstufe in der Datenbankverarbeitung, die es ermöglicht, dass Transaktionen in der Datenbank während der Verarbeitung von anderen Transaktionen gelesen werden können. Dies bedeutet, dass Transaktionen, die noch nicht abgeschlossen sind, von anderen Transaktionen gelesen werden können, was zu inkonsistenten oder unvollständigen Daten führen kann.

In der Isolationsstufe READ UNCOMMITTED verwendet Microsoft SQL Server einen „Allocation Unit Scan“, der die Datenseiten gemäß ihrer physikalischen Struktur abruft. Die Informationen über die von einer Tabelle / Index allokierten Datenseiten werden in der IAM Seite (Index Allocation Map) gespeichert. Um einen Allocation Unit Scan verwenden zu können, müssen zwei wesentliche Bedingungen erfüllt sein:

• Die Tabelle / der Index muss mindestens 64 Datenseiten allokieren
• Es muss gewährleistet sein, dass Metadaten nicht geändert werden

Allocation Unit Scan

Ein Allocation Unit Scan unterscheidet sich von einem B-Tree-Scan durch die – mögliche – unterschiedliche Anordnung der gespeicherten Datenseiten.

Der Leseprozess liest die allokierten Datenseiten aus der IAM (Index Allocation Map) und folgt der Reihenfolge ihres Auftretens. In der obigen Abbildung wird zunächst Seite 204 und anschließend 210, … gelesen. Daraus können unvorhergesehene Ergebnisse in der Ausgabe resultieren.

/*
	Query runs in READ UNCOMMITTED Isolation Level
*/
SELECT * FROM dbo.Customer WITH (NOLOCK);

Die Kundendaten kommen in einer anderen Sortierung beim Client an als im vorherigen Beispiel. Ein Indiz dafür, dass die Daten physikalisch anders angeordnet sind, als im Index.

Quod Erat Demonstrandum

Um das unterschiedliche Verhalten zu demonstrieren, wird die Root-Page des Clustered Index sowie die Position der IAM-Page mit der folgenden Abfrage ermittelt:

SELECT	FORMAT(P.rows, N'N0', 'de') AS rows,
		SIAU.total_pages,
		SIAU.used_pages,
		SIAU.data_pages,
		sys.fn_PhysLocFormatter(SIAU.root_page)			AS root_page,
		sys.fn_PhysLocFormatter(SIAU.first_iam_page)	AS first_iam_page
FROM	sys.system_internals_allocation_units AS SIAU
		INNER JOIN sys.partitions AS P ON (SIAU.container_id = P.partition_id)
WHERE	P.object_id = OBJECT_ID(N'dbo.Customer', N'U');
GO

Erste Datenseite des Clustered Index

Folgt man der root_page des Clustered Index, so muss – von der Root Page ausgehend – dem B-Tree bis zur Leaf Page gefolgt werden.

DBCC TRACEON (3604, -1);
DBCC PAGE (0, 1, 37306, 3);	-- ROOT
DBCC PAGE (0, 1, 37304, 3); -- INTERMEDIATE
DBCC PAGE (0, 1, 36952, 3); -- Leaf

In der Isolationsstufe „READ COMMITTED“ beginnt der Leseprozess mit der ersten Datenseite im Leaf-Level des Clustered Index und folgt der logischen Indexstruktur.

Erste Datenseite in IAM

Die erste IAM Page befindet sich auf Seite 150. Folgt man der Reihenfolge in der IAM, sieht das Ergebnis anders aus

DBCC PAGE (0, 1, 150, 3)	-- IAM
DBCC PAGE (0, 1, 304, 3)	-- Leaf
GO

Sperrverhalten

Die Annahme, dass die Isolationsstufe READ UNCOMMITTED keine Sperren verwendet, ist vollkommen abwegig. JEDE Transaktion – wirklich JEDE – muss sicherstellen, dass nach der Ausführung eine konsistente Umgebung existiert. Somit ist klar, dass auch Leseprozesse, die NOLOCK / READ UNCOMMITTED verwenden, transaktional sicher sein müssen.

Leseprozess READ COMMITTED

Ein Leseprozess, der in der Isolationsstufe READ COMMITTED ausgeführt wird, setzt nur sehr kurz Sperren auf die Ressourcen, die er verarbeitet.

Shared (S)-Locks ermöglichen gleichzeitigen Transaktionen das Lesen (SELECT) einer Ressource. Anderen Transaktionen können die Daten nicht ändern, während Shared Locks auf der Ressource vorhanden sind. Shared Locks für eine Ressource werden freigegeben, sobald der Lesevorgang abgeschlossen ist, es sei denn, die Transaktionsisolationsstufe ist REPEATABLE READ oder höher oder es werden ein Sperrhinweise verwendet, um die Shared Locks für die Dauer der Transaktion beizubehalten.

Das Sperrverhalten ist optimal, da eine Ressource nur so lange blockiert wird, wie sie in Benutzung ist. Durch den Umstand, dass der Leseprozess der logischen Speicherung der Schlüsselattribute des Index folgt, kann ein Datensätze unmittelbar, nachdem er gelesen wurde, wieder für andere Prozesse freigegeben werden.

Leseprozess READ UNCOMMITTED

In der Isolationsstufe READ UNCOMMITTED folgt der Leseprozess NICHT der logischen Anordnung der Datensätze sondern führt einen „Allocation Unit Scan“ aus. Während der gesamten Zeit, in der die Daten gelesen werden, bleibt eine SCH-S-Sperre auf der IAM-Seite bestehen

Microsoft SQL Server verwendet beim Kompilieren und Ausführen von Abfragen Schemastabilitätssperren (Sch-S). Sch-S-Sperren blockieren keine Transaktionssperren, einschließlich exklusiver (X) Sperren. Daher werden andere Transaktionen, einschließlich solcher mit X-Sperren auf einer Tabelle, weiterhin ausgeführt, während eine Abfrage kompiliert wird. Gleichzeitige DDL-Operationen und gleichzeitige DML-Operationen, die Sch-M-Sperren erwerben, können jedoch nicht für die Tabelle ausgeführt werden.

Schema Stability Lock – SCH-S

Mit Hilfe einer Extended Event Session wurde aufgezeichnet, wann Microsoft SQL Server eine SCH-S-Sperre setzt und wann sie wieder freigegeben wird.

In der Isolationsstufe READ COMMITTED wird eine SCH-S Sperre für das Lesen der Metadaten / Struktur des Objekts benötigt. Danach wird die Sperre direkt wieder freigegeben und die Abfrage beginnt.

Anders sieht es aus, wenn eine Abfrage mit der Option NOLOCK / READUNCOMMITTED ausgeführt wird. In diesem Fall muss die Sperre während der kompletten Laufzeit erhalten bleiben, damit Schema-Änderungen nicht durchgeführt werden können. Für das Beispiel mit ca. 800.000 Datensätzen wird die Sperre ca. 10 Sekunden aufrecht erhalten. In dieser Zeit sind alle Operationen, die nicht kooperativ zu SCH-S Sperren sind, nicht möglich! Nicht kompatibel zu SCH-S Sperren sind alle Aktionen, die eine SCH-M (Schema Modification) Sperren.

Microsoft SQL Server verwendet Schemaänderungssperren (Sch-M) während eines DDL-Vorgangs (Table Data Definition Language), z. B. beim Hinzufügen einer Spalte oder beim Löschen einer Tabelle. Während sie gehalten wird, verhindert die Sch-M-Sperre den gleichzeitigen Zugriff auf die Tabelle. Das bedeutet, dass die Sch-M-Sperre alle externen Operationen blockiert, bis die Sperre freigegeben wird.

Mögliche DDL Vorgänge, die durch NOLOCK beeinflusst werden

• INDEX REBUILD (Offline/Online)
• ALTER TABLE …

Problem Read Committed Snapshot Isolation

Die Idee hinter Read Committed Snapshot Isolation ist es, einen Datensatz mit seinem Ursprungswert lesen zu können, während ein anderer Prozess diesen Datensatz ändert. Microsoft SQL Server löst diesen Ansatz, indem VOR der Bearbeitung (DELETE / UPDATE) eine Kopie der zu bearbeitenden Ressourcen in TEMPDB verlagert werden. Ein Leseprozess liest dann nicht mehr aus der Originalressource sondern wird auf die Kopie verwiesen.

Die Kopie wird in der Systemdatenbank TEMPDB verwaltet und Microsoft SQL Server muss den Verweis zur Kopie im Datensatz selbst speichern. Dieser Verweis verwendet einen Pointer, der 14 Bytes lang ist. Diese 14 Bytes werden nach der Bearbeitung des Datensatzes jedoch nicht mehr gelöscht, damit ein Overhead beim Erneuten Bearbeiten vermieden wird.

Read Committed (Snapshot Isolation)

Der Name dieser Isolationsstufe verrät es bereits – es handelt sich um das optimistische Sperrmodell von READ COMMITTED. RSCI kann also nur genutzt werden, wenn der lesende Prozess in der READ COMMITTED Isolationsstufe ist. NOLOCK verwendet jedoch das Isolationslevel READ UNCOMMITTED und somit ist der komplette Overhead, der durch RCSI generiert wird, für Prozesse, die NOLOCK verwenden, „für die Katz'“.

Verweise

Herzlichen Dank fürs Lesen!