Bereits zum 3. Mal wurde ich zu einem Notfalleinsatz gerufen, bei dem ein Microsoft SQL Server völlig unerwartet einen Performanceeinbruch hatte. Die üblichen Verdächtigen (Parameter Sniffing, Statistiken, …) konnten nach kurzer Prüfung ausgeschlossen werden. Die weitere Suche brachte dann eine interessante Ursache zu Tage, die ich so bisher noch nie gesehen habe. Wenn eine Datenbank mit READ COMMITTED SNAPSHOT ISOLATION arbeitet, sollte man seinen Applikationscode (auch in SQL Server) gründlich testen!

Read Committed Snapshot Isolation

Das Problem in jedem Datenbanksystem, mit dem sich ein Programmierer auseinandersetzen muss, ist die Behandlung von „Gleichzeitigkeit“ von vielen Anwendern. In einer Datenbank werden tausende von gleichzeitigen Befehlen zum Auswählen und Bearbeiten von Datensätzen ausgeführt. Microsoft SQL Server muss diese Anforderungen serialisieren und die Anforderungen nacheinander abarbeiten. Bei der Bearbeitung von Datensätzen (Auswählen oder Bearbeiten) verwendet Microsoft SQL Server Sperren, um die Ressourcen während des Zugriffs vor anderen Prozessen zu schützen. Hierbei spielt insbesondere der Schutz von Schreibzugriffen eine wichtige Rolle. Bearbeitet z. B. in einer Lagerverwaltung ein Mitarbeiter die Lagermenge eines Produkts (UPDATE), dann muss Microsoft SQL Server sicherstellen, dass zur gleichen Zeit dieser Datensatz nicht von einem anderen Prozess gelesen werden darf.

Read Committed Workflow #1

Transaktion 1 beginnt um 18:34:50, den Datensatz Nr. 10 zu bearbeiten während Transaktion 2 nur eine Sekunde später versucht, den Datensatz Nr. 10 zu lesen. Transaktion 1 verwendet eine exklusive Sperre (X) auf dem Datensatz. Dadurch wird Transaktion 2 daran gehindert, den Datensatz zu lesen. Dieses Verhalten nennt sich pessimistisches Sperrverhalten und ist das Standardverhalten in der Isolationsstufe „READ COMMITTED„. Um dieses Sperrverhalten zu umgehen, wurde mit Microsoft SQL Server 2008 zum ersten Mal READ COMMITTED SNAPSHOT ISOLATION (RCSI) eingeführt. Der Name „Momentaufnahme“ definiert, dass alle Abfragen in der Transaktion auf dieselbe Version (Momentaufnahme) der Datenbank zurückgehen, die auf dem Zustand der Datenbank zum Zeitpunkt des Beginns der Transaktion basiert. Wenn READ_COMMITTED_SNAPSHOT auf ON festgelegt wird, verwendet Datenbankmodul die Zeilenversionsverwaltung, um jede Anweisung mit einer hinsichtlich der Transaktionen konsistenten Momentaufnahme der Daten so darzustellen, wie sie zu Beginn der Anweisung vorhanden waren. Es werden keine Sperren verwendet, um die Daten vor Updates durch andere Transaktionen zu schützen.

READ COMMITTED SNAPSHOT ISOLATION #2

Sobald RCSI für eine Datenbank aktiviert ist, wird zu Beginn der Transaktion 1 eine Kopie der Datenseite in TEMPDB abgelegt. Wenn anschließend Transaktion 2 Daten aus der Datenseite lesen möchte, wird der Inhalt aus der Kopie gelesen. Dadurch wird sichergestellt, dass Transaktion 2 die gültigen Daten zu Beginn der Transaktion liest und somit einen konsistenten Zustand erhält. Viele Entwickler von konkurrierenden Systemen verwenden gerne diese Alternative, um somit z. B. Deadlock-Situationen zu umgehen. Das diese Lösung nicht immer die „Silver Bullet“ ist, die alle Probleme lösen kann, soll das nachfolgende Beispiel demonstrieren.

