Simon Learning SQL Server

Simon Learning SQL Server

In einem früheren Beitrag habe ich über Sperren, Blockieren und Isolationsstufen gesprochen. In diesem Beitrag werde ich näher auf die Schlüsselbereichssperren eingehen, die in der SERIALISIERBAREN Isolationsstufe verwendet werden, und auch demonstrieren, wie die DMV sys.dm_tran_locks verwendet wird, um vorhandene Sperren anzuzeigen und herauszufinden, auf welchen Ressourcen die Sperren platziert sind.

Schlüsselbereichssperren werden nur in der SERIALISIERBAREN Isolationsstufe verwendet und dienen dazu, Phantomlesungen zu stoppen, die in den anderen drei pessimistischen Parallelitätsisolationsstufen möglich sind, nämlich read uncommitted, read committed und repeatable read . Bei einem Phantomlesen führt eine Transaktion einen Lesevorgang für einen Datenbereich durch, z. B. mithilfe einer Abfrage mit einer WHERE-Klausel, und eine andere Transaktion fügt Daten hinzu, die die Bedingungen der WHERE-Klausel erfüllen. Wenn die erste Transaktion denselben Lesevorgang ausführt, werden nun die neuen Daten zurückgegeben, was dazu führt, dass Phantomdaten beim zweiten Lesevorgang innerhalb derselben Transaktion angezeigt werden. Die Schlüsselbereichssperren werden verwendet, um das Hinzufügen von Phantomdaten zu verhindern, und sie werden auf den Schlüsseln des Index platziert, der zum Abrufen der Daten verwendet wird. Das folgende Beispiel mit der Person.Die Tabelle Person in der AdventureWorks2012-Datenbank zeigt, wie diese Schlüsselbereichssperren verwendet werden.

Die Person.Die Tabelle Person hat einen nicht gruppierten Index IX_Person_LastName_FirstName_MiddleName mit der Spalte LastName als erste Spalte im Indexschlüssel. Dies bedeutet, dass jede Abfrage, bei der nach Nachname gefiltert wird, diesen Index zum Abrufen der Daten verwenden sollte.

Wenn wir eine Abfrage ausführen, um einige Zeilen von Person zurückzugeben.Person, die nach einem Bereich von Nachnamen filtert, dann sollten wir sehen, dass dieser Index verwendet wird. Beispiel:

SELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'ORDER BY LastName

Gibt die folgende Ergebnismenge zurück.

Schlüsselbereich 2

Und wenn wir uns den Ausführungsplan ansehen, können wir sehen, dass dieser Index verwendet wird.

Schlüsselbereich 3

Wenn wir nun die Isolationsstufe auf SERIALISIERBAR setzen, eine Transaktion öffnen und dieselbe select-Anweisung ausführen, sollten wir sehen können, welche Sperren gehalten werden.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Jetzt können wir von einer anderen Verbindung aus die DMV sys.dm_tran_locks abfragen, um die aktuellen Sperren zu sehen. Wir sind nur an den Sperren der Schlüsselressourcen interessiert, daher filtern wir entsprechend.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

 Schlüsselbereich 4

Wir erhalten sieben Zeilen mit zwei verschiedenen request_mode Werten. Das „S“ request_mode entspricht gemeinsam genutzten Sperren für die drei zurückgegebenen Zeilen. Da wir die SERIALISIERBARE Isolationsstufe verwenden, werden diese gemeinsam genutzten Sperren gehalten, bis die Transaktion festgeschrieben oder zurückgesetzt wird, um nicht wiederholbare Lesevorgänge zu verhindern. Der request_mode „RangeS-S“ entspricht den Schlüsselbereichssperren, die gewährt wurden. Aber warum gibt es vier Reihen? Um dies zu beantworten, können wir die von sys.dm_tran_locks zurückgegebenen Daten verwenden und damit herausfinden, welche Ressourcen tatsächlich gesperrt wurden.

Die Spalten, an denen wir interessiert sind, sind resource_associated_entity_id und resource_description. Die Daten in diesen Spalten können je nach resource_type variieren. Für Schlüsselressourcen entspricht die resource_associated_entity_id der partition_id von sys.partitionen

SELECT *FROM sys.partitionsWHERE partition_id in (72057594057523200, 72057594045595648)

 Schlüsselbereich 5

Die zurückgegebene object_id entspricht der Person.Personentisch

SELECT object_name(1765581328)

 Schlüsselbereich 6

Wir können sehen, dass die beiden verschiedenen partition_id-Werte unterschiedlichen Indizes für Person entsprechen.Personen

SELECT * FROM sys.indexes WHERE object_id = 1765581328

 Schlüsselbereich 7

Die sys.partitions view sagt uns, dass die Partitionen, für die wir Sperren haben, für die Indizes PK_Person_BusinessEntityID und IX_Person_LastName_FirstName_MiddleName .

