Wer kennt nicht den folgenden Sachverhalt? Eine Relation besitzt ein Attribut mit der Eigenschaft IDENTITY. Ein Datensatz soll eingetragen werden und dieser Prozess schlägt – aus welchen Gründen auch immer – fehl. Obwohl der Datensatz nicht erfolgreich eingetragen werden konnte, wurde der IDENTITY-Wert um <increment> erhöht. Warum passiert das und wie lässt sich der Grund für dieses Verhalten nachvollziehen? Das in diesem Artikel gezeigte Beispiel beschreibt die Hintergründe für dieses Verhalten in detaillierter Form.

Ausgangsstellung

Um das Verhalten zu demonstrieren, wird eine einfache Relation mit einen Attribut [Id] für die Speicherung fortlaufender Nummern erstellt. Die fortlaufende Nummerierung  wird durch die Eigenschaft IDENTITY sichergestellt. Die Relation besitzt folgenden Aufbau:

CREATE TABLE dbo.dummy
(
    Id           int          NOT NULL IDENTITY (1, 1),
    FirstName    varchar(50)  NOT NULL,
    LastName     varchar(50)  NOT NULL,
    MemberSince  date         NOT NULL,

    CONSTRAINT pk_dummy_id PRIMARY KEY CLUSTERED (Id)
);
GO

Bevor Daten in die Relation eingetragen werden, schauen wir uns die physikalischen Daten der Relation an. Für das Ergebnis wird folgende Abfrage auf [sys].[dm_db_index_physical_stats] ausgeführt:

DECLARE @object_id int = OBJECT_ID('dbo.dummy');

SELECT OBJECT_NAME(i.object_Id) AS table_name,
       i.name                   AS index_name,
       i.type_desc              AS index_type,
       phs.index_level,
       phs.page_count,
       phs.record_count,
       phs.ghost_record_count
FROM   sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), @object_id, DEFAULT, DEFAULT, 'DETAILED') phs        ON (
           i.object_id = phs.object_id AND
           i.index_id = phs.index_id
          )
ORDER BY
        i.index_id ASC

Das Ergebnis zeigt, dass KEINE Daten in der Relation vorhanden sind. Im nächsten Schritt wird eine explizite Transaktion gestartet. Innerhalb dieser geöffneten Transaktion wird ein Datensatz in die Relation eingetragen.

BEGIN TRANSACTION
    INSERT INTO dbo.dummy (Firstname, lastname, membersince)
    VALUES ('Uwe', 'Ricken', '20130101')

Führt man ein SELECT auf die Relation aus, werden keine Datensätze zurück geliefert, da die Transaktion noch nicht abgeschlossen ist. Schaut man sich jedoch erneut die physikalische Struktur des Index an, wird man feststellen, dass dort “ein” Datensatz bereits vorhanden ist.

Obwohl die Transaktion noch nicht bestätigt wurde, wurden bereits Ressourcen verwendet. Ein Datensatz ist der Relation hinzugefügt worden. Microsoft SQL Server führt den INSERT bereits physikalisch aus, obwohl die Transaktion noch nicht abgeschlossen ist. Das belegt auch ein Blick in die offenen Transaktionen sowie das Transaktionsprotokoll:

SELECT CASE WHEN resource_description = ''
            THEN OBJECT_NAME(resource_associated_entity_id)
            ELSE ''
       END,
       resource_type,
       resource_description,
       request_mode,
       request_type,
       request_status
FROM   sys.dm_tran_locks
WHERE  resource_database_id = db_id() AND
       resource_type != 'DATABASE';

Die Abfrage zeigt einen Überblick über die offenen Transaktionen und deren gesperrte Objekte. Die Abfrage wurde nicht nach der weiter oben explizit geöffneten Transaktion gefiltert, da es sich ja um einen Test handelt. Um sicher zu stellen, dass auch nur die betroffenen Session überprüft wird, kann das Attribut [request_session_id] zur weiteren Filterung verwendet werden. Interessant ist jedoch das Ergebnis und dessen Analyse:

Wie man aus der Abbildung erkennen kann, gibt es eine exklusive Zeilensperre. Für die Seite selbst ist ein “Intended exclusive lock” aktiv. Diese Daten bedeuten, dass Microsoft SQL Server zunächst eine exklusive Sperre auf die Zeile gesetzt hat, jedoch eine Seitensperre “ankündigt”. Besonders wichtig bei der Bewertung ist, dass bereits ein KeyHashValue für den Eintrag in den Clustered Index definiert wurde – der Datensatz also bereits auf die Seite 1904 in Datei 1 der Datenbank geschrieben wurde. Um das zu belegen, muss die Seite 1:1904 analysiert werden:

