zlacker

Some time ago, I wrote a queue using SQLite[0]. Instead of SKIP LOCKED, you can use RETURNING to lock-and-read a message and ensure only one worker is going to pick it up:

  UPDATE ... SET status = 'locked' ... RETURNING message_id

Or you can just use an IMMEDIATE transaction, SELECT the next message ID to retrieve, and UPDATE the row.

On top of that, if you want to be extra safe, you can do:

  UPDATE Queue SET status = 'locked' WHERE status = 'ready' AND message_id = '....'

To make sure you that the message you are trying to retrieve hasn't been locked already by another worker.

[0]: https://github.com/litements/litequeue/

[1]: https://github.com/litements/litequeue/blob/3fece7aa9e9a31e4...