SH

Sascha Hess

xenosystems.de - IT-Consulting & Data Management

www.xenosystems.de

NOTFALL-KIT – SQL SERVER 2026

SQL Server

Datenbankfundamente

Transaktionssicherheit, Isolation und Konsistenz für produktive SQL-Server-Umgebungen

1

ACID vollständig verstehen

Atomicity, Consistency, Isolation, Durability — mit konkreten SQL-Beispielen

2

10 Transaktions-Fallen

Typische Fehler in produktiven Systemen — mit sofortigen Korrekturen

3

Isolationsstufen im Alltag

Read Committed, Snapshot, Serializable — wann welche Stufe, warum

4

Deadlock-Diagnose & -Prävention

Deadlocks erkennen, analysieren und dauerhaft vermeiden

5

30-Tage-Stabilitätsplan

Von inkonsistenten Daten und Blocking zu robuster Transaktionsarchitektur

WANN ACID-PROBLEME SICHTBAR WERDEN

■  Daten, die "irgendwie nicht stimmen" — ohne erkennbaren Anlass und ohne Fehlermeldung.

■  Berichte, die zu verschiedenen Zeitpunkten verschiedene Ergebnisse liefern — ohne Datenänderung.

■  Gelegentliche Deadlock-Fehler in der Anwendung — die "einfach passieren".

■  Lange Ladezeiten, die andere Benutzer blockieren — PAGELATCH oder LCK_M-Waits.

■  Nach einem Serverabsturz: Unsicherheit darüber, welche Daten wirklich gesichert sind.

-- Ohne Atomicity: zwei getrennte Statements — gefährlich

UPDATE dbo.Lagerbestand SET Menge = Menge - 10 WHERE ArtikelNr = 'A001';

-- ← Server-Absturz HIER: Menge wurde reduziert, Buchung fehlt

INSERT INTO dbo.Lagerbuchungen (ArtikelNr, Menge, BuchungsTyp) VALUES ('A001', -10, 'ABGANG');

-- Mit Atomicity: Transaktion sichert Alles-oder-Nichts

BEGIN TRANSACTION;

    UPDATE dbo.Lagerbestand SET Menge = Menge - 10 WHERE ArtikelNr = 'A001';

    INSERT INTO dbo.Lagerbuchungen (ArtikelNr, Menge, BuchungsTyp) VALUES ('A001', -10, 'ABGANG');

COMMIT TRANSACTION;

-- Server-Absturz zwischen UPDATE und INSERT → beim Neustart: ROLLBACK beider Operationen

-- Kein halbfertiger Zustand, keine Datenbankinkonsistenz

-- Consistency: Constraint-Verletzung macht gesamte Transaktion ungültig

BEGIN TRANSACTION;

    -- Erster INSERT: gültig

    INSERT INTO dbo.Auftraege (AuftragsNr, KundenNr, Betrag)

    VALUES (1001, 42, 5000.00);

    -- Zweiter INSERT: verletzt FK-Constraint (KundenNr 9999 existiert nicht)

    INSERT INTO dbo.Auftragspositionen (AuftragsNr, ArtikelNr, KundenNr, Menge)

    VALUES (1001, 'X001', 9999, 5);

    -- Fehler: FOREIGN KEY constraint violation

    -- → gesamte Transaktion ist ungültig, ROLLBACK beider Inserts

COMMIT TRANSACTION;  -- wird nie erreicht

-- Consistency-Check: welche Constraints gibt es?

SELECT

    t.name                  AS Tabelle,

    c.name                  AS Constraint_Name,

    c.type_desc             AS Constraint_Typ,

    OBJECT_DEFINITION(c.object_id) AS Definition

FROM sys.objects c

JOIN sys.tables t ON c.parent_object_id = t.object_id

WHERE c.type IN ('C','F','UQ','PK')   -- Check, FK, Unique, PK

ORDER BY t.name, c.type_desc;

-- Problem 1: Dirty Read — Lesen uncommitteter Daten

-- Session A: ändert, committed noch nicht

BEGIN TRANSACTION;

UPDATE dbo.Lagerbestand SET Menge = 0 WHERE ArtikelNr = 'A001';

-- ← noch kein COMMIT

-- Session B (Isolationsstufe READ UNCOMMITTED): liest die 0 — obwohl noch nicht final

SELECT Menge FROM dbo.Lagerbestand WHERE ArtikelNr = 'A001';

-- Ergebnis: 0 — falsch! Session A rollt vielleicht noch zurück.

-- Problem 2: Non-Repeatable Read — dieselbe Zeile, verschiedene Werte

-- Session A:

BEGIN TRANSACTION;

SELECT Preis FROM dbo.Artikel WHERE ArtikelNr = 'A001';  -- liest 100,00

-- ← Session B ändert und committed Preis auf 120,00

SELECT Preis FROM dbo.Artikel WHERE ArtikelNr = 'A001';  -- liest jetzt 120,00

-- Dieselbe Abfrage, gleiche Transaktion, verschiedene Ergebnisse

COMMIT;

-- Problem 3: Phantom Read — neue Zeilen erscheinen innerhalb einer Transaktion

-- Session A:

BEGIN TRANSACTION;

SELECT COUNT(*) FROM dbo.Auftraege WHERE KundenNr = 42;  -- ergibt 5

-- ← Session B fügt neuen Auftrag für Kunde 42 ein und committed

