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

副驾驶聊天 可帮助你避免导致数据库操作缓慢或阻塞的代码，或者缺少或不正确的数据表。

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

副驾驶聊天 可以通过识别事务过程中可能发生锁定或死锁的环节来提供帮助，并可针对事务隔离或死锁解决建议最佳实践，例如调整锁定策略或妥善处理死锁异常。

> \[!NOTE] 本文中显示的响应是示例。
> 副驾驶聊天 响应是不确定的，因此你可能会从此处所示的响应中获取不同的响应。

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

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

### 示例方案

下面的 SQL 片段先更新表中的某一行，然后执行一个耗时数秒的操作，再更新同一表中的另一行。 这存在问题，因为事务会在完成前将第 `id = 1` 行锁定数秒，之后才会释放锁定。 如果在此期间另一个事务启动，并执行类似操作，但它先锁定 `id = 2` 行以便进行更新，随后再尝试锁定 `id = 1` 行，那么两个事务都会互相等待对方完成，从而导致死锁。

```sql id=deadlock-example
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 copy prompt ref=deadlock-example
Is this transaction good?
```

### 示例响应 1

Copilot 告知“SQL 代码中的事务通常很好”，但列出了你可能要考虑的一些事项：锁定持续时间、错误处理和并发。 它指出“该事务持有锁的时间较长，可能导致阻塞或死锁。” 响应中还给出了加入错误处理的修订代码。

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

### 示例提示 2

输入以下跟进提示：

```copilot copy prompt ref=deadlock-example
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 提供以下建议：

> *

**访问顺序：** 确保所有事务都按照相同的顺序访问表和行。

> \*
> **保持事务简短：** 尽量缩短事务持有锁的时间。
> \*
> **使用合适的隔离级别：** 选择满足需求的最低隔离级别。
>
> 以下是修订后的代码示例：

```sql
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 将会触发全表扫描：

```sql id=full-table-scan
BEGIN TRANSACTION;
SELECT * FROM my_table WHERE some_column = 'some_value';
-- More actions here, then:
COMMIT TRANSACTION;
```

### 示例提示

Copilot提出以下问题通常会揭示潜在问题。

```copilot copy prompt ref=full-table-scan
How can I optimize this transaction?
```

### 示例响应

Copilot 给出一些建议，并提到该列应建立索引：

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

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

> 1\.
> **使用合适的索引：** 确保 WHERE 子句使用的列已建立索引。
> 1\.
> **避免长时间运行的事务：** 保持事务尽量简短，以减少锁竞争。

回复中提供的代码示例包含了建立索引的 SQL 命令：

```sql
`CREATE INDEX idx_some_column ON my_table(some_column);`
```

## 避免长期运行的事务

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

### 示例方案

以下 SQL 在 `target_table` 中创建新行，其中新行的数据来自 `source_table` 中满足特定条件的行的部分字段。 如果 `source_table` 非常大，则可能会导致事务运行时间过长。

```sql id=long-running-transaction
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 copy prompt ref=long-running-transaction
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 行，降低数据库负载并避免长时间运行的事务。

```sql
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` 失败的情况，导致删除操作没有日志记录。

```sql id=delete-employee
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 copy prompt ref=delete-employee
How can I ensure that the DELETE and INSERT operations are only performed if both succeed?
```

### 示例响应

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

```sql
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 对话助手的提示设计](/zh/enterprise-cloud@latest/copilot/using-github-copilot/prompt-engineering-for-github-copilot)
* [使用 GitHub Copilot 的最佳做法](/zh/enterprise-cloud@latest/copilot/using-github-copilot/best-practices-for-using-github-copilot)