【踩坑实录】-pg锁表

目录

一、背景

二、查询是否锁表

三、释放表锁

四、简易版解锁

五、生成批量删除死锁进程sql       

六、扩展-发生死锁的原因

---

一、背景

使用pg表时报错 ： 

ERROR: deadlock detected

二、查询是否锁表

select
    t3.datname         as 数据库名,
    t2.relname         as 表名,
    t1.pid             as sql线程号,
    t3.usename         as 用户名,
    t3.client_addr     as 客户端地址,
    t3.query_start     as 查询开始时间,
    t3.wait_event_type as 等待事件类型 , --Lock死锁
    t3.wait_event      as 等待事件,
    t3.state           as 状态,
    t1.mode            as 锁类型,
    t1.granted         as 是否持锁,
    t1.fastpath        as 是否通过快速路径获锁,
    t3.query           as 查询语句
from pg_locks t1
left join pg_class t2 
    on t1.relation = t2.oid
left join pg_stat_activity t3 
    on t1.pid = t3.pid
where t1.locktype ='relation'
;

三、释放表锁

--任意一个都可
select pg_terminate_backend('pid');
select pg_cancel_backend('pid');

示例：
select pg_terminate_backend('123')；
select pg_cancel_backend('456')；

四、简易版解锁

--简单版三步
--1.根据表名查出oid
select oid from pg_class where relname='lock_table_name'; 
--2.根据oid查出pid
select pid from pg_locks where relation='123456'; 
--3.解锁
select pg_cancel_backend('456')； 

五、生成批量删除死锁进程sql       

select 'select pg_cancel_backend( '''|| pid ||''');' pid 
from pg_stat_activity 
where datname='database_name' 
  and wait_event_type = 'Lock';

六、扩展-发生死锁的原因

1.长时间运行的事务

一个事务长时间运行而未提交或回滚，会占用大量资源并锁住相关的数据行或表

2.资源不足

当多个事务同时对同一张表进行大量并发更新操作时，容易产生锁争用问题，导致锁表

3.索引

当对包含索引的表进行更新操作时，数据库需要同时更新索引，这会导致更多的锁争用