Ein Feature, welches mit SQL Server 2005 gekommen ist und meiner Meinung nach bisher nur wenig Beachtung gefunden hat, ist die Möglichkeit auf eine Zeile zuzugreifen, während jemand anders in einer Transaktion eine Änderung durchführt und diese noch nicht mit Commit abgeschlossen hat. Bislang gab es nur die Möglichkeit des Dirty Read, mit dem Nachteil, dass man nie wusste, ob die gelesenen Daten überhaupt jemals Realität werden. Von Roy Ernest wurde auf SQLServerCentral im April 2008 ein Artikel publiziert, den ich im folgenden einfach als Übersetzung wiedergebe, da ich finde, dass hier alle wesentlichen Punkte angesprochen werden und diese gute Vorlage einfach verwenden möchte. Weitere Informationen zu dem Thema finden sich auch im TechNet, wobei Teile daraus bereits in diesem Artikel verwendet wurden.

Acknowledgement/Danksagung

Many thanks to Roy Ernest (author) and Steve Jones (SQLServerCentral) for giving the permission to use this great article.

Anmerkungen des Übersetzers

Ich habe hier versucht möglichst keine Fachbegriffe zu übersetzen, aber bevor ich ins denglish abgleite, verwende ich doch lieber "festgeschrieben" als "committete" usw.! Die Isolationsstufen wie z. B. "committed read" habe ich z. B. unverändert übernommen.

Translation/Übersetzung

Als ich das erste Mal von der Zeilenversionsverwaltung in SQL Server 2005 hörte, hatte ich gemischte Gefühle. Ich war etwas verwirrt und hatte eine vorgefasste Idee, was es wäre. Nachdem ich etwas mehr darüber gelesen hatte, fand ich heraus, dass ich total daneben lag. Aber ich gehöre nicht zu der Art von Mensch, die sich auf die Worte anderer verlässt, wenn ich es aktuell selber ausprobieren und herausfinden kann, ob es wahr ist oder nicht. Wir schauen uns zuerst einmal an, was Zeilenversionsverwaltung ist, wie sie arbeitet und welche Vorteile sie bietet. Danach versuchen wir mal herauszufinden, ob denn die Zeilenversionsverwaltung wirklich das tut, was Microsoft behauptet.

Was ist Zeilenversionsverwaltung?

In der Computer-Sprache bedeutet Version eine Nummer, die angibt, in welcher Ausgabe etwas vorliegt. Zeilenversionsverwaltung arbeitet beinahe in der gleichen Weise. Es gibt Fälle, wo man zwar die zuletzt festgeschriebenen Daten sehen möchte, aber nicht diejenigen Daten benötigt, die noch auf dem Weg zum Status festgeschrieben (committed) sind. Früher war es so, dass wenn man mit "committed read" ein Select auf eine Tabelle abgesetzt hat, die Abfrage mit der Rückgabe der Daten warten musste, bis die aktuelle Transaktion beendet war. Aber mit der Zeilenversionsverwaltung arbeitet das Select ohne Verzögerung. Es bringt die zuletzt festgeschriebenen Daten. Wie es Microsoft sagt: "Versionierung funktioniert, indem alle Transaktionen, die Daten ändern dazu gebracht werden, ältere Versionen der Daten zu halten, so dass ein 'snapshot' der Datenbank (oder von Teilen der Datenbank) aus diesen alten Versionen erstellt werden kann. Wenn ein Satz in einer Tabelle oder einem Index geändert wird, dann wird der neue Satz mit der Sequenz-Nummer der Transaktion versehen, welche die Änderung durchführt. Die vorherige Version des Satzes wird im Versions-Speicher abgelegt und der neue Satz bekommt einen Zeiger auf den alten Satz im Versions-Speicher. Alte Sätze im Versions-Speicher können ebenfalls Zeiger auf noch ältere Sätze beinhalten. Die ganzen alten Versionen eines speziellen Satzes sind durch diese verkettete Liste verbunden und der SQL Server muss unter Umständen einigen Zeigern dieser Liste folgen, um an die richtige Version zu kommen. Versionierte Sätze müssen nur so lange im Versions-Speicher gehalten werden, wie es Operationen gibt, die diese benötigen könnten."

