Im Kommentar zu meinem Artikel “Temporal Tables – Umbenennen von Metadaten” hat ein von mir sehr geschätzter Kollege aus meiner Access-Zeit – Philipp Stiefel (w) – angemerkt, dass eine Gegenüberstellung von Programmierung und Systemlösung interessant wäre. Das finde ich auch – also wurde der Urlaub dazu genutzt, sich mit den unterschiedlichen Lösungsansätzen zu beschäftigen.

Temporal Tables mit Hilfe einer Eigenlösung

Wer noch keine Möglichkeit hat, mit Microsoft SQL Server 2016 zu arbeiten, wird nicht umhin kommen, eine Implementierung von “Temporal Tables” in Eigenregie zu realisieren. Hierzu gibt es drei mögliche Lösungsansätze:

  1. Implementierung in der Clientsoftware
  2. Implementierung durch Stored Procedures
  3. Implementierung durch Trigger

Die Optionen 1 und 2 fallen in diesem Artikel aus der Betrachtung heraus, da diese Lösungen eine Protokollierung verhindern, wenn unmittelbar oder durch Drittsoftware (z. B. Access) Zugang zu den Tabellen zu erwarten ist. Meine Erfahrung hat gezeigt, dass bisher KEINE Software konsequent eine Abstraktionsschicht verwendet (Views / Stored Procedures), um den unmittelbaren Zugang zu den Tabellen zu verhindern. Aus diesem Grund betrachte ich in diesem Artikel ausschließlich die Implementierung durch Trigger.

Grundsätzliche Einschränkungen bei einer programmierten Lösung

Unabhängig von der gewählten Lösung gibt es in einer programmierten Lösung generelle Probleme, die nicht zu lösen sind:

  • Die Tabelle für die Historisierung besitzt KEINE unmittelbare Abhängigkeit zur “Temporal Table”!
  • Die Tabelle für die Historisierung kann ohne Berücksichtigung der “Temporal Table” in ihrer Struktur manipuliert werden und vice versa!
  • Die Tabelle für die Historisierung ist eine gewöhnliche Tabelle. Somit besteht Raum für direkte Manipulationen des Dateninhalts!
  • Möglichkeiten des “Verbergens” von Attributen für die Versionierung bestehen nicht – es muss über Views eine Möglichkeit geschaffen werden.
  • Sofern nicht mit der Enterprise-Edition gearbeitet wird, ist PAGE-Compression (Standard für die History Table) nicht möglich!

Szenario für Trigger

Wie schon in den bisher erstellten Artikeln wird eine Tabelle [dbo].[Customer] für die Beispiele verwendet. Für die Historisierung wird die Tabelle [history].[Customers] verwendet.

IF SCHEMA_ID(N'history') IS NULL
    EXEC sp_executesql N'CREATE SCHEMA [history] AUTHORIZATION dbo;';
    GO

IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
    DROP TABLE dbo.Customers;
    GO

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) NOT NULL    DEFAULT ('2016-01-01T00:00:00'),
    ValidTo    DATETIME2(0) NOT NULL    DEFAULT ('9999-12-31T23:59:59'),

    CONSTRAINT pk_Customers_ID PRIMARY KEY CLUSTERED (Id)
);
GO