SELECT COUNT(*) FROM dbo.Auftraege WHERE KundenNr = 42;  -- ergibt jetzt 6

-- "Phantom"-Zeile ist aufgetaucht

COMMIT;

-- Durability-Diagnostik: ist das Transaction Log gesund?

SELECT

    db.name                             AS Datenbank,

    log_size_mb = ls.cntr_value / 1024.0,

    log_used_mb = lu.cntr_value / 1024.0,

    log_used_pct = ROUND(lu.cntr_value * 100.0 / NULLIF(ls.cntr_value,0), 1),

    -- Zu hoher Log-Verbrauch = Durability-Risiko (Log läuft voll)

    CASE

        WHEN lu.cntr_value * 100.0 / NULLIF(ls.cntr_value,0) > 80 THEN 'KRITISCH'

        WHEN lu.cntr_value * 100.0 / NULLIF(ls.cntr_value,0) > 60 THEN 'WARNUNG'

        ELSE 'OK'

    END                                 AS Status

FROM sys.databases db

JOIN sys.dm_os_performance_counters ls

    ON ls.instance_name = db.name AND ls.counter_name = 'Log File(s) Size (KB)'

JOIN sys.dm_os_performance_counters lu

    ON lu.instance_name = db.name AND lu.counter_name = 'Log File(s) Used Size (KB)'

WHERE db.database_id > 4   -- keine Systemdatenbanken

ORDER BY log_used_pct DESC;

-- Explizite Transaktion: Standard für alle DML-Operationen in Stored Procedures

BEGIN TRANSACTION;                        -- Transaktion starten

    -- DML-Operationen

    UPDATE ...;

    INSERT ...;

    DELETE ...;

COMMIT TRANSACTION;                       -- Permanent auf Disk schreiben

-- Oder bei Fehler:

ROLLBACK TRANSACTION;                     -- Alle Änderungen zurückrollen

-- Transaktion benennen (empfohlen für Klarheit in verschachtelten Szenarien)

BEGIN TRANSACTION Auftrags_Buchung;

    -- ...

COMMIT TRANSACTION Auftrags_Buchung;

-- @@TRANCOUNT: Verschachtelungstiefe der aktuellen Transaktion

-- 0 = keine aktive Transaktion

-- 1 = eine äußere Transaktion

-- 2+ = verschachtelt (nur äußerstes COMMIT wirkt wirklich)

SELECT @@TRANCOUNT AS Transaktionstiefe;

-- Produktionsreifes Template: explizite Steuerung, XACT_STATE(), strukturiertes Logging

CREATE OR ALTER PROCEDURE dbo.SP_Auftrag_Komplett_Buchen

    @AuftragsNr     INT,

    @BenutzerID     NVARCHAR(100)

AS

BEGIN

    SET NOCOUNT ON;

    SET XACT_ABORT OFF;   -- Wir steuern ROLLBACK selbst via TRY/CATCH

    DECLARE @Fehler     NVARCHAR(MAX);

    DECLARE @ProtoID    INT;

    -- Protokoll öffnen

    INSERT INTO dbo.Buchungs_Protokoll (AuftragsNr, Benutzer, Start, Status)

    VALUES (@AuftragsNr, @BenutzerID, SYSUTCDATETIME(), 'LAUFEND');

    SET @ProtoID = SCOPE_IDENTITY();

    BEGIN TRY

        BEGIN TRANSACTION;

            -- Schritt 1: Auftragsstatus prüfen

            IF NOT EXISTS (SELECT 1 FROM dbo.Auftraege

                           WHERE AuftragsNr = @AuftragsNr AND Status = 'FREIGEGEBEN')

                RAISERROR('Auftrag nicht freigegeben oder nicht vorhanden.', 16, 1);

            -- Schritt 2: Lagerabbuchung

            UPDATE dbo.Lagerbestand

            SET Menge = Menge - p.Menge

            FROM dbo.Lagerbestand lb

            JOIN dbo.Auftragspositionen p ON lb.ArtikelNr = p.ArtikelNr

            WHERE p.AuftragsNr = @AuftragsNr;

            -- Schritt 3: Negativen Bestand verhindern

            IF EXISTS (SELECT 1 FROM dbo.Lagerbestand WHERE Menge < 0)

                RAISERROR('Lagerbestand würde negativ — Buchung abgebrochen.', 16, 1);

            -- Schritt 4: Auftragsstatus auf GEBUCHT setzen

            UPDATE dbo.Auftraege

            SET Status = 'GEBUCHT', GebuchtAm = SYSUTCDATETIME(),

                GebuchtVon = @BenutzerID

            WHERE AuftragsNr = @AuftragsNr;

            -- Schritt 5: Audit-Log

            INSERT INTO dbo.AuditLog (Tabelle, Aktion, ObjektID, Benutzer, Zeitpunkt)

            VALUES ('Auftraege', 'BUCHEN', @AuftragsNr, @BenutzerID, SYSUTCDATETIME());

        COMMIT TRANSACTION;

        -- Protokoll schließen: Erfolg

        UPDATE dbo.Buchungs_Protokoll

        SET Ende = SYSUTCDATETIME(), Status = 'ERFOLG'

        WHERE ProtoID = @ProtoID;

    END TRY

    BEGIN CATCH

        -- XACT_STATE():

        --  1 = Transaktion aktiv und committbar → ROLLBACK

        -- -1 = Transaktion aktiv, nicht committbar (Batch-Abbruch) → ROLLBACK

        --  0 = Keine aktive Transaktion → kein ROLLBACK nötig

        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

        SET @Fehler = ERROR_MESSAGE();

        -- Protokoll schließen: Fehler

        UPDATE dbo.Buchungs_Protokoll

        SET Ende = SYSUTCDATETIME(), Status = 'FEHLER', Fehlermeldung = @Fehler

        WHERE ProtoID = @ProtoID;

        -- Fehler nach oben weitergeben

        THROW;

    END CATCH

