ALLOW_SNAPSHOT_ISOLATION – besser als NOLOCK

Ich hatte vor kurzem das Vergnügen, eine eintägige Beratung bei einem langjährigen Kunden durchzuführen. Unter anderem wurde gezeigt, dass man die Performance von Schnittstellen optimierte, indem man die Option ALLOW_SNAPSHOT_ISOLATION für die Datenbank aktivierte. Ich habe mit meinen Erläuterungen zu diesem Thema so falsch gelegen, dass ich das Wochenende dazu genutzt habe, mal etwas genauer hinter die Kulissen von SNAPSHOT ISOLATION zu schauen.

Hochmut kommt vor dem Fall – Lesson Learned!

Unser Kunde setzt eine ERP-Software im Unternehmen ein, das durch Customizing und die Verwendung von Schnittstellen individuell erweitert wurde. Da es immer wieder mal bei den Schnittstellen-Operationen hakte, hat man sich entschieden, die Option ALLOW_SNAPSHOT_ISOLATION für die Datenbank zu aktivieren. Diese Entscheidung ist korrekt, da nicht auszuschließen ist, dass die Applikation mit aktivierter automatischer SNAPSHOT ISOLATION korrekt arbeitet.

Ich erzählte – im Brustton der Überzeugung – dass die Option ALLOW_SNAPSHOT_ISOLATION nicht funktionieren würde, da auch die Versionierung von Schreibprozessen nur dann angewendet wird, wenn die Transaktion in einer SNAPSHOT-Isolationsstufe ausgeführt würde. Das würde implizieren, dass auch die Schreibaktionen zuvor mit einem SET TRANSACTION ISOLATION LEVEL SNAPSHOT eingeleitet werden müssen.

Tja – schöne Bescherung; selten so falsch gelegen, wie mit dieser Aussage.

Die Funktionalität des „Row Versionings“ ist unabhängig davon, ob ALLOW_SNAPSHOT_ISOLATION oder READ_COMMITTED_SNAPSHOT_ISOLATION verwendet wird, aktiv. Der Unterschied besteht darin, dass bei ALLOW_SNAPSHOT_ISOLATION das Lesen von Datensätzen aus dem Version Store (TEMPDB) durch den Entwickler kontrolliert werden kann, während bei RCSI alle Transaktionen automatisch im Snapshot Isolation Level ausgeführt werden.

SNAPSHOT ISOLATION Level

Microsoft SQL Server unterscheidet zwischen „Pessimistic Locking“ und „Optimistic Locking“. Eine Datenbank in Microsoft SQL Server (ausgenommen AZURE Databases und Managed Instances) ist so konfiguriert, dass „Pessimistic Locking“ verwendet wird. Dabei kann ein lesender Prozess eine Ressource nicht lesen, wenn sie durch einen anderen – schreibenden – Prozess blockiert wird. Um diese Blockaden zu „umgehen“, verwenden weniger erfahrende Datenbankprogrammierer sehr gerne die Option „NOLOCK“ oder „READUNCOMMITTED“. BITTE tut das nicht!.

Damit ein lesender Prozess den Zustand einer gesperrten Ressource in einem Zustand lesen kann, wie er VOR der Manipulation besteht, muss eine Kopie der Ressource erzeugt werden; und zwar in der Systemdatenbank TEMPDB. Damit ein lesender Prozess die Kopie lesen kann, muss die betroffene Ressource zusätzlich einen Pointer zur Kopie speichern, der dann vom lesenden Prozess zur Weiterleitung verwendet wird. Dieser Pointer wird „Version Information“ genannt und hat eine feste Größe von 14 Bytes!

Werden alle Datensätze sofort um 14 Bytes erweitert, wenn SNAPSHOT ISOLATION aktiviert wird?

NEIN! Wird SNAPSHOT ISOLATION aktiviert, bleiben bestehende Datensätze davon unberührt. Es wäre ein Desaster, wenn Microsoft SQL Server bei Aktivierung der Option jeden Datensatz um einen Pointer von 14 Bytes erweitern würde. Daraus würden sich zu viele Probleme ergeben, die man nicht wirklich in einer Datenbank haben möchte:

• Durch das Hinzufügen der Version Information zu jedem Datensatz wird der benötigte Platz im Transaktionsprotokoll – je nach Größe der Datenbank – wachsen
• Ein Automatisches Hinzufügen von Version Information würde Datensätze vergrößern. Passen Datensätze nicht mehr auf die ursprüngliche Datenseite, müssten sie neu platziert werden. Daraus ergeben sich:
  • PAGE SPLIT-Operationen (Indexe)
  • FORWARDED RECORDS (Heaps)

Werden die 14 Bytes nach Abschluss der Transaktion wieder vom Datensatz entfernt?

