Optimierte Sperrung

Gilt für: SQL Server 2025 (17.x) Azure SQL-Datenbank Azure SQL Managed InstanceSQL-Datenbank in Microsoft Fabric

Optimierte Sperrung bietet einen verbesserten Mechanismus für die Transaktionssperrung, um die Sperrblockade und den Speicherverbrauch der Sperren für gleichzeitige Transaktionen zu reduzieren.

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 Sperreskalationen und kann bestimmte Arten von Blockierungen verhindern. 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 auf der TID verwendet, um alle geänderten Zeilen zu schützen. Weitere Informationen finden Sie unter Transaktions-ID (TID) Sperrung.
• 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 Gleichzeitigkeit verbessert wird. LAQ erfordert lesesicherte Snapshotisolation (RCSI). Weitere Informationen finden Sie unter Sperren nach der Qualifikation (Lock After Qualification, LAQ).

Beispiel:

• Ohne optimierte Sperrung erfordert das Aktualisieren von 1.000 Zeilen in einer Tabelle unter Umständen 1.000 exklusive Zeilensperren (X), die bis zum Ende der Transaktion aufrechterhalten werden.
• Bei optimierter Sperrung erfordern das Aktualisieren von 1.000 Zeilen in einer Tabelle möglicherweise 1.000 X Zeilensperren, aber jede Sperre wird freigegeben, sobald jede Zeile aktualisiert wird, und nur eine X TID-Sperre wird bis zum Ende der Transaktion gehalten. Da Sperren schnell freigegeben werden, wird die Speicherauslastung der Sperre verringert, und die Sperrenausweitung ist viel weniger wahrscheinlich, was die Workload-Nebenläufigkeit verbessert.

Note

Durch 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, beispielsweise Schemasperren.

Availability

In der folgenden Tabelle sind die Verfügbarkeit und der aktivierte Zustand der optimierten Sperre auf SQL-Plattformen zusammengefasst.

Platform	Available	Standardmäßig aktiviert
Azure SQL-Datenbank	Yes	Ja (immer aktiviert)
SQL-Datenbank in Microsoft Fabric	Yes	Ja (immer aktiviert)
Azure SQL Managed InstanceAUTD	Yes	Ja (immer aktiviert)
Azure SQL Managed Instance2025	Yes	Ja (immer aktiviert)
Azure SQL Managed Instance2022	No	N/A
SQL Server 2025 (17.x)	Yes	Nein (kann pro Datenbank aktiviert werden)
SQL Server 2022 (16.x) und ältere Versionen	No	N/A

Aktivieren und Deaktivieren

Verwenden Sie den ALTER DATABASE ... SET OPTIMIZED_LOCKING = ON | OFF Befehl, um die optimierte Sperre für eine SQL Server-Datenbank zu aktivieren oder zu deaktivieren. Weitere Informationen finden Sie unter ALTER DATABASE SET-Optionen.

Optimiertes Sperren baut auf anderen Datenbank-Features auf:

• Sie müssen die beschleunigte Datenbankwiederherstellung (ADR) für eine Datenbank aktivieren, bevor Sie eine optimierte Sperrung aktivieren können. Umgekehrt müssen Sie zum Deaktivieren des ADR zuerst die optimierte Sperrung deaktivieren, wenn sie aktiviert ist.
• Um den größten Nutzen aus der optimierten Sperrung zu ziehen, sollte die Read Committed Snapshot Isolation (RCSI) für die Datenbank aktiviert sein. Die LAQ-Komponente der optimierten Sperre ist nur dann wirksam, wenn RCSI aktiviert ist.

ADR ist immer in Azure SQL-Datenbank, azure SQL Managed Instance und SQL-Datenbank in Microsoft Fabric aktiviert. RCSI ist in der Azure SQL-Datenbank und der SQL-Datenbank in Microsoft Fabric standardmäßig aktiviert.

Um zu überprüfen, ob diese Optionen für Ihre aktuelle Datenbank aktiviert sind, stellen Sie zuerst eine Verbindung zur Datenbank her und führen dann die folgende T-SQL-Abfrage aus:

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

Ist die optimierte Sperrung aktiviert?

Die optimierte Sperrung ist pro Datenbank aktiviert. Stellen Sie eine Verbindung zu Ihrer Datenbank her und verwenden Sie die folgende Abfrage, um zu überprüfen, ob die optimierte Sperrung aktiviert ist:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS is_optimized_locking_enabled;

Result	Description
0	Die optimierte Sperrung ist deaktiviert.
1	Die optimierte Sperrung ist aktiviert.
NULL	Optimierte Verriegelung ist nicht verfügbar.

