Optimierte Sperrung

  • Artikel

Gilt für:Azure SQL-Datenbank

In diesem Artikel wird die optimierte Sperrfunktion eingeführt, eine neue SQL Server-Datenbank-Engine-Funktion, die einen verbesserten Mechanismus für die Transaktionssperrung bietet, der den Speicherverbrauch und die Blockierung für gleichzeitige Transaktionen reduziert.

Was ist optimierte Sperrung?

Optimierte Sperrung hilft, den Sperrspeicher zu reduzieren, da nur sehr wenige Sperren für große Transaktionen gehalten werden. Darüber hinaus vermeidet eine optimierte Sperrung auch Sperreskalationen. Dies ermöglicht mehr gleichzeitigen Zugriff auf die Tabelle.

Optimierte Sperre besteht aus zwei primären Komponenten: Transaktions-ID (TID) sperren 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).
  • "Lock after qualification" (LAQ) ist eine Optimierung, die Prädikate einer Abfrage für die neueste zugesicherte Version der Zeile auswertet, ohne eine Sperre zu erwerben und so die Parallelität zu verbessern. Weitere Informationen erhalten Sie im Abschnitt " Sperren nach der Qualifikation (LAQ)".

Beispiel:

  • Ohne optimierte Sperrung erfordern das Aktualisieren von 1 Millionen Zeilen in einer Tabelle möglicherweise 1 Millionen exklusive (X)-Zeilensperren, die bis zum Ende der Transaktion gehalten werden.
  • Bei optimierter Sperrung erfordern das Aktualisieren von 1 Millionen Zeilen in einer Tabelle möglicherweise 1 Million X-Zeilensperren, aber jede Sperre wird freigegeben, sobald jede Zeile aktualisiert wird, und nur eine TID-Sperre wird bis zum Ende der Transaktion gehalten.

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

Verfügbarkeit

Derzeit ist die optimierte Sperre 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 Sperrung ist pro Benutzerdatenbank aktiviert. Verbinden ihrer Datenbank verwenden Sie dann die folgende Abfrage, um zu überprüfen, ob die optimierte Sperrung in Ihrer Datenbank aktiviert ist:

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

Wenn Sie nicht mit der in DATABASEPROPERTYEXder Datenbank angegebenen Datenbank verbunden sind, lautet NULLdas Ergebnis . Sie sollten empfangen 0 (optimierte Sperrung ist deaktiviert) oder 1 (aktiviert).

Optimierte Sperrung baut auf anderen Datenbankfeatures auf:

Sowohl ADR als auch RCSI sind in Azure SQL-Datenbank 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();

Übersicht über die Sperrung

Dies ist eine kurze Zusammenfassung des Verhaltens, wenn die optimierte Sperrung nicht aktiviert ist. Weitere Informationen finden Sie im Leitfaden zur Transaktionssperre und Zeilenversionsverwaltung.

In der Datenbank-Engine ist das Sperren ein Mechanismus, der verhindert, dass mehrere Transaktionen dieselben Daten gleichzeitig aktualisieren, um die Datenintegrität und Konsistenz zu schützen.

Wenn eine Transaktion Daten ändern muss, kann sie eine Sperre für die Daten anfordern. Die Sperre wird gewährt, wenn keine anderen widersprüchlichen Sperren für die Daten gespeichert werden, und die Transaktion kann mit der Änderung fortfahren. Wenn eine andere widersprüchliche Sperre für die Daten gehalten wird, muss die Transaktion warten, bis die Sperre freigegeben wird, bevor sie fortgesetzt werden 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 das Datenbankmodul die Semantik für die ANSI SQL-Transaktionsisolationsstufen bereitstellen kann. Obwohl das Sperren in Datenbanken unerlässlich ist, kann sich die Leistung und Skalierbarkeit negativ auf die Leistung und Skalierbarkeit auswirken, da die Gleichzeitigkeit, die Deadlocks, die Komplexität und der Sperraufwand beeinträchtigt werden.

Optimierte Sperrung und Transaktions-ID (TID)

