Sperreskalation: Erst die Ursache finden

Ein Wartungsjob ändert wenige tausend Zeilen, und plötzlich warten fachlich unabhängige Anfragen. Sperreskalation ist eine mögliche Erklärung. Eine Tabellensperre in der Überwachung reicht jedoch nicht als Beweis. Entscheidend sind die tatsächlich inkompatible Sperre und die Transaktion, die sie hält.

Die Beobachtung richtig einordnen

Eine Intent-Exclusive-Sperre auf einem Objekt ist bei Änderungen an darunterliegenden Zeilen normal. Sie signalisiert feinere Sperren und bedeutet nicht, dass sämtliche Leser ausgeschlossen werden. Eine gemeinsame oder exklusive Tabellensperre hat andere Kompatibilitätsregeln. Wer IX mit X verwechselt, untersucht schnell die falsche Ursache.

Die Diagnoseabfrage zeigt gewährte und wartende Objektsperren in der aktuellen Datenbank. Sie ist bewusst eingeschränkt. Fehlt eine solche Sperre, können weiterhin Schlüssel-, Seiten-, Schema- oder Anwendungssperren blockieren. Die benötigten Diagnoseberechtigungen hängen von der SQL-Server-Version ab und gehören in eine kontrollierte Überwachungsrolle.

SELECT request_session_id, resource_type, request_mode,
       request_status, resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
  AND resource_type = 'OBJECT';

Ordnen Sie die Sitzungen der Blockierkette und den aktiven Anweisungen zu. Erfassen Sie bei Verdacht das Extended Event lock_escalation. Eine momentan sichtbare X-Objektsperre verrät nicht, ob sie durch Eskalation, einen expliziten Tabellenhinweis oder eine andere Operation entstand. Das Ereignis ergänzt die fehlende zeitliche Information.

Eine bestimmte Anzahl geänderter Zeilen ist keine garantierte Sicherheitsgrenze. Sperren und Zeilen sind verschiedene Größen. Indizes, Zugriffswege, Isolation und Speicherdruck beeinflussen den Bedarf. ROWLOCK garantiert nicht, dass niemals eskaliert wird. Das Abschalten der Eskalation verändert den Speicherbedarf und kann Blockierung durch Ressourcenmangel ersetzen.

Echte Transaktionsgrenzen schaffen

Reduziert werden müssen die gleichzeitig gehaltenen Ressourcen. Eine Schleife mit kleinen Anweisungen sammelt weiterhin Sperren, wenn eine äußere Transaktion alle Durchläufe umfasst. Erst unabhängige Commits schaffen Freigabepunkte. Prüfen Sie daher auch Aufrufer, Scheduler und Clientcode auf eine unbemerkte äußere Transaktion.

Das Beispiel erzeugt temporäre Übungsdaten und verarbeitet sie in getrennt bestätigten Anweisungen. Es verlangt, dass keine Transaktion offen ist und IMPLICIT_TRANSACTIONS ausgeschaltet ist. Die gewählte Paketgröße erläutert das Verfahren; sie ist keine allgemeine Empfehlung für Produktion.

IF @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2
    THROW 50000, 'Use autocommit with no open transaction.', 1;
CREATE TABLE #Work (Id int PRIMARY KEY, Done bit NOT NULL);
INSERT #Work
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
DECLARE @changed int = 1;
WHILE @changed > 0
BEGIN
    ;WITH batch AS
    (SELECT TOP (500) Id, Done FROM #Work
     WHERE Done = 0 ORDER BY Id)
    UPDATE batch SET Done = 1;
    SET @changed = @@ROWCOUNT;
END;
SELECT COUNT(*) AS Remaining FROM #Work WHERE Done = 0;

Die Auswahl nach Schlüssel macht den Fortschritt nachvollziehbar. Bei echten Tabellen benötigt das Auswahlprädikat einen passenden Zugriffsweg. Ein Paket kann nur 500 Zeilen ändern und trotzdem Millionen Zeilen durchsuchen. Bewerten Sie deshalb gelesene Zeilen, logische Zugriffe, Sperrwartezeiten und Transaktionsdauer zusätzlich zu @@ROWCOUNT.

Parallelbetrieb und Wiederanlauf prüfen

Führen Sie den Job gleichzeitig mit repräsentativen Lese- und Schreibanfragen aus. Vergleichen Sie besonders langsame Anfragen, Eskalationsereignisse und Logaufkommen vor und nach der Änderung. Ein etwas längerer Wartungsjob kann die bessere Lösung sein, wenn Benutzeranfragen ihre Zielzeit einhalten. Sehr kleine Pakete verursachen wiederum zusätzliche Commit-Kosten; die Größe muss aus Messwerten entstehen.

Unabhängige Commits ändern auch das Fehlerverhalten. Ein Abbruch hinterlässt einen teilweise abgeschlossenen Vorgang. Das Auswahlkriterium muss einen sicheren Wiederanlauf erlauben, und ein Fortschrittsdatensatz muss gemeinsam mit den zugehörigen Änderungen bestätigt werden. Benötigt das Geschäft vollständige Atomarität über alle Zeilen, darf Paketverarbeitung diese Zusage nicht stillschweigend ersetzen.

Untersuchen Sie außerdem Trigger, kaskadierende Änderungen, Replikation und den Logtransport einer Verfügbarkeitsgruppe. Diese können die Arbeit hinter einer kleinen Aktualisierung vervielfachen. Prüfen Sie nach einem absichtlichen Abbruch auch, dass erneutes Starten weder Zeilen überspringt noch bereits abgeschlossene fachliche Aktionen doppelt ausführt. Ein überzeugender Fix erklärt den ursprünglichen Blockierer, reduziert den relevanten Ressourcenbedarf und erhält die fachlich erforderliche Transaktionssemantik. Das bloße Verschwinden eines Eskalationsereignisses genügt als Erfolgsnachweis nicht.

Technische Referenzen: Microsoft Learn: Lock escalation · Microsoft Learn: Lock diagnostics.