如何锁定PostgreSQL表以阻止特定process_id插入直至状态变为completed？

核心需求回顾 #

• 同一process_id的新记录插入必须等待该ID的现有pending记录变为completed后才能执行
• 不同process_id的插入请求可以并发执行
• 当process_id的记录状态变为completed后，该ID的新插入请求可正常触发

方案一：行级锁+事务实现 #

直接基于现有表结构，通过行级锁实现同一process_id的串行控制：

操作步骤 #

1. 开启事务，查询目标process_id的最新记录（按timestamp倒序），并对这条记录加排他锁（FOR UPDATE）
2. 如果查询到的记录状态为pending，当前会话自动阻塞，直到该记录状态被更新为completed或failed
3. 锁释放后，插入新的pending记录
4. 提交事务

示例SQL代码 #

BEGIN;

-- 锁定该process_id的最新记录，存在pending则阻塞
SELECT process_id, state
FROM your_table_name
WHERE process_id = 'target_process_id'
ORDER BY timestamp DESC
LIMIT 1
FOR UPDATE;

-- 插入新的pending记录
INSERT INTO your_table_name (process_id, state, timestamp)
VALUES ('target_process_id', 'pending', NOW());

COMMIT;

补充说明 #

• 若目标process_id无任何记录，SELECT无返回结果，直接执行插入即可
• 原有pending记录更新为completed时，行锁自动释放，等待中的插入会话继续执行
• 不同process_id的查询互不锁定，天然支持并发

方案二：Advisory Lock（建议锁）实现 #

如果需要脱离现有记录的锁控制，可使用PostgreSQL的advisory锁直接针对process_id加锁：

操作步骤 #

1. 将字符串类型的process_id通过hashtext()转换为整数（若为整数类型可直接使用）
2. 获取该整数对应的排他advisory锁，同一process_id的会话会阻塞，不同ID不受影响
3. 插入新的pending记录
4. 进程执行完成后，更新记录状态为completed，再释放advisory锁

示例SQL代码 #

-- 针对字符串类型process_id生成哈希锁
SELECT pg_advisory_lock(hashtext('target_process_id'));

-- 插入pending记录
INSERT INTO your_table_name (process_id, state, timestamp)
VALUES ('target_process_id', 'pending', NOW());

-- 进程完成后更新状态
UPDATE your_table_name
SET state = 'completed'
WHERE process_id = 'target_process_id'
AND timestamp = (SELECT MAX(timestamp) FROM your_table_name WHERE process_id = 'target_process_id');

-- 释放锁
SELECT pg_advisory_unlock(hashtext('target_process_id'));

补充说明 #

• Advisory锁是会话级别的，会话意外中断时PostgreSQL会自动释放锁，避免死锁
• 首次插入某process_id时也能确保串行控制，无需依赖现有记录

注意事项 #

• 必须确保进程执行完成后更新状态为completed，否则会导致后续插入请求永久阻塞
• 可根据业务需求决定failed状态是否允许直接插入新记录（比如在查询时过滤failed记录）
• 测试时可用多会话模拟同一/不同process_id的插入请求，验证锁的有效性

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