CREATE TABLE history.Customers
(
    Id         INT          NOT NULL,
    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

Für die Implementierung von “Temporal Tables” mit Triggern müssen zwei Situationen/Ereignisse in der Tabelle berücksichtigt werden.

UPDATE-Trigger

Der UPDATE-Trigger wird ausgeführt, sobald ein bestehender Datensatz manipuliert wird. Der Trigger besitzt eine simple Struktur. Zunächst muss ein Zeitstempel für die Manipulation generiert werden um ihn anschließend in beiden Tabellen für die Gültigkeit zu verwenden. Während der Zeitstempel in der “Temporal Table” für den Beginn der Validierung verwendet wird, muss der in die “History Table” einzufügende Datensatz diesen Zeitstempel für das Ende der Gültigkeit erhalten.

CREATE TRIGGER dbo.trg_Customers_Update
ON dbo.Customers
FOR UPDATE
AS
    SET NOCOUNT ON;

    DECLARE @Timestamp  DATETIME2(0) = GETDATE();

    -- in the first step we insert the "old" record with a validation stamp
    -- into the history tables
    INSERT INTO history.Customers (Id, Name, Street, ZIP, City, ValidFrom, ValidTo)
    SELECT Id, Name, Street, ZIP, City, ValidFrom, @TimeStamp
    FROM   deleted;

    -- now we have to update the original row in the ValidFrom attribute
    UPDATE C
    SET    C.ValidFrom = @TimeStamp
    FROM   dbo.Customers AS C INNER JOIN inserted AS I
           ON (C.Id = I.Id);

    SET NOCOUNT OFF;
GO

DELETE-Trigger

Wird ein Datensatz aus der Tabelle gelöscht, muss der Datensatz in die “History Table” übertragen werden. Ebenfalls muss protokolliert werden, bis wann dieser Datensatz im der “Temporal Table” vorhanden war.

CREATE TRIGGER dbo.trg_Customers_Delete
ON dbo.Customers
FOR DELETE
AS
    SET NOCOUNT ON;

    DECLARE @TimeStamp DATETIME2(0) = GETDATE();

    INSERT INTO history.Customers
    SELECT Id, Name, Street, ZIP, City, ValidFrom, @TimeStamp
    FROM   deleted;

    SET NOCOUNT OFF;
GO

Welche Ressourcen werden bei einem Update blockiert?

Die Verwendung von Triggern ist mit Vorsicht zu genießen – insbesondere in Umgebungen mit hohem Transaktionsvolumen. Die folgende Abbildung zeigt den Prozessaufruf für ein UPDATE.

Coding - Transactional Process

Insgesamt unterteilt sich die Aktualisierung in drei Phasen. In Phase 1 wird der Wert für das Attribut [Street] geändert und der Trigger aktiviert. In der zweiten Phase wird der ursprüngliche Datensatz in der Tabelle [history].[Customers] gespeichert. In diesem Abschnitt wird der zuvor ermittelte Zeitstempel verwendet, um das Gültigkeitsende des Datensatzes zu bestimmen. In der letzten Phase muss erneut der geänderte Datensatz aktualisiert werden, da der ermittelte Zeitstempel nun als neues Startdatum für die Gültigkeit des Datensatzes verwendet wird.

In der Praxis sieht der Prozess wie folgt aus:

-- Activation of output of the locked resources to the client
DBCC TRACEON (3604, 1200, -1);
GO

Die Traceflags sorgen dafür, dass die Sperren, die während der Aktualisierung gesetzt werden, am Client sichtbar gemacht werden. Anschließend wird in einer expliziten Transaktion der Datensatz geändert. Die Transaktion bleibt für weitere Untersuchungen geöffnet!

BEGIN TRANSACTION;
GO
    UPDATE dbo.Customers
    SET    Street = 'Musterweg 1'
    WHERE  Id = 33906;

Blocked resources 01

Die Ausgabe der Ressourcen zeigt, in welcher Reihenfolge die Tabellen/Datensätze blockiert werden. Im ersten Abschnitt wird in der Tabelle [dbo].[Customers] (OBJECT: 6:565577053:0) der Datensatz mit der Id = 33906 exklusiv blockiert. Hierzu wird hierarchisch zunächst die Tabelle und dann die Datenseite mit einem “Intent Exclusive Lock” blockiert. Anschließend wird der Datensatz selbst mit einem eXclusive-Lock blockiert.

Der nächste Abschnitt ist aus Performance-Sicht sehr interessant. Wie man erkennen kann, wird die Datenbank 2 in der Transaktion benutzt. Die Datenbank mit der ID = 2 ist TEMPDB! Bei der Verwendung von Triggern werden zwei Systemtabellen innerhalb eines Triggers benötigt. In einem UPDATE-Prozess sind es die Tabellen [inserted] und [deleted]. Diese Objekte werden in TEMPDB angelegt und verwaltet. Nachdem die Aktualisierung des Datensatzes abgeschlossen wurde, wird der Datensatz wieder freigegeben. Anschließend muss die Tabelle [history].[Customers] (OBJECT: 6:645577338:0) verwendet werden, da der Trigger den vorherigen Datensatz in diese Tabelle einträgt. Abschließend wird erneut eine Aktualisierung (U-Lock –> X-Lock) der Tabelle [dbo].[Customers] durchgeführt, um das Attribut [ValidFrom] neu zu setzen. Neben den eXklusiven Sperren der beiden Tabellen führt insbesondere der Zugriff auf TEMPDB zu einem nicht unerheblichen Einfluss auf die Performance, wenn TEMPDB nicht richtig konfiguriert ist!

Ein Blick in das Transaktionsprotokoll zeigt die Aufrufreihenfolge aus Sicht der durchgeführten Transaktion

SELECT [Current LSN],
       [Operation],
       [Log Record Length] + [Log Reserve] AS LogVolume,
       AllocUnitName,
       [Page ID],
       [Slot ID]
FROM   sys.fn_dblog(NULL, NULL);

FN_DBLOG_01

Sobald die Transaktion beginnt, wird eine Aktualisierung ([LOP_MODIFY_ROW]) auf die Tabelle [dbo].[Customers] durchgeführt. Anschließend wird der “alte” Datensatz in die Tabelle [history].[Customers] eingetragen ([LOP_INSERT_ROWS]). Da der Trigger jedoch erneut die Tabelle [dbo].[Customers] aktualisieren muss, wird diese Tabelle erneut in der Transaktion bearbeitet ([LOP_MODIFY_ROW]).

Trigger = Deadlock

Wie die Aufrufkette in der obigen Prozessbeschreibung zeigt, werden zwei Ressourcen in einer Wechselwirkung zueinander blockiert. Dieses Verhalten kann dazu führen, dass es vermehrt zu Deadlock-Problemen kommt. Die Situation wird durch den Trigger initiiert. Das folgende Beispiel zeigt eine Situation, in der ein Deadlock eine Transaktion beendet.

DEADLOCK-Situation-01

In Transaktion 1 (T1) wird ein SELECT auf [history].[Customers] ausgeführt, dem unmittelbar danach ein SELECT auf [dbo].[Customers] folgt. Wenn zwischen beiden Aufrufen eine Aktualisierung auf [dbo].[Customers] ausgeführt wird, versucht der Trigger eine X-Sperre auf [history].[Customers]. Diese X-Sperre kann nicht gesetzt werden, da  T1 die Tabelle noch im Zugriff hat. Gleichwohl kann T1 nicht auf [dbo].[Customers] zugreifen, da sie von T2 exklusiv blockiert ist.

Das nachfolgende Script wird in einem neuen Abfragefenster gestartet. Um einen Deadlock zu erzwingen wurde eine restriktive Isolationsstufe gewählt: (SERIALIZABLE).

SET TRAN ISOLATION LEVEL SERIALIZABLE;
GO

BEGIN TRANSACTION;
GO
    SELECT * FROM History.Customers AS H
    WHERE  H.Id = 10;
    GO

In einem zweiten Abfragefenster wird die folgende Transaktion gestartet:

UPDATE dbo.Customers
SET    Name = 'db Berater GmbH'
WHERE  Id = 10;

Diese Transaktion wird begonnen aber nicht beendet. Grund dafür sind Blockaden auf der Ressource [history].[Customers].

SELECT DTL.request_session_id,
       CASE WHEN DTL.resource_type = N'OBJECT'
            THEN SCHEMA_NAME(T.schema_id) + N'.' + OBJECT_NAME(DTL.resource_associated_entity_id)
            ELSE DTL.resource_description
       END AS object_resource,
       DTL.request_mode,
       DTL.request_type,
       DTL.request_status
FROM   sys.dm_tran_locks AS DTL LEFT JOIN sys.tables AS T
       ON (DTL.resource_associated_entity_id = T.object_id)
WHERE  DTL.resource_database_id = DB_ID()
       AND DTL.resource_type != N'DATABASE'
ORDER BY
       DTL.request_session_id;

TRAN_LOCKS_01

Die Abbildung zeigt, dass der Prozess 57 eine IX-Sperre auf die Tabelle [history].[Customers] setzen möchte aber nicht erhält, weil Prozess 54 bereits eine S-Sperre auf die Ressource gesetzt hat. Prozess 57 hat aber bereits eine IX-Sperre auf [dbo].[Customers] gesetzt. Nun versucht Prozess 54 ein SELECT auf [dbo].[Customers]:

SELECT * FROM dbo.Customers AS C
WHERE C.Id = 10;

Nach einigen Sekunden wird der Prozess als DEADLOCK-Opfer beendet!

DEADLOCK-Situation-02

Abfragen auf Basis von Zeitstempeln

Mit “System Versioned Temporal Tables” ist es innerhalb einer Abfrage möglich, für einen bestimmten Zeitpunkt den Status des gewünschten Datensatzes zu ermitteln. Diese Möglichkeit besteht für eine “Eigenlösung” natürlich nicht, da die Syntax ohne “System Versioned Temporal Tables” nicht funktioniert. In diesem Fall bleibt nur die Möglichkeit einer programmierten Lösung, die – basierend auf der Annahme, dass ein bestimmter Zeitpunkt angegeben wird – mit Hilfe einer INLINE-Funktion implementiert wird.

CREATE FUNCTION dbo.if_Customers
(
    @Id        int,
    @TimeStamp datetime2(0)
)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM   dbo.Customers
    WHERE  (
            @Id = 0 OR
            Id = @Id
           ) AND
           ValidFrom <= @TimeStamp AND
           ValidTo >= @TimeStamp

    UNION ALL

    SELECT *
    FROM   history.Customers
    WHERE  (
            @Id = 0 OR
            Id = @Id
           ) AND
           ValidFrom <= @TimeStamp AND
           ValidTo >= @TimeStamp
);
GO

