Beheben von Datenbankdeadlocks oder Datenintegritätsproblemen

Komplexe Datenbankvorgänge – insbesondere solche, die Transaktionen betreffen – können zu Deadlocks oder Dateninkonsistenzen führen, die schwer zu debuggen sind.

Hier kann Copilot Chat helfen, Stellen in einer Transaktion zu ermitteln, an denen Sperren oder Deadlocks auftreten können, und Best Practices für die Transaktionsisolation oder die Auflösung von Deadlocks vorschlagen wie z. B. das Anpassen von Sperrstrategien oder das ordnungsgemäße Behandeln von Deadlockausnahmen.

Vermeiden gleichzeitiger Updates in voneinander abhängigen Zeilen

Wenn zwei oder mehr Transaktionen in unterschiedlicher Reihenfolge versuchen, dieselben Zeilen in einer Datenbanktabelle zu aktualisieren, kann dies zu einer zirkulären wait-Bedingung führen.

Beispielszenario

Der folgende SQL-Codeausschnitt aktualisiert eine Zeile einer Tabelle, führt dann einen Vorgang aus, der mehrere Sekunden dauert, und aktualisiert dann eine weitere Zeile in derselben Tabelle. Dies ist problematisch, weil die Transaktion die id = 1-Zeile mehrere Sekunden lang sperrt, bis dies Transaktion abgeschlossen wird, wodurch die Sperre gelöst wird. Wenn während dieser Zeit eine andere Transaktion beginnt, die einen ähnlichen Vorgang ausführt, die id = 2-Zeile jedoch zuerst sperrt und aktualisiert und dann versucht, die id = 1-Zeile zu sperren, werden beide Transaktionen auf den Abschluss der jeweils anderen warten, wodurch ein Deadlock entsteht.

BEGIN TRANSACTION;
UPDATE my_table SET value = 'Some value' WHERE id = 301;
-- Simulate a process taking 5 seconds:
WAITFOR DELAY '00:00:05';
UPDATE my_table SET value = 'Another value' WHERE id = 127;
COMMIT TRANSACTION;

Beispielprompt 1

Du kannst überprüfen, ob Probleme mit dieser Transaktion auftreten.

Wähle im Editor den Transaktionscode aus, und frage Copilot Chat dann:

Is this transaction good?

Beispielantwort 1

Copilot teilt dir mit, dass die Transaktion im SQL-Code allgemein in Ordnung ist, listet jedoch auch einige Hinweise bzgl. der Sperrdauer, Fehlerbehandlung und Parallelität auf. Copilot erwähnt auch, dass die Transaktion längere Sperren enthält, was potenziell zu Blockierungen oder Deadlocks führen kann. Die Antwort enthält auch überarbeiteten Code mit einer Fehlerbehandlung.

In diesem Fall entscheidest du dich jedoch, keine Fehlerbehandlung hinzuzufügen. Aktuell möchtest du lieber potenzielle Deadlocks beheben.

Beispielprompt 2

Gib folgenden Prompt ein:

How can I rewrite this code to reduce the chance of deadlock to a minimum while not adversely affecting performance. Tell me anything else I can do to avoid a deadlock. Note: I don't want to add error handling at this time.

Beispielantwort 2

Copilot gibt dir folgenden Ratschlag:

• Zugriffsreihenfolge: Stelle sicher, dass alle Transaktionen in derselben Reihenfolge auf Tabellen und Zeilen zugreifen.
• Kurzhalten der Transaktionen: Minimiere die Sperrzeiten von Transaktionen.
• Verwenden der geeigneten Isolationsstufen: Verwende die niedrigste Isolationsstufe, die deinen Anforderungen entspricht.

Nachstehend siehst du eine überarbeitete Version deines Codes:

BEGIN TRANSACTION;
-- Update the rows in a consistent order
UPDATE my_table SET value = 'Another value' WHERE id = 127;
UPDATE my_table SET value = 'Some value' WHERE id = 301;
-- Simulate a process taking 5 seconds:
WAITFOR DELAY '00:00:05';
COMMIT TRANSACTION;

Vermeiden von Nichtindexsuchen

Das Durchsuchen von nicht indizierten Tabellenspalten führt in der Regel zu einem vollständigen Tabellenscan. Dieser kann sehr langsam und ressourcenintensiv sein und die Wahrscheinlichkeit von Leistungsengpässen erhöhen.

Beispielszenario

Der folgende SQL-Code führt zu einem vollständigen Tabellenscan, wenn some_column nicht indiziert wurde:

BEGIN TRANSACTION;
SELECT * FROM my_table WHERE some_column = 'some_value';
-- More actions here, then:
COMMIT TRANSACTION;

Beispiel für Prompt

Wenn du Copilot die folgende Frage stellst, wird in der Regel das potenzielle Problem offenkundig.

How can I optimize this transaction?

Beispielantwort

Copilot gibt einige Ratschläge und weist darauf hin, dass die Spalte indiziert werden sollte:

Um die Transaktion zu optimieren, solltest du sicherstellen, dass die Transaktion so kurz wie möglich ausfällt, um die Sperrzeit zu reduzieren und die Leistung zu verbessern. Im Folgenden einige Tipps:

1. Minimieren der in der Transaktion durchgeführten Tasks: Nimm nur erforderliche Vorgänge in die Transaktion auf.
2. Verwenden geeigneter Indizes: Stelle sicher, dass die in der WHERE-Klausel verwendeten Spalten indiziert sind.
3. Vermeiden von lang andauernde Transaktionen: Halte die Transaktionsdauer kurz, um Probleme mit Sperrungen zu vermeiden.