Sie können auch die Katalogansicht "sys.databases " verwenden. Um beispielsweise festzustellen, ob die optimierte Sperrung für alle Datenbanken aktiviert ist, führen Sie die folgende Abfrage aus:

SELECT database_id,
       name,
       is_optimized_locking_on
FROM sys.databases;

Übersicht über die Sperrung

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

Im Datenbankmodul ist die Sperrung ein Mechanismus, der verhindert, dass mehrere Transaktionen dieselben Daten gleichzeitig aktualisieren, um die ACID-Eigenschaften von Transaktionen zu gewährleisten.

Wenn eine Transaktion Daten ändern muss, fordert sie eine Sperre für diese Daten an. 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 versuchen, gleichzeitig auf dieselben Daten zuzugreifen, muss das Datenbankmodul 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 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.

Sperren der Transaktions-ID (TID)

Wenn die auf der Zeilenversionsverwaltung basierenden Isolationsebenen verwendet werden oder wenn die beschleunigte Datenbankwiederherstellung (Accelerated Database Recovery, ADR) aktiviert ist, enthält jede Zeile in der Datenbank intern eine Transaktions-ID (TID). TID wird mit der Zeile gespeichert. Jede Transaktion, die eine Zeile ändert, stempelt die 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 aufrecht. 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, welche die Seiten- und Zeilensperren (Schlüssel) ersetzt.

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

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

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).

Wenn die optimierte Sperre nicht aktiviert ist, enthält die gleiche Anforderung vier Sperren – eine IX (intent exclusive) Sperre auf der Seite, die die Zeilen enthält, und drei X Schlüsselsperren auf jeder Zeile:

Die sys.dm_tran_locks dynamische Verwaltungsansicht (Dynamic Management View, DMV) ist nützlich, um Sperrprobleme zu untersuchen oder zu beheben. Hier wird es verwendet, um eine optimierte Verriegelung in der Praxis zu beobachten.

Sperren nach der Qualifikation (Lock After Qualification, LAQ)

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

Ohne optimiertes Sperren werden Abfrageprädikate 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) angewendet, bevor die Zeile aktualisiert wird.

Mit optimierten Sperrvorgängen und wenn die Momentaufnahmeisolationsstufe (RCSI) aktiviert ist, können READ COMMITTED Prädikate optimistisch auf die neueste zugesicherte Version der Zeile überprüft werden, ohne Sperren einzugehen. 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 angewendet, um die Zeile zu aktualisieren.

Anders gesagt: Die Sperre wird nach der Qualifikation der Zeile zur Änderung angewendet. 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:

/* Confirm that optimized locking and read committed snapshot isolation (RCSI) are both enabled on this database. */
SELECT database_id,
       name,
       is_accelerated_database_recovery_on,
       is_optimized_locking_on,
       is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

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, weil Sitzung 1 eine U-Sperre für die Zeile enthält, die in Sitzung 2 aktualisiert werden muss. Bei der optimierten Sperrung wird Sitzung 2 jedoch nicht blockiert, weil U-Sperren nicht angewendet werden und Spalte a in der aktuellen zugesicherten Version von Zeile 1 gleich 1 ist, wodurch das Prädikat von Sitzung 2 nicht erfüllt wird.

Das Sperren nach der Qualifikation (LAQ) wird optimistisch unter der Annahme ausgeführt, dass eine Zeile nach der Überprüfung des Prädikats nicht geändert wird. Wenn das Prädikat erfüllt ist und die Datenzeile nach der Überprüfung des Prädikats nicht verändert wurde, wird sie von der aktuellen Transaktion geändert.

Da die U Sperren nicht übernommen werden, kann eine gleichzeitige Transaktion die Zeile ändern, nachdem das Prädikat ausgewertet wurde. Wenn es eine aktive Transaktion gibt, die eine X TID-Sperre für die Zeile hat, wartet das Datenbankmodul, bis die Transaktion abgeschlossen ist. Wenn sich die Zeile geändert hat, nachdem das Prädikat zuvor ausgewertet wurde, überprüft das Datenbankmodul das Prädikat erneut (qualifiziert), bevor die Zeile geändert wird. Wenn das Prädikat dann immer noch erfüllt wird, wird die Zeile geändert.

Neuklassifizierung von Prädikaten wird von einer Teilmenge der Abfrage-Engine-Operatoren unterstützt. Wenn die Prädikatrevaluierung erforderlich ist, aber der Abfrageplan einen Operator verwendet, der keine Prädikatrequalifizierung unterstützt, bricht das Datenbankmodul die Verarbeitung der Anweisung intern ab und startet sie ohne LAQ neu. Wenn ein solcher Abbruch auftritt, wird das erweiterte lock_after_qual_stmt_abort-Ereignis ausgelöst.

