Freigeben über

Optimierte Sperrung

  • Artikel

Gilt für: Azure SQL-Datenbank

Dieser Artikel stellt die optimierte Sperrfunktion vor, eine neue Fähigkeit der Datenbank-Engine, die einen verbesserten Mechanismus für die Transaktionssperrung bietet, der den Verbrauch von Sperrspeicher und das Blockieren bei gleichzeitigen Transaktionen reduziert.

Was ist optimierte Sperrung?

Mit der optimierten Sperrung lässt sich der Speicherbedarf für Sperren reduzieren, da sogar bei großen Transaktionen nur sehr wenige Sperren gehalten werden. Darüber hinaus vermeidet eine optimierte Sperrung auch Sperreskalationen. Dies ermöglicht mehr gleichzeitigen Zugriff auf die Tabelle.

Die optimierte Sperrung besteht aus zwei primären Komponenten: Sperren der Transaktions-ID (TID) und Sperren nach der Qualifikation (LAQ).

  • Eine Transaktions-ID (TID) ist ein eindeutiger Bezeichner einer Transaktion. Jede Zeile wird mit der letzten TID beschriftet, die sie geändert hat. Anstelle von potenziell vielen Schlüssel- oder Zeilenbezeichnersperren wird eine einzelne Sperre für die TID verwendet. Weitere Informationen finden Sie unter Sperren der Transaktions-ID (TID).
  • Sperrung nach Qualifizierung (Lock After Qualification, LAQ) ist eine Optimierung, bei der die Prädikate einer Abfrage mithilfe der letzten bestätigten Version der Zeile ausgewertet werden, ohne eine Sperre zu erhalten, wodurch die Nebenläufigkeit verbessert wird. Weitere Informationen finden Sie unter Sperren nach der Qualifikation (LAQ).

Zum Beispiel:

  • Ohne optimierte Sperren könnte die Aktualisierung von 1.000 Zeilen in einer Tabelle 1.000 exklusive (X) Zeilensperren erfordern, die bis zum Ende der Transaktion gehalten werden.
  • Mit optimierten Sperren kann die Aktualisierung von 1.000 Zeilen in einer Tabelle 1.000 X Zeilensperren erfordern, aber jede Sperre wird freigegeben, sobald jede Zeile aktualisiert wurde, und nur eine TID-Sperre wird bis zum Ende der Transaktion gehalten. Da Sperren schnell freigegeben werden, wird die Speicherauslastung der Sperre reduziert, und die Sperrenausweitung ist viel weniger wahrscheinlich, was die Workload-Nebenläufigkeit verbessert.

Hinweis

Durch das Aktivieren einer optimierten Sperrung werden Zeilen- und Seitensperren reduziert oder entfernt, die von den DML-Anweisungen (Data Modification Language), wie z. B.INSERT, UPDATE, DELETE, MERGE, eingerichtet werden. Es wirkt sich nicht auf andere Arten von Datenbank- und Objektsperren aus, z. B. Schemasperren.

Verfügbarkeit

Die optimierte Sperrung ist nur in Azure SQL-Datenbank verfügbar, in allen Dienstebenen und Computegrößen.

Die optimierte Sperrung ist derzeit in Azure SQL Managed Instance oder SQL Server nicht verfügbar.

Ist die optimierte Sperrung aktiviert?

Die optimierte Sperre ist für diese Benutzerdatenbank aktiviert. Stellen Sie eine Verbindung zu Ihrer Datenbank her und verwenden Sie die folgende Abfrage, um zu überprüfen, ob optimiertes Sperren aktiviert ist:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');
Ergebnis Beschreibung
0 Die optimierte Sperrung ist deaktiviert.
1 Die optimierte Sperrung ist aktiviert.
NULL Die optimierte Sperrung ist nicht verfügbar.

Optimiertes Sperren baut auf anderen Datenbank-Features auf:

Sowohl ADR als auch RCSI sind in Azure SQL Database standardmäßig aktiviert. Um zu überprüfen, ob diese Optionen für Ihre aktuelle Datenbank aktiviert sind, stellen Sie eine Verbindung zur Datenbank her, und führen Sie die folgende T-SQL-Abfrage aus:

SELECT name,
       is_read_committed_snapshot_on,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = DB_NAME();

Sperr-Übersicht