Jede Zeile im Datenbank-Engine enthält intern eine Transaktions-ID (TID), wenn die Zeilenversionsverwaltung verwendet wird. Diese TID wird auf dem Datenträger beibehalten. Jede Transaktion, die eine Zeile ändert, stempelt diese Zeile mit seiner TID.

Wenn TID gesperrt wird, anstatt die Sperre auf die Taste der Zeile zu übernehmen, wird eine Sperre für die TID der Zeile übernommen. Die Änderungstransaktion enthält eine X-Sperre auf deren TID. Andere Transaktionen erwerben eine S-Sperre auf der TID, um zu überprüfen, ob die erste Transaktion noch aktiv ist. Bei TID-Sperren werden Seiten- und Zeilensperren weiterhin für Aktualisierungen verwendet, aber jede Seiten- und Zeilensperre wird freigegeben, sobald jede Zeile aktualisiert wird. Die einzige Sperre, die bis zum Ende der Transaktion gehalten wird, ist die X-Sperre für die TID-Ressource, wobei Seiten- und Zeilensperren (Schlüssel) ersetzt werden, wie in der nächsten Demo gezeigt. (Andere Standarddatenbank- und Objektsperren sind von der optimierten Sperrung nicht betroffen.)

Optimierte Sperrung hilft, den Sperrspeicher zu reduzieren, da nur sehr wenige Sperren für große Transaktionen gehalten werden. Darüber hinaus vermeidet eine optimierte Sperrung auch Sperreskalationen. Dadurch können andere gleichzeitige Transaktionen auf die Tabelle zugreifen.

Betrachten Sie das folgende T-SQL-Beispielszenario, das nach Sperren in der aktuellen 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;

A screenshot of the result set of a query on sys.dm_tran_locks for a single session shows only one lock when optimized locking is enabled.

Die gleiche Abfrage ohne den Vorteil einer optimierten Sperrung erstellt vier Sperren:

A screenshot of the result set of a query on sys.dm_tran_locks for a single session shows three locks when optimized locking is not enabled.

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 der Qualifikation (LAQ)

Basierend auf der TID-Infrastruktur ändert sich die optimierte Sperrung, wie Abfrage-Prädikate sichere Sperren sichern.

Ohne optimierte Sperrung werden Prädikate aus Abfragen zeilenweise in einer Überprüfung überprüft, indem zuerst eine Aktualisierungszeile (U)-Zeilensperre durchgeführt wird. Wenn das Prädikat zufrieden ist, wird vor dem Aktualisieren der Zeile eine X-Zeilensperre verwendet.

Bei optimierter Sperrung und wenn die Momentaufnahme Isolationsstufe (RCSI) für Lesezugriff aktiviert ist, werden Prädikate auf die neueste zugesicherte Version angewendet, ohne Zeilensperren zu übernehmen. Wenn das Prädikat nicht erfüllt ist, wird die Abfrage zur nächsten Zeile im Scan verschoben. Wenn das Prädikat zufrieden ist, wird eine X-Zeilensperre verwendet, um die Zeile tatsächlich zu aktualisieren. Die X-Zeilensperre wird freigegeben, sobald die Zeilenaktualisierung abgeschlossen ist, vor dem Ende der Transaktion.

Da die Prädikatauswertung ohne Abrufen von Sperren durchgeführt wird, werden gleichzeitige Abfragen, die verschiedene Zeilen ändern, nicht miteinander blockiert.

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 der Blockierung von Änderungen mit optimierter Sperrung 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 zufrieden ist, warten wir, bis eine aktive Transaktion in der Zeile abgeschlossen ist. Wenn wir auf die S-TID-Sperre warten mussten, hat sich die Zeile möglicherweise geändert, und die neueste zugesicherte Version wurde möglicherweise geändert. In diesem Fall versucht die Datenbank-Engine die Prädikatauswertung in derselben Zeile, anstatt die Transaktion aufgrund eines Aktualisierungskonflikts abzubrechen. Wenn das Prädikat beim Wiederholen qualifiziert 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

Betrachten wir das Ergebnis des obigen Szenarios mit und ohne Sperre nach der Qualifikation (LAQ), einem integralen Bestandteil der optimierten Sperre.

Ohne LAQ

