19

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

Paul
  • 335
  • 1
  • 3
  • 16
  • 1
    Unrelated, but you don't really need the CTE. The `update ... returning` is enough –  Oct 20 '16 at 19:57
  • 1
    http://www.gplivna.eu/papers/gapless_sequences.htm – Nick Barnes Oct 20 '16 at 20:55
  • 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 Oct 21 '16 at 09:14

2 Answers2

18

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Should we take care about **deadlock risk**? https://engineering.harrys.com/2017/06/28/atomic-operations-in-sql.html – iman Apr 14 '19 at 02:41
  • 1
    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. – Laurenz Albe Apr 14 '19 at 06:27
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

John
  • 1,018
  • 12
  • 19
  • 4
    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... – Nick Barnes Oct 20 '16 at 20:54