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!