DBCC TRACEON(3604);
DBCC PAGE ('db_demo', 1, 1904, 3) WITH TABLERESULTS;

Der Platz für den Datensatz ist bereits physikalisch von Microsoft SQL Server vorbelegt. Dieser Datensatz ist innerhalb der Transaktion sichtbar, wenn der “Transaction Isolation Level” READ COMMITTED ist (ist Standard). Fakt ist aber, dass für [Id] bereits ein Wert vergeben wurde, da der Datensatz bereits physikalischen Platz auf der Datenseite belegt. Das zeigt auch eine Untersuchung des Transaktionsprotokolls. Um den zu untersuchenden Inhalt der Transaktionen auf die wesentlichen Aktionen zu reduzieren, wird die nachfolgende Abfrage mittels der undokumentierten DMF fn_dblog() ausgeführt:

SELECT ROW_NUMBER() OVER (ORDER BY [Current LSN]) AS Action_Id,
       [Current LSN],
       Operation,
       Context,
       AllocUnitName,
       [Page ID],
       [Slot ID],
       [Lock Information],
       [Number of Locks],
       [Num Elements]
FROM   fn_dblog(NULL, NULL)
WHERE  Operation IN ('LOP_MODIFY_ROW', 'LOP_FORMAT_PAGE', 'LOP_ROOT_CHANGE', 'LOP_INSERT_ROWS', 'LOP_COMMIT_XACT', 'LOP_DELETE_ROWS', 'LOP_ABORT_XACT')
ORDER BY
       [Current LSN];

Das Ergebnis gibt Aufschluss über die protokollierten Aktionen, die Microsoft SQL Server für das Eintragen des Datensatzes vorgenommen hat:

Interessant ist Action_id 1, auf die im späteren Verlauf dieses Artikels noch eingegangen wird. Auf Grund der Tatsache, dass bisher noch keine Datensätze vorhanden sind, wurde die Allokation der IAM (Index Allocation Map) in PFS (Page Free Space) vorgenommen (Action_Id = 2). Anschließend wird die IAM für den Clustered Index erzeugt (Action_Id = 4). Bevor der Datensatz selbst eingetragen werden kann (Action_id = 12) wurde die Rootpage (Action_Id 7 – 11) für den Clustered Index angelegt. Der Datensatz allokiert – vollständig protokolliert – bereits Ressourcen des Microsoft SQL Servers.

Nun wird die Transaktion rückgängig gemacht; die Transaktion wird mittels ROLLBACK TRANSACTION unvollendet beendet. Interessant ist nun, was im Transaktionslog gespeichert wurde.

Wie man deutlich erkennen kann, hat Microsoft SQL Server beim ROLLBACK nicht einfach die Änderungen an den Datenseiten “nicht gespeichert” vielmehr wurde EXPLIZIT der Eintrag aus der Datenseite wieder gelöscht (Action_Id = 13). Erst anschließend wurde die Transaktion abgebrochen (Action_Id = 14). Exakt dieses Verhalten wird nun auch durch die physikalische Analyse des Indexes widergespiegelt:

Folgt man den Aktionen aus dem Transaktionslog, ist erkennbar, warum [page_count] = 1 ist und keine Datensätze vorhanden sind. Statt eines vorhandenen Datensatze [record_count] wurde auf der – transaktionell vollständig abgeschlossenen – Datenseite der Datensatz als “kann gelöscht werden” markiert [LCK_MARK_AS_GHOST]. Zum Beweis wird auch noch einmal der Inhalt der betroffenen Datenseite mit DBCC PAGE ausgelesen:

Die Datenseite besitzt einen Datensatz, der als “ghosted record” markiert ist (Zeile 34). Der Datensatz wurde nicht von der Datenseite gelöscht sondern er wurde als “zu löschen” markiert (Zeile 42). Der Datensatz selbst ist aber auf der Datenseite noch vorhanden, bis er überschrieben oder gelöscht wird.

Das magische “DBCC CHECKIDENT RESEED”

Mittels DBCC CHECKIDENT kann mit der Option RESEED der Wert für IDENTITY beliebig vor- oder zurück gestellt werden. Die Fragen, die sich in diesem Zusammenhang aufdrängen sind:

