PostgreSQL 死锁问题分析与优化

PostgreSQL 死锁问题分析与优化

目录

问题描述

根据提供的报错信息和搜索到的资料，PostgreSQL 数据库报错 “deadlock detected” 是由于两个事务（Process 3913996 和 Process 28657）在执行 DELETE 操作时相互等待对方持有的锁，导致死锁。具体来说，两个进程都在尝试删除 xxx 表中的记录，但由于锁的相互等待，导致无法继续执行。

解决方法

1. 手动终止事务

可以通过查询数据库的锁信息（如使用 pg_locks 视图），找到死锁涉及的事务，并手动终止其中一个或多个事务，以打破死锁。

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid IN (3913996, 28657);

2. 设置死锁超时

在数据库配置中设置死锁检测超时时间（如使用 deadlock_timeout 参数），当检测到死锁超过指定时间时，数据库会自动终止其中一个事务。

SET deadlock_timeout = '1s';

3. 优化查询

避免在同一表上同时执行多个 DELETE 操作，或者确保 DELETE 操作的顺序一致，以减少死锁的可能性。

4. 使用咨询锁

在某些情况下，可以使用咨询锁（Advisory Lock）来替代表级锁，从而减少死锁的发生。

预防措施

1. 事务隔离级别

适当调整事务的隔离级别，如使用 SERIALIZABLE 隔离级别，可以减少死锁的发生。

2. 批量操作

在进行批量删除操作时，尽量减少单次操作的记录数，或者使用分批次操作，以减少锁的持有时间。

3. 监控与报警

通过监控工具（如 Zabbix）实时监控数据库的死锁情况，并在检测到死锁时及时报警，以便快速处理。

使用 pg_locks 视图查询死锁信息

在 PostgreSQL 中，使用 pg_locks 视图可以详细查询死锁涉及的事务信息。可以执行以下 SQL 语句来检查数据库中是否存在死锁：

SELECT * FROM pg_locks WHERE granted = 'f';

这个查询将返回所有等待锁的事务，即那些没有获得所需锁的事务。如果两个或多个事务互相等待对方释放资源，那么它们就构成了一个死锁。

为了进一步分析死锁涉及的事务信息，可以结合其他表的信息。例如，可以查询指定表的 oid：

SELECT oid FROM pg_class WHERE relname = 't_mytab';

其中 t_mytab 是你认为可能涉及死锁的表名。通过获取表的 oid，可以查询该表正在被执行的进程 pid，从而更深入地了解死锁的具体情况。

配置 deadlock_timeout 参数

配置方法

在 PostgreSQL 的配置文件 postgresql.conf 中，可以设置 deadlock_timeout 参数来指定超时时间。例如：

deadlock_timeout = 5s

这里将超时时间设置为 5 秒。

PostgreSQL 还支持通过命令行或 SQL 语句动态调整 deadlock_timeout 参数。例如，使用以下命令：

ALTER SYSTEM SET deadlock_timeout = '5s';

如果没有明确指定单位，则默认以毫秒为单位。默认值通常为 1 秒（1s），这意味着系统会在锁等待一秒钟后触发死锁检查机制。

最佳实践

在高并发、锁操作频繁的系统中，适当增加 deadlock_timeout 的值可以减少不必要的死锁检查所浪费的时间，但会减慢真正死锁错误的报告速度。例如，可以将 deadlock_timeout 设置为 5 秒或更长的时间，以避免频繁的死锁检查。

当一个会话等待某个类型的锁的时间超过 deadlock_timeout 的值时，可以通过配置参数 log_lock_waits 来记录这些信息。这有助于监控和分析死锁情况。

示例配置：

log_lock_waits = on

不合理的查询和索引设计可能导致不必要的锁竞争和死锁。因此，应优化查询和索引设计，以减少锁冲突和死锁的发生。

优化 DELETE 操作

1. 禁用触发器

