CODEWITHSUNDEEP
postgresqlpostgresql-13
Thiago Sayão
Thiago Sayão

Reputation: 2357

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

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?

Upvotes: 1

Views: 326

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247445

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.

Upvotes: 1

Related Questions