Zeilenversionsverwaltung arbeitet mit den beiden neuen Isolationsstufen, die mit SQL Server 2005 eingeführt wurden. Diese Isolationsstufen sind "SnapShot Isolation" (SI) und "Read Committed SnapShot Isolation" (RCSI). Diese Isolationsstufen lassen ein Select auf die vorherigen festgeschriebenen Werte einer Zeile zugreifen ohne eine Blockade auszulösen. Damit das funktioniert, müsste der SQL Server die vorher festgeschriebene Zeile behalten, auch wenn sie geändert wird. Aber jetzt gibt es die Möglichkeit, dass mehrere Versionen einer Zeile erreichbar sind. Diese mehrfache Versionierung von Zeilen wird Zeilenversionsverwaltung (Row Level Versioning) genannt. Man beachte bitte, dass der SQLServer für die zusätzlichen Versionen der Zeilen die TempDB verwendet. Also sollte man eine gute Wartung und Verwaltung der TempDB haben. Abhängig von den Transaktionen kann die Verwendung der TempDB sehr gross werden.

Lassen Sie uns sehen, ob es tatsächlich funktioniert. Um dies zu testen, benötigt man nur ein System mit SQL Server 2005. (Ich habe dies auf meinem Arbeitsplatz-PC gemacht). Wir werden für diesen Test eine separate Datenbank mit dem Namen TestForRowLevel anlegen.

Legen Sie die Datenbank mit diesem Skript an.

IF DB_ID('TestForRowLevel') is NULL
CREATE DATABASE TestForRowLevel
GO

Weil dies eine Test-Datenbank ist, habe ich alles auf den Standard-Einstellungen gelassen. Wie ich bereits vorher erwähnt habe, wurden zwei neue Isolationsstufen mit SQL Server 2005 eingeführt. Jetzt müssen wir die Möglichkeit zur Verwendung einrichten. Lassen Sie uns die beiden neuen Isolationsstufen mit den unten stehenden Kommandos setzen.

ALTER DATABASE TestForRowLevel
SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER DATABASE TestForRowLevel
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

Starten sie das folgende SQL Kommando um den Status der Datenbank-Optionen herauszufinden, die zur Zeilenversionsverwaltung gehören.

Select * from sys.databases
where [name] = 'TestForRowLevel'
GO

Allow Snapshot Isolation wird nur dann auf ON geschaltet, wenn es keine offenen Transaktionen auf der Datenbank gibt. Bis dahin ist der Status noch PENDING_ON. Wenn alle Transaktionen festgeschrieben sind, wir der Status auf ON geändert.

Jetzt haben wir alle Einstellungen getätigt, die für die Zeilenversionsverwaltung notwendig sind. Einfach, nicht wahr? Damit wir weiter testen können, müssen wir eine Tabelle anlegen. Verwenden Sie das folgende Skript umd die Tabelle anzulegen.