在执行大量 DELETE 操作之前，可以先禁用相关的触发器。这样可以避免在删除过程中触发不必要的操作，从而减少死锁的风险。

2. 使用 CTID 进行分批删除

通过使用 CTID（Contention Token ID）来分批删除数据行，可以有效减少单次 DELETE 操作中的锁竞争。例如，可以先查询出需要删除的 CTID 范围，然后分批进行 DELETE 操作，直到没有更多的 CTID 需要处理为止。

3. 软删除而非硬删除

考虑使用软删除的方式，即在物理上不直接删除数据行，而是在逻辑上标记这些行为已删除。这种方法可以在不影响其他事务的情况下进行数据清理，并且可以更容易地恢复被误删的数据。

4. 优化索引管理

合理设计和维护索引可以减少 DELETE 操作中的锁等待时间。过多的索引会增加写入开销，因此应根据实际需求合理配置索引数量和类型。

5. 调整锁等待策略

通过调整 PostgreSQL 的锁等待时间和死锁检测机制，可以减少不必要的死锁发生。例如，增加锁等待时间阈值，让系统更宽容地处理锁等待情况。

6. 批量处理

尽量将 DELETE 操作分解为多个小批量操作，而不是一次性删除大量数据。这可以减少单次操作中的锁竞争，从而降低死锁的可能性。

咨询锁与表级锁的对比

在 PostgreSQL 中，咨询锁（Advisory Lock）与表级锁相比，在以下场景下更有效避免死锁：

1. 需要确保所有应用遵循一致的锁获取顺序：咨询锁可以由应用程序显式地获取和释放，因此可以更容易地控制锁的获取顺序，从而避免死锁。
2. 减少锁的竞争：咨询锁可以用于应用程序之间的协调，而不是直接锁定数据库对象，从而减少锁的竞争和死锁的可能性。
3. 灵活的锁管理：咨询锁可以用于实现更复杂的锁管理策略，例如，可以使用咨询锁来实现读写锁、信号量等高级锁机制。

使用监控工具实时监控死锁

要配置和使用 Zabbix 来实时监控 PostgreSQL 数据库的死锁情况，可以按照以下步骤进行：

1. 创建监控用户

在 PostgreSQL 上创建一个专门用于监控的用户，并为其分配必要的权限。同时，确保 pg_hba.conf 文件中允许该用户通过网络访问数据库。

2. 下载监控脚本

访问 Zabbix 官方网站，下载适用于 PostgreSQL 的监控脚本。将这些脚本部署到 PostgreSQL 服务器上，以便它们能够定期检查数据库状态。

3. 配置 Zabbix Server

编辑 Zabbix Server 的配置文件（如 /etc/zabbix/zabbix_server.conf），确保其数据库连接配置正确，指向 PostgreSQL 数据库的位置。

4. 添加主机条目

在 Zabbix Web 界面中添加一个新的主机条目，关联系统自带的 Template DB PostgreSQL 模板。这一步骤可以帮助你快速设置监控项和触发器。

5. 配置 Zabbix Agent

如果需要在远程服务器上运行 Zabbix Agent，确保其正确安装并配置，以便能够收集 PostgreSQL 的相关数据。

6. 设置 deadlock_timeout

可以通过修改 PostgreSQL 的配置文件（如 postgresql.conf），设置 deadlock_timeout 参数为一个合理的值（例如 15 分钟），以捕捉潜在的死锁情况。

7. 查看监控数据

配置完成后，可以在 Zabbix Web 界面中查看 PostgreSQL 数据库的实时监控数据，包括死锁情况。如果需要更美观的数据展示，可以考虑将 Zabbix 数据导入到 Grafana 中进行进一步的可视化。

死锁对业务的影响

1. 性能问题

死锁会导致数据库操作停滞，严重影响系统的性能和可用性。当出现死锁时，数据库可能运行缓慢，甚至无法响应用户的