END

-- Savepoints: Rücksprungpunkte innerhalb einer Transaktion

-- Nützlich wenn einzelne Schritte optional sind, die Haupttransaktion aber weiterlaufen soll

BEGIN TRANSACTION;

    -- Pflichtschritt: muss gelingen

    UPDATE dbo.Auftraege SET Status = 'GEBUCHT' WHERE AuftragsNr = 1001;

    SAVE TRANSACTION Vor_Benachrichtigung;   -- Savepoint setzen

    BEGIN TRY

        -- Optionaler Schritt: E-Mail-Eintrag in Queue

        INSERT INTO dbo.Mail_Queue (Empfaenger, Betreff, Body)

        VALUES ('kunde@example.com', 'Auftragsbestätigung', 'Ihr Auftrag 1001 wurde gebucht.');

    END TRY

    BEGIN CATCH

        -- Nur bis zum Savepoint zurückrollen — Haupttransaktion bleibt aktiv

        ROLLBACK TRANSACTION Vor_Benachrichtigung;

        -- Fehler loggen, aber nicht werfen — Mail war optional

        INSERT INTO dbo.Fehlerprotokoll (Meldung) VALUES (ERROR_MESSAGE());

    END CATCH

    -- Haupttransaktion wird committed — auch wenn Mail-Schritt fehlschlug

    INSERT INTO dbo.Lagerbuchungen (AuftragsNr, Zeitpunkt) VALUES (1001, SYSUTCDATETIME());

COMMIT TRANSACTION;

-- SET XACT_ABORT ON: jeder Laufzeitfehler rollt die gesamte Transaktion zurück

-- Empfohlen wenn kein TRY/CATCH vorhanden ist — z.B. in einfachen Skripten

SET XACT_ABORT ON;

BEGIN TRANSACTION;

    UPDATE dbo.Kunden SET Umsatzklasse = 'A' WHERE KundenNr = 42;

    -- Wenn dieses Statement fehlschlägt → automatisch ROLLBACK der gesamten Transaktion

    UPDATE dbo.Kunden SET Umsatzklasse = 'B' WHERE KundenNr = 43;

COMMIT TRANSACTION;

SET XACT_ABORT OFF;

-- WICHTIG: SET XACT_ABORT ON und TRY/CATCH zusammen brauchen XACT_STATE()-Prüfung

-- weil XACT_ABORT die Transaktion in Zustand -1 (nicht committbar) versetzen kann

-- → immer XACT_STATE() prüfen, niemals blind ROLLBACK aufrufen

-- Anti-Pattern: kein Fehler-Handling

BEGIN TRANSACTION;

UPDATE dbo.Konten SET Saldo = Saldo - 1000 WHERE KontoNr = 1;

UPDATE dbo.Konten SET Saldo = Saldo + 1000 WHERE KontoNr = 2; -- wenn dies scheitert?

COMMIT;  -- wird trotzdem ausgeführt wenn kein Abbruch-Signal

-- Richtig: TRY/CATCH + XACT_STATE()

BEGIN TRY

    BEGIN TRANSACTION;

        UPDATE dbo.Konten SET Saldo = Saldo - 1000 WHERE KontoNr = 1;

        IF @@ROWCOUNT = 0 RAISERROR('Konto 1 nicht gefunden.', 16, 1);

        UPDATE dbo.Konten SET Saldo = Saldo + 1000 WHERE KontoNr = 2;

        IF @@ROWCOUNT = 0 RAISERROR('Konto 2 nicht gefunden.', 16, 1);

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

    THROW;

END CATCH

-- Anti-Pattern: Transaktion umschließt Benutzerabfrage oder langsame Schleife

BEGIN TRANSACTION;

    -- Langsame Vorbereitung innerhalb der Transaktion — hält Sperren

    SELECT @Preis = Preis FROM dbo.Artikel WHERE ArtikelNr = @Art;  -- Sperre!

    WAITFOR DELAY '00:00:30';  -- Simulation: Benutzer denkt nach

    UPDATE dbo.Auftraege SET Preis = @Preis WHERE AuftragsNr = @Nr;

COMMIT;

-- Richtig: Sperre nur während der eigentlichen Schreiboperation

-- Schritt 1: Daten lesen AUSSERHALB der Transaktion

SELECT @Preis = Preis FROM dbo.Artikel WHERE ArtikelNr = @Art;  -- keine Sperre

-- Schritt 2: Benutzer entscheidet (außerhalb der Transaktion)

-- ... Benutzer denkt nach ...

-- Schritt 3: Transaktion nur für den eigentlichen Schreibvorgang

BEGIN TRANSACTION;

    UPDATE dbo.Auftraege SET Preis = @Preis WHERE AuftragsNr = @Nr;

