{"meta":{"title":"修复数据库死锁或数据一致性问题","intro":"Copilot Chat 可帮助你避免导致数据库操作缓慢或阻塞的代码，或者缺少或不正确的数据表。","product":"GitHub Copilot","breadcrumbs":[{"href":"/zh/copilot","title":"GitHub Copilot"},{"href":"/zh/copilot/tutorials","title":"教程"},{"href":"/zh/copilot/tutorials/copilot-cookbook","title":"GitHub Copilot 指南"},{"href":"/zh/copilot/tutorials/copilot-cookbook/refactor-code","title":"重构代码"},{"href":"/zh/copilot/tutorials/copilot-cookbook/refactor-code/fix-database-deadlocks","title":"修复数据库死锁"}],"documentType":"article"},"body":"# 修复数据库死锁或数据一致性问题\n\nCopilot Chat 可帮助你避免导致数据库操作缓慢或阻塞的代码，或者缺少或不正确的数据表。\n\n复杂的数据库操作，尤其是涉及事务的操作，可能引发难以排查的死锁或数据不一致。\n\nCopilot Chat 可以通过识别事务过程中可能发生锁定或死锁的环节来提供帮助，并可针对事务隔离或死锁解决建议最佳实践，例如调整锁定策略或妥善处理死锁异常。\n\n> \\[!NOTE] 本文中显示的响应是示例。\n> Copilot Chat 响应是不确定的，因此你可能会从此处所示的响应中获取不同的响应。\n\n## 避免对相互依赖的行进行并发更新\n\n当两个或更多事务以不同顺序更新同一表中的行时，会造成循环等待。\n\n### 示例方案\n\n下面的 SQL 片段先更新表中的某一行，然后执行一个耗时数秒的操作，再更新同一表中的另一行。 这存在问题，因为事务会在完成前将第 `id = 1` 行锁定数秒，之后才会释放锁定。 如果在此期间另一个事务启动，并执行类似操作，但它先锁定 `id = 2` 行以便进行更新，随后再尝试锁定 `id = 1` 行，那么两个事务都会互相等待对方完成，从而导致死锁。\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### 示例提示 1\n\n可以检查该事务是否存在问题。\n\n在编辑器中，选择事务代码，然后询问 Copilot Chat：\n\n```copilot copy prompt ref=deadlock-example\nIs this transaction good?\n```\n\n### 示例响应 1\n\nCopilot 告知“SQL 代码中的事务通常很好”，但列出了你可能要考虑的一些事项：锁定持续时间、错误处理和并发。 它指出“该事务持有锁的时间较长，可能导致阻塞或死锁。” 响应中还给出了加入错误处理的修订代码。\n\n在此场景中，你选择暂不添加错误处理。 当前，你希望解决潜在的死锁风险。\n\n### 示例提示 2\n\n输入以下跟进提示：\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### 示例响应 2\n\nCopilot 提供以下建议：\n\n> *\n\n**访问顺序：** 确保所有事务都按照相同的顺序访问表和行。\n\n> \\*\n> **保持事务简短：** 尽量缩短事务持有锁的时间。\n> \\*\n> **使用合适的隔离级别：** 选择满足需求的最低隔离级别。\n>\n> 以下是修订后的代码示例：\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## 避免非索引搜索\n\n对未建立索引的列进行搜索通常会触发表扫描。 这可能非常缓慢且消耗大量资源，从而增加了性能瓶颈出现的可能性。\n\n### 示例方案\n\n如果 `some_column` 未建立索引，则以下 SQL 将会触发全表扫描：\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### 示例提示\n\nCopilot提出以下问题通常会揭示潜在问题。\n\n```copilot copy prompt ref=full-table-scan\nHow can I optimize this transaction?\n```\n\n### 示例响应\n\nCopilot 给出一些建议，并提到该列应建立索引：\n\n> 为优化事务，应确保事务尽可能简短，以减少锁定并提升性能。 下面是一些提示：\n>\n> 1.\n\n**尽量减少事务中的工作量：** 仅将必要的操作包含在事务中。\n\n> 1\\.\n> **使用合适的索引：** 确保 WHERE 子句使用的列已建立索引。\n> 1\\.\n> **避免长时间运行的事务：** 保持事务尽量简短，以减少锁竞争。\n\n回复中提供的代码示例包含了建立索引的 SQL 命令：\n\n```sql\n`CREATE INDEX idx_some_column ON my_table(some_column);`\n```\n\n## 避免长期运行的事务\n\n避免执行长时间运行的查询或事务，因为它们会严重影响性能并对其他操作造成瓶颈。 此外，它们还会消耗大量内存和日志空间，使应用程序在高负载环境中更容易出现故障。\n\n### 示例方案\n\n以下 SQL 在 `target_table` 中创建新行，其中新行的数据来自 `source_table` 中满足特定条件的行的部分字段。 如果 `source_table` 非常大，则可能会导致事务运行时间过长。\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### 示例提示\n\n可以询问 Copilot 是否可以避免长时间运行的事务。\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### 示例响应\n\nCopilot 建议以下策略：\n\n* **分批处理：** 将数据分批处理，而不是在一个大型事务中一次性完成。 可以使用具有限制和偏移量的循环来完成此操作。\n* **编制索引：** 确保 `WHERE` 子句中使用的列（`department`、`salary`、`grade` 和 `performance_rating`）已建立索引以加速查询。\n* **锁定策略：** 使用行级锁定以避免锁住整个表。\n\n它提供了以下示例，说明如何实现分批处理。 此 SQL 每次处理 1000 行，降低数据库负载并避免长时间运行的事务。\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 会提示你将建议代码中的 `primary_key_column` 替换为 `source_table` 的实际主键列名称。\n\n## 避免数据完整性问题\n\n保持数据库内数据的准确性、一致性和完整性至关重要。 设计不佳的查询可能导致数据缺失或错误。\n\n### 示例方案\n\n以下 SQL 事务会删除一条员工记录并写入一条删除日志。 然而，可能出现 `DELETE` 成功但 `INSERT` 失败的情况，导致删除操作没有日志记录。\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### 示例提示\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### 示例响应\n\nCopilot 告诉你通过使用带有错误处理的事务来实现这一点。 它使用 SQL Server 的 `TRY...CATCH` 构造提供以下示例：\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\n建议的代码可确保若 `DELETE` 或 `INSERT` 任一环节失败，事务将回滚，数据库不会发生部分更新。\n\n## 延伸阅读\n\n* [GitHub Copilot 对话助手的提示设计](/zh/copilot/concepts/prompting/prompt-engineering)\n* [使用 GitHub Copilot 的最佳做法](/zh/copilot/get-started/best-practices)"}