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
PostgreSQL: Update is not atomic.
A PostgreSQL transaction is atomic, consistent, isolated, and durable. These properties are often referred to as ACID: Atomicity guarantees that the transaction completes in an all-or-nothing manner. Consistency ensures the change to data written to the database must be valid and follow predefined rules.
The UPSERT statement is a DBMS feature that allows a DML statement's author to either insert a row or if the row already exists, UPDATE that existing row instead. That is why the action is known as UPSERT (simply a mix of Update and Insert).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With