COMMIT;

-- Transaktionsdauer: Millisekunden statt Minuten

-- Implicit Transactions diagnostizieren: offene Transaktionen erkennen

SELECT

    s.session_id,

    s.login_name,

    s.host_name,

    s.program_name,

    s.open_transaction_count,

    s.last_request_start_time,

    DATEDIFF(MINUTE, s.last_request_start_time, GETDATE()) AS Offen_Seit_Min,

    t.text AS Letzter_SQL_Text,

    -- Lange offene Transaktionen sind immer ein Problem

    CASE

        WHEN DATEDIFF(MINUTE, s.last_request_start_time, GETDATE()) > 30

        THEN 'KRITISCH: > 30 Min offen'

        WHEN DATEDIFF(MINUTE, s.last_request_start_time, GETDATE()) > 5

        THEN 'WARNUNG: > 5 Min offen'

        ELSE 'OK'

    END AS Bewertung

FROM sys.dm_exec_sessions s

CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) t

WHERE s.open_transaction_count > 0

  AND s.is_user_process = 1

ORDER BY Offen_Seit_Min DESC;

-- Die verschachtelte Transaktions-Falle

CREATE OR ALTER PROCEDURE dbo.SP_Inner AS

BEGIN

    SET NOCOUNT ON;

    -- @@TRANCOUNT ist bereits 1 (äußere Transaktion)

    BEGIN TRANSACTION;  -- @@TRANCOUNT wird 2

        UPDATE dbo.Tabelle SET Wert = 'X' WHERE ID = 1;

    -- Fehler tritt auf:

    ROLLBACK TRANSACTION;  -- rollt ALLES zurück, @@TRANCOUNT → 0 !

    -- Wenn die äußere Procedure jetzt COMMIT aufruft → Fehler: keine aktive Transaktion

END;

-- Richtig: innere Procedure wirft Fehler, äußere entscheidet

CREATE OR ALTER PROCEDURE dbo.SP_Inner_Korrekt AS

BEGIN

    SET NOCOUNT ON;

    BEGIN TRY

        -- kein eigenes BEGIN TRANSACTION in verschachtelter Procedure

        UPDATE dbo.Tabelle SET Wert = 'X' WHERE ID = 1;

        IF @@ROWCOUNT = 0 RAISERROR('Kein Datensatz gefunden.', 16, 1);

    END TRY

    BEGIN CATCH

        THROW;  -- Fehler nach oben — äußere Procedure entscheidet über ROLLBACK

    END CATCH

END;

-- Diagnose: DML-Operationen mit Table Scans (potenzielle Blocking-Quellen)

SELECT TOP 10

    qs.total_elapsed_time / qs.execution_count / 1000  AS avg_ms,

    qs.execution_count,

    qs.total_logical_reads / qs.execution_count        AS avg_reads,

    SUBSTRING(st.text, 1, 300)                         AS QueryText,

    qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE st.text LIKE '%UPDATE%'

   OR st.text LIKE '%DELETE%'

ORDER BY qs.total_logical_reads DESC;

-- Anti-Pattern: NOLOCK auf Finanzdaten

SELECT SUM(Nettobetrag) AS Tagesumsatz

FROM dbo.Auftraege WITH (NOLOCK)   -- liest möglicherweise zurückgerollte Transaktionen!

WHERE CAST(Auftragsdatum AS DATE) = CAST(GETDATE() AS DATE);

-- Lösung 1: NOLOCK entfernen (funktioniert mit RCSI, Kapitel 8)

SELECT SUM(Nettobetrag) AS Tagesumsatz

FROM dbo.Auftraege

WHERE CAST(Auftragsdatum AS DATE) = CAST(GETDATE() AS DATE);

-- Lösung 2: Explizit Snapshot-Isolation für Lesekonsistenz

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SELECT SUM(Nettobetrag) AS Tagesumsatz

FROM dbo.Auftraege

WHERE CAST(Auftragsdatum AS DATE) = CAST(GETDATE() AS DATE);

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Log-Truncation verhindernde Faktoren identifizieren

SELECT

    db.name                 AS Datenbank,

    db.log_reuse_wait_desc  AS Warte_Auf,

    -- Was verhindert Log-Truncation?

    CASE db.log_reuse_wait_desc

        WHEN 'LOG_BACKUP'          THEN 'Log-Backup fehlt — sofort Log-Backup ausführen!'

        WHEN 'ACTIVE_TRANSACTION'  THEN 'Lange offene Transaktion — siehe sys.dm_exec_sessions'

        WHEN 'REPLICATION'         THEN 'Replikation liest Log — Replikations-Agent prüfen'

        WHEN 'DATABASE_MIRRORING'  THEN 'Spiegelung hängt — Mirror-Status prüfen'

        WHEN 'AVAILABILITY_REPLICA' THEN 'Always On Replica hängt — AG-Status prüfen'

        WHEN 'NOTHING'             THEN 'OK — Log kann beim nächsten Backup abgeschnitten werden'

        ELSE                            'Unbekannt — Microsoft Docs prüfen'

    END                     AS Massnahme

FROM sys.databases db

WHERE db.database_id > 4

ORDER BY db.log_reuse_wait_desc;

-- Anti-Pattern: alles auf einmal

DELETE FROM dbo.Protokoll WHERE Datum < '2023-01-01';  -- 10 Millionen Zeilen, Stunden, Log-Explosion