Wir können dies alles aus Gründen der Übersichtlichkeit in einer Abfrage kombinieren.

 SELECT L.resource_type ,L.resource_description ,L.resource_associated_entity_id ,L.request_mode ,L.request_type ,L.request_status ,OBJECT_NAME(I.object_id) AS TableName ,I.object_id ,I.index_id ,I.name AS IndexName ,I.type_desc FROM sys.dm_tran_locks L INNER JOIN sys.partitions P ON P.partition_id = L.resource_associated_entity_id INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id WHERE L.resource_type = 'KEY' ORDER BY L.request_mode

 Schlüsselbereich 8

Dies zeigt uns, dass drei gemeinsam genutzte Sperren für die Zeilen selbst (die Blattebene des gruppierten Index) und vier Schlüsselbereichssperren für Schlüssel innerhalb des nicht gruppierten Index platziert werden. Aber das hat die Frage nicht beantwortet, warum wir vier Schlösser haben?

Bevor wir das tun, werde ich zwei Möglichkeiten demonstrieren, um zu beweisen, dass die drei gemeinsam genutzten Sperren in den Zeilen platziert sind, die von der ursprünglichen Abfrage zurückgegeben wurden, und dann verwenden, um herauszufinden, auf welchen Ressourcen die vier „RangeS-S“ -Sperren platziert sind.

Zunächst verwenden wir die Werte %%physloc%% und %%lockres%% zusammen mit dem sys .fn_PhysLocFormatter() Funktion, um uns die Datei, Seite und Slot der Zeilen von der ursprünglichen Abfrage zurückgegeben zu sagen. Zweitens verwenden wir diese Informationen im DBCC-Seitenbefehl.

Wir müssen nur unsere ursprüngliche Abfrage wie folgt aktualisieren

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, %%lockres%% AS LockResource, *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

 Schlüsselbereich 9

Wir können jetzt sehen, dass sich die Zeile für Dennis Ware physisch in der Datendatei 1, auf Seite 2806 und an Steckplatz 3 befindet. Wir können auch sehen, dass es sich um eine Sperrressource handelt (05c585e839d4).

Wenn wir auf die von sys.dm_tran_locks zurückgegebenen Daten sys.dm_tran_locks , können wir sys.dm_tran_locks , dass eine der gemeinsam genutzten Sperren eine resource_description von (05c585e839d4) .dm_tran_locks , sodass diese Sperre in dieser Zeile im gruppierten Index platziert ist.

Jetzt können wir den folgenden DBCC-Seitenbefehl ausführen, um tatsächlich Informationen darüber anzuzeigen, was sich auf der Seite befindet, aber damit dies Ergebnisse liefert, müssen wir das Trace-Flag 3604 aktivieren.

DBCC TRACEON (3604);DBCC PAGE (10, 1, 2806, 3); -- DB_ID, FileId, PageId, Format

Hier werden viele Textinformationen angezeigt, und Sie können im Parameter format eine andere Nummer angeben, um unterschiedliche Datenmengen zurückzugeben. In diesem Fall ist das erste Bit der Seitenkopf und danach haben wir den Zuordnungsstatus. Sie werden sehen, dass der erste verwendete Slot Slot 0 ist. Es gibt einen Offset-Wert, der uns sagt, wo auf der Seite dieser Slot beginnt, und einen Längenwert, der uns sagt, wie lang der Datensatz ist.

Schlüsselbereich 10

Wenn wir nach unten scrollen, bis wir Slot 3 finden, und dann an dem Datenblock vorbei scrollen, können wir die physischen Daten für jede Spalte sehen und bestätigen, dass dies die Zeile für Dennis Ware ist.

Schlüsselbereich 11

Wenn wir nun bis zum Ende von Slot 3 zurückgehen, sehen wir

KeyHashValue = (05c585e839d4)

Dies entspricht der resource_description in sys.dm_tran_locks und dem von %%lockres%% zurückgegebenen Wert.

Aber wie machen wir etwas Ähnliches für die Schlüsselbereichsschlösser? Wie finden wir heraus, welche Ressourcen sie sperren? Eine Möglichkeit wäre, sys.dm_db_database_page_allocations zu verwenden, um herauszufinden, welche Seiten zu einem nicht gruppierten Index gehören. Wir übergeben die database_id, die object_id und die index_id des Index IX_Person_LastName_FirstName_MiddleName und es wird eine Zeile für jede Seite zurückgegeben, die in diesem Index verwendet wird. Wir können dann DBCC PAGE mit den angegebenen allocated_page_page_id Werten ausführen und die Seite finden, die Dennis Ware enthält.

SELECT allocated_page_page_id, *FROM sys.dm_db_database_page_allocations (DB_ID(N'AdventureWorks2012'), object_Id('AdventureWorks2012.Person.Person'), 4, null, 'DETAILED')WHERE is_allocated = 1

Wir können dann DBCC PAGE mit dem angegebenen Wert ausführen und die Seite finden, die Dennis Ware enthält.

DBCC PAGE (10, 1, 24745, 3); -- DB_ID, FileId, PageId, Format

Das Praktische ist, dass DBCC PAGE in diesem Fall die Daten auf der Seite in einer Ergebnismenge zurückgibt, sodass wir ziemlich schnell die richtige Seite finden können, in diesem Fall Seite 24745.