• Wird der aktuelle Wert von IDENTITY in der Relation / im Index gespeichert?
• Wird beim Zurücksetzen des Wertes für IDENTITY der Speicher für nicht vergebene ID auf den Datensätzen freigegeben?

Beide Fragen können klar mit NEIN beantwortet werden. Das der aktuelle Wert für eine IDENTITY-Eigenschaft nicht in der Relation selbst oder in einem der Indexe gespeichert wird, zeigt das Transaktionslog, nachdem IDENTITY zurückgesetzt wurde.

DBCC CHECKIDENT('dbo.Dummy', RESEED, 0);

Das Transaktionslog zeigt detailliert, dass keine Änderungen an der Relation selbst durchgeführt wurden sondern an einer “System Base Table” [sys].[syscolpars]!

Diese “System Base Table” kann man nicht direkt aufrufen. Hierzu bedarf es einer “Dedicated Admin Connection”. Um sich mit einer DAC auf die Instanz zu verbinden, wird im Verbindungsdialog der Text “ADMIN:” vor dem Namen der Instanz angegeben. Erst in einer DAC ist der Zugriff auf die Systemrelationen möglich. Sie können jedoch nicht bearbeitet werden!

Führt man anschließend die folgende Abfrage aus, zeigt sie den aktuellen Wert für die IDENTITY-Eigenschaft für das Attribut [Id]

SELECT colid, name, idtval, CAST(idtval AS int) AS int_value
FROM   sys.syscolpars
WHERE  id = OBJECT_ID('dbo.dummy', 'U')

Der allokierte Speicher im Clustered Index wird NICHT frei gegeben. Dies belegt ein Blick auf die physikalischen Eigenschaften des Index als auch ein Blick auf die Datenseite selbst belegen. Wird jedoch ein neuer Datensatz eingetragen, wird die ID um 1 hochgesetzt und erneut Speicher in der Datenseite allokiert. Der “ghosted record” wird überschrieben! Was genau passiert bei dem folgenden SQL-Befehl zum Eintragen eines neuen Datensatzes:

INSERT INTO dbo.dummy (FirstName, LastName, MemberSince) VALUES ('Beate', 'Ricken', '20130101');

Zunächst auch hier wieder ein Blick in das Transaktionsprotokoll, um die einzelnen Transaktionen zu dokumentieren:

Besser kann man die Aktion nicht beschreiben! Zunächst wurde der nächste Wert für die [Id] ermittelt (ACHTUNG – das ist eine in sich vollständig abgeschlossene Transaktion!), wie man aus Action_Id 1 und Action_Id 2 erkennen kann). Action_Id 2 führt ein COMMIT aus – damit ist diese Transaktion beendet! Erst danach werden die Daten in die Relation [dbo].[dummy] eingetragen und die Transaktion abgeschlossen (Action_Id 4 und Action_Id 5). Ein Blick auf die physikalischen Indexinformationen sowie die Datenseite vervollständigen das Bild.

Der “ghosted record” ist aus dem Index verschwunden und ein Datensatz wurde dem Index hinzugefügt, wie [record_count] und [ghost_record_count] belegen.

Die Datenseite belegt, dass der Datensatz, der vormals als “ghosted record” markiert war, nun zu einem “primary record” umgewandelt worden ist. Das der Datensatz nicht “hinter” dem vormals eingetragenen Datensatz eingetragen wurde, erkennt man an der Slot-Number; die ist unverändert Slot 0 geblieben.

Fazit

Die Eigenschaft IDENTITY für ein Attribut in einer Relation ist eine vollständig in sich abgeschlossene  transaktionsprotokollierte Funktion. Ein einmal vergebener IDENTITY-Wert ist für Microsoft SQL Server nicht mehr verfügbar und wird somit nicht mehr verwendet. Um einen IDENTITY-Wert wieder zurück zu setzen, muss mit DBCC CHECKIDENT der letzte verfügbare Wert bestimmt werden. Bei jedem Einfügevorgang wird der nachfolgende IDENTITY-Wert ermittelt und in einer in sich abgeschlossenen Transaktion in einer Systemrelation gespeichert. Dieser Wert wird dann für die “aufrufende” Transaktion verwendet. Wird die Transaktion nicht vollständig ausgeführt, wird der Wert verworfen und kann von weiteren Transaktionen nicht mehr verwendet werden.

Herzlichen Dank fürs Lesen!