-- Richtig: Batch-Delete mit kontrollierter Transaktionsgröße

DECLARE @Batch INT = 5000;

DECLARE @Geloescht INT = 1;

WHILE @Geloescht > 0

BEGIN

    DELETE TOP (@Batch) FROM dbo.Protokoll

    WHERE Datum < '2023-01-01';

    SET @Geloescht = @@ROWCOUNT;

    -- Kurze Pause: andere Transaktionen kommen durch

    IF @Geloescht = @Batch WAITFOR DELAY '00:00:01';

END

-- Laufzeit: identisch, aber keine Log-Explosion, kein Dauerblocken

READ UNCOMMITTED

Möglich

Möglich

Möglich

Maximal

Nie für Finanzdaten

READ COMMITTED

Verhindert

Möglich

Möglich

Hoch

Standard — aber mit RCSI

REPEATABLE READ

Verhindert

Verhindert

Möglich

Mittel

Selten nötig

SNAPSHOT

Verhindert

Verhindert

Verhindert

Hoch

Empfohlen für Leseanfragen

SERIALIZABLE

Verhindert

Verhindert

Verhindert

Niedrig

Nur für kritische Operationen

-- READ UNCOMMITTED: nur für "Ungefähr-Zahlen" ohne Genauigkeitsanspruch

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT COUNT(*) AS Ungefaehre_Zeilenanzahl FROM dbo.Riesige_Tabelle;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- REPEATABLE READ: wenn innerhalb einer Transaktion dieselbe Zeile

-- mehrfach gelesen werden muss und immer denselben Wert haben soll

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

    SELECT @Saldo = Saldo FROM dbo.Konten WHERE KontoNr = 1;

    -- ... komplexe Berechnung ...

    SELECT @Saldo_Check = Saldo FROM dbo.Konten WHERE KontoNr = 1;

    -- Garantiert: @Saldo = @Saldo_Check — niemand hat geändert

    IF @Saldo <> @Saldo_Check RAISERROR('Concurrent modification detected.', 16, 1);

COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- SERIALIZABLE: wenn die Ergebnismenge einer Abfrage stabil bleiben muss

-- z.B. Jahresabschluss: keine neuen Buchungen während der Verarbeitung

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

    SELECT SUM(Betrag) FROM dbo.Buchungen WHERE Jahr = 2025;

    -- Garantiert: keine neue Zeile mit Jahr = 2025 kann entstehen

    -- während diese Transaktion aktiv ist (Range Lock auf Jahr = 2025)

COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- SNAPSHOT: konsistente Lesansicht ohne Sperren (benötigt RCSI oder SI — Kapitel 8)

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

    -- Liest den Zustand der Datenbank zum Transaktionsstartpunkt

    -- Keine Sperren auf gelesene Daten → kein Blocking durch Schreiber

    SELECT * FROM dm.Fact_Auftraege WHERE Jahr = 2025;

COMMIT;

-- Welche Isolationsstufen werden aktuell genutzt?

SELECT

    s.session_id,

    s.login_name,

    s.program_name,

    CASE s.transaction_isolation_level

        WHEN 0 THEN 'UNSPECIFIED'

        WHEN 1 THEN 'READ UNCOMMITTED'

        WHEN 2 THEN 'READ COMMITTED'

        WHEN 3 THEN 'REPEATABLE READ'

        WHEN 4 THEN 'SERIALIZABLE'

        WHEN 5 THEN 'SNAPSHOT'

    END                             AS Isolationsstufe,

    s.open_transaction_count,

    s.last_request_start_time

FROM sys.dm_exec_sessions s

WHERE s.is_user_process = 1

  AND s.transaction_isolation_level = 1   -- READ UNCOMMITTED filtern

ORDER BY s.login_name;

-- Viele READ UNCOMMITTED Sessions → NOLOCK-Problem (Kapitel 4, Punkt 6)

Shared

S

Lesen — andere dürfen auch lesen

S, IS, U

Update

U

Lesen mit Absicht zu ändern

S, IS

Exclusive

X

Schreiben — exklusiv

Nichts

Intent Shared

IS

Zeigt an: Shared Lock auf tieferer Ebene

S, IS, IX, U

Intent Exclusive

IX

Zeigt an: Exclusive Lock auf tieferer Ebene

IS, IX

Schema

Sch

Schemaänderungen

Nichts

-- Blocking-Überblick: wer blockiert wen?

SELECT

    blocking.session_id             AS Blocker_Session,

    blocking.login_name             AS Blocker_Login,

    blocking.host_name              AS Blocker_Host,

    blocked.session_id              AS Geblockt_Session,

    blocked.login_name              AS Geblockt_Login,

    DATEDIFF(SECOND,

        blocked.last_request_start_time,

        GETDATE())                  AS Geblockt_Seit_Sek,

    wt.wait_type                    AS Warte_Typ,

    wt.wait_duration_ms             AS Wartezeit_ms,

    SUBSTRING(bt.text, 1, 200)      AS Blocker_SQL,

    SUBSTRING(wt_text.text, 1, 200) AS Geblockt_SQL

FROM sys.dm_exec_sessions blocked

JOIN sys.dm_exec_requests r ON blocked.session_id = r.session_id

JOIN sys.dm_exec_sessions blocking ON r.blocking_session_id = blocking.session_id

