{"meta":{"title":"Fixing database deadlocks or data integrity issues","intro":"Copilot Chat can help you avoid code that causes slow or blocked database operations, or tables with missing or incorrect data.","product":"GitHub Copilot","breadcrumbs":[{"href":"/en/copilot","title":"GitHub Copilot"},{"href":"/en/copilot/tutorials","title":"Tutorials"},{"href":"/en/copilot/tutorials/copilot-cookbook","title":"GitHub Copilot Cookbook"},{"href":"/en/copilot/tutorials/copilot-cookbook/refactor-code","title":"Refactor code"},{"href":"/en/copilot/tutorials/copilot-cookbook/refactor-code/fix-database-deadlocks","title":"Fix database deadlocks"}],"documentType":"article"},"body":"# Fixing database deadlocks or data integrity issues\n\nCopilot Chat can help you avoid code that causes slow or blocked database operations, or tables with missing or incorrect data.\n\nComplex database operations–particularly those involving transactions–can lead to deadlocks or data inconsistencies that are hard to debug.\n\nCopilot Chat can help by identifying points in a transaction where locking or deadlocks could occur, and can suggest best practices for transaction isolation or deadlock resolution, such as adjusting locking strategies or handling deadlock exceptions gracefully.\n\n> \\[!NOTE] The responses shown in this article are examples. Copilot Chat responses are non-deterministic, so you may get different responses from the ones shown here.\n\n## Avoiding simultaneous updates on interdependent rows\n\nWhen two or more transactions attempt to update the same rows in a database table, but in different orders, it can cause a circular wait condition.\n\n### Example scenario\n\nThe following SQL snippet updates one row of a table, then performs an operation that takes several seconds, then updates another row in the same table. This is problematic because the transaction locks the `id = 1` row for several seconds before the transaction completes, releasing the lock. If another transaction starts during this time that performs a similar operation, but locks the `id = 2` row first, so that it can update the row, and then attempts to lock the `id = 1` row, then both transactions will be left waiting for the other to complete, resulting in deadlock.\n\n```sql id=deadlock-example\nBEGIN TRANSACTION;\nUPDATE my_table SET value = 'Some value' WHERE id = 301;\n-- Simulate a process taking 5 seconds:\nWAITFOR DELAY '00:00:05';\nUPDATE my_table SET value = 'Another value' WHERE id = 127;\nCOMMIT TRANSACTION;\n```\n\n### Example prompt 1\n\nYou can check whether there are any problems with this transaction.\n\nIn the editor, select the transaction code, then ask Copilot Chat:\n\n```copilot copy prompt ref=deadlock-example\nIs this transaction good?\n```\n\n### Example response 1\n\nCopilot tells you that \"the transaction in your SQL code is generally fine,\" but lists some things you may want to consider: lock duration, error handling, and concurrency. It mentions that \"the transaction holds locks for an extended period, potentially leading to blocking or deadlocks.\" The response also includes revised code that adds error handling.\n\nIn this case, you decide not to add error handling. Right now you want to address the potential for deadlocks.\n\n### Example prompt 2\n\nEnter this follow-up prompt:\n\n```copilot copy prompt ref=deadlock-example\nHow 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.\n```\n\n### Example response 2\n\nCopilot gives the following advice:\n\n> * **Order of Access:** Ensure that all transactions access tables and rows in the same order.\n> * **Keep Transactions Short:** Minimize the time a transaction holds locks.\n> * **Use Appropriate Isolation Levels:** Use the lowest isolation level that meets your requirements.\n>\n> Here is a revised version of your code:\n\n```sql\nBEGIN TRANSACTION;\n-- Update the rows in a consistent order\nUPDATE my_table SET value = 'Another value' WHERE id = 127;\nUPDATE my_table SET value = 'Some value' WHERE id = 301;\n-- Simulate a process taking 5 seconds:\nWAITFOR DELAY '00:00:05';\nCOMMIT TRANSACTION;\n```\n\n## Avoiding non-index searches\n\nSearching table columns that have not been indexed will typically result in a full table scan. This can be very slow and resource-intensive, increasing the likelihood of performance bottlenecks.\n\n### Example scenario\n\nThe following SQL will result in a full table scan if `some_column` is not indexed:\n\n```sql id=full-table-scan\nBEGIN TRANSACTION;\nSELECT * FROM my_table WHERE some_column = 'some_value';\n-- More actions here, then:\nCOMMIT TRANSACTION;\n```\n\n### Example prompt\n\nAsking Copilot the following question will usually reveal the potential problem.\n\n```copilot copy prompt ref=full-table-scan\nHow can I optimize this transaction?\n```\n\n### Example response\n\nCopilot gives some advice, and mentions that the column should be indexed:\n\n> To optimize the transaction, you should ensure that the transaction is as short as possible to reduce locking and improve performance. Here are a few tips:\n>\n> 1. **Minimize the work done in the transaction:** Only include the necessary operations within the transaction.\n> 2. **Use appropriate indexes:** Ensure that the columns used in the WHERE clause are indexed.\n> 3. **Avoid long-running transactions:** Keep the transaction duration short to avoid locking issues.\n\nThe suggested code in the response includes the SQL command for indexing the column:\n\n```sql\n`CREATE INDEX idx_some_column ON my_table(some_column);`\n```\n\n## Avoiding long-running transactions\n\nIt's important to avoid long-running queries and transactions, where possible, as these can severely degrade performance and cause bottlenecks for other operations. Additionally, they can consume significant memory and log space, making your application more prone to failure in high-load environments.\n\n### Example scenario\n\nThe following SQL creates new rows in `target_table`, containing data from selected fields of rows in `source_table` where various conditions are met. If `source_table` is extremely large this may result in a long-running transaction.\n\n```sql id=long-running-transaction\nBEGIN TRANSACTION;\n\nINSERT INTO target_table (first_name, last_name, email, dept, role, hire_date)\nSELECT first_name, last_name, email, department, job_title, start_date\nFROM source_table\nWHERE (department = 'Engineering' AND (salary > 95000 OR grade > 5))\n  OR (department = 'Marketing' AND performance_rating = 'Excellent');\n\nCOMMIT;\n```\n\n### Example prompt\n\nYou can ask Copilot whether it's possible to avoid long-running transactions.\n\n```copilot copy prompt ref=long-running-transaction\nsource_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?\n```\n\n### Example response\n\nCopilot suggests the following strategies:\n\n* **Batch Processing:** Process the data in smaller batches instead of a single large transaction. This can be done using a loop with a limit and offset.\n* **Indexing:** Ensure that the columns used in the `WHERE` clause (`department`, `salary`, `grade`, and `performance_rating`) are indexed to speed up the query.\n* **Locking:** Use row-level locking to avoid locking the entire table.\n\nIt supplies the following example of how you can implement batch processing. This SQL processes the data in batches of 1000 rows at a time, reducing the load on the database and preventing long-running transactions.\n\n```sql\nDECLARE @BatchSize INT = 1000;\nDECLARE @Offset INT = 0;\nDECLARE @RowCount INT;\n\nSET @RowCount = @BatchSize;\n\nWHILE @RowCount = @BatchSize\nBEGIN\n    BEGIN TRANSACTION;\n\n    INSERT INTO target_table (first_name, last_name, email, dept, role, hire_date)\n    SELECT first_name, last_name, email, department, job_title, start_date\n    FROM source_table\n    WHERE (department = 'Engineering' AND salary > 95000)\n       OR (department = 'Engineering' AND years_of_experience > 5)\n       OR (department = 'Marketing' AND performance_rating = 'Excellent');\n    ORDER BY primary_key_column\n    OFFSET @Offset ROWS FETCH NEXT @BatchSize ROWS ONLY;\n\n    SET @RowCount = @@ROWCOUNT;\n    SET @Offset = @Offset + @BatchSize;\n\n    COMMIT;\nEND;\n```\n\nCopilot tells you to replace `primary_key_column` in the suggested code with the name of the actual primary key column of `source_table`.\n\n## Avoiding data integrity issues\n\nIt's important that the information in your databases remains accurate, consistent, and complete. Poorly designed queries can result in missing or incorrect data.\n\n### Example scenario\n\nThe following SQL transaction deletes an employee record and creates a deletion log. However, it's possible for the `DELETE` operation to succeed and the `INSERT` operation to fail, in which case there is no log record for the deletion.\n\n```sql id=delete-employee\nBEGIN TRANSACTION;\n\nDELETE FROM employees\nWHERE employee_id = 101;\n\nINSERT INTO deletion_log (employee_id, deletion_date, reason)\nVALUES (101, GETDATE(), 'Voluntary Resignation');\n\nCOMMIT TRANSACTION;\n```\n\n### Example prompt\n\n```copilot copy prompt ref=delete-employee\nHow can I ensure that the DELETE and INSERT operations are only performed if both succeed?\n```\n\n### Example response\n\nCopilot tells you that you achieve this by using a transaction with error handling. It gives the following example using SQL Server's `TRY...CATCH` construct:\n\n```sql\nBEGIN TRY\n    BEGIN TRANSACTION;\n\n    DELETE FROM employees\n    WHERE employee_id = 101;\n\n    INSERT INTO deletion_log (employee_id, deletion_date, reason)\n    VALUES (101, GETDATE(), 'Voluntary Resignation');\n\n    COMMIT TRANSACTION;\nEND TRY\nBEGIN CATCH\n    ROLLBACK TRANSACTION;\n    -- Handle the error, e.g., log it or re-throw\n    DECLARE @ErrorMessage NVARCHAR(4000);\n    DECLARE @ErrorSeverity INT;\n    DECLARE @ErrorState INT;\n\n    SELECT\n        @ErrorMessage = ERROR_MESSAGE(),\n        @ErrorSeverity = ERROR_SEVERITY(),\n        @ErrorState = ERROR_STATE();\n\n    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);\nEND CATCH;\n```\n\nThe suggested code ensures that if either the `DELETE` or `INSERT` operation fails, the transaction is rolled back and no changes are made to the database.\n\n## Further reading\n\n* [Prompt engineering for GitHub Copilot Chat](/en/copilot/concepts/prompting/prompt-engineering)\n* [Best practices for using GitHub Copilot](/en/copilot/get-started/best-practices)"}