postgresql中死锁问题排查

最近在做一个业务数据导入导出的功能需求，在导入过程中出现了系统卡死的现象。怀疑为数据库死锁导致，具体排查流程如下。

排查流程

查询ACTIVITY的状态等信息

SELECT T
	.PID,
	T.STATE,
	T.QUERY,
	T.WAIT_EVENT_TYPE,
	T.WAIT_EVENT,
	T.QUERY_START    
FROM
	PG_STAT_ACTIVITY T
WHERE
	T.DATNAME = '数据库用户名';

avatar

上面查询结果中：pid就是ACTIVITY的唯一标识，state就是活动状态，query就是正在执行的sql语句，query——start就是开始执行的时间。

查询死锁的ACTIVITY

SELECT T
	.PID,
	T.STATE,
	T.QUERY,
	T.WAIT_EVENT_TYPE,
	T.WAIT_EVENT,
	T.QUERY_START    
FROM
	PG_STAT_ACTIVITY T 
WHERE
	T.DATNAME = '数据库用户名' 
	AND T.WAIT_EVENT_TYPE = 'Lock';

将第二条查询语句的pid字段的数字值记录下来，执行下面的查询语句可以解锁：
1
select PG_CANCEL_BACKEND('6984');

上面的查询语句，执行了pg_cancel_backend()函数，该函数是取消后台操作，回滚未提交事物的用途。

解决idle in transaction
1
select pg_terminate_backend ('21967')
小结

根据以上流程，排查出truncate语句与select语句执行过程中出现了Lock清空。追踪到业务场景中，在执行导入的过程中存在权限校验，校验需要查询当前用户的登录状态。登录过程包含sql如下：

1
2
3

select id,user_name from user where id=1;

truncate table user reset identity cascade;

其中select语句的状态一直是“idle in transaction”的状态。

业务逻辑中存在监控请求，每隔10s请求一次，也存在权限校验的场景：

1	select id,user_name from user where id=1;

所以后续的sql也是处于Lock的状态。

继续排查“idle in transaction”的问题

PostgreSql中一直有连接长时间处于idle in transaction的问题

数据库连接的几种状态

pg_stat_activity 是一张postgresql的系统视图，它的每一行都表示一个系统进程，显示与当前会话的活动进程的一些信息，比如当前会话的状态和查询等。它的state字段表示当前进程的状态，一共有六种：

Active(活动): 进程正在执行某个语句
Idle(空闲): 进程正在等待客户端的指令
idle in transaction(事务空闲):进程在处理事务的过程中，但当前没有执行任何语句
idle in transaction (aborted)(事务空闲-退出):除了事务中声明一个错误外，其余情况与idle in transaction相同
fastpath function call(快速通道函数调用): 后台正在执行某个快速通道函数
Disabled(禁用): 报告状态被禁用

Postgresql在9.6版本提供了idle_in_transaction_session_timeout 参数，用于释放一直处于idle in transaction状态下的连接。
尝试在postgresql.conf 文件中添加idle_in_transaction_session_timeout参数控制，参数单位为毫秒

1	idle_in_transaction_session_timeout=30000

问题最终解决方案

导入用户数据功能时，业务加锁处理；

数据库修改相关配置，添加

1	idle_in_transaction_session_timeout=30000

排查流程

PostgreSql中一直有连接长时间处于idle in transaction的问题

问题最终解决方案

参考文章