JOIN sys.dm_os_waiting_tasks wt ON blocked.session_id = wt.session_id

CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) bt

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) wt_text

WHERE r.blocking_session_id > 0

ORDER BY Geblockt_Seit_Sek DESC;

-- Historisches Blocking-Pattern: welche Tabellen und Objekte sind am häufigsten betroffen?

SELECT

    wt.wait_type,

    COUNT(*)                        AS Haeufigkeit,

    AVG(wt.wait_duration_ms)        AS Avg_Wartezeit_ms,

    MAX(wt.wait_duration_ms)        AS Max_Wartezeit_ms,

    SUM(wt.wait_duration_ms)        AS Gesamt_Wartezeit_ms

FROM sys.dm_os_wait_stats wt

WHERE wt.wait_type LIKE 'LCK_%'     -- nur Lock-Waits

  AND wt.waiting_tasks_count > 0

ORDER BY Gesamt_Wartezeit_ms DESC;

-- Lock Escalation für einzelne Tabellen deaktivieren (bei großen Batch-Operationen)

-- Vorsicht: kann zu erhöhtem Lock-Speicherverbrauch führen

ALTER TABLE dbo.Lagerbuchungen SET (LOCK_ESCALATION = DISABLE);

-- Alternativ: Lock Escalation auf Partition-Ebene begrenzen (bei partitionierten Tabellen)

ALTER TABLE dbo.Lagerbuchungen SET (LOCK_ESCALATION = AUTO);

-- Lock Escalation Events überwachen

SELECT

    OBJECT_NAME(object_id)          AS Tabelle,

    index_id,

    lock_escalation_desc            AS Eskalations_Einstellung

FROM sys.tables

WHERE lock_escalation_desc <> 'TABLE'   -- non-default Einstellungen

ORDER BY OBJECT_NAME(object_id);

Session A hält Sperre auf Tabelle Kunden,

         wartet auf Sperre auf Tabelle Auftraege.

Session B hält Sperre auf Tabelle Auftraege,

         wartet auf Sperre auf Tabelle Kunden.

→ Deadlock. SQL Server wählt Opfer → Rollback → Fehler 1205.

-- Extended Events: Deadlock-Graph dauerhaft aufzeichnen

CREATE EVENT SESSION Deadlock_Capture ON SERVER

ADD EVENT sqlserver.xml_deadlock_report

ADD TARGET package0.ring_buffer

    (SET max_memory = 51200)

WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

ALTER EVENT SESSION Deadlock_Capture ON SERVER STATE = START;

-- Deadlock-Graphen aus dem Ring Buffer lesen

SELECT

    xdr.value('@timestamp', 'datetime2')            AS Zeitpunkt,

    xdr.query('.')                                  AS Deadlock_Graph_XML

FROM (

    SELECT CAST(target_data AS XML) AS target_data

    FROM sys.dm_xe_session_targets t

    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address

    WHERE s.name = 'Deadlock_Capture'

      AND t.target_name = 'ring_buffer'

) AS data

CROSS APPLY target_data.nodes('//RingBufferTarget/event') AS XEventData(xdr)

ORDER BY Zeitpunkt DESC;

-- Alternativ: Trace Flag 1222 — ausführlichere Deadlock-Informationen im Error Log

DBCC TRACEON (1222, -1);   -- global aktivieren

-- Im SQL Server Error Log: EXEC xp_readerrorlog 0, 1, 'deadlock';

-- Strategie 1: konsistente Zugriffsreihenfolge in ALLEN Prozeduren

-- Wenn jede Transaktion immer zuerst Kunden, dann Auftraege sperrt — kein Deadlock möglich

-- FALSCH: Session A: Kunden → Auftraege; Session B: Auftraege → Kunden

-- RICHTIG: beide immer in derselben Reihenfolge

CREATE OR ALTER PROCEDURE dbo.SP_Transfer_Korrekt

    @VonKunde INT, @NachKunde INT, @Betrag DECIMAL(18,2)

AS

BEGIN

    SET NOCOUNT ON;

    -- Immer kleinere ID zuerst sperren → konsistente Reihenfolge

    DECLARE @ErsteID INT  = MIN(@VonKunde, @NachKunde);

    DECLARE @ZweiteID INT = MAX(@VonKunde, @NachKunde);

    BEGIN TRANSACTION;

    BEGIN TRY

        UPDATE dbo.Kunden SET Guthaben = Guthaben - @Betrag

        WHERE KundenNr = @ErsteID;   -- immer kleinere ID zuerst

        UPDATE dbo.Kunden SET Guthaben = Guthaben + @Betrag

        WHERE KundenNr = @ZweiteID;

        COMMIT;

    END TRY

    BEGIN CATCH

        IF XACT_STATE() <> 0 ROLLBACK;

        THROW;

    END CATCH

END

-- Strategie 2: Zeilenversion statt Sperren (RCSI — Kapitel 8)

-- Lesende Transaktionen sperren nichts mehr → Deadlock-Klasse "Leser vs. Schreiber" entfällt

-- Strategie 3: Retry-Logik in der Anwendung für Deadlock-Opfer (Fehler 1205)

-- Deadlocks sind nie vollständig eliminierbar — Anwendung muss damit umgehen können

-- Strategie 4: Sperr-Timeout als Sicherheitsnetz

