Atomic UPDATE to increment integer in Postgresql

Question

I'm trying to figure out if the query below is safe to use for the following scenario:

I need to generate sequential numbers, without gaps. As I need to track many of them, I have a table holding sequence records, with a sequence integer column.

To get the next sequence, I'm firing off the SQL statement below.

WITH updated AS (
  UPDATE sequences SET sequence = sequence + ? 
  WHERE sequence_id = ? RETURNING sequence
)
SELECT * FROM updated;

My question is: is the query below safe when multiple users fire this query at the database at the same time without explicitly starting a transaction?

Note: the first parameter is usually 1, but could also be 10 for example, to get a block of 10 new sequence numbers

Unrelated, but you don't really need the CTE. The update ... returning is enough — user330315
– user330315, Commented Oct 20, 2016 at 19:57
Unfortunately, from the environment I'm forced to use, I cannot execute an UPDATE statement directly and receive whatever is returned back. As a workaround, I have to wrap it in a WITH — Paul
– Paul, Commented Oct 21, 2016 at 9:14

Laurenz Albe · Accepted Answer · 2016-10-20 20:00:10Z

17

Yes, that is safe.

While one such statement is running, all other such statements are blocked on a lock. The lock will be released when the transaction completes, so keep your transactions short. On the other hand, you need to keep your transaction open until all your work is done, otherwise you might end up with gaps in your sequence.
That is why it is usually considered a bad idea to ask for gapless sequences.

answered Oct 20, 2016 at 20:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iman Over a year ago

Should we take care about deadlock risk? engineering.harrys.com/2017/06/28/atomic-operations-in-sql.html

Laurenz Albe Over a year ago

This statement alone cannot cause a deadlock, but it can cause one in combination with the other statements that run in the same transaction. You always have to consider deadlocks - either avoid them or be ready to repeat transactions.

John · Accepted Answer · 2016-10-20 20:07:07Z

0

Unless I misunderstand the question, that's what the SERIAL data type is for:

https://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL

answered Oct 20, 2016 at 20:07

John

1,19014 silver badges24 bronze badges

1 Comment

Nick Barnes Over a year ago

A SERIAL will leave a gap in the sequence if an insert is rolled back. A table-based generator will be rolled back along with the insert, so it doesn't have this issue. The downside is that you're locking out all concurrent inserts until you commit...

Collectives™ on Stack Overflow

Atomic UPDATE to increment integer in Postgresql

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related