Dies ist eine kurze Zusammenfassung des Verhaltens, wenn optimiertes Sperren nicht aktiviert ist. Weitere Informationen siehe Handbuch zu Transaktionssperren und Zeilenversionsverwaltung.

In der Datenbank-Engine ist das Sperren ein Mechanismus, der verhindert, dass mehrere Transaktionen dieselben Daten gleichzeitig aktualisieren, um die ACID-Eigenschaften der Daten zu gewährleisten.

Wenn eine Transaktion Daten ändern muss, kann sie eine Sperre für die Daten anfordern. Die Sperre wird gewährt, wenn keine anderen konkurrierenden Sperren auf die Daten gehalten werden, und die Transaktion kann mit der Änderung fortfahren. Wenn eine andere konkurrierende Sperre auf die Daten gehalten wird, muss die Transaktion warten, bis die Sperre freigegeben wird, bevor sie fortfahren kann.

Wenn mehrere Transaktionen gleichzeitig versuchen, auf dieselben Daten zuzugreifen, muss die Datenbank-Engine potenziell komplexe Konflikte mit gleichzeitigen Lese- und Schreibvorgängen lösen. Das Sperren ist einer der Mechanismen, mit denen die Engine die Semantik für die Isolationsebenen der ANSI-SQL-Transaktion bereitstellen kann. Obwohl Sperren in Datenbanken unverzichtbar sind, können reduzierte Gleichzeitigkeit, Deadlocks, Komplexität und Sperr-Mehraufwand die Leistung und Skalierbarkeit beeinträchtigen.

Optimierte Sperren und Transaktions-ID (TID)-Sperren

Jede Zeile in der Datenbank enthält intern eine Transaktions-ID (TID), wenn die auf der Zeilenversionsverwaltung basierenden Isolationsebenen verwendet werden. Diese TID bleibt auf der Festplatte erhalten. Jede Transaktion, die eine Zeile ändert, stempelt diese Zeile mit ihrer TID.

Bei der TID-Sperre wird nicht der Schlüssel der Zeile gesperrt, sondern die TID der Zeile. Die ändernde Transaktion hält eine X-Sperre auf ihre TID. Andere Transaktionen erwerben eine S-Sperre für die TID, um zu warten, bis die erste Transaktion abgeschlossen ist. Bei der TID-Sperre werden Seiten- und Zeilensperren weiterhin für Änderungen verwendet, aber jede Seiten- und Zeilensperre wird freigegeben, sobald jede Zeile geändert wurde. Die einzige Sperre, die bis zum Ende der Transaktion gehalten wird, ist die X-Sperre für die TID-Ressource, die die Seiten- und Zeilensperren (Schlüssel) ersetzt.

Betrachten Sie das folgende Beispiel, in dem Sperren für die aktuelle Sitzung angezeigt werden, während eine Schreibtransaktion aktiv ist:

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

UPDATE t0
SET b = b + 10;

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
      AND
      resource_type IN ('PAGE','RID','KEY','XACT');

COMMIT TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Wenn die optimierte Sperrung aktiviert ist, enthält die Anforderung nur eine einzelne X-Sperre für die XACT-Ressource (Transaktion).

Ein Screenshot der Ergebnismenge einer Abfrage auf sys.dm_tran_locks für eine einzelne Sitzung zeigt nur eine Sperre, wenn optimiertes Sperren aktiviert ist.

Wenn die optimierte Sperre nicht aktiviert ist, enthält dieselbe Anforderung vier Sperren – drei X-Schlüsselsperren auf jeder Zeile und eine beabsichtigte exklusive IX-Sperre (Intent Exclusive) auf der Seite, die die Zeilen enthält:

Ein Screenshot der Ergebnismenge einer Abfrage auf sys.dm_tran_locks für eine einzelne Sitzung zeigt drei Sperren, wenn optimiertes Sperren nicht aktiviert ist.

Die dynamische Verwaltungsansicht (DMV) sys.dm_tran_locks kann nützlich sein, um Sperrprobleme zu untersuchen oder zu beheben, einschließlich der Beobachtung optimierter Sperrung in Aktion.

Optimiertes Sperren und Sperren nach Qualifikation (Lock After Qualification, LAQ)

Basierend auf der TID-Infrastruktur ändert die optimierte Sperrung, wie DML-Anweisungen wie INSERT, UPDATE, DELETE und MERGE Sperren erwerben.

