PostgreSQL并发事务中合适的隔离级别及细粒度锁问题

首先，你遇到的是丢失更新问题：在REPEATABLE READ隔离级别下，两个事务基于事务启动时的快照看到tableX.id=1的count值都是0，最后各自更新为1，导致预期的累加结果丢失。

不用直接上SERIALIZABLE，更轻量的解决方案 #

1. 显式行级锁（推荐）

在事务开始时，对tableX的目标行加行级锁，替代普通的SELECT：

BEGIN;
-- 加行级锁，阻止其他事务修改该行直到当前事务结束
SELECT * FROM tableX WHERE id = 1 FOR UPDATE;
SELECT count(*) FROM tableY;
UPDATE tableX SET count = count + 1 WHERE id = 1;
COMMIT;

第一个事务会先锁定tableX.id=1的行，第二个事务执行SELECT ... FOR UPDATE时会被阻塞，直到第一个事务提交。等第二个事务继续执行时，看到的count已经是1，更新后就会得到2，完全符合预期。
如果只是要阻止修改、不阻止读取，也可以用SELECT ... FOR NO KEY UPDATE，锁的粒度更轻。

2. 乐观锁机制

如果不想用阻塞式锁，可以用乐观锁，更新时带上之前读取的count值做条件：

BEGIN;
SELECT count FROM tableX WHERE id = 1 INTO v_count;
SELECT count(*) FROM tableY;
-- 仅当count值和之前读取的一致时才更新
UPDATE tableX SET count = count + 1 WHERE id = 1 AND count = v_count;
-- 检查更新影响的行数，如果为0说明有并发更新，需要重试
COMMIT;

这种方式适合并发量不高的场景，不需要加锁，但需要应用层处理更新失败后的重试逻辑。

关于SERIALIZABLE隔离级别的误解 #

PostgreSQL的SERIALIZABLE隔离级别采用的是可序列化快照隔离（SSI），并不会因为执行SELECT count(*) FROM tableY就锁定整个Y表。SSI通过跟踪事务之间的读写依赖，在检测到可能导致序列化异常的并发操作时，回滚其中一个事务。
但这种方式会增加事务回滚的概率，需要应用层处理重试，性能上也不如显式行级锁高效，所以不是最优解。

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