Teilen über

Optimierte Sperrung

  • Artikel

Gilt für: Azure SQL-Datenbank

Dieser Artikel stellt die optimierte Sperrfunktion vor, eine neue Fähigkeit der SQL Server Database 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 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.

Di 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 erhalten Sie im Abschnitt zum Sperren der Transaktions-ID (TID).
  • Sperrung nach Qualifizierung (Lock After Qualification, LAQ) ist eine Optimierung, bei der die Prädikate einer Abfrage auf der letzten bestätigten Version der Zeile ausgewertet werden, ohne eine Sperre zu erhalten, wodurch die Gleichzeitigkeit verbessert wird. Weitere Informationen erhalten Sie im Abschnitt Sperren nach der Qualifikation (LAQ).

Zum Beispiel:

  • Ohne optimierte Sperren könnte die Aktualisierung von 1 Million Zeilen in einer Tabelle 1 Million exklusive (X) Zeilensperren erfordern, die bis zum Ende der Transaktion gehalten werden.
  • Mit optimierten Sperren kann die Aktualisierung von 1 Million Zeilen in einer Tabelle 1 Million 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.

In diesem Artikel werden diese beiden Kernkonzepte der optimierten Sperrung ausführlich behandelt.

Verfügbarkeit

Derzeit ist die optimierte Sperrung nur in Azure SQL-Datenbank verfügbar. Weitere Informationen finden Sie unter Wo ist die derzeit optimierte Sperrung 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 in Ihrer Datenbank aktiviert ist:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX('testdb', 'IsOptimizedLockingOn');

Wenn Sie nicht mit der in DATABASEPROPERTYEX angegebenen Datenbank verbunden sind, wird das Ergebnis NULL lauten. Sie sollten 0 (optimiertes Sperren ist deaktiviert) oder 1 (aktiviert) erhalten.

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, verwenden Sie die folgende T-SQL-Abfrage:

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 Integrität und Konsistenz der Daten zu schützen.

Wenn eine Transaktion Daten ändern muss, kann sie eine Sperre für diese 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 auf dieselben Daten zugreifen dürfen, muss die Datenbank-Engine potenziell komplexe Konflikte mit gleichzeitigen Lese- und Schreibvorgängen lösen. Das Sperren ist einer der Mechanismen, mit denen die Datenbank-Engine die Semantik für die ANSI-SQL-Transaktionsisolationsebenen 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-Engine enthält intern eine Transaktions-ID (TID), wenn die Zeilenversionsverwaltung verwendet wird. 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 erhalten eine S-Sperre auf die TID, um zu prüfen, ob die erste Transaktion noch aktiv ist. Bei der TID-Sperre werden Seiten- und Zeilensperren weiterhin für Aktualisierungen verwendet, aber jede Seiten- und Zeilensperre wird freigegeben, sobald jede Zeile aktualisiert 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, wie in der nächsten Demo gezeigt wird. (Andere Standard-Datenbank- und Objektsperren sind von der optimierten Sperre nicht betroffen).

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

Betrachten Sie das folgende T-SQL-Beispielszenario, das nach Sperren für die aktuelle Sitzung des Benutzers sucht:

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

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

BEGIN TRAN
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 TRAN
GO
DROP TABLE IF EXISTS t0;

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.

Die gleiche Abfrage ohne den Vorteil des optimierten Sperrens erzeugt vier Sperren:

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 sys.dm_tran_locks dynamische Verwaltungsansicht (Dynamic Management View, DMV) 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)

Aufbauend auf der TID-Infrastruktur ändert das optimierte Sperren, wie Abfrageprädikate Sperren sichern.

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 X-Zeilensperre genommen, bevor die Zeile aktualisiert wird.

