PostgreSQL 排查链接锁问题常用SQL语句

1. 背景

        正常情况下，PostgreSQL只要连上了就能愉快地使用了，但是在一些特别的场景，如压测或者某些不可描述的异常，会出现数据库连接异常的情况，比如链接数占满了，无法释放的情况，这时，我们会猜测是不是数据库死锁了？或者链接数达到最大值了？

2. 常用SQL语句

2.1. 查询PostgreSQL设置的最大连接数

-- 查询最大连接数
SHOW max_connections;

2.2. 查询当前使用中的连接数

-- 查询当前连接数
SELECT COUNT(*) FROM pg_stat_activity;

针对上述情况，我列举了几个常用的sql语句，用于排查“死锁”问题

2.3. 查询当前所有连接数据库用户和用户其连接数

-- 查询当前所有连接数据库用户和用户其连接数（按用户名分组统计）
SELECT usename, COUNT(*) FROM pg_stat_activity GROUP BY usename ORDER BY count DESC;

-- 查询特定数据库用户当前连接数(your_db_user_name替换成自己的数据库名称)
SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'your_db_user_name';

2.4. 统计所有数据库和其大小

# 查询数据库和每个数据库的大小
SELECT 
    pg_database.datname AS "database_name", 
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size_in_mb 
FROM pg_database 
ORDER BY pg_database_size(pg_database.datname) DESC;

2.5. 查询是否存在锁表记录

-- 显示出没有被授予的锁，也就是等待的锁
SELECT * FROM pg_locks WHERE granted = false;
-- 等价如下SQL
SELECT * FROM pg_locks WHERE granted = 'f';

注意：在PostgreSQL中，布尔类型的值可以用'true'，'false'，'t'，或者'f'来表示。

• 'true'或者't'表示真；
• 'false'或者'f'表示假。

所以当你在查询结果中看到't'，那就和'true'等价，都表示该进程已经被授予了锁。同样的，'f'和'false'等价，都表示该进程仍在等待锁。

2.6. 查询详细的锁信息

-- 查询更详细的锁信息，包括哪些语句在等待锁
SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age", 
         a.pid 
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
WHERE NOT l.GRANTED;

每一行都表示一个等待的锁。大部分列都是自解释的，但是这里要提一下age列：当一个事务等待一个锁，但是却没有进展时，会显示这个进程已经多长时间没有作出响应了。

2.7. 如何解锁？

2.7.1. 定位并终止阻赛的事务

        在一般情况下，解锁最直接的方式就是终止导致锁表的进程或事务。首先要确定持有锁的事务，可以通过如下语句：

-- 确定持有锁的事务
SELECT * FROM pg_stat_activity WHERE waiting = 't';

2.7.1.1 pg_locks和pg_stat_activity的区别？

        这两个视图查询的内容其实是不同的。具体选择用哪个视图，取决于你需要查询的信息。

SELECT * FROM pg_stat_activity;

pg_stat_activity视图包含了所有连接到数据库服务器的活跃进程的信息。

这个视图通常用于查看正在运行的查询以及其它会话信息。如果需要诊断阻塞指定进程的问题，该视图是一个非常好的起点。在这个查询中，waiting='t'的情况指的是，这个进程正在等待获取一把被其它进程持有的锁。

SELECT * FROM pg_locks WHERE granted = false;

pg_locks视图显示了所有活跃的锁以及等待锁的进程。granted = false表示锁尚未被授予，即有进程正在等待获取此锁，可能是由于有其它进程持有了这把锁而未能立即获取。

总的来说，如果你需要查看生成锁的查询，可能用pg_stat_activity会更有用。如果你想看哪些锁正在被等待，pg_locks会更有用。两者视图的使用应根据实际需求和场景来选择。

2.7.2. 查找到pid后，按pid终止进程

-- 按PID终止进程
SELECT pg_terminate_backend(PID);

PID需要替换成实际的进程ID。

1. 如果不能直接终止导致锁表的事务，就需要查找并修复导致阻赛的程序逻辑错误。

2. 如果是长期的阻碍，可能需要考虑数据库的性能优化或者硬件的升级等更深入的措施。

终止事务有可能会丢失数据或者让数据状态变得不一致。在大部分情况下，最好先找出为什么会发生锁表情况，然后修复引发问题的原因，而不是简单的直接终止事务。

2.7.2.1 pg_cancel_backend和pg_terminate_backend的区别？

pg_cancel_backend和pg_terminate_backend函数类似于UNIX中的SIGINT和SIGTERM信号。

• pg_cancel_backend(PID)：这个函数会发送一个请求来取消后端当前的查询。PID是你想要取消查询的进程的PID。这个效果类似于UNIX的SIGINT信号，查询会尽可能的安全取消，比如说在一个安全的点回滚到开始状态。

• pg_terminate_backend(PID)：这个函数会中断一个连接和该连接上的任何活动。PID是你想要终止的后端进程的PID。这个函数发送的是一个terminate(BSIGTERM)信号给指定的后端，使得后端进程立即退出。

总结一下，pg_cancel_backend试图取消后端的执行的查询但是保持连接，而pg_terminate_backend则直接断开指定的连接。

2.8. 参考

ChatGPT