PostgreSQL 9.5.10生产环境两条更新语句死锁问题求助

老哥，我来帮你捋捋这个生产环境里的死锁问题。先把你没写完的更新语句补全，方便咱们分析：

update entitlements.stream_general sg 
set stream_offset_id = nextval('entitlements.stream_general_stream_offset_id_seq'), 
    should_update_offset_id = false 
from (select id, topic, stream_id 
      from entitlements.stream_general 
      where should_update_offset_id = true 
      limit 1000) sg2 
where sg.id = sg2.id 
  and sg.topic = sg2.topic 
  and sg.stream_id = sg2.stream_id;

可能触发死锁的核心原因 #

• 行锁获取顺序不一致：你的子查询用了limit 1000但没加order by，PostgreSQL每次执行时返回的行顺序可能随机变化。当多个会话同时跑这个更新时，比如会话1先锁行A再锁行B，会话2先锁行B再锁行A，就会形成循环等待，直接触发死锁。
• 并发查询重叠数据集：因为子查询没有加锁，多个会话可能同时查到同一批should_update_offset_id = true的行，更新时互相等对方释放锁，时间一长就容易死锁。
• 锁持有时间过长：如果这个更新是在一个大事务里，或者后续还有其他操作，锁会被持有更久，增加了锁冲突的概率。

针对性的解决办法 #

1. 强制统一行锁顺序（最关键） #

给子查询加上稳定的order by，比如用主键id或者唯一组合键，确保所有事务都以相同的顺序获取行锁，从根源上避免循环等待。修改后的SQL：

update entitlements.stream_general sg 
set stream_offset_id = nextval('entitlements.stream_general_stream_offset_id_seq'), 
    should_update_offset_id = false 
from (select id, topic, stream_id 
      from entitlements.stream_general 
      where should_update_offset_id = true 
      order by id  -- 用主键或唯一键排序，保证顺序稳定
      limit 1000) sg2 
where sg.id = sg2.id 
  and sg.topic = sg2.topic 
  and sg.stream_id = sg2.stream_id;

2. 跳过已锁定的行（适合允许延迟处理的场景） #

如果业务上允许暂时跳过被其他会话锁定的行，可以在子查询里加for update skip locked，这样事务只会处理当前无锁的行，不会等待锁，自然不会死锁。注意这可能导致部分行需要后续轮询处理：

update entitlements.stream_general sg 
set stream_offset_id = nextval('entitlements.stream_general_stream_offset_id_seq'), 
    should_update_offset_id = false 
from (select id, topic, stream_id 
      from entitlements.stream_general 
      where should_update_offset_id = true 
      order by id 
      limit 1000 
      for update skip locked) sg2 
where sg.id = sg2.id 
  and sg.topic = sg2.topic 
  and sg.stream_id = sg2.stream_id;

3. 优化查询效率，缩短锁持有时间 #

• 给should_update_offset_id字段加索引，让子查询快速定位目标行，减少扫描时间：

create index idx_stream_general_should_update on entitlements.stream_general(should_update_offset_id);

• 确保这个更新是单独的小事务，不要在事务里加其他无关操作，尽快释放锁。

死锁排查小技巧 #

如果死锁还没解决，你可以：

• 去PostgreSQL的日志里找死锁报告，里面会详细列出参与死锁的事务、各自持有的锁和等待的锁，这是定位问题的关键。
• 实时查询pg_locks系统视图，看看当前哪些会话在等锁，以及锁的类型和对象：

select * from pg_locks where not granted;

内容的提问来源于stack exchange，提问作者mdev