Mit optimierten Sperren und bei aktivierter Isolationsebene für Momentaufnahme-Isolationsstufen (Read-Committed-Snapshots, 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.

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 TRAN
UPDATE t1
SET b=b+10
WHERE a=1;
BEGIN TRAN
UPDATE t1
SET b=b+10
WHERE a=2;
COMMIT TRAN
COMMIT TRAN

Das Verhalten des Blockierens ändert sich mit dem optimierten Sperren im vorherigen Beispiel. Ohne optimierte Sperrung wird Sitzung 2 blockiert.

Bei optimierter Sperrung wird Session 2 jedoch nicht blockiert, da die neueste zugesicherte Version von Zeile 1 a=1 enthält, was das Prädikat von Sitzung 2 nicht erfüllt.

Wenn das Prädikat erfüllt ist, warten wir, bis eine aktive Transaktion in der Zeile beendet ist. Wenn wir auf die S TID-Sperre warten müssten, könnte sich die Zeile geändert haben, und die letzte übertragene Version könnte sich geändert haben. In diesem Fall wird die Datenbank-Engine die Prädikatauswertung in derselben Zeile erneut ausgeführt, anstatt die Transaktion aufgrund eines Aktualisierungskonflikts abzubrechen. Wenn das Prädikat beim erneuten Versuch gültig ist, wird die Zeile aktualisiert.

Betrachten Sie das folgende Beispiel, wenn eine Prädikatänderung automatisch wiederholt wird:

CREATE TABLE t2
(a int not null
,b int null);

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

Änderungen des Abfrageverhaltens mit optimierter Sperrung und RCSI

Gleichzeitige Systeme unter lesesicherer Momentaufnahme-Isolationsstufe (RCSI) mit Workloads, 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 t1 basierend auf der Spalte b aktualisiert, die während der Transaktion T1 aktualisiert wurde.

CREATE TABLE t1 (a int not null, b int null);

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

Lassen Sie uns das Ergebnis des obigen Szenarios mit und ohne LAQ, einem integralen Bestandteil des optimierten Sperrens, bewerten.

Ohne LAQ

Ohne LAQ wird die Transaktion T2 blockiert und wartet auf den Abschluss der Transaktion T1.

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

 a | b
 1 | 3

Mit LAQ

Mit LAQ verwendet Transaktion T2 die letzte bestätigte Version der Zeile b (b=1 im Versionsspeicher), um ihr Prädikat (b=2) auszuwerten. Diese Zeile ist nicht qualifiziert; daher wird sie übersprungen und T2 geht zur nächsten Zeile über, 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 t1 die folgenden Zeilen:

 a | b
 1 | 2

Wichtig

Auch ohne LAQ sollten Anwendungen nicht davon ausgehen, dass SQL Server (unter Versionsverwaltungsisolationsstufen) eine strenge Reihenfolge garantiert, ohne Sperrhinweise zu verwenden. Unsere allgemeine Empfehlung für Kunden auf nebenläufigen Systemen unter RCSI mit Workloads, die auf eine strikte Ausführungsreihenfolge von Transaktionen angewiesen sind (wie in der vorherigen Übung gezeigt), ist die Verwendung strengerer Isolationsstufen.

Diagnose-Ergänzungen für optimiertes Sperren

Zur Unterstützung der Überwachung und Fehlerbehebung von Blockierungen und Deadlocks mit optimierten Sperren, halten Sie Ausschau nach den folgenden Ergänzungen:

  • Wartetypen für optimiertes Sperren
    • XACT Wartetypen 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. Selten.
  • 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. Falls nicht aktiviert, aktivieren Sie RCSI anhand des folgenden Beispiels:

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

In Azure SQL Database ist RCSI standardmäßig aktiviert mit lesegesicherte Isolationsstufe als Standardisolierungsstufe. Wenn RCSI aktiviert ist und die lesegesicherte Isolationsstufe verwendet wird, blockieren Leser keine Autoren, und Autoren blockieren keine Leser. Die Leser lesen eine Version der Zeile aus der Momentaufnahme, die zu Beginn der Abfrage gemacht 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 am Ende der Transaktion ablaufen. Bei Verwendung strengerer Isolationsstufen wie „Repeatable read“ oder Serializable muss die Datenbank-Engine Zeilen- und Seitensperren sowohl für Leser als auch für Writer bis zum Ende der Transaktion beibehalten, was den Umfang an Blockierungen und den Speicherbedarf für Sperren vergrößert.

Vermeiden von Sperrhinweisen

Tabellen- und Abfragehinweise werden zwar geschätzt, verringern aber den Vorteil der optimierten Sperrung. Sperrhinweise wie UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK usw. in Ihren Abfragen reduzieren die vollständigen Vorteile der optimierten Sperrung. Wenn Sie solche Sperrhinweise in den Abfragen haben, wird die Datenbank-Engine gezwungen, Zeilen-/Seitensperren zu übernehmen und sie bis zum Ende der Transaktion zu halten, 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 mit Auswahl über UPDLOCK 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 UPDATE-Anweisung aktualisiert werden. Zum Beispiel:

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

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

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

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

Häufig gestellte Fragen (FAQ)

Wo ist die derzeit optimierte Sperrung verfügbar?

Derzeit ist die optimierte Sperrung in Azure SQL-Datenbank verfügbar.

Optimierte Sperrung ist in den folgenden Dienstebenen verfügbar:

  • Alle DTU-Dienstebenen
  • alle vCore-Dienstebenen, einschließlich bereitgestellter und serverloser Dienstebenen.

Optimierte Sperrung ist derzeit nicht verfügbar in:

  • Verwaltete Azure SQL-Instanz
  • SQL Server 2022 (16.x)

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.

Zugehöriger Inhalt