Eine Frage in einem englischsprachigen Forum für Microsoft SQL Server motivierte mich, diesen Artikel über die verschiedenen ISO-Isolationsstufen zu schreiben. Dieser Artikel beschäftigt sich mit der restriktivsten Isolationsstufe – SERIALIZABLE. In einem zuvor geschriebenen Artikel habe ich das Sperrverhalten von SELECT-Statements in den verschiedenen Isolationsstufen beschrieben. Dieser Artikel befasst sich nicht mit den Grundlagen von Isolationsstufen sondern behandelt die besonderen Merkmale.
Inhaltsverzeichnis
Isolationsstufe SERIALIZABLE
Die Isolationsstufe SERIALIZABLE stellt als restriktivste Stufe sicher, dass alle Aktionen von Transaktionen sich nicht gegenseitig beeinflussen. Der Anwender kann sicher sein, dass die Transaktion in jedem Fall einen gültigen Zustand besitzt. Es werden alle Anomalien des Mehrbenutzerbetriebes verhindert. Der Einsatz bietet sich für Transaktionssysteme an, die Daten häufig verändern und nicht für den Bereich der Datenanalyse eingesetzt werden. Nur in der Isolationsstufe SERIALIZABLE sind sogenannte Bereichssperren möglich. Eine Bereichssperre verhindert, dass während der aktiven Transaktion in den ausgewählten Bereich weitere Datensätze durch andere Transaktionen eingefügt werden oder bestehende Datensätze der ausgewählten Datenmenge durch andere Transaktionen geändert werden können.
Testumgebung
Für diese Artikelreihe wird eine Tabelle [dbo].[CustomerOrderDetails] verwendet. In dieser Tabelle befinden sich 3.000.000 Datensätze. Der gruppierte Primärschlüssel erstreckt sich über zwei Attribute ([Order_Id], [Position]). Weitere Indexe sind für die Tabelle nicht vorhanden.
CREATE TABLE dbo.CustomerOrderDetails
(
Order_Id INT NOT NULL,
Position INT NOT NULL,
Article_Id INT NOT NULL,
Quantity NUMERIC(10, 2) NOT NULL,
Unit CHAR(5) NOT NULL,
Price SMALLMONEY NOT NULL,
Currency CHAR(3) NOT NULL,
CONSTRAINT pk_CustomerOrderDetails_Order_Id PRIMARY KEY CLUSTERED
(
Order_Id ASC,
Position ASC
)
);
GO
Sperrverhalten bei pessimistic locking
Standardmäßig verwenden Datenbanken in Microsoft SQL Server das sogenannte “pessimistic locking”. Hierbei handelt es sich um ein Sperrverhalten, bei dem ein schreibender Zugriff einen lesenden Zugriff auf die gleiche Ressource verhindert. Die zweite Transaktion darf den Datensatz nicht eher lesen, bevor er nicht von der ersten Transaktion wieder freigegeben wird; ansonsten entstehen Dirty Reads.
Gleichzeitige Lesen durch zwei Transaktionen
Das erste Beispiel zeigt, wie sich das Leseverhalten in der restriktiven Isolationsstufe SERIALIZABLE verhält. Dazu werden in Transaktion 1 (T1) alle Bestelldetails aus der Bestellung 1001 angezeigt. In Transaktion 2 wird ebenfalls diese Bestellung abgefragt.
--Transaktion 1!
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION;
GO
SELECT * FROM dbo.CustomerOrderDetails
WHERE Order_Id = 1001;
GO
SELECT resource_type,
resource_description,
request_mode,
request_type,
request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_type <> N'DATABASE';
GO
Achtung – die Transaktion 1 ist noch nicht bestätigt und somit noch offen!
Die Abbildung zeigt im Ergebnis 3 Bestelldetails für die Bestellung 1001; schaut man in die Sperrliste, fällt auf, dass 4 (!) Datensätze (Keys) gesperrt sind. Microsoft SQL Server verwendet in der Isolationsstufe SERIALIZABLE Bereichssperren, wenn ein Suchkriterium nicht eindeutig ist. Da nur nach dem Prädikat [Order_Id] gesucht wurde, kann nicht sichergestellt werden, dass nur ein Datensatz zurückgeliefert wird. Microsoft SQL Server verhindert durch Bereichssperren, dass zwischen den gefundenen Datensätzen weiteren Datensätze eingefügt werden können. Ein Blick auf die gesperrten Datensätze verdeutlicht dieses Verhalten.
SELECT *
FROM dbo.CustomerOrderDetails
WHERE %%lockres%% IN
(
SELECT resource_description
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND resource_type = N'KEY'
)
ORDER BY
Order_Id,
Position;
GO
Die Abfrage liefert alle Datensätze, die in der geöffneten Transaktion blockiert werden.
Die Abgrenzungen zeigen die Ergebnismenge der Abfrage. Microsoft SQL Server verhindert auf Grund der Isolationsstufe SERIALIZABLE, dass während der Ausführung der Transaktion weitere Bestelldetails für die Bestellung 1001 erfasst werden können (Position > 3). Aus diesem Grund muss der erste Datensatz der Bestellung 1002 ebenfalls gesperrt werden; er repräsentiert die untere Grenze des Bereichs, in dem keine weiteren Daten eingefügt werden dürfen.
In einer zweiten Transaktion werden ebenfalls Informationen zur Bestellung 1001 in der Isolationsstufe SERIALIZABLE abgerufen.
Die Abbildung zeigt, dass beide Transaktionen ohne Einschränkungen auf die Daten zugreifen können. Ein lesender Zugriff verhindert nicht einen anderen lesenden Zugriff auf die angeforderten Ressourcen.
Schreibender Zugriff…
Der schreibende Zugriff erfordert einen exklusiven Sperrmechanismus. Für Microsoft SQL Server bedeutet diese Einschränkung, dass ein Prozess so lange mit dem Schreiben warten muss, bis ein exklusiver Zugriff auf die Ressourcen möglich ist. Der schreibende Zugriff ist in zwei Situationen möglich:
während Daten gelesen werden
Wenn Transaktion 1 die Daten mit der einschränkenden Isolationsstufe SERIALIZABLE liest, werden Ressourcen erst dann freigegeben, wenn die Transaktion beendet ist. Während die Transaktion geöffnet ist, müssen alle Datensätze, die sich im Bereich der Ausgabe befinden, eine S-Sperre beibehalten. Was macht Transaktion 2, wenn sie Daten ändern möchte, die in diesem Bereich vorhanden sind?
—Transaktion 2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
UPDATE dbo.CustomerOrderDetails
SET Price = Price * 1.10
WHERE Order_Id = 1001;
GO
Die Ausführung der Abfrage wird verzögert, bis Transaktion 1 vollständig abgeschlossen ist.
Transaktion 2 versucht, eine exklusive Bereichssperre (RangeX-X) auf den ersten Datensatz anzuwenden. Dieser Datensatz (wie auch alle weiteren Datensätze) sind durch Transaktion 1 mit einer Bereichssperre belegt. Aus diesem Grund muss Transaktion 2 warten. Die Isolationsstufe von Transaktion 2 ist hierbei vollkommen irrelevant. Entscheidend ist in der vorliegenden Situation, dass eine exklusiver Sperre nicht mit einer lesenden Sperre kompatibel ist.
während Daten geändert werden
Wie schaut es jedoch aus, wenn Transaktion 1 Änderungen an Datensätzen durchführt, während eine weitere Transaktion die Daten ebenfalls ändern möchte?
—Transaktion 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
UPDATE dbo.CustomerOrderDetails
SET Price = Price * 1.10
WHERE Order_Id = 1001;
GO
SELECT request_session_id,
resource_type,
resource_description,
request_mode,
request_type,
request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_type <> N'DATABASE'
ORDER BY
request_session_id;
GO
In Transaktion 1 werden für alle Bestelldetails aus Bestellung 1001 die Preise um 10% erhöht. Die Transaktion ist noch nicht abgeschlossen!
Die Transaktion hält exklusive Bereichssperren auf 4 Datensätze (RangeX-X). Wenn eine zweite Transaktion ebenfalls Daten der Bestellung 1001 anpassen möchte, ergibt sich ein Konflikt, der nicht aufgelöst werden kann.
Das Verhalten von Transaktion 2 ist logisch. Da keine exklusive Sperre gesetzt werden kann, muss Transaktion 2 warten, bis Transaktion 1 die Auswahl von Datensätzen abgeschlossen hat. Interessant jedoch ist in diesem Zusammenhang, wie sich Transaktion 2 verhält, wenn nicht Bestellung 1001 geändert werden soll sondern Bestellung 1002. Wenn man die Sperren auf den betroffenen Datensätzen vergleicht, wird man feststellen, dass Transaktion 1 erneut eine Sperre auf die erste Position von Bestellung 1002 gesetzt hat.
Bei dem Versuch, Daten der Bestellung 1002 zu aktualisieren, muss Transaktion 2 erneut warten. Transaktion 1 muss den ersten Datensatz von Bestellung 1002 blockieren, um zu verhindern, dass ein neuer Datensatz für Bestellung 1001 mit der Position 4 eingefügt wird (Bereichssperre). Der Einfluss der Sperre erstreckt sich aber nicht nur auf die Bestellung 1001 sondern auch auf den ersten Datensatz der Bestellung 1002.
Lesender Zugriff…
während Daten gelesen werden
Diese Art des konkurrierenden Zugriffs ist nicht durch explizites Sperrverhalten der Transaktionen untereinander gekennzeichnet. Beide Transaktionen verwenden eine Bereichssperre für die Auswahl von Daten. Ein lesender Prozess blockiert keinen anderen lesenden Prozess!
während Daten geschrieben werden
Sobald Daten geschrieben werden, verändert sich diese kooperative Arbeitsweise bei mehreren Transaktionen. Die nachfolgende Abbildung zeigt die Sperren, die von Transaktion 1 (SPID: 52) während einer Aktualisierung gesetzt werden.
Will ein weiterer Prozess (SPID: 53) auf die gleichen Orderdetails zugreifen, wird versucht eine lesende Bereichssperre (RangeS-S) zu setzen. Diese Sperre ist nicht kompatibel mit einer exklusiven Sperre und muss warten.
Zusammenfassend kann man sagen, dass sich in der Isolationsstufe SERIALIZABLE im Rahmen mehrerer Transaktionen nichts ändert. Der Unterschied ist, dass Microsoft SQL Server die Sperren nicht auf einzelne Datensätze anwendet, sondern immer eine Bereichssperre verwendet, um zu vermeiden, dass zwischen den bestehenden Datensätzen neue Datensätze hinzugefügt werden.
Sperrverhalten bei optimistic locking
Mit Microsoft SQL Server 2005 wurde zum ersten Mal “optimistic locking” in die Datenbankwelt von Microsoft implementiert. Von optimistic locking spricht man bei Verfahren, die einen parallelen Zugriff von mehreren Benutzern auf denselben Datensatz konfliktarm und ohne Inkonsistenzen regeln. Microsoft hat mit SQL Server 2005 die Option ALLOW_SNAPSHOT_ISOLATION und READ_COMMITTED_SNAPSHOT implementiert. Das Festlegen der READ_COMMITTED_SNAPSHOT-Option ermöglicht Zugriff auf versionierte Zeilen mit der READ COMMITTED-Isolationsstufe. Wenn die READ_COMMITTED_SNAPSHOT-Option auf OFF festgelegt ist, muss die Snapshot-Isolationsstufe für jede Sitzung explizit festgelegt werden, um auf versionierte Zeilen zuzugreifen. Für die nachfolgenden Beispiele wird die READ_COMMITTED_SNAPSHOT-Isolationsstufe für die Testdatenbank aktiviert.
ALTER DATABASE CustomerOrders SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
Gleichzeitiges Lesen durch zwei Transaktionen
Zu diesem Szenario bedarf es nicht vieler Worte. Wie bereits im ersten Beispiel demonstriert, ist ein gleichzeitiges Lesen von mehreren Transaktionen ohne weiteres möglich. Solange die Sperrmuster zueinander kompatibel sind, verhalten sich die Sperren kooperativ.
Schreibender Zugriff
Beim schreibenden Zugriff ändert sich das Verhalten der unterschiedlichen Transaktionen. Hierbei kommt es jedoch darauf an, welcher Art die “führende” Transaktion ist. Abhängig davon, ob Transaktion 1 schreibend oder lesend die Daten blockiert, verhält sich Transaktion 2 unterschiedlich.
während Daten gelesen werden
Wenn Transaktion 1 die Daten mit der einschränkenden Isolationsstufe SERIALIZABLE liest, werden Ressourcen erst dann wieder freigegeben, wenn die Transaktion vollständig abgeschlossen ist. So lange wird auf alle Datensätze, die sich im Bereich der Ausgabe befinden, eine RangeS-S-Sperre gehalten. Was macht Transaktion 2, wenn sie Daten ändern möchte, die in diesem Bereich vorhanden sind?
—Transaktion 2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
UPDATE dbo.CustomerOrderDetails
SET Price = Price * 1.10
WHERE Order_Id = 1001;
GO
Die Abbildung zeigt das Sperrverhalten beider Transaktionen. Während Transaktion 1 (SPID: 55) eine Bereichssperre gesetzt hat, muss Transaktion 2 (SPID: 52) darauf warten, eine Bereichssperre für einen exklusiven Zugriff zu erhalten. Bei “optimistic locking” kann ein lesender Prozess also auch weiterhin einen schreibenden Prozess blockieren.
während Daten geschrieben werden
Ähnlich verhält es sich, wenn Transaktion 1 Daten ändert. In diesem Fall hilft optimistic locking nicht, da eine exklusive Bereichssperre auf die betroffenen Daten gesetzt wird. Transaktion 2 kann erst dann schreibend auf die Daten zugreifen, wenn Transaktion 1 die Sperren wieder löst.
Entscheidend bei diesem Szenario ist – unabhängig davon, ob optimistic locking aktiviert ist – dass JEDES andere Isolationslevel ausser READ COMMITTED die Verwendung von optimistic locking ausser Kraft setzt!
CHECK-Einschränkungen (Plausibilitätsprüfungen)
Wenn man das Sperrverhalten im Isolationsmodus SERIALIZABLE in den oben gezeigten Beispielen genauer untersucht, weiß man, dass eine Sperre auf den den ersten Datensatz der nächsten Bestellung (1002) erforderlich ist, weil Microsoft SQL Server verhindern möchte, dass eine weitere Position (4) eingetragen wird. Wie verhält sich Microsoft SQL Server, wenn mittels CHECK-Einschränkung bereits eine Restriktion vorhanden ist, die nachfolgende Einträge verhindert?
Zunächst wird für die Testtabelle festgelegt, dass maximal 3 Positionen pro Bestellungen erlaubt sind:
ALTER TABLE dbo.CustomerOrderDetails ADD CONSTRAINT chk_Position_Max CHECK (Position <= 3); GO
Lässt man obige anschließend erneut eine Abfrage für Bestellung 1001 laufen, stellt man fest, dass Microsoft SQL Server weiterhin den ersten Datensatz von Bestellung 1002 sperrt. Das Verhalten erklärt sich dadurch, dass transaktionale Isolationsstufen KEINE Regeln, die auf Ebene von Metadaten festgelegt werden, berücksichtigt. Transaktionen orientieren sich am ACID-Modell und müssen – gemäß ihrer Bestimmung – einen konsistenten Status gewährleisten.
Herzlichen Dank fürs Lesen!
