Wie verhindert man SQL-Sperrprobleme (Blocking und Deadlocks)?

Eingefrorene Bildschirme beim Monatsabschluss, eine Anwendung, die beim Speichern einer Rechnung nicht mehr reagiert, die Fehlermeldung „Datensatz von einem anderen Benutzer gesperrt“ … Hinter den meisten dieser Symptome stecken Sperrprobleme (Locking).

Trennen wir zunächst zwei Begriffe, denn ihre Lösungen unterscheiden sich.

Blocking und Deadlock sind nicht dasselbe

Blocking (Sperrwartezeit): Ein Prozess wartet auf eine Sperre, die ein anderer Prozess freigibt. Das ist normal — so stellt SQL Server die Datenkonsistenz sicher. Problematisch wird es, wenn die Wartezeit lang wird und sich kettenartig aufbaut. Sobald der erste Prozess fertig ist, läuft der wartende Prozess weiter.

Deadlock (tödliche Verklemmung): Zwei Prozesse warten gegenseitig auf die Sperre des jeweils anderen, und keiner kommt weiter. SQL Server erkennt diese Situation und wählt eine Seite als „Opfer“ aus, das abgebrochen wird (Fehler 1205). Das heißt, ein Deadlock löst sich von selbst, aber eine Transaktion schlägt fehl.

Die meisten Nutzerbeschwerden betreffen eigentlich Blocking; Deadlocks sind seltener, aber auffälliger.

Den aktuellen Status sehen

Um zu sehen, wer gerade wen blockiert:

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

Für Deadlocks speichert die system_health-Sitzung in Extended Events standardmäßig die letzten Deadlock-Grafiken — eine rückwirkende Analyse ist also auch nach dem Vorfall noch möglich.

Wege, Sperrprobleme zu vermeiden

1. Transaktionen kurz halten

Der häufigste Fehler ist, bei offener Transaktion auf Benutzereingaben zu warten oder lange Berechnungen durchzuführen. Eine Transaktion sollte nur den Moment des Schreibens in die Datenbank umfassen; Geschäftslogik und Benutzerinteraktion gehören nicht hinein.

2. Immer in derselben Reihenfolge auf Objekte zugreifen

Die klassische Ursache für Deadlocks ist, dass zwei verschiedene Codeteile dieselben zwei Tabellen in umgekehrter Reihenfolge ansprechen. Eine konsistente Zugriffsreihenfolge in der gesamten Anwendung beseitigt einen erheblichen Teil der Deadlocks.

3. Die richtigen Indizes anlegen

Dies ist der am meisten übersehene, aber wirksamste Punkt. Fehlt ein passender Index, durchsucht SQL Server die gesamte Tabelle und sperrt weit mehr Zeilen als nötig. Ein zusätzlicher Index verkleinert den gesperrten Bereich und reduziert Blocking direkt.

4. READ COMMITTED SNAPSHOT in Betracht ziehen

ALTER DATABASE [Datenbankname] SET READ_COMMITTED_SNAPSHOT ON;

Mit dieser Einstellung lesen Leseoperationen eine Zeilenversion der Daten, statt auf schreibende Prozesse zu warten. Leser-Schreiber-Konflikte entfallen weitgehend. Der Preis ist zusätzliche Last auf tempdb; außerdem kann sich das Anwendungsverhalten ändern, daher unbedingt zuerst in einer Testumgebung prüfen. Nutzen Sie eine Standard-ERP-Lösung, stellen Sie sicher, dass der Hersteller diese Einstellung unterstützt.

5. NOLOCK ist keine Lösung

WITH (NOLOCK) wartet nicht auf Sperren, kann aber noch nicht bestätigte (dirty) Daten lesen, dieselbe Zeile zweimal oder gar nicht lesen. Das birgt das Risiko falscher Zahlen in einem Finanzbericht. Es löst das Sperrproblem nicht, sondern macht es nur unsichtbar. Überall dort, wo Konsistenz wichtig ist, sollte es vermieden werden.

6. Reporting von der Transaktionslast trennen

Umfangreiche Berichte erzeugen langlebige gemeinsame Sperren in der Live-Datenbank. Eine Nur-Lese-Replik oder eine separate Reporting-/BI-Datenbank beseitigt eine wesentliche Quelle von Sperrproblemen.

7. Massenoperationen aufteilen

Ein einzelnes UPDATE über Millionen von Zeilen kann eine Lock-Eskalation auslösen und die gesamte Tabelle sperren. Dieselbe Arbeit in Paketen zu erledigen, hält sowohl die Sperrdauer als auch das Log-Wachstum unter Kontrolle.

8. Wiederholungslogik (Retry) in der Anwendung einbauen

Deadlocks auf null zu reduzieren ist in den meisten Systemen unrealistisch. Wird eine Transaktion beim Auftreten von Fehler 1205 nach einer kurzen Wartezeit automatisch wiederholt, löst sich das Problem, ohne dass der Benutzer je einen Fehler sieht.

Ohne Monitoring nicht steuerbar

Sperrprobleme häufen sich meist zu bestimmten Zeiten und bei bestimmten Transaktionen. Warnmeldungen für lang andauerndes Blocking, die regelmäßige Prüfung der Deadlock-Grafiken und das Protokollieren der häufigsten Muster helfen, das Problem zu erkennen, bevor es zur Beschwerde wird.

ÇAP Teknoloji führt Sperranalysen in SQL-Server-Umgebungen durch, wertet Deadlock-Grafiken aus und schlägt dauerhafte Korrekturen auf Anwendungs- und Indexebene vor. Bei Einfrierungen während Ihrer Abschlussperioden können Sie uns gerne kontaktieren.

Kontaktieren Sie uns, um mehr über unsere SQL-Beratung zu erfahren oder ein kostenloses Angebot anzufordern und die Sperr- und Leistungsprobleme Ihrer Datenbank dauerhaft zu lösen.