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
- Temporal Tables – INSTEAD OF Trigger
- Temporal Tables – Verwendung von Triggern
- Temporal Tables – Behandlung von NULL-Einschränkungen
- Temporal Tables – Umbenennung von Metadaten
- Temporal Tables – Programmierung vs. Systemlösung
- Temporal Tables – Verwendung von Triggern
Herzlichen Dank fürs Lesen!