Ohne optimiertes Sperren werden Prädikate aus Abfragen zeilenweise in einem Scan überprüft, indem zunächst eine Zeilensperre für die Aktualisierung (U) genommen wird. Wenn das Prädikat erfüllt ist, wird eine exklusive Zeilensperre (X) genommen, bevor die Zeile aktualisiert wird.

Mit optimierten Sperren und bei aktivierter READ COMMITTED-Momentaufnahme-Isolationsstufen (RCSI) werden Prädikate auf die letzte bestätigte Version angewendet, ohne dass Zeilensperren erforderlich sind. Wenn das Prädikat nicht erfüllt ist, geht die Abfrage zur nächsten Suchzeile über. Wenn das Prädikat erfüllt ist, wird eine X-Zeilensperre genommen, um die Zeile tatsächlich zu aktualisieren. Die X-Zeilensperre wird freigegeben, sobald die Zeilenaktualisierung abgeschlossen ist, also vor dem Ende der Transaktion.

Da die Auswertung der Prädikate ohne Sperren durchgeführt wird, blockieren sich gleichzeitige Abfragen, die verschiedene Zeilen ändern, nicht gegenseitig.

Zum Beispiel:

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

INSERT INTO t1
VALUES (1,10),(2,20),(3,30);
GO
Sitzung 1 Sitzung 2
BEGIN TRANSACTION;
UPDATE t1
SET b = b + 10
WHERE a = 1;
BEGIN TRANSACTION;
UPDATE t1
SET b = b + 10
WHERE a = 2;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

Ohne optimierte Sperrung wird Sitzung 2 blockiert, da Sitzung 1 eine U-Sperre für die Zeilensitzung 2 enthält, die aktualisiert werden muss. Bei optimierter Sperrung wird Sitzung 2 jedoch nicht blockiert, da U-Sperren nicht verwendet werden und die zuletzt comittete Version von Zeile 1, Spalte a 1 entspricht, was das Prädikat von Sitzung 2 nicht erfüllt.

Da mit LAQ keine U-Sperren verwendet werden, kann eine gleichzeitige Transaktion die Zeile ändern, nachdem das Prädikat ausgewertet wurde. Wenn das Prädikat erfüllt ist und keine andere aktive Transaktion in der Zeile vorhanden ist (keine X-TID-Sperre), wird die Zeile geändert. Wenn eine aktive Transaktion vorhanden ist, wartet die Datenbank-Engine, bis diese abgeschlossen ist, und wertet das Prädikat zum Zeitpunkt der Änderung erneut aus, da die andere Transaktion die Zeile möglicherweise geändert hat. Wenn das Prädikat immer noch erfüllt wird, wird die Zeile geändert.

Betrachten Sie das folgende Beispiel, in dem die Prädikatauswertung automatisch wiederholt wird, da eine andere Transaktion die Zeile geändert hat:

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO
Sitzung 1 Sitzung 2
BEGIN TRANSACTION;
UPDATE t3
SET b = b + 10
WHERE a = 1;
BEGIN TRANSACTION;
UPDATE t3
SET b = b + 10
WHERE a = 1;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

Änderungen des Abfrageverhaltens mit optimierter Sperrung und RCSI

Gleichzeitige Workloads unter lesesicherer Momentaufnahme-Isolationsstufe (RCSI), die auf einer strengen Ausführungsreihenfolge von Transaktionen basieren, können bei aktivierter optimierter Sperrung unterschiedliche Abfrageverhalten aufweisen.

Betrachten Sie das folgende Beispiel, bei dem die Transaktion T2 die Tabelle t4 basierend auf der Spalte b aktualisiert, die während der Transaktion T1 aktualisiert wurde.

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO
Sitzung 1 Sitzung 2
BEGIN TRANSACTION T1;
UPDATE t4
SET b = 2
WHERE a = 1;
BEGIN TRANSACTION T2;
UPDATE t4
SET b = 3
WHERE b = 2;
COMMIT TRANSACTION;
COMMIT TRANSACTION;

Lassen Sie uns das Ergebnis des obigen Szenarios mit und ohne Lock After Qualification (LAQ) bewerten.

Ohne LAQ

Ohne LAQ wird die UPDATE-Anweisung in Transaktion T2 blockiert und wartet auf den Abschluss der Transaktion T1. Nach Abschluss von T1 aktualisiert T2 die Zeileneinstellungsspalte b auf 3, damit das Prädikat erfüllt ist.

