sql - 在 PostgreSQL 数据库中生成序列号 - 并发和隔离级别

Question

类似的问题； Update SQL with consecutive numbering

我希望能够通过递增名为 SeqNum 的表中的列 num 来生成序列号。 SeqNum表布局；

|num|
|===|
| 0 |

正在运行的查询；

BEGIN TRANSACTION
UPDATE SeqNum
SET num = num + 1
SELECT num from SeqNum
COMMIT TRANSACTION

我的问题是，如果我有多个进程同时使用 READ COMMITTED 隔离级别运行此查询，select 子句是否总是会返回唯一的更新值。我假设这将是一致的，并且没有两个进程会返回相同的数字......显然这都是在一个事务中运行的。如果它不在事务中，我希望它可能返回重复值。

我不确定行为如何根据隔离级别发生变化(如果有的话)。

Answer 1

In PostgreSQL, you can request any of the four standard transaction isolation levels. But internally, there are only three distinct isolation levels, which correspond to the levels Read Committed, Repeatable Read, and Serializable. When you select the level Read Uncommitted you really get Read Committed ...¹

在read committed 隔离级别中，脏读 根据标准是不可能的，这意味着这些事务无法读取由并发未提交事务写入的数据。根据标准，只能发生在未提交隔离级别(但不会发生在 PostgreSQL 中:四个隔离级别仅定义哪些现象不能发生，他们没有定义哪些现象必须发生)。

简而言之，您的 select 子句不会始终返回唯一值。如果您将其重写为 UPDATE ... RETUNRING ... 也不会，但时间窗口会非常小，因此多个事务返回相同值的可能性会低得多。

但幸运的是，PostgreSQL 中唯一不受事务影响的是 sequence。 :

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values. ²

Because sequences are non-transactional, changes made by setval are not undone if the transaction rolls back. ²