Die Funktion muss beide Tabellen abfragen und den jeweiligen Zeitraum berücksichtigen. Mit “System Versioned Temporal Tables” gibt es weitere Abfragemöglichkeiten, die in einer programmierten Version selbst erstellt werden müssten. Bei der Erstellung der Funktionen ist zu berücksichtigen, dass es sich immer um Inline-Funktionen handelt. Ansonsten könnte es Probleme bei Abfragen geben, die diese Funktion mittels JOIN oder CROSS APPLY verwenden, da in solchen Fällen immer von einer Rückgabemenge von 1 Datensatz ausgegangen wird!

Szenario für “System Versioned Temporal Tables”

Das gleiche Szenario wird nun für “System Versioned Temporal Tables” durchgeführt. Hierbei interessiert primär, welche Ressourcen belegt/blockiert werden und wie sich das Transaktionsvolumen im Verhältnis zur Triggerlösung verhält.

Welche Ressourcen werden bei einem Update blockiert?

Erneut wird ein Update auf einen Datensatz in der “System Versioned Temporal Table” durchgeführt, um zu prüfen, welche Ressourcen gesperrt werden.

Blocked resources 02

Die Ausgabe der blockierten Ressourcen zeigt die Reihenfolge, in der die Tabellen/Datensätze blockiert werden. Hervorzuheben sind die ersten beiden Sperren. Anders als bei der “programmierten” Version sperrt Microsoft SQL Server bereits zu Beginn der Transaktion BEIDE Tabellen! Zu Beginn wird ein Intent Exclusive Lock auf die Tabelle [dbo].[Customers] (OBJECT: 6:565577053:0) gesetzt um unmittelbar im Anschluss die Tabelle [history].[Customers] (OBJECT: 6:629577281:0) zu sperren. Durch die IX-Sperren wird signalisiert, dass in tieferen Hierarchien eine X-Sperre gesetzt werden soll. Sobald ein IX-Sperre auf die Datenseite (PAGE: 6:1:818) gesetzt wurde, kann eine X-Sperre auf den Datensatz in [dbo].[Customers] angewendet werden.

