PostgreSQL出现死锁该如何解决-数据库

昨天在对一张表执行一条update语句的时候,没有修改成功,直接终止执行,就疯狂点击执行,执行了很多次这条语句导致了表被死锁了,这篇文章主要给大家介绍了关于PostgreSQL出现死锁该如何解决的相关资料,需要的朋友可以参考下

什么是数据库死锁

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

在理解数据库死锁之前，我们先来明确下数据库的锁到底是什么？有过Java编程经验的同学都知道，Java中的锁是为了解决共享数据的并发访问安全问题，防止并发访问导致的共享数据出现错乱。那么在数据库领域，数据库中的锁又是来干什么的呢？实际上在数据库中所也是解决并发问题。假如在同一时刻，可能存在多个事务对同一张表的同一个字段进行数字的加减操作，如果没有任何的控制措施也同样会导致各种各样的数据一致性问题。因此数据库的锁实际上也是为了保证数据一致性的一种手段，对可能存在的并发操作进行控制。

下面以一个例子来进行说明，假设有这样两个事务，事务A中包含如下语句：

UPDATE user SET name = '小慕' where id = 1
UPDATE product SET price = price * 10 WHERE id = 2

事务B中包含如下语句：

UPDATE product SET price = price * 100 WHERE id = 2
UPDATE user SET name = '小枫' WHERE id = 1

如果这两个事务并发执行，那么他们可能存在如下的执行情况，当事务A执行的时候，首先运行了查询语句：

UPDATE user SET name = '小慕' where id = 1

相当于事务A给id为1的数据行加上了排他锁，但是事务并没有执行完也就是说此时事务A持有user表的id为1的排他锁，排他锁的特性就是此时其他事务不能对数据进行删除和修改，因此只有等待事务结束释放锁之后才能重新获取。

IiBzcmM9

此时事务B执行更新语句获取了product表id为2的排他锁，接着事务B开始执行user表的update语句，需要获取user表的id为1的排他锁。但是此时事务A并未提交，因此事务A持有表user的id为1的排他锁，事务B只有乖乖阻塞等待事务A释放锁。而此时事务A执行update语句，需要获取product的id为2的排他锁，但是此时事务B持有该排他锁，因此也需要等待事务B锁释放。

UPDATE product SET price = price * 10 WHERE id = 2

IiBzcmM9

事务A在等待事务B结束释放锁，而事务B又在等待事务A释放锁，最终陷入了互相等待的情况也就是所谓的死锁。

IiBzcmM9

那么数据库出现死锁又会导致什么问题呢？数据库死锁会导致严重的性能问题，可能平台因为数据库死锁而导致运行缓慢，严重影响用户正常使用业务，因此如果出现数据库死锁情况需要及时发现以及解决。

定位死锁

//先确定数据库有没有死锁情况发生
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出现死锁后如何定位分析，那么接下来就需要总结分析分析下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锁造成的死锁。

（4）在我们自己的代码中，尽量以一致的顺序获取对象上的锁，避免事务中SQL交互执行，从而降低死锁发生的概率。

附：数据库中常见的死锁原因与解决方案

1. 事务之间对资源访问顺序的交替

出现原因：

一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源

2. 并发修改同一记录

出现原因：主要是由于没有一次性申请够权限的锁导致的。

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项目中经常发生。

解决方法：

a. 乐观锁，实现写-写并发

b. 悲观锁：使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

3. 索引不当导致的死锁

出现原因：

如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

另外一种情况是由于二级索引的存在，上锁的顺序不同导致的

解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。