Nachdem beide Transaktionen übertragen wurden, enthält Tabelle t4 die folgenden Zeilen:

 a | b
 1 | 3

Mit LAQ

Bei LAQ verwendet Transaktion T2 die neueste comittete Version der Zeile, wobei die Spalte b für die Auswertung ihres Prädikats (b = 2) gleich 1 ist. Diese Zeile ist nicht qualifiziert; daher wird sie übersprungen, und die Anweisung wird beendet, ohne von Transaktion T1 blockiert worden zu sein. In diesem Beispiel beseitigt LAQ die Blockierung, führt aber zu anderen Ergebnissen.

Nachdem beide Transaktionen übertragen wurden, enthält Tabelle t4 die folgenden Zeilen:

 a | b
 1 | 2

Wichtig

Auch ohne LAQ sollten Anwendungen nicht davon ausgehen, dass die Datenbank-Engine eine strenge Reihenfolge garantiert, ohne Sperrhinweise zu verwenden, wenn Isolationsstufen verwendet werden, die auf der Zeilenversionsverwaltung basieren. Unsere allgemeine Empfehlung für Kunden, die gleichzeitige Workloads unter RCSI ausführen, die auf eine strikte Ausführungsreihenfolge von Transaktionen angewiesen sind (wie in der vorherigen Übung gezeigt), ist die Verwendung strengerer Isolationsstufen wie REPEATABLE READ und SERIALIZABLE.

Diagnose-Ergänzungen für optimiertes Sperren

Die folgenden Verbesserungen helfen Ihnen beim Überwachen und Beheben von Blockierungen und Deadlocks, wenn eine optimierte Sperrung aktiviert ist:

  • Wartetypen für optimiertes Sperren
    • XACT-Wartetypen für die S-Sperre in der TID, und Ressourcenbeschreibungen in sys.dm_os_wait_stats (Transact-SQL):
      • LCK_M_S_XACT_READ – Tritt auf, wenn eine Aufgabe auf eine freigegebene Sperre eines XACT wait_resource Typs wartet, mit der Absicht zu lesen.
      • LCK_M_S_XACT_MODIFY – Tritt auf, wenn eine Aufgabe auf eine freigegebene Sperre eines XACT wait_resource Typs wartet, mit der Absicht, sie zu ändern.
      • LCK_M_S_XACT – Tritt auf, wenn eine Aufgabe auf eine freigegebene Sperre eines XACT wait_resource Typs wartet, in dem die Absicht nicht abgeleitet werden kann. Dies ist nicht üblich.
  • Sperrung der Sichtbarkeit von Ressourcen
  • Sichtbarkeit von Ressourcen abwarten
  • Deadlock-Graph
    • Unter jeder Ressource im <resource-list> des Deadlock-Berichts meldet jedes <xactlock>-Element die zugrunde liegenden Ressourcen und spezifische Informationen für Sperren jedes Mitglieds eines Deadlocks. Weitere Informationen und ein Beispiel finden Sie unter Optimiertes Sperren und Deadlocks.

Bewährte Methoden mit optimierter Sperrung

Aktivieren der Momentaufnahme-Isolationsstufe (Read Committed Snapshot Isolation, RCSI)

Um die Vorteile der optimierten Sperrung zu maximieren, empfiehlt es sich, die lesegesicherte Momentaufnahmen-Isolationsstufe (Read-Committed-Snapshot-Isolierung, RCSI) für die Datenbank zu aktivieren und die READ COMMITTED-Isolierung als Standardisolierungsebene zu verwenden. Wenn sie noch nicht aktiviert ist, aktivieren Sie RCSI, indem Sie eine Verbindung mit der master-Datenbank herstellen und die folgende Anweisung ausführen:

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

In Azure SQL Database ist RCSI standardmäßig aktiviert und READ COMMITTED ist die Standardisolierungsstufe. Wenn RCSI aktiviert ist und die Isolationsstufe READ COMMITTED verwendet wird, lesen Leser eine Version der Zeile aus der Momentaufnahme, die am Anfang der Anweisung erstellt wurde. Mit LAQ qualifizieren Schreiber Zeilen gemäß dem Prädikat auf der Grundlage der letzten festgeschriebenen Version der Zeile, ohne U-Sperren zu erwerben. Mit LAQ wartet eine Abfrage nur, wenn die Zeile qualifiziert ist und eine aktive Schreibtransaktion für diese Zeile vorliegt. Das Qualifizieren basierend auf der neuesten Commitversion und das Sperren nur der qualifizierten Zeilen verringert die Blockierung und erhöht die Parallelität.

