Generating a sequence number in a PostgreSQL database - concurrency & isolation levels

Question

Similar question; Update SQL with consecutive numbering

I want to be able to generate a sequence number by incrementing a column num in a table called SeqNum. The SeqNum table layout;

|num|
|===|
| 0 |

The query being run;

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

My question is if I have multiple processes running this query with a READ COMMITTED isolation level at the same time, will the select clause always return a unique updated value. I'm assuming this will be consistent and no two processes would ever return the same num... Obviously this is all running in the one transaction. If it wasn't in a transaction I would expect it to potentially return duplicate values.

I'm not sure how the behavior changes (if at all) depending on the isolation level.

instead of `update; select` it would be better to use `update .. returning`. But why don't you use a real sequence in the first place? — , Jun 05 '14 at 15:46

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

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 ...¹

In read committed isolation level dirty reads are not possible per standard, which means these transactions cannot read data written by a concurrent uncommitted transaction. That can only happen in the read uncommitted isolation level per standard (but won't happen in PostgreSQL: the four isolation levels only define which phenomena must not happen, they do not define which phenomena must happen).

In short your select clause won't return a unique value always. Neither will, if you rewrite it to UPDATE ... RETUNRING ..., but the time window will be really small, so chances will be much more lower to multiple transactions return the same value.

But lucky for you, the only thing in PostgreSQL, which doesn't affected by transactions, is the 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. ²

Generating a sequence number in a PostgreSQL database - concurrency & isolation levels

1 Answers1