PostgreSql死锁问题处理_pgsql数据表死锁

背景

并发场景下，大批量消费mq消息更新到数据库时，没有对数据进行统一排序导致的死锁问题。通过报错日志定位到了具体的业务代码以及导致死锁的sql。

即执行sql发生了如下的情况：

事务A更新数据1、2，事务B更新数据2、1，出现了循环等待锁的情况

Session_A执行：update table_name set column = xxx WHERE id = 1;

Session_B执行：update table_name set column = xxx WHERE id = 2;

Session_A执行：update table_name set column = xxx WHERE id = 2;

Session_B执行：update table_name set column = xxx WHERE id = 1;

很多情况下，数据库发生死锁都是代码逻辑存在bug造成的，需要我们对业务代码有一定的了解才能更好的排查出问题，特别是数据量很大的时候，成千上万条日志你根本无从下手，需要结合代码推测可能的原因，用排除法才能更快的解决问题。

解决步骤

1. 查询正在执行/空闲的sql（非必须）

SELECT * from pg_stat_activity WHERE state <> ''

从上面的结果可以看出是trs_ps_media这张表的数据在更新时发生了死锁。

2. 查询表中存在的锁

select a.locktype, a.database, a.pid, a.mode, a.relation, b.relname
from pg_locks a join pg_class b on a.relation = b.oid
where lower(b.relname) = 'table_name';

3. 锁模式（类型）

当我们对表的结构或者数据进行增删改查等操作时，数据库可能会先加锁（行锁/表锁），事务对同一资源的竞争或者操作顺序的不当就可能会造成死锁，通过取消或者杀死锁事务进程基础死锁。

4. 解除死锁

取消函数和中断函数

pg_cancel_backend()：取消后台操作，回滚未提交事物，不会释放数据库连接；

pg_terminate_backend()：中断session，回滚未提交事物，释放数据库连接；

需要注意，pg_cancel_backend()只能杀死select 语句，对其他语句不生效，这时候改用pg_terminate_backend()即可，如果在某些时候pg_terminate_backend()也不能杀死session，那么可以在os层面：kill -9 pid 杀死进程。

杀死锁事务进程

select pg_cancel_backend('8954'); -- 8954为pid

批量杀死锁事务进程（当锁比较多的时候）

-- 1、查询指定表的oid（全局对象标志符/表的唯一id）

select oid from pg_class where relname='table_name';

-- 2、批量解除死锁（以下358009为步骤1中查询到的死锁表的oid）

select 'select pg_cancel_backend( '''|| pid ||''');' pid 
from pg_locks where relation='358009'