Ohne LAQ wird die Transaktion T2 blockiert und warten, bis die Transaktion T1 abgeschlossen ist.

Nach dem Commit beider Transaktionen enthält die Tabelle t1 die folgenden Zeilen:

 a | b
 1 | 3

Mit LAQ

Mit LAQ verwendet Transaktion T2 die neueste zugesicherte Version der Zeile b (b=1 im Versionsspeicher), um das Prädikat (b=2) auszuwerten. Diese Zeile ist nicht qualifiziert; daher wird sie übersprungen, und T2 wechselt zur nächsten Zeile, ohne dass die Transaktion T1 blockiert wurde. In diesem Beispiel entfernt LAQ die Blockierung, führt jedoch zu unterschiedlichen Ergebnissen.

Nach dem Commit beider Transaktionen enthält die 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 gleichzeitigen Systemen unter RCSI mit Workloads, die auf einer strengen Ausführungsreihenfolge von Transaktionen (wie in der vorherigen Übung gezeigt) basieren, besteht darin, strengere Isolationsstufen zu verwenden.

Diagnosezufügungen für eine optimierte Sperrung

Um die Überwachung und Problembehandlung beim Blockieren und Deadlocking mit optimierter Sperrung zu unterstützen, suchen Sie nach den folgenden Ergänzungen:

  • Warten von Typen auf optimierte Sperrung
    • 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-Typs wait_resource wartet, mit der Absicht zu lesen.
      • LCK_M_S_XACT_MODIFY – Tritt auf, wenn eine Aufgabe auf eine freigegebene Sperre eines XACT-Typs wait_resource wartet, mit der Absicht, sie zu ändern.
      • LCK_M_S_XACT – Tritt auf, wenn eine Aufgabe auf eine freigegebene Sperre eines XACT-Typs wait_resource wartet, wobei die Absicht nicht abgeleitet werden kann. Selten.
  • Sperrung der Sichtbarkeit von Ressourcen
    • XACT Sperren von Ressourcen. Weitere Informationen finden Sie unter sys.dm_tran_locks (Transact-SQL).For more information, see resource_description in sys.dm_tran_locks (Transact-SQL).
  • Warten der Ressourcensichtbarkeit
  • Deadlock-Diagramm
    • Unter jeder Ressource im Deadlock-Bericht <resource-list>meldet jedes <xactlock> Element die zugrunde liegenden Ressourcen und spezifischen Informationen für Sperren jedes Elements eines Deadlocks. Weitere Informationen und ein Beispiel finden Sie unter "Optimierte Sperrung" und "Deadlocks".

Bewährte Methoden mit optimierter Sperrung

Aktivieren der Momentaufnahme Isolation (Read Committed Momentaufnahme Isolation, RCSI)

Um die Vorteile der optimierten Sperre zu maximieren, wird empfohlen, lesesicherte Momentaufnahme Isolation (RCSI) für die Datenbank zu aktivieren und lesesicherte Isolation als Standardisolationsstufe zu verwenden. Wenn sie nicht aktiviert ist, aktivieren Sie RCSI mithilfe des folgenden Beispiels:

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

In Azure SQL-Datenbank ist RCSI standardmäßig aktiviert und lesesichert die Standardisolationsstufe. Wenn RCSI aktiviert ist und lesesicher isolationsstufe verwendet wird, blockieren Leser keine Autoren und Autoren blockieren keine Leser. Leser lesen eine Version der Zeile aus der Momentaufnahme zu Beginn der Abfrage. Mit LAQ qualifizieren Autoren Zeilen pro Prädikat basierend auf der neuesten zugesicherten Version der Zeile, ohne U-Sperren zu erwerben. Bei LAQ wartet eine Abfrage nur, wenn die Zeile qualifiziert ist und eine aktive Schreibtransaktion für diese Zeile vorhanden ist. Das Qualifizieren basierend auf der neuesten zugesicherten Version und sperren nur die qualifizierten Zeilen reduziert die Blockierung und erhöht die Parallelität.

