Im englischsprachigen Forum für Microsoft SQL Server kam eine Frage auf, in der es darum ging, dass zwei explizite Transaktionen den gleichen Datensatz / Datensätze einer System Versioned Temporal Table ändern wollen. Dabei kommt es zu Konflikten, die schwierig zu lösen sind. Bei den Überlegungen zu einer adäquaten Lösung war schnell klar, wo das Problem liegt – nicht aber, wie man dieses Problem am einfachsten lösen kann. Mit diesem Artikel möchte ich eine Lösungsmöglichkeit anbieten, die mich selbst nicht wirklich überzeugt. Dennoch ist es aus meiner Sicht die – aktuell – einzige Lösung für das Problem.

Inhaltsverzeichnis

Datum für Gültigkeitsbereich eines Datensatzes

Um das nachfolgende Problem zu verstehen, muss man das Konzept der System Versioned Temporal Tables in Bezug auf historische Datensätze verstehen. Wenn durch eine DML-Operation ein Datensatz in einer Temporal Table geändert wird, muss der ursprüngliche Datensatz mit einem Datumsbereich gespeichert werden, in dem er gültig gewesen ist. Hierzu verwendet Microsoft SQL Server IMMER die Startzeit der Transaktion! Das folgende Beispiel zeigt eine aus diesem Umstand resultierende Problematik:

CREATE TABLE dbo.Customers
(
    Id        INT          NOT NULL    IDENTITY (1, 1),
    Name      VARCHAR(100) NOT NULL,
    Street    VARCHAR(100) NOT NULL,
    ZIP       CHAR(5)      NOT NULL,
    City      VARCHAR(100) NOT NULL,
    ValidFrom DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT ('2016-01-01T00:00:00'),
    ValidTo   DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL DEFAULT ('9999-12-31T23:59:59'),

    CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
);
GO

CREATE TABLE history.Customers
(
    Id        INT          NOT NULL    IDENTITY (1, 1),
    Name      VARCHAR(100) NOT NULL,
    Street    VARCHAR(100) NOT NULL,
    ZIP       CHAR(5)      NOT NULL,
    City      VARCHAR(100) NOT NULL,
    ValidFrom DATETIME2(0) NOT NULL,
    ValidTo   DATETIME2(0) NOT NULL
);
GO

Problemstellung

Das eingefügte Video (Bitte etwas Nachsicht – ist mein erster Versuch, den Blog mit Videos zu versehen!) zeigt das Problem, wenn eine Transaktion zu lang läuft und erst zum Schluss die gewünschte Änderung an einem Datensatz durchführt.

Transaktion 1


-- TRANSACTION 1 starts a transaction which 
-- changes the affected record after 10 sec.
BEGIN TRANSACTION;
GO
    -- what is the start time of the transaction
    SELECT DTAT.transaction_begin_time
    FROM   sys.dm_tran_current_transaction AS DTCT
           INNER JOIN sys.dm_tran_active_transactions AS DTAT
           ON (DTCT.transaction_id = DTAT.transaction_id);

    RAISERROR (N'This is my loooong running process...', 0, 1) WITH NOWAIT;
    DECLARE    @I INT = 10
    WHILE @I > 0
    BEGIN
        RAISERROR (N'%i seconds to go...', 0, 1, @I) WITH NOWAIT;
        WAITFOR    DELAY '00:00:01'
        SET @I -= 1;
    END

    UPDATE dbo.Customers
    SET    Name = 'This is me'
    WHERE  Id = 10;
COMMIT TRANSACTION;
GO

Transaktion 2

-- TRANSACTION 2 starts a transaction and immediately
-- after the start it changes the affected record.
BEGIN TRANSACTION;
GO
    -- what is the start time of the transaction
    SELECT CAST(DTAT.transaction_begin_time AS DATETIME2(0))
    FROM   sys.dm_tran_current_transaction AS DTCT
           INNER JOIN sys.dm_tran_active_transactions AS DTAT
           ON (DTCT.transaction_id = DTAT.transaction_id);

    UPDATE dbo.Customers
    SET    Name = 'This is me'
    WHERE  Id = 10;