Einige Anweisungen, z. B. Anweisungen mit variabler Zuweisung und Anweisungen mit der UPDATE, kann man nicht abbrechen und neu starten, ohne ihre Semantik zu ändern. Für solche Aussagen wird LAQ nicht verwendet.

Im folgenden Beispiel wird das Prädikat neu ausgewertet, 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;

Indexsperren umgehen (SIL)

Bei der TID-Sperre werden exklusive Zeilensperren von kurzer Dauer (X) und absicht-exklusive (IX) Seitensperren verwendet, um Zeilen zu ändern. Wenn RCSI und LAQ verwendet werden, sind diese Datenbanksperren nur erforderlich, wenn andere Abfragen auf die Zeile zugreifen und erwarten, dass sie unverändert bleibt. Beispiele für solche Abfragen sind diejenigen, die unter den REPEATABLE READ oder SERIALIZABLE Isolationsstufen ausgeführt werden oder die entsprechenden Sperrhints verwenden. Solche Abfragen werden als Zeilensperrabfragen (RLQ) bezeichnet.

Wenn keine RLQ-Abfragen auf eine Zeile zugreifen, kann das Datenbankmodul das Erstellen von Zeilen- und Seitensperren beim Ändern einer Zeile überspringen und nur eine exklusive Seitensperre verwenden. Durch diese Optimierung wird der Sperraufwand reduziert und die ACID-Transaktionssemantik beibehalten. Das Überspringen von Zeilen- und Seitensperren ist besonders vorteilhaft für Transaktionen, die eine große Anzahl von Zeilen ändern.

Derzeit wird die SIL-Optimierung nur in den folgenden Fällen verwendet:

• INSERT Aussagen über Heaps.
  • IX Seitensperren werden übersprungen.
• UPDATE Aussagen zu gruppierten Indizes, nicht gruppierten Indizes und Heaps.
  • IX Seitensperren und X Zeilensperren werden übersprungen.

Die SIL-Optimierung wird derzeit in den folgenden Fällen nicht verwendet:

• DELETE -Anweisungen.
• UPDATE Anweisungen für Heaps, wenn die Zeile vorhandene Weiterleitungszeiger enthält oder neue Weiterleitungszeiger durch die Aktualisierung hinzugefügt werden.
• Wenn die geänderte Zeile Spalten enthält, die die LOB-Datentypen verwenden, wie varchar(max), nvarchar(max), varbinary(max) und json.
• Für Seitenzeilen, die in derselben Transaktion aufgeteilt wurden.

LAQ-Heuristiken

Wie in Lock after qualification (LAQ) beschrieben, können Anweisungen, die Abfrageoperatoren verwenden, die keine Prädikatneuqualifizierung unterstützen, intern neu gestartet und ohne LAQ verarbeitet werden. Wenn dies häufig der Fall ist, kann der Aufwand für die Neuverarbeitung erheblich werden. Um den Aufwand zu minimieren, verwendet die optimierte Sperre einen heuristischen Feedbackmechanismus, der LAQ deaktiviert, wenn der Overhead Schwellenwerte überschreitet.

Für die Zwecke des Feedbackmechanismus wird die durch eine Anweisung geleistete Arbeit als Anzahl logischer Lesevorgänge gemessen. Wenn das Datenbankmodul eine Zeile ändert, die von einer anderen Transaktion geändert wurde, nachdem die Verarbeitung der Anweisung gestartet wurde, wird die Arbeit der Anweisung als potenziell vergeblich betrachtet, da die Anweisung möglicherweise wiederholt verarbeitet werden muss.

Wenn Anweisungen ausgeführt werden, verwaltet die Datenbank-Engine LAQ-Feedbackdaten, die die potenziell verschwendete Arbeit, die Häufigkeit der Erneutverarbeitung von Anweisungen und die gesamte Arbeit derjenigen Anweisungen nachverfolgen, die möglicherweise neu verarbeitet werden können.

LAQ ist deaktiviert, wenn das Verhältnis der potenziell verschwendeten Arbeit zur Gesamtarbeit oder das Verhältnis der Anzahl der neu verarbeiteten Anweisungen zur Gesamtanzahl der Anweisungen ihre jeweiligen Schwellenwerte überschreitet. Wenn beide Verhältnisse unter Schwellenwerten fallen, wird LAQ reaktiviert.

LAQ-Feedbackdaten werden auf zwei Ebenen nachverfolgt:

