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.
Vorgehen, wenn eine Beschwerde eingeht
Wenn Anwender einen eingefrorenen Bildschirm melden, sollten Sie nicht vorschnell Sitzungen beenden. Mit der folgenden Reihenfolge lösen Sie das Problem und finden zugleich die eigentliche Ursache.
- Klären Sie, ob es sich um Blocking oder einen Deadlock handelt. Wartende Sitzungen deuten auf Blocking hin, Fehler 1205 auf einen Deadlock.
- Finden Sie die Sitzung am Anfang der Kette. Sie wartet auf niemanden, aber andere warten auf sie.
- Halten Sie fest, zu welcher Anwendung, welchem Benutzer und welcher Abfrage diese Sitzung gehört. Sie benötigen diese Angaben später für die Ursachenanalyse.
- Wirkt die Sitzung inaktiv, hält aber eine offene Transaktion, wurde in der Anwendung möglicherweise eine Transaktion nicht geschlossen.
- Entscheiden Sie erst zuletzt über das Beenden. KILL setzt die Transaktion zurück, und bei großen Transaktionen kann auch das Zurücksetzen Zeit brauchen.
Worauf Sie im Deadlock-Graphen achten sollten
Ein Deadlock-Graph wirkt zunächst kompliziert. Drei Fragen genügen jedoch:
- Welche Prozesse sind kollidiert? Der Graph enthält die Abfrage oder Prozedur, die jeder Prozess ausgeführt hat.
- Auf welcher Ressource? Tabelle, Index und Sperrtyp zeigen, wo sich das Problem konzentriert.
- Wer wurde als Opfer gewählt? Der abgebrochene Prozess ist die Quelle des Fehlers, den der Anwender sieht.
Tauchen immer wieder dieselben zwei Prozeduren auf, liegt die Lösung sehr wahrscheinlich in der Zugriffsreihenfolge oder in einem fehlenden Index. Werten Sie Graphen nicht einzeln aus, sondern gruppiert nach wiederkehrenden Mustern.
Für Blocking können Sie die Option blocked process threshold setzen. Wartezeiten oberhalb des definierten Werts werden dann als Bericht aufgezeichnet und lassen sich später auswerten.
Checkliste für das Entwicklungsteam
Die meisten Sperrprobleme lassen sich schon beim Schreiben des Codes vermeiden. In Code-Reviews können Sie diese Fragen stellen:
- Enthält die Transaktion einen externen Serviceaufruf oder eine Dateioperation?
- Wird die Transaktion im Fehlerfall in jedem Fall geschlossen?
- Sind die Filterspalten in Update-Anweisungen indiziert?
- Greift neuer Code in derselben Reihenfolge auf Tabellen zu wie der bestehende Code?
Für diese Prüfung mit Daten aus Ihrer Produktivumgebung können Sie im Rahmen einer SQL-Beratung Unterstützung erhalten.
Häufig gestellte Fragen
Ist es sicher, eine blockierende Sitzung mit KILL zu beenden?
KILL beendet die Sitzung und setzt die offene Transaktion zurück, die Datenkonsistenz bleibt also erhalten. Die Arbeit des Anwenders geht jedoch verloren, und bei großen Transaktionen kann das Zurücksetzen lange dauern. Die Sperren bleiben währenddessen bestehen. Halten Sie vorher fest, was die Sitzung ausgeführt hat, um Wiederholungen zu vermeiden.
Löst ein Lock-Timeout das Problem?
SET LOCK_TIMEOUT legt fest, wie lange eine Abfrage höchstens auf eine Sperre wartet. Nach Ablauf der Zeit erhält die Abfrage einen Fehler. Die Einstellung beseitigt nicht die Ursache des Wartens, sie verhindert nur unbegrenztes Warten. Die Anwendung muss den Fehler abfangen und eine verständliche Meldung anzeigen oder den Vorgang wiederholen.
Können wir bestimmen, welcher Prozess bei einem Deadlock abbricht?
Ja. Standardmäßig wählt SQL Server den Prozess als Opfer, dessen Zurücksetzen am wenigsten aufwendig ist. Mit SET DEADLOCK_PRIORITY können Sie einer Sitzung eine niedrige oder hohe Priorität geben. Erhält etwa eine Berichtsabfrage im Hintergrund eine niedrige Priorität, sinkt die Wahrscheinlichkeit, dass kritische Vorgänge wie das Speichern einer Rechnung abgebrochen werden.
Verringert bessere Server-Hardware die Sperrprobleme?
Teilweise. Schnellere Datenträger und mehr Arbeitsspeicher lassen Abfragen früher enden, sodass Sperren kürzer gehalten werden. Designbedingte Probleme wie lange offene Transaktionen, fehlende Indizes oder eine uneinheitliche Zugriffsreihenfolge löst Hardware jedoch nicht. Wenn Sie zuerst die Ursache ermitteln, vermeiden Sie unnötige Investitionen.
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.