Testumgebung

Alle Systeme, auf denen ich die Probleme im Zusammenhang mit RCSI gefunden habe, verwendeten einen ähnlich gelagerten Workload. Dabei geht es um relativ kleine Tabellen (<=75.000 Datensätze), die durch eine sehr hohe Anzahl von INSERT-und LÖSCH-Vorgängen aufgefallen sind. Der Inhalt dieser Tabellen waren „rotierende“ Daten, die z. B. in einer Lagerverwaltung verwendet werden. Z. B. werden in der Automobilindustrie Statusinformationen über die Karosserieträger an jeder Stelle auf der Fertigungsstrecke gesammelt. Damit kann man feststellen, an welcher Stelle im Produktionszyklus die Karosserie aktuell ist. Sobald der Fertigungsprozess abgeschlossen ist, kann der Statusdatensatz aus dem System gelöscht werden. Das Verhalten mit Hilfe einer Testtabelle simuliert werden. Dabei werden jedoch nur neue Datensätze eingetragen und alte Datensätze gelöscht:

Rotating data

Die Abbildung zeigt die Abfolge des Workloads. Als Anfangsbestand werden n Datensätze verwendet. Anschließend wird in einer Endlosschleife jeweils ein neuer Datensatz hinzugefügt und anschließend ein Bestandsdatensatz nach dem FIFO-Prinzip aus der Tabelle gelöscht.Dieses System verdeutlicht den Workload, wie er z. B. in automatisierter Lagersoftware oder der Automobilindustrie verwendet wird.

Aktivieren von RCSI für Demo-Datenbank

Zunächst muss die Datenbank für RCSI vorbereitet werden. Am einfachsten geht das mit T-SQL, wie der nachfolgende Code zeigt:

-- Use the demo database and activate RCSI
USE master;
GO
 
ALTER DATABASE demo_db SET READ_COMMITTED_SNAPSHOT ON;
GO
 
USE demo_db;
GO

Erstellen der Demotabelle mit ein paar Beispieldatensätzen

Nachdem die Datenbank für RCSI präpariert wurde, kann mit der Testtabelle fortgefahren werden. Um schnell und effizient ein paar Tausend Datensätze zu generieren, verwende ich das folgende Skript:

IF OBJECT_ID(N'dbo.demo_table', N'U') IS NOT NULL
    DROP TABLE dbo.demo_table;
    GO
 
CREATE TABLE dbo.demo_table
(
    ID    INT       NOT NULL    IDENTITY (1, 1),
    C1    CHAR(100) NOT NULL
);
GO

INSERT INTO dbo.demo_table (C1)
SELECT TOP (1000)
       CAST(TEXT AS CHAR(100)) AS C1
FROM   sys.messages
WHERE  language_id = 1031;
GO
 
CREATE UNIQUE CLUSTERED INDEX cuix_demo_table_Id
ON dbo.demo_table (Id);
GO

Sobald die Tabelle erstellt ist, wird die folgende Abfrage ausgeführt, um die Effizienz des Index auf [Id] zu prüfen:

SELECT MAX(Id) FROM dbo.demo_table;

Die Anzahl der Datenseiten sowie der Datenmenge ist überschaubar und das Experiment kann beginnen.

-- How many data pages and ghost records do we have?
SELECT  page_count,
        avg_page_space_used_in_percent,
        record_count,
        version_ghost_record_count
FROM    sys.dm_db_index_physical_stats
        (
            DB_ID(),
            OBJECT_ID(N'dbo.demo_table', N'U'),
            1,
            NULL,
            N'DETAILED'
        )
WHERE   index_level = 0;
GO

image

Starten des Workloads

