postgresql - 保护 CTE 中的关键资源表(WITH 子句)

Question

我正在使用我更新的中间表，以确保不能在不能同时访问的关键表上处理其他并发操作。

事务 1

BEGIN
UPDATE locktable
/* Do some stuff */
...
COMMIT

并发事务2

BEGIN
Update locktable
/* Do some other stuff */
...
COMMIT

这样我可以确定事务 1 和事务 2 是原子的。

出于简化和性能原因，我将代码更改为 WITH 子句语句。我想知道是否可以像 CTE 一样保证操作的原子性。

CTE 简化示例:

事务 1

WITH 

lock_op AS (
UPDATE locktable
...
RETURNING id),

some_stuff AS
(
/* Do insert and update operations with RETURNING clause*/
...
)

SELECT * 
FROM some_stuff
WHERE EXISTS (SELECT 1 FROM lock_op)

并发事务2

WITH 

lock_op AS (
UPDATE locktable
...
RETURNING id),

other_stuff AS
(
/* Do insert and update operations with RETURNING clause*/
...
)

SELECT * 
FROM other_stuff
WHERE EXISTS (SELECT 1 FROM lock_op)

基本上，我想知道“SELECT 1 FROM lock_op”是否在来自 some_stuff 和 other_stuff 的任何 INSERT 和 UPDATE 之前启动，因此，是否正在保护我的关键数据暂时由 WITH 范围分隔的事务？

Answer 1

您在这里没有相同的订购保证。没有 promise lock_op 的查询将在 some_stuff 的查询之前执行。

除此之外还算可以。行锁在 lock_op 中获取并一直保持到包装 CTE 的隐式事务(如果您不使用显式开始/提交)被提交。

要获得这样的排序保证，您可以使用带有 OFFSET 0 的子查询，或者您可以使 some_stuff 中的查询直接依赖于 lock_op 以确保首先对其进行评估。

就我个人而言，如果可以减少 MVCC 行变动，我会保持原样，也许使用 SELECT ... FOR UPDATE 而不是 UPDATE。

---

对于其他读者，重要的是要注意，此张贴者并未假设以某种方式在单个语句中执行某些操作会使它们自动发生，从而不受并发影响。这种假设绝对错误。 CTE 并不是神奇的并发修复酱。

您必须使用行或表锁定或(谨慎和理解)使用SERIALIZABLE 隔离 + 重试循环。

最简单的方法是在进行更改的事务中LOCK TABLE ... IN EXCLUSIVE MODE。这允许并发读取，但不允许写入。

对于更细粒度的锁定，请使用带有 SELECT ... FOR UPDATE 的子查询或 CTE 术语。