Erst, wenn die exklusive Sperre auf de Datensatz angewendet werden kann, wird in der Tabelle für den neu hinzuzufügenden Datensatz eine entsprechende Datenseite nebst Slot gesperrt. Dieser Teil der Transaktion beschreibt das Hinzufügen des ursprünglichen Datensatzes in die Tabelle [history].[Customers].

Ein Blick in das Transaktionsprotokoll zeigt die Aufrufreihenfolge aus Sicht der durchgeführten Transaktion.

Locked resources 02

Zu Beginn wird die Zeile in [dbo].[Customers] aktualisiert um anschließend in [history].[Customers] den vorherigen Datensatz einzutragen. Die Zeilen 6 – 10 sind für die Bewertung der Transaktion irrelevant; sie zeigen lediglich, dass in [history].[Customers] vor dem Eintragen eines neuen Datensatzes eine neue Datenseite allokiert wurde.

Deadlock-Szenarien

Sicherlich sind auch in diesem Szenario DEADLOCK-Gefahren vorhanden. Sie entsprechen aber anderer Natur und liegen eher im Design der Indexe. Ist es in der programmierten Version notwendig, die Objekte “sequentiell” und getrennt voneinander zu sperren, so lässt eine Systemlösung von “Temporal Tables” dieses Szenario nicht zu. Microsoft SQL Server sperrt immer BEIDE Tabellen vor einer Manipulation von Datensätzen. Damit kann sich kein weiterer Prozess zwischen die Transaktion schieben; ein Deadlock ist – bezüglich der beschriebenen Version – nicht mehr möglich!

Abfragen auf Basis von Zeitstempeln

Ganz klar liegt hier der große Vorteil von “System Versioned Temporal Tables”. Statt – wie in der programmierten Version – mit eigenen Funktionen die Funktionalität von “Temporal Tables” nachzubauen, bedient man sich im neuen Feature von Microsoft SQL Server 2016 lediglich der erweiterten Syntax von System Versioned Temporal Tables.

SELECT * FROM dbo.Customers
FOR SYSTEM_TIME AS OF '2016-07-05T16:00:00' AS C
WHERE  Id = 33906;

Statt komplizierter Abfragen reicht der Hinweis “FOR SYSTEM_TIME…” um verschiedene Abfragemöglichkeiten basierend auf Zeitstempeln zu generieren. Ob diese Abfragen performant sind oder Verbesserungspotential haben, soll in einem anderen Artikel detailliert untersucht werden.

Bisher veröffentlichte Artikel zu System Versioned Temporal Tables

Vielen Dank fürs Lesen!