Schlüsselbereich 12

Wenn wir nun die resource_description Werte für unsere vier Schlüsselbereichssperren in sys .dm_tran_locks Wir können sehen, dass die „RangeS-S“ -Sperren tatsächlich für die Zeilen gewährt wurden, die Daten für Dennis Ware, Victoria Ware, Clay Warten und Aaron Washington enthalten. Aber Aaron Washington ist nicht in der Ergebnismenge enthalten, also warum ist diese vierte Tastensperre vorhanden? Dies liegt daran, dass beim Übernehmen eines Schlüsselbereichs für einen Schlüssel verhindert wird, dass Daten unmittelbar vor einem Schlüssel mit einer Schlüsselbereichssperre hinzugefügt werden. Mit anderen Worten, es können keine Daten zwischen beispielsweise Victoria Ware und Clay Warthen, z. Jennifer Warner.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (888888, 'VC', 'Jennifer', 'Warner')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

 Schlüsselbereich 13

Hier sehen wir die wartende Schlüsselbereichsverriegelung.

Es bedeutet auch, dass zwischen Timothy Ward und Aaron Washington keine Daten hinzugefügt werden können und dass die Schlüsselbereichssperren tatsächlich mehr Daten sperren, als die Abfrage in der offenen Transaktion erfüllen könnte. Zum Beispiel konnten wir keine Zeile für Simon Wartz einfügen, die zwischen Clay Warthen und Aaron Washington platziert würde, obwohl sie in der ursprünglichen Abfrage in der offenen Transaktion nicht zurückgegeben würde.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (999999, 'VC', 'Simon', 'Wartz')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

 Schlüsselbereich 14

Jetzt wissen wir, wie die Schlüsselbereichssperren verwendet werden, und wir können herausfinden, auf welchen Ressourcen die Schlüsselbereichssperren vorhanden sind, ohne die Seiten des nicht gruppierten Index durchsuchen zu müssen. Wir können einfach einen Tabellenhinweis einfügen, um einen Teil der benötigten Daten zurückzugeben.

SELECT sys.fn_PhysLocFormatter(%%physloc%%), %%lockres%%, *FROM Person.Person WITH (INDEX = IX_Person_LastName_FirstName_MiddleName)WHERE LastName BETWEEN 'Ware' AND 'Warthen'

 Schlüsselbereich 18

Wie Sie sehen, erhalten wir dieselben Datei-, Seiten- und Slot-Daten und dieselbe resource_description für die drei von der Abfrage zurückgegebenen Zeilen. Wir wissen jetzt, dass das vierte „S-S“ -Schloss auf dem Schlüssel im Index unmittelbar nach dem Schlüssel für Clay Warthen, d. H. Slot 140, platziert ist. Auch hier können wir die DBCC-SEITE verwenden, um die Daten auf Seite 25065 anzuzeigen.

In der Demonstration, die ich gerade durchlaufen habe, haben wir zufällig einen nicht gruppierten Index, wobei die erste Spalte im Clusterschlüssel diejenige ist, die wir in unserer WHERE Klausel verwenden. Aber was passiert, wenn wir den nicht gruppierten Index entfernen? Oder mit anderen Worten, was passiert, wenn wir eine Abfrage für eine andere Tabelle ausführen, die nach einer Spalte filtert, die nicht in einem Indexschlüssel enthalten ist? Um dies leicht zu demonstrieren, können wir einfach den nicht gruppierten Index entfernen.

DROP INDEX ON .BEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Wir erhalten die gleichen Zeilen wie erwartet zurück, aber diesmal musste SQL Server den Clustered Index verwenden, um die Daten abzurufen.

Schlüsselbereich 15

Also, welche Schlösser haben wir? Dieses Mal gibt es keine Schlüsselbereichssperre, die angewendet werden kann, sodass eine gemeinsame Sperre für den gesamten Tisch platziert werden muss.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_modeSELECT object_name(1765581328)

 Schlüsselbereich 16

Da diese gemeinsame Sperre bis zum Abschluss der Transaktion beibehalten wird, können keine Daten in der Tabelle aktualisiert werden, bis die Transaktion abgeschlossen ist, obwohl wir nur drei Zeilen gelesen haben.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (777777, 'VC', 'Simon', 'ZZZZZ')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_mode

 Schlüsselbereich 17

Dies müssen Sie beachten, wenn Sie die SERIALISIERBARE Isolationsstufe verwenden.

Zusammenfassung

Wenn eine Schlüsselspalte zum Filtern von Daten in einer Abfrage verwendet wird, können Schlüsselbereichssperren für die Schlüssel in diesem Index vorgenommen werden. Sie werden für alle Schlüssel verwendet, die die Abfrage erfüllen, sowie für den nacheinander nächsten Schlüssel außerhalb der Abfrage. Dies verhindert dann, dass Daten hinzugefügt werden, die einen neuen Schlüssel unmittelbar vor einem Schlüssel mit einer Schlüsselbereichssperre einfügen würden.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.