USE [TestForRowLevel]
GO
/****** Object: Table [dbo].[test] Script Date: 02/18/2008 06:35:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[test](
[intCol] [int] NULL
) ON [PRIMARY]
END

Jetzt haben wir eine Tabelle zum testen. Lassen Sie uns die Tabelle mit ein paar Testdaten füllen.

insert into dbo.test (intCol)
select 1212121
UNION ALL
select 123789
UNION ALL
select 789456
UNION ALL
select 987456
UNION ALL
select 987321

Jetzt sind wir bereit für einige Tests. Lassen Sie uns zuerst mal ein Select auf die Tabelle absetzen.

Select * from dbo.test

Lassen Sie das folgende Skript laufen, um die Daten zu ändern. Beachten Sie bitte hierbei, dass das COMMIT noch auskommentiert ist.

begin tran a

Update test
Set intCol = 2
where intCol = 1212121

--Commit tran a

Wie sie sehen können, haben wir die Daten geändert, aber diese Änderungen noch nicht festgeschrieben. Wenn Snapshot Isolation nicht eingeschaltet ist, würde ein Select in einer anderen Transaktion nicht ausgeführt werden. Es würde auf diese Transaktion warten, bis sie fertig ist. Aber mit der Option SnapShot-Isolation auf ON und Read Committed SnapShot ebenfalls eingeschaltet, sollten wir in der Lage sein, die zuletzt festgeschriebenen Daten zu sehen. Lassen Sie uns sehen, ob dies wirklich funktioniert. Setzen Sie ein Select in einem anderen Prozess (SPID) auf die Tabelle ab.

Select * from dbo.test

Sie sollten die anfangs eingefügten Werte sehen. Das heißt, Sie bekommen das gleiche Ergebnis, wie in dem Moment, als wir das Select oben zum ersten Mal abgesetzt haben. Die Werte, die Sie sehen sind:

intCol
------
1212121
123789
789456
987456
987321

Bevor wir die die Transaktion festschreiben, lassen Sie uns ein weiteres Update auf die Tabelle auf die gleiche Zeile ausführen.

begin tran b

Update test
Set intCol = 5
where intCol = 2

--Commit tran b

Wenn wir das oben stehende Skript ausführen, werden Sie sehen, dass es auf das erste Update wartet, bis es festgeschrieben wird. Das bedeutet, dass selbst wenn die Zeilenversionsverwaltung eingeschaltet ist, das Verhalten der Updates nicht geändert wird. Updates funktionieren nur, wenn alle Transaktionen für das Objekt festgeschrieben sind. Lassen Sie uns das erste Update festschreiben. Führen Sie einfach die auskommentierte Anweisung aus. Commit tran a. Jetzt haben Sie die erste Transaktion festgeschrieben (tran a). Dies führt zu einer Kettenreaktion. Das zweite Update, welches darauf gewartet hat, dass das erste Update festgeschrieben wurde, vollendet jetzt die Ausführung der Anweisung. Bitte denken Sie daran, dass wir immer noch nicht die zweite Transaktion festgeschrieben haben (tran b). Wenn Sie jetzt das Select auf die Tabelle absetzen, werden wir die Werte sehen, welche die zuletzt festgeschriebene Transaktion geschrieben hat.

Select * from dbo.test

sollte anzeigen

intCol
------
2
123789
789456
987456
987321

Auch wenn das zweite Update durchgeführt wurde, ist es immer noch nicht festgeschrieben. Deshalb sehen wir jetzt die Werte aus der ersten Transaktion, weil diese festgeschrieben wurden. Lassen Sie uns die zweite Transaktion festschreiben. Führen Sie einfach Commit tran b aus. Wenn Sie ein Select auf die Tabelle absetzen, werden Sie sehen, dass der erste Wert, der vorher 2 war, nun 5 ist.

Das war einfach und leicht zu testen. Es beweist auch, dass die Zeilenversionsverwaltung so arbeitet, wie es Microsoft behauptet. Es zeigt auch, dass das konkurierende Arbeiten mit dieser Methode vereinfacht wird.

Meine Erfahrung mit Microsoft hat mich gelehrt, dass man sicher sein kann, dass wenn sie etwas neues einführen, diese Logik auch an einigen anderen Stellen verwendet wird. Wo sonst wird also die Zeilenversionsverwaltung verwendet? Kämpfen wir uns durch die Aussagen von Microsoft. Das folgende ist eine Reihe von anderen Stellen, wo die Zeilenversionverwaltung zum Einsatz kommt.

Trigger

Von Anfang an waren Trigger die einzigen Stellen, wo wir auf historisierte Daten zugreifen konnten. Eine der Eigenschaften von Triggern war die Fähigkeit auf die Pseudo-Tabelle mit Namen "Deleted" zuzugreifen. Wann immer ein Update oder Delete gegen eine Tabelle mit Trigger ausgeführt wurde, wurden alle betroffenen Sätze (Informationen) in der "Deleted" Pseudo-Tabelle gespeichert. Der Weg, auf dem die Tabelle zu ihren Daten kam, war etwas teurer. Die Daten wurden durch einen Blick auf die Sätze aus dem Transaktionslog gewonnen, welche die aktuelle Transaktion geschrieben hatte. Das Durchsuchen des Transaktionslogs kann sehr teuer werden, da es für Schreib- und nicht für Leseoperationen optimiert ist.

Aber in SQL 2005, wird die "Deleted" Tabelle durch die Zeilenversionsverwaltung verwirklicht. Wenn eine Zeile einer Tabelle mit Trigger geändert oder gelöscht wird, wird die Pseudo-Tabelle durch die Zeilenversionsverwaltung bereitgestellt, auch wenn die Isolations-Stufe nicht gesetzt ist. Wenn der Trigger auf die Daten zugreifen muss, dann nimmt er diese aus dem Versionsspeicher, den Microsoft für die Zeilenversionsverwaltung verwendet. Das bedeutet, dass der SQL Server nicht durch das Transaktionslog gehen muss, um an die Daten zu kommen. Das verbessert die Geschwindigkeit der Trigger. Es gibt aber natürlich den kleinen Haken bei der Sache, dass hierdurch die TempDB mehr belastet wird, als bei SQL 2000.

Online Index Erstellung

Die Erstellung und vor allem der Rebuild von Indizes ist zwar nicht neu beim SQL Server, aber in 2005 kann man es jetzt durchführen, ohne den Index oder die Tabelle offline zu nehmen. In den Vorgängerversionen wurde die komplette Tabelle gesperrt und war für die Anwender nicht mehr zugreifbar, wenn man einen gruppierten (clustered) Index neu erstellt hat. Bei der Neuerstellung von nicht gruppierten (non-clustered) Indizes war der Index unbrauchbar. Aber mit der Zeilenversionsverwaltung erlaubt SQL Server 2005 Indizes komplett online aufzubauen, ohne die normalen Datenoperationen auf der Tabelle zu beeinflussen. Alle Änderungen an der Tabelle werden versioniert. Es spielt auch keine Rolle, ob Snapshot Isolation eingeschaltet ist oder nicht. Die Daten werden aus dem Versionsspeicher gelesen.

Multiple Active Result Sets (MARS)

Multiple Active Result Sets (MARS ) ist ein Feature der Client-Seite. Aber es basiert sehr stark auf der Zeilenversionsverwaltung. Vor SQL Server 2005 waren die Datenbank-Applikationen bei der Verwendung von Standard Result-Sets nicht in der Lage mehrere aktive Statements in einer Verbindung zu haben. Die Anwendung musste zuerst den ersten Batch beenden, bevor sie einen neuen starten konnte.

Wenn man mit SQL Server 2005 z. B. eine Select und eine Update Anweisung in einer MARS Verbindung ausführt, kann das UPDATE mit der Ausführung starten, bevor das SELECT das komplette Result-Set verarbeitet hat. Auch hier gibt es einen Haken. Das UPDATE muss seine Verarbeitung beendet haben, bevor das SELECT Statement weiterverarbeit werden kann. Alle Änderungen des UPDATE werden versioniert. Wenn beide Anweisungen (SELECT und UPDATE) in derselben Transaktion laufen, werden alle Änderungen des UPDATE nicht im SELECT sichtbar sein. Der Grund hierfür ist, dass das SELECT die versionierten Daten verarbeitet. Das sind die festgeschriebenen Daten vor dem UPDATE welches in der gleichen Transaktion läuft.

Zum Schluss

Wir haben gesehen, dass SQL 2005 eine coole Funktion mitgebracht hat, die Zeilenversionsverwaltung genannt wird. Diese hält Versionen der Daten, die geändert werden. Wir haben gelernt, dass sie arbeitet, wie Microsoft behauptet. Sie können die zuletzt festgeschriebenen Werte sehen ohne Blockaden. Wir haben auch gesehen, dass diese Versionierung die Performance von Update- und Delete-Triggern und Index-Erstellung erhöht und für Multiple Active Result Sets verwendet wird.

Lassen Sie uns auf die Vorteile bei der Verwendung des Snapshot Isolation Levels schauen.

• Select Statements sperren keine Daten beim Lesen.
• Select Statements greifen auf die letzten festgeschriebenen Werte pro Zeile zu.
• Er reduziert die Möglichkeiten eines Deadlocks.
• Er reduziert die Last auf dem Server durch weniger Sperren.
• Lock-Eskalierung wird reduziert.

Für jede neue Funktionalität muss man irgendwie bezahlen. Lassen Sie uns zum Schluss auf die Kosten schauen

• Er erhöht die Nutzung der Ressourcen wenn Daten geändert werden, da die versionierten Daten in der tempDB verwaltet werden.
• Update und Delete Transaktionen werden mehr Ressourcen nutzen, da sie ein Snapshot in der tempDB anlegen müssen. Dies kann höhere IO-Last, CPU- und Speicher-Verbrauch bedeuten.
• Die TempDB muss genügend Platz haben, um die zusätzlichen Anforderungen zu erfüllen.
• Jede Zeile benötigt 14 Bytes zusätzlich um die Versionen zu verwalten.
• Falls es lange Ketten von versionierten Zeilen gibt, kann dies die Performance beim Lesen beeinflussen.