Optimized Locking: Wo sind meine Sperren hin?

Dass Sperren fehlen, die man jahrelang zur Diagnose genutzt hat, irritiert. Dabei ist Optimized Locking keine Revolution aus dem Nichts, sondern der dritte Schritt einer Linie, die mit SQL Server 2019 begann. Wer die Schritte kennt, versteht, warum die Sperren fehlen und wo sie trotzdem noch da sind.

In 60 Sekunden

• Optimized Locking besteht aus zwei Teilen: TID Locking und Lock After Qualification (LAQ). Es wirkt auf die Sperren von INSERT, UPDATE, DELETE und MERGE.
• TID Locking: Eine schreibende Transaktion hält bis zum Commit nur eine X-Sperre auf ihre eigene Transaktions-ID. Zeilen- und Seitensperren gibt sie gleich nach der Änderung wieder frei.
• LAQ: Ein UPDATE oder DELETE prüft seine WHERE-Bedingung gegen die zuletzt bestätigte Version einer Zeile, ohne zu sperren. Gesperrt wird nur, was wirklich qualifiziert. LAQ funktioniert nur mit Read Committed Snapshot (RCSI).
• Voraussetzung ist Accelerated Database Recovery (ADR), erklärt in der Vertiefung ADR. In SQL Server 2025 ist Optimized Locking standardmäßig aus, in Azure SQL Database an.
• Was es nicht ist: kein Ersatz für RCSI, kein Freibrief gegen Deadlocks und kein Abschied von der Lock Escalation. Und es ist eine der seltenen Performance-Funktionen, die in Grenzfällen das Ergebnis ändern kann.

Warum es sich fremd anfühlt

Drei getrennte Schalter wirken zusammen: ADR, RCSI und Optimized Locking. Viele Erklärungen behandeln sie als einen. Dazu kommt, dass man nach dem Einschalten weniger sieht als vorher. Und je nach Plattform ist die Voreinstellung eine andere: Eine Demo in Azure SQL Database zeigt etwas anderes als derselbe Code auf einem frisch installierten SQL Server 2025. Die Linie von 2017 bis 2025 steht auf der Übersicht; hier geht es um die beiden Bausteine von 2025.

Baustein 1: TID Locking

Jede Zeile trägt die ID der Transaktion, die sie zuletzt geändert hat (TID). Optimized Locking nutzt das so:

1. Die schreibende Transaktion nimmt eine X-Sperre auf ihre eigene TID und hält sie bis zum Commit.
2. Für jede Zeile nimmt sie kurz die gewohnte Zeilensperre, ändert die Zeile und gibt die Zeilensperre sofort wieder frei.
3. Will eine andere Transaktion diese Zeile ändern, sieht sie die TID in der Zeile. Läuft diese Transaktion noch, wartet sie mit einer S-Sperre auf die TID, nicht auf die Zeile.

Der Schutz bleibt gleich, denn niemand überschreibt eine nicht bestätigte Änderung. Gehalten wird aber nur noch eine Sperre statt tausender. Der Lock-Speicher sinkt, und die Eskalationsschwelle wird viel seltener erreicht. Sie existiert aber weiter.

Im Lab blieb es von 1 bis 50.000 geänderten Zeilen bei der XACT-Sperre und einer Absichtssperre auf die Tabelle, solange die Zeilen schon einmal geändert worden waren. Ohne Optimized Locking belegten 51.391 gehaltene Sperren rund 7,8 MB Lock-Speicher, etwa 150 Byte je Sperre. Die Schwelle zählt je Index: Ein UPDATE einer indizierten Spalte über 2.000 Zeilen hielt 6.000 Schlüsselsperren, ohne zu eskalieren. In LAB-10 hielt ein UPDATE über 1.000 Zeilen mit ADR und RCSI 1.060 Sperren, mit Optimized Locking 3.

Die Ausnahme: Seiten, die sich in der Transaktion teilen. Ändert eine Transaktion Zeilen auf vollen Seiten zum ersten Mal, wächst unter ADR der Datensatz um die Versionsinformation (im Lab von 234 auf 255 Byte), die Seite teilt sich, und für jede in dieser Transaktion geteilte Seite bleibt eine Seitensperre (PAGE IX) bis zum Commit. Im Lab waren es nach dem Laden genau so viele Seitensperren wie neue Seiten: 59 bei 2.000 Zeilen, 1.839 bei einem Scan über die ganze Tabelle, 56 nach einem Index-Rebuild. Beim zweiten Lauf auf denselben Zeilen: null. Kompilierung, Plan-Cache, Sitzung, Query Store oder ein Neustart der Instanz ändern daran nichts; nur frische Zeilen lösen es aus. Microsoft nennt für Skip Index Locks die Ausnahme „Zeilen auf Seiten, die in derselben Transaktion geteilt wurden“; dass die Seitensperre dann bis zum Transaktionsende bleibt, ist gemessen, nicht dokumentiert. Ohne RCSI trat der Effekt im Lab nur beim Scan auf.