Neben der Verringerung der Blockierung wird auch der benötigte Sperrspeicher reduziert. Das liegt daran, dass die Leser keine Sperren verwenden und die Schreiber nur kurzzeitige Sperren verwenden, anstatt Sperren, die bis zum Ende der Transaktion gehalten werden. Bei Verwendung strengerer Isolationsstufen wie REPEATABLE READ oder SERIALIZABLE behält die Datenbank-Engine Zeilen- und Seitensperren sowohl für Leser als auch für Writer bis zum Ende der Transaktion bei, selbst dann, wenn die optimierte Sperrung aktiviert ist, was den Umfang an Blockierungen und den Speicherbedarf für Sperren vergrößert.

Vermeiden von Sperrhinweisen

Tabellen- und Abfragehinweise wie UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK usw. werden zwar berücksichtigt, wenn die optimierte Sperrung aktiviert ist, sie verringern aber den Nutzen der optimierten Sperre. Sperrhinweise erzwingen, dass die Datenbank-Engine Zeilen-/Seitensperren übernimmt und sie bis zum Ende der Transaktion hält, um die Absicht der Sperrhinweise zu berücksichtigen. Einige Anwendungen verfügen über eine Logik, in der Sperrhinweise benötigt werden, z. B. beim Lesen einer Zeile im UPDLOCK-Hinweis und späteren Aktualisierungen. Wir empfehlen die Verwendung von Sperrhinweisen nur bei tatsächlichem Bedarf.

Mit optimiertem Sperren gibt es keine Einschränkungen für bestehende Abfragen und Abfragen müssen nicht neu geschrieben werden. Abfragen, die keine Hinweise verwenden, profitieren am meisten von optimierten Sperren.

Ein Tabellen-Hinweis für eine Tabelle in einer Abfrage deaktiviert nicht die optimierte Sperrung für andere Tabellen in derselben Abfrage. Außerdem wirkt sich das optimierte Sperren nur auf das Sperrverhalten von Tabellen aus, die durch eine DML-Anweisung wie INSERT, UPDATE, DELETE oder MERGE aktualisiert werden. Zum Beispiel:

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

Im vorherigen Abfragebeispiel wird nur die Tabelle t6 durch den Sperrhinweis beeinflusst, während t5 dennoch von einer optimierten Sperrung profitieren kann.

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

Im vorherigen Abfragebeispiel verwendet nur die Tabelle t5 die REPEATABLE READ-Isolationsstufe und hält Sperren bis zum Ende der Transaktion. Andere Updates auf t5 können weiterhin von einer optimierten Sperrung profitieren. Dasselbe gilt für den HOLDLOCK-Hinweis.

Häufig gestellte Fragen (FAQ)

Ist die optimierte Sperrung standardmäßig sowohl in neuen als auch in bestehenden Datenbanken aktiviert?

In Azure SQL-Datenbank, ja.

Wie kann ich erkennen, ob die optimierte Sperrung aktiviert ist?

Siehe Ist die optimierte Sperrung aktiviert?.

Was passiert, wenn die beschleunigte Datenbankwiederherstellung (Accelerated Database Recovery, ADR) in meiner Datenbank nicht aktiviert ist?

Wenn ADR deaktiviert ist, wird automatisch auch das optimierte Sperren deaktiviert.

Was ist, wenn ich erzwingen möchte, dass Abfragen trotz optimierter Sperrung blockiert werden?

Wenn Kunden, die RCSI verwenden, die Blockierung zwischen zwei Abfragen erzwingen möchten, wenn die optimierte Sperrung aktiviert ist, verwenden Sie den READCOMMITTEDLOCK-Abfragehinweis.

Wird die optimierte Sperrung für schreibgeschützte sekundäre Replikate verwendet?

Nein, da DML-Anweisungen nicht für schreibgeschützte Replikate ausgeführt werden können, und die entsprechenden Zeilen- und Seitensperren werden nicht übernommen.

Wird die Sperre beim Ändern von Daten in tempdb und in temporären Tabellen optimiert?

Derzeit leider nicht.

Zugehöriger Inhalt