Mit dem folgenden Code wird der oben beschriebene Workload in einem neuen Abfragefenster gestartet. Hierbei wird zunächst eine Transaktion geöffnet und anschließend wird fortlaufend am Ende der Tabelle ein neuer Datensatz eingefügt. Nach 10 ms wird der Datensatz mit der kleinsten [ID] aus der Tabelle entfernt. Die Besonderheit in diesem Workload (und genau das war auch der Fehler in allen 3 Vorfällen!) ist, dass die Transaktion während des gesamten Laufs (in einem Fall mehrere Tage) geöffnet bleibt!

BEGIN TRANSACTION;
GO
 
    -- Insert new record into dbo.Customers
    WHILE (1 = 1)
    BEGIN
        -- wait 10 ms before each new process
        WAITFOR DELAY '00:00:00:010';
 
        INSERT INTO dbo.demo_table(C1)
        SELECT C1
        FROM   dbo.demo_table
        WHERE  Id = (SELECT MIN(Id) FROM dbo.demo_table);
 
        -- Wait 10 ms to delete the first record from the table
        WAITFOR DELAY '00:00:00:010';
 
        -- Now select the min record from the table
        DELETE dbo.demo_table WHERE Id = (SELECT MIN(Id) FROM dbo.demo_table);
    END

Alle 10 ms wird innerhalb einer expliziten Transaktion für die Simulation der erste Datensatz am Ende neu eingefügt. Weitere 10 ms später wird der älteste Datensatz gelöscht. Dadurch ergibt sich ein rotierendes System, wie es in den drei Vorfällen angewendet wurde. Diese Transaktion lief für insgesamt 3 Minuten. In einem weiteren Fenster wurde eine simple Aggregation zu Beginn und zum Ende der Demo ausgeführt. Überraschend war das produzierte IO am Ende des Tests!

-- result of the I/O with RCSI
SELECT MAX(ID) FROM dbo.demo_table;
GO
 
-- result of the I/O without RCSI
SELECT MAX(ID) FROM dbo.demo_table WITH (NOLOCK);
GO

IO Output after Workload

Der erste Output zeigt eine deutliche Erhöhung des IO während die Ausführung in der READ UNCOMMITTED Isolationsstufe die erwarteten 2-3 IO produziert. Über die Laufzeit hat sich die Anzahl der allokierten Datenseiten wie folgt verändert:

no_of_version_ghost_record_count

Der lineare Anstieg der Anzahl von version_ghost_record_count ist zu erwarten, da die Zeitintervalle für die Verarbeitung der Daten nicht variiert.

Grund für dieses Verhalten

Der Grund für dieses Verhalten ist einfach und schnell erläutert; der Workload für die „Rotation der Daten“ verursacht permanent neue Kopien von Datenseiten, die innerhalb der Transaktion bearbeitet werden. Würden nicht kontinuierlich am Ende der Tabelle neue Datensätze eingefügt werden, wäre die Anzahl der Datenseiten im Version Store auf die existierende Datenmenge begrenzt. Die Aggregationsfunktion in Verbindung mit RCSI muss jedes Mal den Version Store berücksichtigen, um einen Wert zurückzuliefern. Wird die Abfrage jedoch in der Isolationsstufe READ UNCOMMITTED ausgeführt, kann RCSI nicht greifen (wird nur in Verbindung mit READ COMMITTED verwendet!) und es entstehen Dirty Reads. In diesem Fall kann Microsoft SQL Server auf den Index zugreifen, ohne den Version Store zu berücksichtigen.

Lösung des Problems

Der Fehler in allen beobachteten Fällen liegt nicht bei Microsoft SQL Server sondern am implementierten Workload. Da die Transaktionen nur zu beginn explizit implementiert wurden, bleibt die Transaktion geöffnet und der Version Store kann nicht freigegeben werden. Jeder Vorgang muss – sofern eine explizite Transaktion im Spiel ist – mit einem COMMIT TRANSACTION beendet werden. Nur dann wird der Version Store wieder freigegeben und die version_ghost_records entfernt.

Vielen Dank fürs Lesen!