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 innerhalb einer Transaktion identifiziert, an denen Sperren oder Deadlocks auftreten könnten, und bewährte Methoden für die Transaktionsisolation oder die Auflösung von Deadlocks empfiehlt, z. B. durch Anpassung von Sperrstrategien oder durch eine saubere Behandlung von Deadlock-Ausnahmen.
Hinweis
Bei den in diesem Artikel veranschaulichten Antworten handelt es sich um Beispiele. Copilot-Chat Antworten sind nicht deterministisch, daher erhalten Sie möglicherweise unterschiedliche Antworten von den hier gezeigten Antworten.
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 dann: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 in der Regel einwandfrei ist", aber es werden einige Dinge aufgeführt, die Sie berücksichtigen sollten: 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 folgende Ratschläge:
Order of Access: Stellen Sie sicher, dass alle Transaktionen auf Tabellen und Zeilen in derselben Reihenfolge 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;
Beispielaufforderung
Wenn Sie die folgende Frage stellen Copilot , wird in der Regel das potenzielle Problem offengelegt.
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 erwähnt, 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. Hier einige Tipps:
Minimieren der in der Transaktion durchgeführten Tasks: Nehmen Sie nur erforderliche Vorgänge in die Transaktion auf.
1. Verwenden geeigneter Indizes: Stellen Sie sicher, dass die in der WHERE-Klausel verwendeten Spalten indiziert sind. 1. 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;
Beispielaufforderung
Sie können bei Copilot nachfragen, 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?
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 die folgenden Strategien vor:
- Batchverarbeitung: Verarbeiten Sie die Daten in kleineren Batches anstelle einer einzelnen großen Transaktion. Dies kann mit einer Schleife mit einem Limit und Offset erfolgen.
- Indizierung: Stellen Sie sicher, dass die in der
WHERE-Klausel verwendeten Spalten (department,salary,gradeundperformance_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 weist Sie an, primary_key_column durch den Namen der tatsächlichen Primärschlüsselspalte von source_table im vorgeschlagenen Code 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;
Beispielaufforderung
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 zeigt Ihnen, dass Sie dies durch die Verwendung einer Transaktion mit Fehlerbehandlung erreichen. Im folgenden Beispiel wird das TRY...CATCH-Konstrukt von SQL Server verwendet:
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.