SET LOCK_TIMEOUT 5000;   -- nach 5 Sekunden Wartezeit → Fehler statt unendliches Warten

-- Gibt Fehler 1222 zurück — Anwendung muss reagieren

-- RCSI aktivieren (einmalige Operation, erfordert kurze Exklusivität)

-- Schritt 1: Andere Verbindungen trennen

-- Schritt 2: RCSI aktivieren

ALTER DATABASE ERP_Produktiv SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- Schritt 3: Prüfen ob aktiviert

SELECT

    name,

    is_read_committed_snapshot_on,

    snapshot_isolation_state_desc

FROM sys.databases

WHERE name = 'ERP_Produktiv';

-- Kosten: tempdb wird für Zeilenversionen genutzt — tempdb muss auf SSD liegen!

-- Monitoring: Versionsspeicher-Größe im Auge behalten

SELECT

    reserved_mb    = SUM(version_store_reserved_page_count) * 8.0 / 1024,

    used_mb        = SUM(version_store_used_page_count) * 8.0 / 1024

FROM sys.dm_db_file_space_usage

WHERE database_id = 2;  -- tempdb

-- RCSI: READ COMMITTED verhält sich wie Snapshot — automatisch, keine Code-Änderung

-- Snapshot Isolation: explizite Transaktion mit konsistentem Startpunkt

-- Snapshot Isolation aktivieren (unabhängig von RCSI)

ALTER DATABASE ERP_Produktiv SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Snapshot-Transaktion: liest immer den Zustand zum Transaktionsstartpunkt

-- Auch wenn andere Transaktionen zwischendurch committen

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

    -- Zeitpunkt T1: Bestand = 100

    SELECT Bestand FROM dbo.Lagerbestand WHERE ArtikelNr = 'A001';

    -- Andere Transaktion committed: Bestand = 80

    -- Snapshot liest immer noch T1-Zustand: Bestand = 100

    SELECT Bestand FROM dbo.Lagerbestand WHERE ArtikelNr = 'A001';

    -- Ergebnis: 100 (nicht 80!) — konsistente Sicht zum Transaktionsstartpunkt

COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Wann RCSI, wann Snapshot Isolation?

-- RCSI:     für alle normalen READ COMMITTED Leseanfragen — Power BI, ERP-Masken

-- Snapshot: für Transaktionen die eine konsistente Sicht über mehrere Statements brauchen

--           z.B. Berichtsextraktion, Jahresabschluss, lange Analyse-Transaktionen

-- Snapshot Isolation erkennt Update-Konflikte: wenn zwei Transaktionen

-- dieselbe Zeile ändern wollen, gewinnt die erste — die zweite bekommt Fehler 3960

-- Dies ist optimistic concurrency: kein Warten, stattdessen Fehler + Retry

-- Retry-Logik in Stored Procedure für Snapshot Isolation

CREATE OR ALTER PROCEDURE dbo.SP_Lager_Update_Mit_Retry

    @ArtikelNr  NVARCHAR(20),

    @Menge      DECIMAL(18,4)

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @Versuche INT = 0;

    DECLARE @MaxVersuche INT = 3;

    WHILE @Versuche < @MaxVersuche

    BEGIN

        BEGIN TRY

            SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

            BEGIN TRANSACTION;

                UPDATE dbo.Lagerbestand

                SET Menge = Menge - @Menge

                WHERE ArtikelNr = @ArtikelNr AND Menge >= @Menge;

                IF @@ROWCOUNT = 0

                    RAISERROR('Kein ausreichender Bestand.', 16, 1);

            COMMIT TRANSACTION;

            SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

            RETURN 0;   -- Erfolg

        END TRY

        BEGIN CATCH

            IF XACT_STATE() <> 0 ROLLBACK;

            SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

            -- Fehler 3960: Snapshot-Update-Konflikt → Retry

            IF ERROR_NUMBER() = 3960 AND @Versuche < @MaxVersuche - 1

            BEGIN

                SET @Versuche += 1;

                WAITFOR DELAY '00:00:00.100';   -- 100ms warten vor Retry

            END

            ELSE

                THROW;   -- anderer Fehler oder max. Versuche → nach oben

        END CATCH

    END

END

-- ERP-Transaktions-Diagnostik: welche ERP-Prozesse verursachen Blocking?

SELECT

    r.session_id,

    r.blocking_session_id,

    s.program_name                          AS ERP_Prozess,

    s.host_name,

    r.wait_type,

    r.wait_time / 1000.0                    AS Wartezeit_Sek,

    r.total_elapsed_time / 1000.0           AS Laufzeit_Sek,

    SUBSTRING(t.text, 1, 300)               AS Aktuelles_Statement,

    -- Transaktionsgröße schätzen

    r.writes * 8.0 / 1024                   AS Schreibmenge_MB,

    r.logical_reads / 1000.0               AS Lesemenge_K_Pages

FROM sys.dm_exec_requests r

JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

WHERE r.blocking_session_id > 0

  AND s.is_user_process = 1

ORDER BY r.wait_time DESC;

-- Typische ERP-Blocking-Quellen und Gegenmaßnahmen:

-- 1. Lohnlauf (SAP PC00, Dynamics Payroll): Wartungsfenster einrichten

-- 2. Monatsabschluss: separaten Datenbankbenutzer ohne Concurrent-User-Zugriff