COMMIT;

SELECT * FROM dbo.Customers FOR SYSTEM_TIME ALL
WHERE    Id = 10;
GO

Zunächst wird Transaktion 1 (links) gestartet. In diesem Code ist eine Verzögerung von 10 Sekunden implementiert, die ein “laaaaaanges” Laufzeitverhalten in der Prozedur simuliert. Während Transaktion 1 läuft, wird im rechten Fenster Transaktion 2 gestartet. In Transaktion 2 wird eine Änderung an Datensatz 10 durchgeführt, während Transaktion 1 noch läuft. Sobald Transaktion 1 den gleichen Datensatz ändern möchte, wird die Transaktion mit einem Fehler beendet!

Ursache für den Abbruch ist Transaktion 2! Bei der Speicherung von Daten in der Verlaufstabelle muss ein Datum angegeben werden, BIS zu dem ein Datensatz in der ursprünglichen Tabelle gültig gewesen ist. Als validen Zeitstempel verwendet Microsoft SQL Server den Beginn einer Transaktion. T1 beginnt um 13:45:27.810; eine Änderung des Datensatzes findet aber erst 10 Sekunden später statt. Transaktion 2 beginnt um 13:45:33.000 (5 Sekunden später) und führt unmittelbar eine Änderung am Datensatz ID = 10 durch. Sobald Transaktion 1 den gleichen Datensatz ändern möchte, kommt es zu einem zeitlichen Konflikt, da Microsoft SQL Server versucht, den “früheren” Zeitstempel zu verwenden. Dadurch entstehen inkonsistente Daten in der Verlaufstabelle. Aus diesem Grund schlägt die Transaktion fehl und es findet ein Rollback statt.

Lösung

Um das Problem der Verzögerung zu lösen, muss der zu ändernde Datensatz zu Beginn der Transaktion blockiert werden. Nur so ist es möglich, eine weitere Transaktion daran zu hindern, den Datensatz zu ändern, während in Transaktion 1 andere Operationen ausgeführt werden.

-- TRANSACTION 1 starts a transaction which 
-- changes the affected record after 10 sec.
BEGIN TRANSACTION;
GO
    -- what is the start time of the transaction
    SELECT DTAT.transaction_begin_time
    FROM   sys.dm_tran_current_transaction AS DTCT
           INNER JOIN sys.dm_tran_active_transactions AS DTAT
           ON (DTCT.transaction_id = DTAT.transaction_id);

    -- Block the affected record with a X-Lock
     SELECT * FROM dbo.Customers WITH (UPDLOCK, HOLDLOCK)
     WHERE    Id = 10;

    RAISERROR (N'This is my loooong running process...', 0, 1) WITH NOWAIT;
    DECLARE    @I INT = 10
    WHILE @I > 0
    BEGIN
        RAISERROR (N'%i seconds to go...', 0, 1, @I) WITH NOWAIT;
        WAITFOR    DELAY '00:00:01'
        SET @I -= 1;
    END

    UPDATE dbo.Customers
    SET    Name = 'This is me'
    WHERE  Id = 10;
COMMIT TRANSACTION;
GO

Der Code von Transaktion 1 wurde minimal geändert, unmittelbar nach Beginn der Transaktion wird der Datensatz mittels eines UPDLOCK und HOLDLOCK innerhalb der Transaktion gesperrt. Somit kann Transaktion 2 nicht auf den Datensatz zugreifen sondern muss warten, bis Transaktion 1 beendet ist.

Bisher veröffentlichte Artikel zu System Versioned Temporal Tables

Herzlichen Dank fürs Lesen!

Temporal Tables – lang laufende Transaktionen