Diese Ausnahme hat eine Folge: Lock Escalation gibt es mit Optimized Locking weiterhin. Ein UPDATE über 100.000 Zeilen auf einer frisch geladenen Tabelle teilte im Lab praktisch jede Seite (2.870 auf 5.715), hielt dafür Seitensperren und eskalierte in 18 von 18 ersten Läufen auf zwei Builds zur exklusiven Tabellensperre, auch mit ROWLOCK. Die 36 Folgeläufe auf denselben Zeilen eskalierten nie. LOCK_ESCALATION = DISABLE verhindert die Eskalation; dann bleiben 2.857 Seitensperren bis zum Commit. Typisch ist das nach dem Laden oder nach einem Rebuild mit Füllfaktor 100. Ob freier Platz auf den Seiten den Effekt verringert, haben wir nicht gemessen.

Mit Optimized Locking v2 (SQL Server 2025, Azure SQL) kann SQL Server die kurzen Zeilen- und Seitensperren bei bestimmten INSERT- und UPDATE-Fällen ganz überspringen (Skip Index Locks). Das gilt nur, solange auf der Seite keine Abfrage mit REPEATABLE READ, SERIALIZABLE oder entsprechenden Hinweisen mitliest, und nur mit RCSI.

Baustein 2: Lock After Qualification

Ohne LAQ arbeitet ein UPDATE … WHERE so: Es liest jede Kandidatenzeile mit einer U-Sperre, prüft die Bedingung und wandelt die Sperre bei einem Treffer in X um. Bei einer gesperrten Zeile wartet es, auch wenn diese Zeile die Bedingung gar nicht erfüllt. Das gilt auch unter RCSI: RCSI schützt Leser, aber Schreiber qualifizieren dort weiterhin über U-Sperren auf der aktuellen Zeile.

Mit LAQ prüft das UPDATE die Bedingung ohne Sperre gegen die zuletzt bestätigte Version. Nur Zeilen, die passen, werden gesperrt und geändert. Ändert sich eine Zeile zwischen Prüfung und Sperre, wird sie erneut geprüft. Notfalls startet SQL Server das Statement intern neu, sichtbar als Extended Event lock_after_qual_stmt_abort.

Das typische Ergebnis: Zwei UPDATEs, die über einen Scan verschiedene Zeilen treffen, blockieren sich nicht mehr. Im Lab ändert Session A Zeile 50.000 und hält die Transaktion offen. Session B ändert per Scan, ohne passenden Index, eine ganz andere Zeile:

Von 16 Konfigurationen über vier Versionen lässt nur die letzte Session B vorbei. In LAB-10 wartete der Scan-Schreiber mit ADR und RCSI im Median 1.296,94 ms, mit Optimized Locking 8,04 ms; unter Last stieg der Durchsatz von 52,89 auf 117,06 Vorgänge je Sekunde.

LAQ greift nicht in diesen Fällen:

• ohne RCSI oder unter einer anderen Isolationsstufe als READ COMMITTED;
• mit UPDLOCK, XLOCK, HOLDLOCK oder READCOMMITTEDLOCK;
• bei MERGE, Variablenzuweisung oder OUTPUT als Resultset. Microsoft nennt auch OUTPUT … INTO eine Tabellenvariable als Ausschluss; in unseren Tests auf SQL Server 2025 CU9 und CU10 blockierte aber nur OUTPUT an den Client (LCK_M_S_XACT_MODIFY). OUTPUT … INTO eine Tabellenvariable, eine temporäre oder eine permanente Tabelle lief in 3 von 3 Läufen je Build am gesperrten Datensatz vorbei. Ob die Dokumentation ungenau ist oder das Verhalten geändert wurde, wissen wir nicht;
• auf Tabellen mit Columnstore-Index;
• bei Plänen mit mehr als einem Seek- oder Scan-Operator für die zu ändernden Zeilen;
• wenn die LAQ-Heuristik zu viel verschwendete Arbeit feststellt. Seit v2 wird LAQ dann je Plan abgeschaltet und das im Query Store festgehalten; vorher galt die Abschaltung für die ganze Datenbank.

Die eine Verhaltensänderung

Eine Tabelle ohne Index enthält eine Zeile mit a = 1, b = 1.

• Ohne LAQ wartet Session 2 auf Session 1. Nach deren Commit sieht sie b = 2, die Bedingung passt, und das Ergebnis ist b = 3.
• Mit LAQ prüft Session 2 gegen die zuletzt bestätigte Version b = 1. Die Zeile passt nicht, Session 2 wartet nicht und ändert nichts. Das Ergebnis ist b = 2.