Nein! Die Version Informationen sind – und bleiben – ein Bestandteil des Datensatzes.

Werden die 14 Bytes bei Deaktivierung von SNAPSHOT ISOLATION wieder vom Datensatz entfernt?

Nein! Die 14 Bytes werden erst dann wieder freigegeben, wenn ein INDEX REBUILD durchgeführt wird. Ein REORGANIZE reicht nicht aus!

Kann ein Reader trotz SNAPSHOT ISOLATION blockiert werden?

Ja! Tatsächlich kann es trotz SNAPSHOT ISOLATION zur Blockade eines lesenden Prozesses kommen. Der Grund für dieses Verhalten liegt darin begründet, wo die Version Information gespeichert ist; im Datensatz selbst. Ein Nonclustered Index besitzt keine Version Information! Wird ein Wert in einem Nonclustered Index geändert, wird der Datensatz blockiert; eine Kopie vom Index wird NICHT in TEMPDB gespeichert. Ein lesender Prozess, der auf den Nonclustered Index zugreift, muss warten, bis die Schreibsperre wieder aufgehoben ist.

/*
	We begin a transaction on a table with
	- a clustered index on message_id and language_id
	- a nonclustered index on severity

	Inside the transaction we update the severity.
	While the transaction is open we check the open locks
*/
BEGIN TRANSACTION
GO
	SELECT	TOP (10)
			sys.fn_PhysLocFormatter(%%physloc%%) AS pos,
			*
	FROM	dbo.messages
	ORDER BY
		message_id,
		language_id;

	UPDATE	dbo.messages
	SET		severity = 0
	WHERE	message_id = 21
			AND language_id = 1028;

	SELECT * FROM sys.dm_tran_locks
	WHERE	request_session_id = @@spid;

Kann man SNAPSHOT ISOLATION während des Betriebs aktivieren/deaktivieren?

It depends! Es gibt zwei unterschiedliche Stufen der Implementierung. Die automatische Verwendung von SNAPSHOT ISOLATION erfordert eine exklusive Sperre auf die Datenbank.

ALLOW_SNAPSHOT_ISOLATION

Diese Option kann während des regulären Betriebs aktiviert werden. Microsoft SQL Server benötigt für die Aktivierung/Deaktivierung eine U(pdate)-Sperre auf die Datenbank. Sobald diese Sperre gesetzt ist, kann ALLOW_SNAPSHOT_ISOLATION aktiviert/deaktiviert werden. Jedoch kann es zu Verzögerungen kommen, wenn noch offene Transaktionen in der Datenbank vorhanden sind.

READ_COMMITTED_SNAPSHOT_ISOLATION

Read Committed Snapshot Isolation (RCSI) ist identisch im Verhalten zu Allow Snapshot; jedoch wird das Transaktionslevel für JEDE Transaktion standardmäßig im SNAPHOT Isolation Level ausgeführt. Mit der erstgenannten Option kann ein Entwickler das selbst entscheiden!

Um RCSI zu aktivieren, benötigt der Prozess einen exklusiven (X) Zugriff auf die Datenbank. Daraus leitet sich ab, dass diese Option nicht im laufenden Betrieb aktiviert/deaktiviert werden kann. Für die Umstellung darf es keine weitere Verbindung zur Datenbank geben!

SNAPSHOT ISOLATION braucht mehr Speicher!

Dieses Statement hört man immer wieder in Gesprächen. Richtig ist, dass bei Aktivierung von SNAPSHOT Isolation ein Pointer auf den Verweis in TEMPDB gesetzt werden muss, wenn Datensätze für die Bearbeitung gesperrt werden müssen. Dieser Pointer besitzt eine Größe von 14 Bytes und speichert neben der Position der Kopie des ursprünglichen Datensatzes zusätzlich eine Transaction Sequence Number. SQL Server weist jeder Transaktion eine eindeutige Transaction Sequence Number (XSN) zu. Diese Nummer wird verwendet, um die Reihenfolge von Transaktionen zu verfolgen und die entsprechenden Versionen von Datenzeilen im Version Store zuzuordnen.

00000000000000B4:   00650020 00650078 00700072 00650073 00730069  .e. .e.x.p.r.e.s.s.i
00000000000000C8:   006f006e 00200027 0025002e 002a006c 00730027  .o.n. .'.%...*.l.s.'
00000000000000DC:   002e0000 00000000 0000007e 04000000 00        ...........~.....

Version Information = 
	Transaction Timestamp: 1150
	Version Pointer: Null

Diese 14 Bytes müssen natürlich zur regulären Größe des Datensatzes hinzugefügt werden. Bei einer kleinen Datenbank ist das vernachlässigbar; haben wir jedoch Millionen von Datensätzen in einer Tabelle, sollte der Speicherbrauch immer mit einkalkuliert werden.