• Für einen Abfrageplan.

  • Das Datenbankmodul beginnt mit dem Nachverfolgen des LAQ-Feedbacks für einen Plan für das erste Vorkommen der Neuverarbeitung von Anweisungen.
  • Wenn eine Abfrage im Abfragespeicher erfasst wird, wird auch das LAQ-Feedback im Abfragespeicher erfasst. Das Datenbankmodul verwendet dieses Feedback, um LAQ für den Plan aktiviert oder deaktiviert zu lassen, wenn die Datenbank neu gestartet wird.
  • Abfragepläne mit erfasstem LAQ-Feedback weisen eine Zeile mit einem übereinstimmenden plan_id Wert in der sys.query_store_plan_feedback Katalogansicht auf. Die feature_id und feature_desc Spalten sind auf 4 bzw. LAQ Feedback festgelegt.

• Für eine Datenbank.

  • Feedback wird für alle Anweisungen aggregiert, die kein Feedback auf Abfrageplanebene haben, z. B. wenn eine Abfrage nicht im Abfragespeicher erfasst wird.
  • Das Feedback wird seit dem Start der Datenbank nachverfolgt und nach jedem Start neu erstellt.

Bei der Entscheidung, ob LAQ für eine Anweisung verwendet werden soll, verwendet das System ggf. das Abfrageplan-Feedback. Andernfalls wird das Feedback auf Datenbankebene verwendet. Dies bedeutet, dass einige Anweisungen mit LAQ ausgeführt werden können, und einige können ohne LAQ ausgeführt werden. Beispielsweise kann LAQ für einen Abfrageplan deaktiviert und für die Datenbank aktiviert sein, oder umgekehrt.

LAQ-Einschränkungen

Die Sperre nach der Qualifizierung wird in den folgenden Szenarien nicht verwendet:

• Wenn von LAQ Heuristik deaktiviert.
• Wenn widersprüchliche Sperrhinweise, wie beispielsweise UPDLOCK, READCOMMITTEDLOCK, XLOCK oder HOLDLOCK, verwendet werden.
• Wenn die Transaktionsisolationsstufe anders als READ COMMITTED ist, oder wenn die READ_COMMITTED_SNAPSHOT Datenbankoption deaktiviert ist.
• Wenn die geänderte Tabelle über einen Spaltenspeicherindex verfügt.
• Wenn die DML-Anweisung eine variable Zuordnung enthält.
• Wenn die DML-Anweisung eine OUTPUT Klausel enthält.
• Wenn die DML-Anweisung mehrere Index-Such- oder Scanoperator verwendet, um die zu ändernden Zeilen zu lesen.
• In MERGE-Anweisungen.

Änderungen des Abfrageverhaltens mit optimierter Sperrung und RCSI