Microsoft dokumentiert diesen Fall selbst und ordnet ihn ein: Unter versionsbasierten Isolationsstufen dürfen Anwendungen ohne Sperrhinweise ohnehin keine feste Ausführungsreihenfolge voraussetzen. Wer sie braucht, verwendet REPEATABLE READ oder SERIALIZABLE. Im Lab exakt nachgestellt: In allen Konfigurationen ohne LAQ, auch mit Optimized Locking ohne RCSI, endet es mit b = 3, mit LAQ mit b = 2, in drei von drei Läufen.

Das ist der wichtigste Grund, Optimized Locking nicht ohne Test einzuschalten. Ein Grund, darauf zu verzichten, ist es nicht.

Was bleibt, wie es war

• Leser unter klassischem READ COMMITTED. Optimized Locking betrifft DML-Sperren. Dass Leser nicht auf Schreiber warten, leistet weiterhin RCSI. Im Lab wartete ein SELECT unter READ COMMITTED mit Optimized Locking auf die geänderte Zeile, mit dem neuen Wartetyp LCK_M_S_XACT_READ, und las nach dem Commit den neuen Wert. Unter RCSI wartete es nicht und las den alten. Die Dokumentation beschreibt das nicht ausdrücklich.
• Konflikte auf derselben Zeile. Wer eine Zeile ändern will, die eine offene Transaktion geändert hat, wartet weiterhin, jetzt auf deren TID (LCK_M_S_XACT_MODIFY). Das Ergebnis stimmt danach in allen Konfigurationen.
• Deadlocks. Bestimmte Muster, besonders solche mit U-Sperren, entfallen. Zwei Transaktionen, die über Kreuz die Zeilen der jeweils anderen ändern, laufen aber weiterhin in einen Deadlock; wie der Graph dann aussieht, zeigt Deadlockgraph lesen.
• Sperrhinweise. UPDLOCK, READCOMMITTEDLOCK oder REPEATABLE READ blockieren weiter, nur mit den neuen Wartetypen.
• Lock Escalation. Die Schwellen sind unverändert, sie werden nur selten erreicht. Unter REPEATABLE READ, SERIALIZABLE und mit Sperrhinweisen halten Transaktionen weiterhin Zeilen- und Seitensperren bis zum Ende.
• Schemasperren und andere Objektsperren bleiben, wie sie sind.
• TempDB und temporäre Tabellen nutzen Optimized Locking nicht, ebenso wenig lesbare Secondaries.

Die Schaltermatrix

Alle Zellen sind gemessen (Lab E1 bis E4, E11).

Was Sie in der Diagnose jetzt sehen

• sys.dm_tran_locks: Ressourcentyp XACT. Der Besitzer hält eine X-Sperre mit resource_associated_entity_id = 0. Ein Wartender zeigt in resource_description die XACT-Ressource und dahinter die betroffene KEY- oder RID-Ressource. Die Lagebild-Abfrage der Übersicht fasst das je Sitzung zusammen.
• Wartetypen: LCK_M_S_XACT_MODIFY (Warten mit Änderungsabsicht), LCK_M_S_XACT_READ (mit Leseabsicht), selten LCK_M_S_XACT. Wer Blocking-Dashboards nur auf LCK_M_U, LCK_M_X und LCK_M_S filtert, übersieht diese Wartezeiten.
• wait_resource in sys.dm_exec_requests, zum Beispiel XACT: 23:2476:0 KEY: 23:72057594049593344 (…).
• Deadlock-Report: Das Element <xactlock> enthält eine <UnderlyingResource>. Ältere Parser kennen es möglicherweise nicht.
• Extended Events: lock_after_qual_stmt_abort zeigt LAQ-Neustarts. locking_stats meldet regelmäßig je Datenbank den Status, Eskalationen und Gründe für Abfragen ohne LAQ. locking_stats2 liefert Statistiken zu v2.
• Query Store: sys.query_store_plan_feedback mit feature_desc = 'LAQ Feedback'.
• Blocked Process Report: Er zeigt waitresource="XACT: … KEY: …" und lockMode="S", aber keinen Wartetyp. Ob ein Leser oder ein Schreiber wartet, erkennen Sie nur in den DMVs. Vorsicht beim Filtern: Ein Extended-Events-Filter auf den Datenbanknamen unterdrückt den Report ganz, weil ihn ein Systemtask im Kontext von master erzeugt.

Einschalten, prüfen, zurücknehmen

-- 1. ADR (Voraussetzung), siehe Vertiefung ADRALTER DATABASE [MeineDB] SET ACCELERATED_DATABASE_RECOVERY = ON; -- 2. Optional, aber für LAQ nötig: RCSIALTER DATABASE [MeineDB] SET READ_COMMITTED_SNAPSHOT = ON; -- 3. Optimized LockingALTER DATABASE [MeineDB] SET OPTIMIZED_LOCKING = ON; -- PrüfenSELECT name, is_accelerated_database_recovery_on,       is_read_committed_snapshot_on, is_optimized_locking_onFROM sys.databases WHERE name = N'MeineDB';

