修复数据库死锁或数据一致性问题

Copilot 对话助手 可帮助避免编写导致数据库操作缓慢、阻塞，或产生缺失/错误数据的代码。

本文内容

复杂的数据库操作，尤其是涉及事务的操作，可能引发难以排查的死锁或数据不一致。

Copilot 对话助手 可识别事务中可能出现锁定或死锁的位置，并提供事务隔离级别或死锁处理的最佳实践建议，例如调整锁策略或更优雅地处理死锁异常。

注意

本文中显示的响应是示例。 Copilot 对话助手 的回答是不确定的，因此你可能会得到与这里所显示的不同的回答。

避免对相互依赖的行进行并发更新

当两个或更多事务以不同顺序更新同一表中的行时，会造成循环等待。

示例方案

下面的 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 对话助手 查询：

Copilot Chat prompt
Is this transaction good?

示例响应 1

Copilot 表示“你的 SQL 事务整体上没有问题”，但列出了需考虑的点：锁持续时间、错误处理和并发性。 它指出“该事务持有锁的时间较长，可能导致阻塞或死锁。” 响应中还给出了加入错误处理的修订代码。

在此场景中，你选择暂不添加错误处理。 当前，你希望解决潜在的死锁风险。

示例提示 2

输入以下跟进提示：

Copilot Chat prompt
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 提问后，它通常会指出潜在问题。

Copilot Chat prompt
How can I optimize this transaction?

示例响应

Copilot 会给出建议，并指出应为该列建立索引：

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

  1.           **尽量减少事务中的工作量：** 仅将必要的操作包含在事务中。

  2.           **使用合适的索引：** 确保 WHERE 子句使用的列已建立索引。

  3.           **避免长时间运行的事务：** 保持事务尽量简短，以减少锁竞争。

回复中提供的代码示例包含了建立索引的 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 咨询是否能避免长时间运行的事务。

Copilot Chat prompt
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 建议采取以下策略：

  •           **分批处理：** 将数据分批处理，而不是在一个大型事务中一次性完成。 可通过使用循环并设置 limit 与 offset 实现。

  •           **编制索引：** 确保 `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;

示例提示

Copilot Chat prompt
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 任一环节失败，事务将回滚，数据库不会发生部分更新。

其他阅读材料