Postgresql sequence: lock strategy to prevent record skipping on a table queue

Question

I have a table that acts like a queue (let's call it queue) and has a sequence from 1..N.

Some triggers inserts on this queue (the triggers are inside transactions).

Then external machines have the sequence number and asks the remote database: give me sequences greater than 10 (for example).

The problem:

In some cases transaction 1 and 2 begins (numbers are examples). But transaction 2 ends before transaction 1. And in between host have asked queue for sequences greater than N and transaction 1 sequences are skipped.

How to prevent this?

This may be difficult in PostGreSQL because PostGreSQL is unnable to have pessimistic locking that will serialize the transaction. Try with ACCESS EXCLUSIVE table explicit locking. — SQLpro
– SQLpro, Commented May 11, 2021 at 12:42
That would work I think, but seems like a huge performance hit, it would serialize all transactions, would't it? — Thiago Sayão
– Thiago Sayão, Commented May 11, 2021 at 13:16
I am thinking on a double queue solution where a job locks queue1 and copies to queue2. So queue2 would not skip records and locking would be quickier. — Thiago Sayão
– Thiago Sayão, Commented May 11, 2021 at 14:25
You can inspire you from Microsoft SQL Server which have queues tables for Service Broker and implements a RECEIVE pseudo SQL order that discard while reading (simultaneous SELECT and DELETE) the rows... By the way @Thiago Sayão does the same in a reversed manner but keeps the row in a purging table... — SQLpro
– SQLpro, Commented May 11, 2021 at 14:42
By the way, recently PG has a process that seems to be similar in a new contrib.... A way to explore : pgxn.org/dist/pg_message_queue — SQLpro
– SQLpro, Commented May 11, 2021 at 14:44

Laurenz Albe · Accepted Answer · 2021-05-11 14:57:10Z

1

I would proceed like this:

add a column state to the table that you change as soon as you process an entry

get the next entry with

SELECT ... FROM queuetab
WHERE state = 'new'
ORDER BY seq
LIMIT 1
FOR UPDATE SKIP LOCKED;

update state in the row you found and process it

As long as you do the last two actions in a single transaction, that will make sure that you are never blocked, get the first available entry and never skip an entry.

answered May 11, 2021 at 14:57

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Thiago Sayão Over a year ago

The probem is that the queue has N consumers, so I can't have a state column.

Laurenz Albe Over a year ago

That should work with any number of concurrent consumers without locking anybody. The state is independent of which consumer grabs an entry (but of course you can update the state to reflect the consumer that processed it).

Collectives™ on Stack Overflow

Postgresql sequence: lock strategy to prevent record skipping on a table queue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related