Postgres执行update查询导致Lock Wait飙升、CPU占满问题求助

• 索引不匹配导致锁范围过大：当前UPDATE语句的WHERE条件依赖resource_id、date、start_time、end_time四个字段，但已建索引包含无关的node_id字段，且缺少start_time、end_time两个筛选字段。数据库执行UPDATE时无法通过索引精准定位目标行，只能通过范围扫描过滤剩余条件，过程中会加范围锁/GAP锁，锁定的行数远大于实际需要更新的行数，高并发下大量请求出现锁等待。
• 不存在的记录更新额外加锁：直接执行UPDATE时，如果目标记录不存在，数据库为了避免幻读会加GAP锁，进一步放大锁冲突的概率，这类无效请求也会占用大量CPU资源。
• 同resource_id事件并发更新冲突：同一个resource_id的不同时段事件被不同线程并行消费时，即使索引正确，也会因为同时操作同resource_id的关联行出现锁排队，并发量越高冲突越严重。

1. 优先修正索引（最高优先级） #

新建完全匹配UPDATE WHERE条件的索引，确保数据库可以精准定位单行记录，只加行锁避免范围锁：

CREATE INDEX idx_resource_update ON resource(resource_id, date, start_time, end_time);

原resource_update_select索引如果无其他业务使用可以直接删除，避免优化器选错索引。同时注意检查UPDATE语句的字段名是否正确（示例中的endTime和表结构的end_time字段名不一致会直接导致索引失效）。

2. 优化记录不存在的处理逻辑 #

不需要先做无锁SELECT再判断，根据业务场景选择对应方案：

• 如果业务仅更新已存在的记录：使用带行锁的查询判断存在后再更新，避免无效UPDATE加GAP锁：

  -- 先查询，走新建的精准索引，只加共享行锁不会影响其他更新
  SELECT 1 FROM resource WHERE resource_id = ? AND date = ? AND start_time = ? AND end_time = ? FOR SHARE;
  -- 只有查询返回结果时再执行UPDATE
  

• 如果业务要求记录不存在时新增：直接用原子Upsert操作，减少一次数据库交互同时避免并发插入冲突：
  • MySQL语法：

  INSERT INTO resource (resource_id, date, start_time, end_time, available, updated_at, created_at)
  VALUES (?, ?, ?, ?, ?, NOW(), NOW())
  ON DUPLICATE KEY UPDATE available = VALUES(available), updated_at = VALUES(updated_at);
  

  • PostgreSQL语法：

  INSERT INTO resource (resource_id, date, start_time, end_time, available, updated_at, created_at)
  VALUES (?, ?, ?, ?, ?, NOW(), NOW())
  ON CONFLICT (resource_id, date, start_time, end_time) DO UPDATE 
  SET available = EXCLUDED.available, updated_at = EXCLUDED.updated_at;
  

  注意需要先给(resource_id, date, start_time, end_time)建唯一约束才能使用Upsert。

3. 消费侧做分区收敛 #

GCP Pub/Sub支持按resource_id做消息分区，同一个resource_id的所有事件会被分配到同一个分区，同一个分区的消息仅会被同一个消费线程/Pod消费，从业务侧彻底消除同一resource_id的事件并发更新的锁冲突。

4. 批量合并更新 #

如果业务允许10~50ms的延迟，消费端攒批后按(resource_id, date, start_time, end_time)去重，只保留每个唯一键的最新事件，再批量执行Upsert，可大幅降低数据库的请求量，进一步减少锁冲突概率。

5. 数据库锁参数优化 #

如果使用MySQL数据库，可将事务隔离级别从默认的REPEATABLE READ调整为READ COMMITTED，该级别会自动消除GAP锁，在绝大多数业务场景下不会影响数据一致性，可进一步降低锁冲突概率。

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