PostgreSQL多线程并行更新数据：避免死锁的正确查询写法

我有一组id与first_name的配对数据：

1  | Oliver
2  | James 
...

需要更新名字包含字母J的行，并返回更新后的id，且该请求会以多线程方式执行。我写了如下查询，但了解到这种情况下执行顺序无法保证，可能引发死锁。我可以预先对id数组排序，请问如何编写能安全在多线程执行的正确查询？

原查询：

WITH data(id, new_first_name) AS (
    SELECT * FROM UNNEST(ARRAY[1,2], ARRAY['Oliver','James'])
)
UPDATE table1 t1
SET first_name = data.new_first_name
FROM data
WHERE t1.id = data.id
and data.new_first_name like 'J%'
RETURNING t1.id;

要解决死锁问题，核心就是让所有线程的更新操作按统一的顺序（比如id从小到大）去获取行锁，这样就不会出现互相等待锁的情况。调整后的安全查询如下：

WITH data(id, new_first_name) AS (
    -- 先过滤出需要更新的数据，再按id升序排序
    SELECT id, new_first_name
    FROM UNNEST(ARRAY[1,2], ARRAY['Oliver','James']) AS t(id, new_first_name)
    WHERE new_first_name LIKE 'J%'
    ORDER BY id ASC
)
UPDATE table1 t1
SET first_name = data.new_first_name
FROM data
WHERE t1.id = data.id
-- 强制按id升序执行更新，确保锁的获取顺序统一
ORDER BY t1.id ASC
RETURNING t1.id;

关键要点 #

• 先在CTE里过滤并排序：把需要更新的行（名字含J）筛选出来后，按id升序排列，保证后续更新的数据源是有序的。
• 更新时加ORDER BY：PostgreSQL支持在UPDATE语句末尾加ORDER BY，强制按指定顺序更新行，这样每个线程都会按相同的顺序获取锁，彻底避免死锁。
• 双重保障：如果你已经预先对传入的id数组做了排序，加上查询里的排序能进一步确保顺序不会出错，降低风险。

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