Komplexe Datenbankvorgänge – insbesondere solche, die Transaktionen betreffen – können zu Deadlocks oder Dateninkonsistenzen führen, die schwer zu debuggen sind.
Copilot-Chat kann helfen, indem es Stellen in einer Transaktion identifiziert, an denen Sperren oder Deadlocks auftreten könnten, und kann Best Practices für die Transaktionsisolierung oder Deadlock-Behebung vorschlagen, wie z. B. die Anpassung von Sperrstrategien oder die angemessene Behandlung von Deadlockausnahmen.
Hinweis
Bei den in diesem Artikel veranschaulichten Antworten handelt es sich um Beispiele. Da die Copilot-Chat-Antworten nicht deterministisch sind, erhältst du möglicherweise andere Antworten als die hier dargestellten.
Vermeiden gleichzeitiger Updates in voneinander abhängigen Zeilen
Wenn zwei oder mehr Transaktionen versuchen, dieselben Zeilen in einer Datenbanktabelle zu aktualisieren, jedoch in unterschiedlicher Reihenfolge, kann dies zu einer zirkulären Wartebedingung führen.
Beispielszenario
Der folgende SQL-Codeabschnitt aktualisiert eine Zeile einer Tabelle, führt dann eine Operation durch, die mehrere Sekunden dauert, und aktualisiert anschließend 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
Sie können überprüfen, ob Probleme mit dieser Transaktion auftreten.
Wählen Sie im Editor den Transaktionscode aus, und fragen Sie Copilot-Chat:
Is this transaction good?
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; Is this transaction good?
Is this transaction good?
Beispielantwort 1
Copilot teilt Ihnen mit, dass „die Transaktion in Ihrem SQL-Code im Allgemeinen in Ordnung ist“, listet aber einige Punkte auf, die Sie möglicherweise berücksichtigen möchten: Sperrdauer, Fehlerbehandlung und Parallelität. 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 entscheiden Sie sich jedoch, keine Fehlerbehandlung hinzuzufügen. Jetzt sollten Sie sich mit dem Potenzial für Deadlocks auseinandersetzen.
Beispielprompt 2
Geben Sie diese Folgeäußerung 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.
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; 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.
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 Ihnen folgenden Ratschlag:
**Zugriffsreihenfolge:** Stellen Sie sicher, dass alle Transaktionen in der gleichen Reihenfolge auf Tabellen und Zeilen zugreifen.**Kurzhalten der Transaktionen:** Minimieren Sie die Sperrzeiten von Transaktionen.**Verwenden der geeigneten Isolationsstufen:** Verwenden Sie die niedrigste Isolationsstufe, die Ihren Anforderungen entspricht.Hier ist eine überarbeitete Version Ihres 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 Nicht-Index-Suchen
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 Sie Copilot die folgende Frage stellen, wird in der Regel das potenzielle Problem offenkundig.
How can I optimize this transaction?
BEGIN TRANSACTION; SELECT * FROM my_table WHERE some_column = 'some_value'; -- More actions here, then: COMMIT TRANSACTION; How can I optimize this transaction?
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, sollten Sie sicherstellen, dass die Transaktion so kurz wie möglich ist, um Sperrungen zu reduzieren und die Leistung zu verbessern. Im Folgenden einige Tipps:
**Minimieren der in der Transaktion durchgeführten Tasks:** Nehmen Sie nur erforderliche Vorgänge in die Transaktion auf.**Verwenden geeigneter Indizes:** Stellen Sie sicher, dass die in der WHERE-Klausel verwendeten Spalten indiziert sind.**Vermeiden von lang andauernde Transaktionen:** Halten Sie 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 von zeitintensiven 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 Ausführung der 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
Sie können Copilot fragen, ob es möglich ist, lang andauernde Ausführungen von 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?
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; 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?
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:** Verarbeiten Sie die Daten in kleineren Batches anstelle einer einzelnen großen Transaktion. Dies kann mithilfe einer Schleife mit einem Grenzwert und einem Offset erfolgen. -
**Indizierung:** Stellen Sie sicher, dass die in der `WHERE`-Klausel verwendeten Spalten (`department`, `salary`, `grade` und `performance_rating`) indiziert werden, um die Abfrage zu beschleunigen. -
**Sperren:** Verwenden Sie 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 Sie 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 Ihren 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?
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; How can I ensure that the DELETE and INSERT operations are only performed if both succeed?
How can I ensure that the DELETE and INSERT operations are only performed if both succeed?
Beispielantwort
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.