Gleichzeitige Workloads unter Read Commited-Momentaufnahme-Isolationsstufe (Read Committed Snapshot Isolation, 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;

Im folgenden Abschnitt wird das Ergebnis des vorherigen Szenarios mit und ohne Sperre nach der Qualifikation (LAQ) ausgewertet.

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 Zeile und legt die Spalte b auf 3 fest, damit das Prädikat erfüllt ist.

Nachdem für beide Transaktionen ein Commit ausgeführt wurde, enthält Tabelle t4 die folgenden Zeilen:

 a | b
 1 | 3

Mit LAQ

Mit LAQ verwendet Transaktion T2 die neueste Version der Zeile, für die ein Commit ausgeführt wurde, wobei die Spalte b gleich 1 ist, um das zugehörige Prädikat (b = 2) auszuwerten. Die Zeile erfüllt die Bedingungen nicht; daher wird sie übersprungen, und die Anweisung wird ausgeführt, ohne dass die Transaktion T1 blockiert wurde. In diesem Beispiel beseitigt LAQ die Blockierung, führt aber zu anderen Ergebnissen.

Nachdem für beide Transaktionen ein Commit ausgeführt wurde, enthält Tabelle t4 die folgenden Zeilen:

 a | b
 1 | 2

Important

Auch ohne LAQ sollten Anwendungen nicht davon ausgehen, dass das Datenbankmodul strenge Sortierung garantiert, ohne Sperrhinweise zu verwenden, wenn Zeilenversionsbasierte Isolationsstufen verwendet werden. Unsere allgemeine Empfehlung für Kunden, die gleichzeitige Workloads unter RCSI ausführen, die auf einer strengen Ausführungsreihenfolge von Transaktionen basieren (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 Beobachten und Beheben von Blockierungen und Deadlocks, wenn eine optimierte Sperrung aktiviert ist:

• Wartetypen für optimiertes Sperren
  • XACT Wartetypen für die Sperre auf die S TID und Ressourcenbeschreibungen in sys.dm_os_wait_stats:
    • LCK_M_S_XACT_READ – Tritt auf, wenn eine Aufgabe auf eine freigegebene Sperre eines XACTwait_resource-Typs wartet, mit der Absicht zu lesen.
    • LCK_M_S_XACT_MODIFY – Tritt auf, wenn eine Aufgabe auf eine freigegebene Sperre eines XACTwait_resource-Typs wartet, mit der Absicht, sie zu ändern.
    • LCK_M_S_XACT – Tritt auf, wenn eine Aufgabe auf eine freigegebene Sperre eines XACTwait_resource-Typs wartet, bei dem die Absicht nicht abgeleitet werden kann. Dieses Szenario ist nicht üblich.
• Sperrung der Sichtbarkeit von Ressourcen
  • XACT: Gesperrte Ressourcen. Weitere Informationen finden Sie unter resource_descriptionsys.dm_tran_locks.
• Sichtbarkeit von Ressourcen abwarten
  • XACT: Wartende Ressourcen. Weitere Informationen finden Sie unter wait_resourcesys.dm_exec_requests.
• Deadlock-Diagramm
  • Unter jeder Ressource im Deadlock-Bericht <resource-list> 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.
• Erweiterte Ereignisse
  • Das lock_after_qual_stmt_abort Ereignis wird ausgelöst, wenn eine Anweisung intern aufgrund eines Konflikts mit einer anderen Transaktion erneut verarbeitet wird. Weitere Informationen finden Sie unter Sperren nach der Qualifikation (Lock After Qualification, LAQ).
  • Das locking_stats Ereignis wird für jede Datenbank alle paar Minuten ausgelöst und stellt aggregierte Sperrstatistiken für das Zeitintervall bereit, z. B. die Anzahl der Sperreskalationen, ob TID-Sperre und LAQ-Komponenten der optimierten Sperrung aktiviert sind, und die Anzahl der Abfragen, bei denen LAQ aus verschiedenen Gründen nicht verwendet wurde. Dieses Ereignis wird auch dann ausgelöst, wenn die optimierte Sperrung deaktiviert ist.
  • In SQL Server und Azure SQL Managed Instance wird das locking_stats2 Ereignis für jede Datenbank alle paar Minuten ausgelöst und liefert die skip index locks und LAQ-Heuristikstatistiken für das Zeitintervall.

Bewährte Methoden mit optimierter Sperrung

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

Um die Vorteile der optimierten Sperre zu maximieren, empfiehlt es sich, die Read Committed Snapshot Isolation (RCSI) für die Datenbank zu aktivieren und diese Isolationsstufe als Standard zu verwenden READ COMMITTED.

In der Azure SQL-Datenbank und sql-Datenbank in Microsoft Fabric ist RCSI standardmäßig aktiviert und READ COMMITTED ist die Standardisolationsstufe. Wenn RCSI aktiviert ist und die Isolationsstufe READ COMMITTED verwendet wird, lesen Reader 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 und 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.

Vermeiden von Sperrhinweisen

Tabellen- und Abfragehinweise wie UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCK usw. werden zwar geschätzt, wenn die optimierte Sperrung aktiviert ist, verringern aber den Vorteil der optimierten Sperrung. Sperrhinweise zwingen die Datenbank-Engine, Zeilen- oder Seitensperren anzuwenden 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 im UPDLOCK-Hinweis und bei der späteren Aktualisierung. Wir empfehlen die Verwendung von Sperrhinweisen nur bei tatsächlichem Bedarf.

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 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. 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 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, Azure SQL Managed Instance^AUTD und SQL-Datenbank in Microsoft Fabric ja. In SQL Server 2025 (17.x) ist die optimierte Sperrung standardmäßig deaktiviert, kann jedoch für jede Benutzerdatenbank aktiviert werden, die über eine aktivierte beschleunigte Datenbankwiederherstellung verfügt.

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

Siehe Ist die optimierte Sperrung aktiviert?

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

Wenn RCSI aktiviert ist, verwenden Sie den READCOMMITTEDLOCK Tabellenhinweis, um die Blockierung zwischen zwei Abfragen zu erzwingen, wenn die optimierte Sperrung aktiviert ist.

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

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

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

Derzeit leider nicht.

Verwandte Inhalte

• Handbuch zu Transaktionssperren und Zeilenversionsverwaltung
• Read Commited-Momentaufnahme-Isolationsstufe (Read Committed Snapshot Isolation, RCSI).
• sys.dm_tran_locks (Transact-SQL)
• Schnellere Datenbankwiederherstellung