Zusätzlich zur reduzierten Blockierung wird der erforderliche Sperrspeicher reduziert. Dies liegt daran, dass Leser keine Sperren übernehmen, und Autoren nehmen nur kurze Sperrungen ein, anstatt sperren, die am Ende der Transaktion ablaufen. Bei Verwendung strengerer Isolationsstufen wie wiederholbares Lesen oder Serialisieren wird die Datenbank-Engine gezwungen, Zeilen- und Seitensperren bis zum Ende der Transaktion für Leser und Autoren zu speichern, was zu einem erhöhten Blockierungs- und Sperrspeicher führt.

Vermeiden von Sperrhinweisen

Während Tabellen- und Abfragehinweise berücksichtigt werden, reduzieren sie den Vorteil einer 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 "UPDLOCK" und späteren Aktualisierungen. Es wird empfohlen, nur bei Bedarf Sperrhinweise zu verwenden.

Bei optimierter Sperrung gibt es keine Einschränkungen für vorhandene Abfragen, und Abfragen müssen nicht neu geschrieben werden. Abfragen, die keine Hinweise verwenden, profitieren am meisten von der optimierten Sperrung.

Ein Tabellenhinweis auf eine Tabelle in einer Abfrage deaktiviert die optimierte Sperrung für andere Tabellen in derselben Abfrage nicht. Darüber hinaus wirkt sich die optimierte Sperre nur auf das Sperrverhalten von Tabellen aus, die von einer UPDATE-Anweisung aktualisiert werden. 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 sie dennoch von einer optimierten Sperrung profitieren können.

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 t3 können weiterhin von einer optimierten Sperrung profitieren. Das gleiche gilt für den HOLDLOCK-Hinweis.

Häufig gestellte Fragen (FAQ)

Wo ist die derzeit optimierte Sperrung verfügbar?

Derzeit ist eine 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 Sperre standardmäßig sowohl in neuen als auch in vorhandenen Datenbanken optimiert?

In Azure SQL-Datenbank ja.

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

Siehe Ist die optimierte Sperrung aktiviert?

Was geschieht, wenn die beschleunigte Datenbankwiederherstellung (ADR) für meine Datenbank nicht aktiviert ist?

Wenn ADR deaktiviert ist, wird auch die optimierte Sperre automatisch deaktiviert.

Was geschieht, wenn ich erzwinge, dass Abfragen trotz optimierter Sperrung blockiert werden?

Verwenden Sie für Kunden, die RCSI verwenden, um das Blockieren zwischen zwei Abfragen zu erzwingen, wenn die optimierte Sperrung aktiviert ist, den READCOMMITTEDLOCK-Abfragehinweis.

Kann ich die optimierte Sperrung deaktivieren?

Derzeit können Kunden eine Supportanfrage erstellen, um die optimierte Sperrung zu deaktivieren.

Führen Sie die folgenden Schritte aus, um eine neue Supportanfrage aus dem Azure-Portal für Azure SQL-Datenbank zu erstellen.

  1. Überprüfen Sie zunächst, ob die optimierte Sperre für Ihre Datenbank aktiviert ist.

  2. Wählen Sie im Azure-Portal im Menü die Option Hilfe und Support aus.

    A screenshot of the Azure portal identifying the help and support link.

  3. Wählen Sie unter Hilfe und Support die Option Supportanfrage erstellen aus.

    A screenshot of the Azure portal showing how to create a new support request.

  4. Wählen Sie als Problemtyp die Option Technisch aus.

  5. Wählen Sie für Abonnement, Dienst und Ressource die gewünschte SQL-Datenbank aus.

  6. Geben Sie in der Zusammenfassung "Optimierte Sperrung deaktivieren" ein.

  7. Wählen Sie für "Problemtyp" die Option "Leistung" und "Abfrageausführung" aus.

  8. Wählen Sie für "Problemuntertyp" die Option "Blockieren" und "Deadlocks" aus.

  9. Geben Sie in zusätzlichen Details so viele Informationen wie möglich an, warum Sie die optimierte Sperrung deaktivieren möchten. Wir sind interessiert, die Gründe und Anwendungsfälle für die Deaktivierung der optimierten Sperrung mit Ihnen zu überprüfen.

Zugehöriger Inhalt