PostgreSQL死锁了怎么办及处理方法

时间:2023-02-03 慕枫技术笔记人气:0

什么是数据库死锁

在操作系统领域当中，死锁指的是两个或者两个以上的进程在运行的过程中，因为争夺共同的访问资源而相互等待阻塞，最终造成阻碍进程继续执行的一种阻塞现象。那么在数据库领域当中死锁又是怎样的表现形式呢？

如下图所示，假设事务A持有行1的共享锁，事务B持有行2的共享锁，那么此时事务A请求持有行2的排他锁，那么在事务B释放资源之前都处于阻塞等待的状态，同样的事务B请求持有行1的排他锁，在事务A 释放资源之前同样也是处于阻塞等待的状态。也就是说事务 B 完成之后事务 A 才能完成，而事务A的完成又依赖于事务B的完成，这就形成了循环依赖的问题，最终导致死锁情况的发生。

如何确定死锁位置

//先确定数据库有没有死锁情况发生
select * from pg_stat_activity where datname = 'product_db';

//查询可能锁了的表的oid
select oid from pg_class where relname='product';

//查询对应的pid
select pid from pg_locks where relation='oid'  //上面查询出来的oid

//取消或者终止对应的进程
select pg_cancel_backend(pid);
select pg_terminate_backend(pid)；

死锁的可能原因以及解决办法

以上分析了PostgreSQL出现死锁后如何定位分析，那么接下来就需要总结分析分析下数据库出现死锁情况的原因以及一般的应对解决办法。

1、索引使用问题导致的死锁问题

索引使用存在问题的话会导致死锁问题，假设在一个数据查询的事务当中，进行数据检索的时候没办法按照SQL中的where条件进行查询，因此导致了全表扫描，那么此时数据库表的行级锁会上升为表级锁。如果此时有多个未能按照where条件进行数据查询的事务存在，那么就容易导致数据库死锁问题。也就是说在数据库表数据量比较大的时候，对应进行数据查询的表没有建立索引或者说索引创建的不合理导致无法通过索引进行数据查询，只能通过全表索引，这样的场景下就容易产生死锁。

如何避免：
在进行数据查询的时候，对应的SQL语句不宜太过复杂，也就是说尽量避免多张表的关联查询。

2、不同事务之间的访问顺序问题

当用户A 访问数据库表A时，此时对表A加了共享锁，然后又访问数据库表B。而此时另一个用户B 访问表B，对表B加了共享锁，然后试图访问表A。但是用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，也就是说互相等待对方释放资源，从而导致了死锁的发生。

如何避免：
这种情况在实际项目中遇到的可能比较多，主要还是需要通过控制代码的执行逻辑，避免多表操作时同时锁住多个资源。

避免死锁的实战建议

（1）如果平台中存在大事务，尽量将其拆分为小事务。因为大事务一般操作的数据库表或者数据都比较多，因此造成死锁或者阻塞的概率就会相对较大。

（2）为数据库表设计合理的索引，尽量避免数据查询时索引未覆盖或者索引失效的情况，因为全表扫描会会导致给表中的数据行上锁，大大增加了数据库产生死锁的概率。

（3）如果业务允许，我们可以尝试将隔离级别调低，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

补充：下面在看下Postgresql死锁的处理

背景：

对表进行所有操作都卡住，原因可能是更新表时导致这个表死锁了，开始进行排查

解决一：查询pg_stat_activity有没有记录

pg版本10.2

select pid,query,* from pg_stat_activity where datname='死锁的数据库' and wait_event_type = 'Lock';
select pg_cancel_backend('死锁那条数据的pid值');##只能杀死select 语句, 对其他语句不生效
pg_terminate_backend('死锁那条数据的pid值');#select,drop等各种操作

执行后发现select和delete表时正常执行，但truncate和drop表时会一直运行，也不报错。

“drop table” 和 “truncate table” 需要申请排它锁"ACCESS EXCLUSIVE"，执行这个命令卡住时，说明此时这张表上还有操作正在进行，比如查询等，

那么只有等待这个查询操作完成，“drop table” 或"truncate table"或者增加字段的SQL才能获取这张表上的 "ACCESS EXCLUSIVE"锁，操作才能进行下去。

解决二：查询pg_locks是否有这个对象的锁

select oid,relname from pg_class where relname='table name';
select locktype,pid,relation,mode,granted,* from pg_locks where relation= '上面查询出来的oid';
select pg_terminate_backend('进程ID');

问题解决！！！

坑：一开始不知道pg_cancel_backend(‘死锁那条数据的pid值');##只能杀死select 语句, 对其他语句不生效，杀了进程查询发现还存在，反复杀反复存在，换了pg_terminate_backend(‘进程ID')问题就解决了。

PS：postgresql表死锁问题的排查方式

1.查询激活的执行中的sql,查看有哪些更新update的sql。

select *
from pg_stat_activity
where state = 'active';

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) = 'h5_game';

3. 杀掉死锁进程

select pg_terminate_backend(pid)
from pg_stat_activity
where state = 'active'
and pid != pg_backend_pid()
--and pid = 14172
and pid in (select a.pid
from pg_locks a
join pg_class b on a.relation = b.oid
where lower(b.relname) = 'news_content')

锁模式

/* NoLock is not a lock mode, but a flag value meaning "don't get a lock" */
#define NoLock                 0
 
#define AccessShareLock         1        /* SELECT */
#define RowShareLock          2        /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock        3        /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4       /* VACUUM (non-FULL),ANALYZE, CREATE
                                         * INDEX CONCURRENTLY */
#define ShareLock                5        /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock  6        /* like EXCLUSIVE MODE, but allows ROW
                                         * SHARE */
#define ExclusiveLock          7        /* blocks ROW SHARE/SELECT...FOR
                                         * UPDATE */
#define AccessExclusiveLock       8        /* ALTER TABLE, DROP TABLE, VACUUM
                                         * FULL, and unqualified LOCK TABLE */

加载全部内容