In einem Forumsbeitrag auf msdn wurde gefragt, wie genau Microsoft SQL Server vorgeht, wenn eine Fremdschlüsselbeziehung implementiert werden muss. Insbesondere ging es dabei um die Frage, welche Sperren Microsoft SQL Server setzt und welchen Einfluss diese Sperren auf die Performance haben. Ich habe mich etwas genauer mit den “Internals” beschäftigt und in diesem Artikel zusammengefasst.

Inhaltsverzeichnis

Fremdschlüssel

Definition

Fremdschlüssel werden in Relationalen Datenbanksystemen implementiert, um die referenzielle Integrität von Datensätzen aus unterschiedlichen Tabellen gewähren zu können. So können in einer Auftragstabelle keine Aufträge erfasst werden, wenn es dazu keinen passenden Kunden gibt. Eine Fremdschlüssel-Einschränkung muss nicht notwendigerweise mit einer PRIMARY KEY-Einschränkung in einer anderen Tabelle verknüpft sein. Sie kann so definiert werden, dass sie auf die Spalten einer UNIQUE-Einschränkung in einer anderen Tabelle verweist.

Implementierung

Ein Fremdschlüssel ist eine Einschränkung; sie betrifft die Metadaten der Tabelle selbst. Deshalb ist die Implementierung immer mit einem ALTER TABLE verbunden.

ALTER TABLE dbo.CustomerOrders
ADD CONSTRAINT fk_Customers_Id
FOREIGN KEY (Customer_Id)
REFERENCES dbo.Customers(Id);
GO

Das Codebeispiel erstellt eine Fremdschlüssel-Einschränkung auf der Tabelle [dbo].[CustomerOrders] zur Tabelle [dbo].[Customers] über die Attribute [Customer_Id] zu [Id]. Damit ist gewährleistet, dass keine Aufträge erfasst werden können, wenn keine passende [ID] in der Tabelle [dbo].[Customers] vorhanden ist.

Insight the engine

Wie behandelt Microsoft SQL Server diese Anfrage intern? Dazu gib es zwei Fragestellungen:

Welche Ressourcen werden während der Erstellung einer Fremdschlüssel-Einschränkung blockiert?
Welche Schritte müssen intern ausgeführt werden, um eine Fremdschlüssel-Einschränkung zu implementieren?

Ressourcen

Um festzustellen, welche Ressourcen während der Implementierung blockiert werden, lässt man die Erstellung des Fremdschlüssels in einer Transaktion laufen. So können die gesetzten Sperren untersucht werden.

BEGIN TRANSACTION;
GO
    ALTER TABLE dbo.CustomerOrders
    ADD CONSTRAINT fk_Customers_Id
    FOREIGN KEY (Customer_Id)
    REFERENCES dbo.Customers(Id);
    GO

    SELECT DTL.resource_type,
           DTL.request_mode,
           DTL.request_type,
           DTL.request_status,
           CASE resource_type
                WHEN N'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
                WHEN N'KEY' THEN OBJECT_NAME(P.object_id)
                ELSE NULL
           END AS resource_object
    FROM   sys.dm_tran_locks AS DTL
           LEFT JOIN sys.partitions AS P
           ON DTL.resource_associated_entity_id = P.hobt_id
    WHERE  request_session_id = @@SPID;
    GO

Microsoft SQL Server setzt [SCH-M] (Schema Modification) Sperren sowohl auf die betroffenen Tabellen als auch auf das Fremdschlüsselobjekt. Sperren des Typs [Sch-M] werden von Microsoft SQL Server verwendet, wenn auf Objekte DDL-Vorgänge ausgeführt werden. Während einer [Sch-M]-Sperre werden gleichzeitige Zugriffe auf die Tabelle verhindert.

Die Schlüsselsperren [KEY] werden im obigen Kontext verwendet, um die Schemaänderungen in den internen Systemobjekten zu speichern.

sys.sysschobjs	Jede Zeile stellt ein Objekt in der Datenbank dar.
sys.syssingleobjrefs	Enthält eine Zeile für jeden allgemeinen N-zu-1-Verweis.

Workload

Bei der Implementierung einer Fremdschlüssel-Einschränkung vermutet man eine Menge Arbeit für Microsoft SQL Server; dem ist aber nicht so. Die folgende Abbildung zeigt die Transaktionsschritte, die während der Implementierung ausgeführt werden.

SELECT [Current LSN],
       Operation,
       Context,
       [Lock Information]
FROM   sys.fn_dblog(NULL, NULL)
ORDER BY
       [Current LSN] ASC;
GO

Was auf dem ersten Blick umständlich und kompliziert aussieht entpuppt sich bei genauer Betrachtung der Einzelschritte als logisch:

Nachdem die Transaktion geöffnet wurde (Zeile 4) wird eine Schema-Modifikationssperre auf das Objekt [dbo].[CustomerOrders] gesetzt.
Sobald die Sperre erfolgreich gesetzt worden ist, kann für den Fremdschlüssel ein Eintrag in die Tabelle [sys].[sysschobjs] gesetzt werden. Erst, wenn der Name des Fremdschlüsselobjekts eingetragen ist, ist die interne ID für das Objekt bekannt.
Ist die ID bekannt, kann das Objekt mit einer SCH-M Sperre für Zugriffe von außen geschützt werden (Zeile 7).
Anschließend werden die Informationen zur Fremdschlüsseleinschränkung in die Systemtabelle eingetragen werden (Zeile 8 – 11).
Es sind nicht mehrere Eintragungen sondern die Systemtabelle besitzt mehrere Indexe (4!).

Im ersten Schritt wurde der existierende Eintrag für [dbo].[CustomerOrders] verändert (Status); anschließend wurde das Fremdschlüsselobjekt hinzugefügt.
Sind alle Informationen zum Fremdschlüssel erfasst, wird die Tabelle [dbo].[Customers] blockiert (Zeile 12)
In den Zeilen 13 – 19 werden erneut Daten in die Systemtabellen [sys].[syssingleobjrefs] geschrieben.
Nachdem alle Informationen über die Fremdschlüsselbeziehung manifestiert wurden, können die Sperren wieder aufgehoben werden (Zeile 20 – 25)
bevor dann die Transaktion beendet wird (Zeile 27)

Zusammenfassung

Die Implementierung eines Fremdschlüssels behandelt Microsoft SQL Server sehr effizient. Wichtigste Voraussetzung für die schnelle Implementierung eines Fremdschlüssels ist das Setzen einer SCH-M-Sperre auf die betroffenen Objekte, dann ist der Rest nur noch ein Kinderspiel.

Vielen Dank fürs Lesen!

Inside the engine – Erstellung eines Fremdschlüssels