Der in der Antwort vorgeschlagene Code enthält den SQL-Befehl zum Indizieren der Spalte:

`CREATE INDEX idx_some_column ON my_table(some_column);`

Vermeiden lang andauernder Transaktionen

Lang andauernde Abfragen und Transaktionen sollten nach Möglichkeit unbedingt vermieden werden, da sie die Leistung erheblich beeinträchtigen und Engpässe für andere Vorgänge verursachen können. Darüber hinaus können sie einen erheblichen Anteil des Arbeits- und Protokollspeichers verbrauchen, wodurch deine Anwendung in Umgebungen mit hoher Auslastung fehleranfälliger wird.

Beispielszenario

Der folgende SQL-Code erstellt neue Zeilen in target_table, die Daten aus ausgewählten Zeilenfeldern in source_table enthalten, die verschiedene Bedingungen erfüllen. Wenn source_table besonders groß ist, kann dies zu einer lang andauernden Transaktion führen.

BEGIN TRANSACTION;

INSERT INTO target_table (first_name, last_name, email, dept, role, hire_date)
SELECT first_name, last_name, email, department, job_title, start_date
FROM source_table
WHERE (department = 'Engineering' AND (salary > 95000 OR grade > 5))
  OR (department = 'Marketing' AND performance_rating = 'Excellent');

COMMIT;

Beispiel für Prompt

Du kannst Copilot fragen, ob es möglich ist, lang andauernde Transaktionen zu vermeiden.

source_table is an extremely large table. Without altering the table, how can I ensure this SQL doesn't become a long-running query, that blocks other operations?

Beispielantwort

Copilot schlägt folgende Strategien vor:

• Batchverarbeitung: Verarbeite die Daten in kleineren Batches anstelle einer einzelnen großen Transaktion. Dies kann mithilfe einer Schleife mit einem Grenzwert und einem Offset erfolgen.
• Indizierung: Stelle sicher, dass die in der WHERE-Klausel verwendeten Spalten (department, salary, grade und performance_rating) indiziert werden, um die Abfrage zu beschleunigen.
• Sperren: Verwende die Sperrung auf Zeilenebene, um eine Sperrung der gesamten Tabelle zu vermeiden.

Copilot liefert das folgende Beispiel für die Implementierung einer Batchverarbeitung. In diesem SQL-Code werden die Daten in Batches von jeweils 1.000 Zeilen verarbeitet, wodurch die Auslastung der Datenbank sinkt und lang andauernde Transaktionen vermieden werden.

DECLARE @BatchSize INT = 1000;
DECLARE @Offset INT = 0;
DECLARE @RowCount INT;

SET @RowCount = @BatchSize;

WHILE @RowCount = @BatchSize
BEGIN
    BEGIN TRANSACTION;

    INSERT INTO target_table (first_name, last_name, email, dept, role, hire_date)
    SELECT first_name, last_name, email, department, job_title, start_date
    FROM source_table
    WHERE (department = 'Engineering' AND salary > 95000)
       OR (department = 'Engineering' AND years_of_experience > 5)
       OR (department = 'Marketing' AND performance_rating = 'Excellent');
    ORDER BY primary_key_column
    OFFSET @Offset ROWS FETCH NEXT @BatchSize ROWS ONLY;

    SET @RowCount = @@ROWCOUNT;
    SET @Offset = @Offset + @BatchSize;

    COMMIT;
END;

Copilot fordert dich auf, primary_key_column im vorgeschlagenen Code durch den Namen der tatsächlichen Primärschlüsselspalte source_table zu ersetzen.

Vermeiden von Datenintegritätsproblemen

Es ist wichtig, dass die Informationen in deinen Datenbanken korrekt, konsistent und vollständig bleiben. Mangelhaft konzipierte Abfragen können zu fehlenden oder falschen Daten führen.

Beispielszenario

Die folgende SQL-Transaktion löscht einen Mitarbeiterdatensatz und erstellt ein Löschprotokoll. Es ist jedoch möglich, dass der DELETE-Vorgang erfolgreich verläuft, während der INSERT-Vorgang fehlschlägt. In diesem Fall gibt es keinen Protokolldatensatz für den Löschvorgang.

BEGIN TRANSACTION;

DELETE FROM employees
WHERE employee_id = 101;

INSERT INTO deletion_log (employee_id, deletion_date, reason)
VALUES (101, GETDATE(), 'Voluntary Resignation');

COMMIT TRANSACTION;

Beispiel für Prompt

How can I ensure that the DELETE and INSERT operations are only performed if both succeed?

Copilot teilt dir mit, dass du dies mithilfe einer Transaktion mit Fehlerbehandlung erreichst. Copilot gibt das folgende Beispiel aus, in dem das TRY...CATCH-Konstrukt von SQL Server verwendet wird:

BEGIN TRY
    BEGIN TRANSACTION;

    DELETE FROM employees
    WHERE employee_id = 101;

    INSERT INTO deletion_log (employee_id, deletion_date, reason)
    VALUES (101, GETDATE(), 'Voluntary Resignation');

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- Handle the error, e.g., log it or re-throw
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

Der vorgeschlagene Code stellt sicher, dass die Transaktion zurückgesetzt wird und keine Änderungen an der Datenbank vorgenommen werden, wenn der DELETE- oder der INSERT-Vorgang fehlschlägt.

Weiterführende Themen

• Prompt Engineering für GitHub Copilot
• Best Practices für die Verwendung von GitHub Copilot