Für die Speicherung der Kopie eines Datensatzes muss berücksichtig werden, dass die Systemdatenbank TEMPDB mehr Speicher benötigt.

Testszenario

Für die einzelnen Demonstrationen dient ein einfaches Verfahren:

• Anlegen einer Datenbank [demo_db]
• Erstellen einer Tabelle [dbo].[messages] aus [sys].[messages
• Auswertung der Struktur eines Datensatzes bei aktivierter / deaktivierter SNAPSHOT Isolation

/* Create the demo database */
CREATE DATABASE [demo_db];
GO

USE [demo_db];
GO

SELECT * INTO dbo.messages FROM sys.messages;
GO

SNAPSHOT ISOLATION und existierende Datensätze

Erst nach der Speicherung der Daten in der Tabelle wird SNAPSHOT ISOLATION aktiviert. Anschließend wird eine Datenseite ausgelesen um zu prüfen, ob ein Pointer zum Datensatz hinzugefügt wurde.

/* Activate SNAPSHOT Isolation AFTER the rows have been inserted */
ALTER DATABASE demo_db SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

/* Get an example record with it's position details */
SELECT	sys.fn_PhysLocFormatter(%%physloc%%) AS Position, *
FROM	dbo.messages
WHERE	severity = 12;
GO

/* Show information about the record structure */
DBCC TRACEON (3604);
DBCC PAGE (0, 1, 305, 3) WITH TABLERESULTS;
GO

Die Abbildung zeigt einen bereits vorhandenen Datensatz. Es werden KEINE Informationen zur Version Information gespeichert; bestehende Datensätze bleiben unverändert, solange sie nicht geändert werden.

Aktualisierung von existierenden Datensätzen.

Sobald ein bestehender Datensatz für einen Schreibprozess blockiert werden muss, greift SNAPSHOT Isolation. Eine Kopie des bestehenden Datensatzes wird in TEMPDB ausgelagert und der aktuelle Datensatz erhält die Adresse der Position als Version Information.

BEGIN TRANSACTION
GO
	UPDATE	dbo.messages
	SET		Text = N'Snaphot Isolation'
	WHERE	message_id = 484
			AND language_id = 1033;
	GO

DBCC PAGE(0, 1, 305, 3);
GO

Sobald der Datensatz modifziert wird, erhält der Datensatz weitere 14 Bytes am Ende der regulären Daten. Diese 14 Bytes geben Aufschluss darüber, an welcher Stelle in TEMPDB sich der ursprüngliche Datensatz befindet.

Einfügen neuer Datensätzen

Ein neuer Datensatz erhält automatisch eine Reservierung von 14 Bytes für die Version Information, Die Informationen der Version Information verbleiben permanent im Datensatz!

BEGIN TRANSACTION
GO
	INSERT INTO dbo.messages
	(message_id, language_id, is_event_logged, severity, text)
	VALUES
	(0, 0, 0, 0, 'SNAPSHOT Isolation');

	SELECT	sys.fn_PhysLocFormatter(%%physloc%%) AS Position, * FROM dbo.messages
	WHERE	message_id = 0
			AND language_id = 0;

	DBCC PAGE (0, 1, 8145, 3);

Es fällt auf, dass die Version Information ein Bestandteil des Datensatzes ist, aber kein Verweis in die Systemdatenbank TEMPDB erfolgt. Das ist logisch, da der Datensatz neu hinzugefügt wurde.

Fazit

Viele Entwickler verwenden NOLOCK, um Schreibsperren zu umgehen. Dabei nehmen sie billigend Dirty Reads in Kauf. Ist man auf korrekte – transaktionssichere – Ergebnisse angewiesen, ist diese Technik unbrauchbar. Statt mit NOLOCK zu arbeiten, ist es empfehlenswert, die Option ALLOW_SNAPSHOT_ISOLATION zu verwenden.

• Um mit Row Versioning zu arbeiten, muss ein schreibende Prozess nicht explizit m Isolation Level SNAPSHOT ausgeführt werden.
• ALLOW_SNAPSHOT_ISOLATION bewirkt, das automatisch Row Versioning für Schreibaktionen verwendet wird.
• Existierende Datensätze werden bei Aktivierung von Snapshot Isolation NICHT geändert
• Neue Datensätze werden automatisch mit 14 Bytes für die Version Information versehen
• Um als lesender Prozess nicht blockiert zu werden, muss explizit das Isolation Level SNAPSHOT für die Session aktiviert werden.
• Wird ein Non-Clustered Index für Schreibprozesse gesperrt, muss ein lesender Prozess, der einen Zugriff benötigt, trotz SNAPSHOT ISOLATION warten!

Herzlichen Dank fürs Lesen!