保护 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)

基本上，我想知道"从lock_op中选择 1"是否在从 some_stuff 和other_stuff进行任何插入和更新之前启动，因此，是否在事务的暂时保护我的关键数据由 WITH 范围限定？

Answer 1

您在这里没有相同的排序保证。没有承诺 该lock_op的查询将在some_stuff之前执行。

否则是合理的。行锁在lock_op中采用并保持，直到提交包装 CTE 的隐式事务(如果不使用显式开始/提交)。

要获得这样的排序保证，您可以将子查询 in-FROM 与OFFSET 0一起使用，或者您可以直接依赖lock_opsome_stuff查询以确保首先对其进行评估。

就个人而言，我会保持原样，如果可以的话，也许会用SELECT ... FOR UPDATE而不是UPDATE来减少 MVCC 行流失。

---

对于其他读者来说，重要的是要注意，这张海报并不是假设以某种方式在单个语句中做事会使它们原子地发生，不受并发效应的影响。这种假设是绝对错误的。CTE 不是神奇的并发修复酱。

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

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

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