Flask应用中FOR UPDATE SKIP LOCKED并发失效问题排查

问题现象 #

在并发连接场景中，执行以下SQL语句时，SKIP LOCKED未达到预期效果：

UPDATE note_queue
SET seq_number = seq_number + 1, rand = random()
WHERE id IN (
SELECT id
FROM note_queue
ORDER BY seq_number, rand
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING id;

表定义 #

Table "public.note_queue"
   Column   |  Type  | Collation | Nullable | Default  
------------+--------+-----------+----------+----------
 id         | bigint |           |          | 
 seq_number | bigint |           |          | 0
 rand       | real   |           |          | random()
Indexes:
    "idx_note_queue" btree (seq_number, rand)
    "idx_note_queue_id" btree (id)

并发测试结果 #

用100并发连接模拟请求后，查询结果如下：

changesets=# select seq_number, count(*) from note_queue group by seq_number;
 seq_number | count  
------------+--------
          0 | 499832
          1 |   4073
          2 |    266
          3 |      3
(4 rows)

预期同一时间仅存在两个不同的seq_number值，但实际出现了0、1、2、3多个层级，只有单连接加threading.Lock()时符合预期。

问题根源 #

1. 并发下的同层级批量升级
   逻辑上想优先选取seq_number最小的记录升级，但并发场景中，大量事务会同时读取到seq_number=0的未锁定记录，各自锁定不同的行并将其seq_number从0改为1，短时间内就会出现大量seq_number=1的记录。

2. SKIP LOCKED触发跳级更新
   当seq_number=0的记录被全部锁定后，后续事务的子查询会跳过所有锁定的行，转而选取seq_number=1的记录进行升级；同理，当seq_number=1的记录也被大量锁定时，又会出现升级到3的情况，最终产生多层级的seq_number。

3. 基于行当前值的更新逻辑缺陷
   seq_number = seq_number + 1是基于单条记录的当前值做更新，而非全局统一的递增序列，这导致不同行的seq_number会从同一初始值同步升级，无法保证全局仅存在两个层级。

解决思路 #

如果目标是保证全局仅存在最多两个seq_number层级（未处理的基础层级和正在升级的层级），可调整逻辑：

• 先获取当前全局最小的seq_number值，批量锁定该层级的所有记录再统一升级，避免多个事务同时操作不同层级；
• 改用全局序列（nextval()）生成seq_number，确保每个记录的seq_number是全局唯一的递增数值，从根本上避免同层级批量升级的问题；
• 如果必须单条处理，可在事务中先锁定全局最小的seq_number对应的批次，再选取单条记录升级，防止跳级。

内容的提问来源于stack exchange，提问作者Michał B.