Pg批量更新数据产生的死锁问题

本文描述了在SpringKafka中两个并发执行的批量更新操作导致PostgreSQL死锁的问题,分析了死锁产生的原因,涉及事务、行锁和索引的影响,以及提出了解决方案——确保按相同字段顺序更新以避免死锁。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

两个服务同时批量更新相同Pg表的相同记录时,产生如下错误:

[2024-01-18 09:17:52,172  host: 173.16.34.10  ERROR  alarm  [org.springframework.kafka.KafkaListenerEndpointContainer#3-0-C-1]  com.zhongbao.alarm.service.impl.AlarmServiceImpl  - <ZG-COMMON-JS-ALARM>批量更新失败:]
org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 2345 waits for ShareLock on transaction 177149283; blocked by process 30377.
Process 30377 waits for ShareLock on transaction 177149284; blocked by process 2345.
  Hint: See server log for query details.
  Where: while updating tuple (2672,3) in relation "alarm_data_20240117"
### The error may exist in class path resource [mapper/AlarmDataMapper.xml]
### The error may involve com.zhongbao.alarm.mapper.AlarmDataMapper.updateAlarmBatch-Inline
### The error occurred while setting parameters

死锁的产生原因

两个更新事务(如批量update)在已获取锁(行锁)的情况下,都尝试去获取对方的锁(行锁),会互相等待,形成死锁 !死锁的详细信息,出现在服务器日志中。

推测:
1、基于update …的批量更新,在提交给Pg后,Pg自动生成批量更新的事务;
2、更新事务欲修改行数据时,会获取锁来锁定该行,直到事务执行完毕后释放所有的行锁(Pg会自动为更新添加行锁);
3、多个更新事务因修改记录相互等待对方的释放锁时,即发生死锁;
4、死锁后,直到其中一个事务超期结束,其锁被释放,死锁才打开;

查看Pg所有锁: SELECT * FROM pg_locks;
查看Pg的行锁: CREATE EXTENSION pgrowlocks; SELECT * FROM pgrowlocks('表名');

死锁的解决办法

The correct way to perform such operations is to lock resources in the same order.
For example: in this case, accounts(表) can be locked in ascending order of their numbers.即批量更改表数据,需按照相同字段的相同排序去批量修改,保证修改顺序即可。

   /// 顺序更新数据,以防止死锁
   oldAlarmDataList.sort(new Comparator<AlarmData>() {
       @Override
       public int compare(AlarmData o1, AlarmData o2) {
           return o1.getSid().compareTo(o2.getSid());
       }
   });
   startTime = System.currentTimeMillis();
   this.alarmAllMapper.updateAlarmAllBatch(oldAlarmDataList);
   log.info("更新消息数量:{}, 耗时:{}",
           oldAlarmDataList.size(), System.currentTimeMillis() - startTime);

死锁模拟(作者没模拟出来,MD…)

=> create table accounts (
	acc_no  integer,
	amount  integer
);
=> insert into accounts values(0,0),(1,1),(2,2),(3,3),(4,4);
=> create index on accounts(amount desc);
/// 自增函数,延迟1秒后加100
=> CREATE FUNCTION inc_slow(n numeric) RETURNS numeric AS $$
  SELECT pg_sleep(1);
  SELECT n + 100.00;
$$ LANGUAGE SQL;
/// 安装扩展函数
=> CREATE EXTENSION pgrowlocks;
///// 第一会话 顺序修改表记录
/// Since tuples on the table page are located in ascending order of the amount (exactly how we added them),
/// they will also be updated in the same order. 
/// explain命令输出结果中的"Seq Scan"表示顺序扫描,即全表扫描。
=> EXPLAIN (costs off) UPDATE accounts SET amount = inc_slow(amount);

|           QUERY PLAN         
|  ----------------------------
|   Update on accounts
|     ->  Seq Scan on accounts
|  (2 rows)
///// 第二会话 逆序修改表记录
/// 禁止顺序扫描
=> SET enable_seqscan = off;
/// 注意:这里的100如果写成100.00会因数据类型不一致导致索引失效 !!!
/// explain命令输出结果中的"Index Scan"表示索引扫描。
=> EXPLAIN (costs off)
UPDATE accounts SET amount = inc_slow(amount) WHERE amount > 100; 

|                         QUERY PLAN                       
|  --------------------------------------------------------
|   Update on accounts
|     ->  Index Scan using accounts_amount_idx on accounts
|           Index Cond: (amount > 100.00)
|  (3 rows)
/// 查看sql线程(pid)对记录的加锁情况
=> SELECT * FROM pgrowlocks('accounts');

当两个sql线程同时运行,其更新的记录"碰头"后,会产生死锁 !

其他

特别注意: 在执行"SET enable_seqscan = off;“之前,虽然已添加索引,但执行"UPDATE accounts SET amount = inc_slow(amount) WHERE amount > 100;”
时候,Pg依然进行的全表扫描!因此推测:对应较简单的查询,即使建立并使用索引过滤,Pg依然会进行全表扫描。

参考文档
https://blog.youkuaiyun.com/pg_hgdb/article/details/100653715

<think>嗯,用户问的是数据库死锁怎么解决。首先,我得先回忆一下数据库死锁的基本概念和常见原因。死锁是指两个或多个事务在执行过程中,因争夺资源而造成的一种互相等待的现象,如果没有外力介入,这些事务都无法继续执行。可能的原因包括事务之间的资源竞争、加锁顺序不一致,或者系统设计不合理导致循环等待。 接下来,我需要按照用户提供的系统级指令来组织回答。用户希望结构清晰,分步骤解决问题,所以可能需要分点说明。同时,要使用中文,并且数学表达式用$...$或$$...$$格式,不过在这个问题中可能不太涉及数学公式,但如果有涉及事务等待图或者算法的话,可能需要用LaTeX。 首先,可能需要介绍死锁的定义和原因,然后分步骤说明解决和预防的方法。解决死锁通常有检测与恢复、预防策略、避免策略,还有优化应用设计等。需要确保每个步骤都清晰,并且给出实际的例子或方法,比如设置锁超时、调整事务顺序等。 还要注意用户可能需要的是实际操作的解决方案,比如数据库配置参数,或者代码层面的调整。比如在MySQL中设置innodb_lock_wait_timeout,或者在代码中捕获死锁异常并重试事务。这些具体的技术细节需要准确,不能出错。 另外,用户可能对如何分析死锁原因感兴趣,比如查看数据库日志,使用SHOW ENGINE INNODB STATUS等命令。这部分可能需要详细说明,帮助用户诊断问题。 同时,要确保回答真实可靠,可能需要提到不同数据库系统的差异,比如Oracle、MySQL、PostgreSQL在处理死锁时的不同机制,但用户可能主要关注通用的方法,或者特定于某个数据库的解决方式。由于用户没有指定具体数据库,可能需要保持通用,但适当举例。 还要考虑预防措施,比如保持事务简短,按顺序访问资源,使用合适的索引减少锁冲突,这些都能有效减少死锁的发生概率。 最后,可能需要总结处理流程,比如先监控分析,再根据情况选择解决和预防策略。同时提醒用户死锁无法完全避免,但可以通过优化减少其发生。 在整理这些思路时,要确保结构清晰,分点明确,使用用户容易理解的语言,避免过于技术化的术语,但必要时给出术语并简单解释。同时,检查是否有遗漏的关键点,比如锁超时、死锁检测机制、事务重试等。</think>### 数据库死锁的解决与预防方法 #### 一、死锁的定义与原因 **死锁**是指两个或多个事务因争夺资源而陷入相互等待的状态,导致无法继续执行。常见原因包括: 1. **资源竞争**:事务同时请求相同资源(如行锁、表锁)。 2. **加锁顺序不一致**:事务以不同顺序获取锁,形成循环依赖。 3. **长事务与高并发**:长时间持有锁或高并发场景易引发死锁。 --- #### 二、解决方案:主动检测与恢复 1. **启用数据库死锁检测** - 多数数据库(如MySQL、PostgreSQL)内置死锁检测机制,发现死锁后自动终止“代价较小”的事务。 - **示例**:MySQL通过参数`innodb_deadlock_detect=ON`开启检测。 2. **设置锁超时时间** - 事务等待锁超过阈值后自动回滚,避免无限等待。 - **示例**:MySQL设置`innodb_lock_wait_timeout=50`(单位:秒)。 3. **手动干预** - 通过日志定位死锁事务,强制终止其一: ```sql -- PostgreSQL终止进程 SELECT pg_terminate_backend(pid); ``` --- #### 三、预防策略:减少死锁概率 1. **统一加锁顺序** - 所有事务按固定顺序访问资源(如按主键升序更新数据)。 - **示例**:对表A和表B的操作,始终先锁表A再锁表B。 2. **缩短事务长度** - 事务尽量简短,减少锁持有时间。避免在事务中执行耗时操作(如文件IO)。 3. **使用低隔离级别** - 降低隔离级别(如`READ COMMITTED`),减少锁竞争。但需权衡数据一致性。 4. **合理设计索引** - 优化查询索引,减少全表扫描导致的锁范围扩大。 5. **使用乐观锁** - 通过版本号(如`CAS`机制)替代悲观锁,适用于低冲突场景。 --- #### 四、应用层优化 1. **重试机制** - 捕获死锁异常(如MySQL的`1213`错误码),自动重试事务。 ```java // Java示例:重试3次 int retries = 3; while (retries-- > 0) { try { executeTransaction(); break; } catch (DeadlockException e) { rollback(); } } ``` 2. **批量提交** - 将多个操作合并为批量提交,减少锁竞争频率。 --- #### 五、诊断与分析工具 1. **查看数据库日志** - MySQL:`SHOW ENGINE INNODB STATUS`输出死锁详细信息。 - PostgreSQL:日志中记录`deadlock detected`及事务ID。 2. **监控工具** - 使用Prometheus+Grafana监控锁等待情况,或APM工具(如SkyWalking)分析慢事务。 --- #### 六、总结流程 1. **监控**:通过日志和工具定位死锁。 2. **解决**:终止事务或设置超时。 3. **预防**:优化事务设计、索引和并发控制。 4. **容错**:代码层添加重试逻辑。 > **关键原则**:死锁无法彻底避免,但可通过降低冲突概率和快速恢复减少影响。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值