• Jeder dieser Schritte braucht einen Moment, in dem keine anderen Verbindungen auf der Datenbank aktiv sind. Ein Wartungsfenster ist also nötig.
• Zurück geht es in umgekehrter Reihenfolge: erst Optimized Locking aus, dann ADR. Wer es andersherum versucht, bekommt Msg 12134 („Optimized Locking is enabled for this database. To disable Accelerated Database Recovery, disable Optimized Locking and try again.“). Optimized Locking ohne ADR scheitert mit Msg 12133. SQL Server 2022 und älter kennen die Option nicht und melden einen Syntaxfehler (Msg 102).
• Editionen: Optimized Locking gibt es in SQL Server 2025 Enterprise und Standard, nicht in Express. ADR steht seit 2019 in Enterprise, Standard und Web zur Verfügung.
• Kosten: Sie entstehen fast ganz durch ADR: Platz für den Persistent Version Store, Logvolumen, verzögertes Aufräumen bei langen Transaktionen. Details und Messwerte in der Vertiefung ADR.
• Vor dem Einschalten testen: Code mit Sperrhinweisen bringt wenig Nutzen. Code, der sich auf eine Ausführungsreihenfolge unter RCSI verlässt, kann andere Ergebnisse liefern (siehe t4). Monitoring und Deadlock-Auswertung müssen XACT kennen.

Sechs Missverständnisse

Methode und Grenzen

Wir haben das Verhalten am 02.10.2026 nachgemessen: SQL Server 2017 CU31, 2019 CU32, 2022 CU27 und 2025 CU9 (Developer Edition, Linux-Container), je Version allein auf einer Test-VM mit 8 GB und 8 CPUs, eine synthetische Tabelle mit 100.000 Zeilen, jede Messung dreimal; LAB-10 am 05.10.2026 auf SQL Server 2025 mit 90 Lock- und Rollbackmessungen, 126 Konfliktfällen und 36 Lastläufen; die Gegenproben zu Seitenteilung, OUTPUT und Eskalation am 08.11.2026 auf SQL Server 2025 CU9 und CU10 (17.0.5035.5), je drei Läufe auf frischen Datenbanken. Alles darüber hinaus stammt aus Microsoft Learn, dem Forschungspaper zu ADR und den genannten Fachbeiträgen. Grenzen:

• Die Schwellen der LAQ-Heuristik veröffentlicht Microsoft nicht.
• Für die v2-Funktionen nennt Microsoft keinen genauen Build.
• Wie sich Leser unter klassischem READ COMMITTED mit aktivem Optimized Locking verhalten, beschreibt die Dokumentation nicht. Unser Ergebnis (LCK_M_S_XACT_READ) stammt aus einem Build.
• Dass Seitensperren für in der Transaktion geteilte Seiten bis zum Commit bleiben, und wie SQL Server die Sperren bis zur Eskalationsschwelle zählt (das Extended Event meldete 5.000 bis 7.488 bei höchstens rund 2.500 gleichzeitig sichtbaren Sperren), ist gemessen, aber von Microsoft nicht beschrieben.
• Das Verhalten von OUTPUT … INTO unter LAQ weicht von der Dokumentation ab; zwei Builds, ein Host.
• lock_after_qual_stmt_abort konnten wir nur mit TOP (1) … ORDER BY auslösen, und nur beim ersten Lauf. Danach schaltet das LAQ-Feedback im Query Store LAQ für diesen Plan ab.
• Zeitwerte stammen von kleinen VMs mit SATA-SSD. Sie zeigen Größenordnungen, keine Prognose für Ihr System.
• Azure SQL Database haben wir nicht gemessen.

Weiterlesen

• Microsoft Learn: Optimized locking; Accelerated database recovery; Transaction Locking and Row Versioning Guide; Deadlocks Guide.
• Antonopoulos et al.: Constant Time Recovery in Azure SQL Database (VLDB 2019), der wissenschaftliche Hintergrund von ADR.
• Dimitri Furman (Microsoft): Introducing optimized locking v2 (18.11.2025).
• Paul White: Data Modifications under Read Committed Snapshot Isolation.
• Bei PSG: Der Leser, der die Tabelle sperrt, Deadlockgraph lesen.

Sie überlegen, es einzuschalten?

Oder Ihr Blocking-Monitoring zeigt seit dem Upgrade Rätselhaftes? In der Performance-Analyse messen wir an Ihrer Last, wo LAQ greift, wo Hinweise es verhindern und welche Abfragen sich anders verhalten. Die Mechanik dahinter vermitteln wir in der PTS-Schulung.

Weitere Locking-Themen