修复数据库死锁或数据完整性议题

复杂的数据库操作（尤其是涉及事务的操作）可能会导致难以调试的死锁或数据不一致。

Copilot Chat 可帮助识别事务中可能发生锁定或死锁的点，并可提出隔离事务或解决死锁的最佳做法建议，例如调整锁定策略或从容地处理死锁异常。

Note

本文中显示的响应是示例。 Copilot Chat 响应是不确定的，所以你可能会得到与这里所显示的不同的响应。

避免同时更新相互依赖的行

如果两个或多个事务尝试更新数据库表中的相同行，但顺序不同，则可能会导致循环等待条件。

示例方案

以下 SQL 代码片段更新表的一行，然后执行一个需要几秒钟的操作，接着更新同一表中的另一行。这是有问题的，因为在事务完成并释放锁之前，事务会锁定 id = 1 行几秒钟。如果另一个执行类似操作的事务在此期间启动，由于先锁定了 id = 2 行来更新该行，然后又试图锁定 id = 1 行，那么这两个事务都将等待对方完成，从而导致死锁。

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;

示例提示 1

你可以检查此事务是否存在任何问题。

在编辑器中，选择事务代码，然后询问 Copilot Chat：

Is this transaction good?

示例响应 1

Copilot 告诉你“SQL 代码中的事务总体上没有问题”，但列出了一些你可能需要考虑的事项：锁定持续时间、错误处理和并发性。它提到“事务持有锁的时间较长，可能导致阻止或死锁”。回复中还包括添加了错误处理的修订代码。

在这种情况下，你决定不添加错误处理。目前，你想要解决出现死锁的可能性。

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.

示例响应 2

Copilot 给出了以下建议：

访问顺序： 确保所有事务以相同顺序访问表和行。

保持事务简短： 最大程度地减少事务持有锁的时间。

使用适当的隔离级别： 使用符合要求的最低隔离级别。

下面是你的代码的修改版本：

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;

避免非索引搜索

搜索未编制索引的表列通常会导致全表扫描。这可能会非常缓慢且耗费资源，增加出现性能瓶颈的可能性。

示例方案

如果 some_column 未编制索引，以下 SQL 将导致全表扫描：

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

示例提示

询问 Copilot 以下问题通常会发现潜在的问题。

How can I optimize this transaction?

示例响应

Copilot 给出了一些建议，并提到应该对列编制索引：

为优化事务，你应该确保事务尽可能简短，以减少锁定并提高性能。下面是一些提示：

尽量减少事务中完成的工作： 在事务中仅包含必要的操作。

使用适当的索引： 确保 WHERE 子句中使用的列已编制索引。

避免长时间运行的事务： 保持事务持续时间较短以避免锁定议题。

回复中的建议代码包括用于对列编制索引的 SQL 命令：

`CREATE INDEX idx_some_column ON my_table(some_column);`

避免长时间运行的事务

请务必尽可能避免长时间运行的查询和事务，因为它们会严重降低性能，并对其他操作造成瓶颈。此外，它们还会占用大量内存和日志空间，使应用程序在高负载环境中更容易出现故障。

示例方案

以下 SQL 在 target_table 中创建新行，其中包含来自满足各种条件的 source_table 中所选行字段的数据。如果 source_table 非常大，则可能会导致长时间运行的事务。

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;

示例提示

你可以询问 Copilot 是否可以避免长时间运行的事务。

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?

示例响应

Copilot 建议采取以下策略：

批处理： 以较小的批而不是一个大型事务处理数据。这可以使用带有限制和偏移的循环来实现。
编制索引： 确保对 WHERE 子句中使用的列（department、salary、grade 和 performance_rating）编制索引以加快查询速度。
锁定： 使用行级锁定来避免锁定整个表。

它提供了以下示例，说明如何实现批处理。此 SQL 一次以 1000 行作为一个批来处理数据，减少了数据库负载，避免了长时间运行的事务。

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 告诉你将建议代码中的 primary_key_column 替换为 source_table 的实际主键列的名称。

避免数据完整性议题

数据库中的信息必须保持准确、一致和完整。设计不当的查询会导致数据缺失或不正确。

示例方案

以下 SQL 事务删除员工记录并创建删除日志。但是，DELETE 操作有可能成功，而 INSERT 操作有可能失败，在这种情况下，删除没有日志记录。

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?

Copilot 告诉你可以通过使用带有错误处理的事务来实现这一点。它给出了以下使用 SQL Server 的 TRY...CATCH 构造的示例：

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;

建议的代码可确保当 DELETE 或 INSERT 操作失败时，事务回滚，不会对数据库进行任何更改。

其他阅读材料

“GitHub Copilot 的提示设计”
“有关使用 GitHub Copilot 的最佳做法”

修复数据库死锁或数据完整性议题

本文内容

避免同时更新相互依赖的行

示例方案

示例提示 1

示例响应 1

示例提示 2

示例响应 2

避免非索引搜索

示例方案

示例提示

示例响应

避免长时间运行的事务

示例方案

示例提示

示例响应

避免数据完整性议题

示例方案

示例提示

其他阅读材料