-- 3. MRP/Bedarfsplanung: Read Committed Snapshot aktivieren (Kapitel 8)

-- 4. Massenimporte (EDI, CSV): Batch-Größe begrenzen, außerhalb Kernzeiten

-- Transaktionen über Connection-Pooling-Grenzen hinweg diagnostizieren

-- Verbindungen mit ungewöhnlich langen Transaktionszeiten

SELECT

    s.session_id,

    s.login_name,

    s.program_name,

    s.open_transaction_count,

    DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) AS Trans_Dauer_Sek,

    at.transaction_type,

    at.transaction_state,

    -- Transaktion die länger als 5 Minuten offen ist: fast immer ein Bug

    CASE WHEN DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) > 300

         THEN 'KRITISCH: > 5 Min'

         WHEN DATEDIFF(SECOND, at.transaction_begin_time, GETDATE()) > 60

         THEN 'WARNUNG: > 1 Min'

         ELSE 'OK'

    END AS Bewertung

FROM sys.dm_exec_sessions s

JOIN sys.dm_tran_session_transactions st ON s.session_id = st.session_id

JOIN sys.dm_tran_active_transactions at ON st.transaction_id = at.transaction_id

WHERE s.is_user_process = 1

  AND at.transaction_state = 2   -- aktive Transaktionen

ORDER BY Trans_Dauer_Sek DESC;

-- Retry-fähige Stored Procedure: Beispiel für kritische Buchungen

CREATE OR ALTER PROCEDURE dbo.SP_Buchung_Mit_Retry

    @BuchungsID     INT,

    @MaxVersuche    INT = 3

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @Versuch INT = 1;

    DECLARE @Erfolg  BIT = 0;

    WHILE @Versuch <= @MaxVersuche AND @Erfolg = 0

    BEGIN

        BEGIN TRY

            -- Eigentliche Buchungslogik

            EXEC dbo.SP_Buchung_Intern @BuchungsID;

            SET @Erfolg = 1;

        END TRY

        BEGIN CATCH

            IF XACT_STATE() <> 0 ROLLBACK;

            DECLARE @FehlerNr INT = ERROR_NUMBER();

            -- Retry-fähige Fehler

            IF @FehlerNr IN (

                1205,   -- Deadlock-Opfer

                1222,   -- Lock-Timeout

                3960,   -- Snapshot-Update-Konflikt

                40001,  -- Azure SQL Deadlock

                40613   -- Azure SQL Datenbank nicht verfügbar

            ) AND @Versuch < @MaxVersuche

            BEGIN

                -- Exponentielles Backoff: 100ms, 200ms, 400ms

                DECLARE @Wartezeit NVARCHAR(12) =

                    '00:00:00.' + RIGHT('000' + CAST(@Versuch * 100 AS VARCHAR), 3);

                WAITFOR DELAY @Wartezeit;

                SET @Versuch += 1;

            END

            ELSE

            BEGIN

                -- Nicht retry-fähig oder Max-Versuche erreicht → weiterwerfen

                INSERT INTO dbo.Fehlerprotokoll

                    (Prozedur, FehlerNr, Meldung, Versuch, Zeitpunkt)

                VALUES ('SP_Buchung_Mit_Retry', @FehlerNr,

                        ERROR_MESSAGE(), @Versuch, SYSUTCDATETIME());

                THROW;

            END

        END CATCH

    END

END

VOR DEM START

Führen Sie die Diagnose-Abfragen aus den Kapiteln 3.1 (offene Transaktionen), 6.2 (aktuelles Blocking) und 4.8 (Log-Füllstand) als Baseline aus und dokumentieren Sie die Ergebnisse. Erstellen Sie ein vollständiges Backup. Kommunizieren Sie geplante Änderungen an Isolationsstufen und Datenbankeinstellungen an alle Anwendungsbetreiber — diese Änderungen können Anwendungsverhalten beeinflussen.

ERGEBNIS NACH 30 TAGEN

Ihr Ergebnis nach 30 Tagen: RCSI aktiviert und Power BI greift nie mehr blockierend auf ERP-Daten zu, alle kritischen Stored Procedures haben TRY/CATCH und XACT_STATE()-basierten ROLLBACK, Deadlocks werden aufgezeichnet und analysiert, ein automatisches Monitoring meldet Blocking und Log-Probleme bevor Benutzer es merken — und das Transaction Log läuft nicht mehr voll.

SQL Server DB Health Check

Professioneller Audit Ihres SQL Servers — Performance, Transaktionsmuster, Blocking-Analyse, Isolationsstufen und Backup-Strategie. Scope: 3-5 Tage.

Transaktions-Architektur-Review

Analyse bestehender Stored Procedures und Transaktionsmuster — Blocking-Ursachen, Deadlock-Analyse, RCSI-Readiness, Refactoring-Plan.

SQL Server Performance-Tuning

Gezielte Optimierung von Abfragen, Indizes, Isolationsstufen und Transaktionsarchitektur in produktiven Umgebungen.

ERP-Einführungsberatung

Begleitung von ERP-Projekten (MACH, Dynamics NAV, APplus) — Datenbankdesign, Transaktionskonzept, Performance-Architektur, Go-Live-Support.

Interim IT-Management

Übernahme der IT-Steuerung auf Zeit — Budgetplanung, Dienstleister-Management, strategische IT-Ausrichtung.