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

Copilot Chat 可以帮助您避免导致数据库操作缓慢或阻塞、或表中缺失或错误数据的代码。

本文内容

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

Copilot Chat 可以帮助识别事务中可能出现锁定或死锁的点，并建议事务隔离或死锁解决的最佳实践，例如调整锁定策略或优雅地处理死锁异常。

注意

本文中显示的响应为示例。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

Copilot 提示
Is this transaction good?

示例响应 1

Copilot 告诉您“您 SQL 代码中的事务总体上是可以的”，但列出了一些您可能需要考虑的事项：锁定持续时间、错误处理和并发性。它提到“事务的锁定时间过长，可能导致阻塞或死锁”。响应中还包括了添加错误处理的修改代码。

在这种情况下，您决定暂不添加错误处理。目前您想解决潜在的死锁问题。

示例提示 2

输入以下后续提示

Copilot 提示
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 提示
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 提示
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 提示
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 任一操作失败，事务将回滚，数据库不做任何更改。

延伸阅读

© . This site is unofficial and not affiliated with GitHub, Inc.