2

I have a query like below in PostgreSQL:

UPDATE 
     queue 
SET 
  queue.status   = 'PROCESSING' 
WHERE 
    queue.status   = 'WAITING' AND
    queue.id       = (SELECT id FROM queue WHERE STATUS = 'WAITING' LIMIT 1 )
RETURNING 
    queue.id

and many workers try to process one work at a time (that's why I have sub-query with limit 1). After this update, each worker grabs information about the id and processes the work, but sometimes they grab the same work and process it twice or more. The isolation level is Read Committed.

My question is how can I guarantee one work is going to be processed once? I know there is so many post out there but I can say I have tried most of them and it didn't help () ;

  • I have tried SELECT FOR UPDATE, but it caused deadlocked situation.
  • I have tried pg_try_advisory_xact_lock, but it caused out of shared memory
  • I tried adding AND pg_try_advisory_xact_lock(queue.id) to the outer query's WHERE clause, but ... [?]

Any help would be appreciated.

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
pmoubed
  • 3,842
  • 10
  • 37
  • 60
  • 1
    You might want to use the new `serializable` isolation level introduced in PostgreSQL 9.1: http://wiki.postgresql.org/wiki/Serializable and http://drkp.net/papers/ssi-vldb12.pdf –  Jan 25 '13 at 08:32

3 Answers3

7

A lost update won't occur in the situation you describe, but it won't work properly either.

What will happen in the example you've given above is that given (say) 10 workers started simultaneously, all 10 of them will execute the subquery and get the same ID. They will all attempt to lock that ID. One of them will succeed; the others will block on the first one's lock. Once the first backend commits or rolls back, the 9 others will race for the lock. One will get it, re-check the WHERE clause and see that the queue.status test no longer matches, and return without modifying any rows. The same will happen with the other 8. So you used 10 queries to do the work of one query.

If you fail to explicitly check the UPDATE result and see that zero rows were updated you might think you were getting lost updates, but you aren't. You just have a concurrency bug in your application caused by a misunderstanding of the order-of-execution and isolation rules. All that's really happening is that you're effectively serializing your backends so that only one at a time actually makes forward progress.

The only way PostgreSQL could avoid having them all get the same queue item ID would be to serialize them, so it didn't start executing query #2 until query #1 finished. If you want to you can do this by LOCKing the queue table ... but again, you might as well just have one worker then.

You can't get around this with advisory locks, not easily anyway. Hacks where you iterated down the queue using non-blocking lock attempts until you got the first lockable item would work, but would be slow and clumsy.

You are attempting to implement a work queue using the RDBMS. This will not work well. It will be slow, it will be painful, and getting it both correct and fast will be very very hard. Don't roll your own. Instead, use a well established, well tested system for reliable task queueing. Look at RabbitMQ, ZeroMQ, Apache ActiveMQ, Celery, etc. There's also PGQ from Skytools, a PostgreSQL-based solution.

Related:

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • What if I add `pg_try_advisory_xact_lock(queue.id)` to the `WHERE` clause? I edited my question and I added it to my query ! – pmoubed Jan 25 '13 at 16:53
  • @PMoubed Please don't edit your question to change things that're already there; add to it, so answers made before your edit still make sense. It's possible that using `pg_try_advistory_xact_lock` will work; I cannot off the top of my head immediately see why it wouldn't, but I haven't looked into it in detail. Didn't you just say that you got "out of shared memory" errors when trying that earlier, though? – Craig Ringer Jan 25 '13 at 22:50
  • My question is how to use the advisory locks to avoid this problem ! – pmoubed Jan 27 '13 at 01:18
  • @PMoubed "This problem": What problem? Running out of shared memory? Please be detailed and explicit. – Craig Ringer Jan 27 '13 at 03:13
  • Yes, by this problem I meant "out of shared memory". any idea how to fix it ? – pmoubed Jan 27 '13 at 16:28
  • @PMoubed Google search for `postgresql advisory lock out of shared memory` found the answer in the official documentation: http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS, read the 4th paragraph "Both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections.". I also wonder if your query is trying to lock more rows than you think it is due to `LIMIT` vs `WHERE` ordering execution. You may need to `LIMIT` in an inner query. – Craig Ringer Jan 28 '13 at 04:54
  • @PMoubed If you want to explore this issue further - out of shared memory with advisory locks - please post a new question that focused on just that and link to it here. Be *detailed*. Show your code, exact text of error messages, sample table data, exact PostgreSQL version, relevant configuration options, etc. I stand by my answer that the best solution to this is to use an existing, well-established and known working message/task queue system. – Craig Ringer Jan 28 '13 at 04:57
  • @PMoubed Oh, and read the 5th paragraph of the documentation linked to above, it discusses ordering issues that may be related to your problem, beginning "In certain cases using advisory locking methods"... – Craig Ringer Jan 28 '13 at 04:58
0

SKIP LOCKED can be used to implement queue in PostgreSql. see

tchelidze
  • 8,050
  • 1
  • 29
  • 49
0

In PostgreSQL, lost update happens in READ COMMITTED and READ UNCOMMITTED but if you use SELECT FOR UPDATE in READ COMMITTED and READ UNCOMMITTED, lost update doesn't happen.

In addition, lost update doesn't happen in REPEATABLE READ and SERIALIZABLE whether or not you use SELECT FOR UPDATE. *Error happens if there is a lost update condition.

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129
  • Can you give an example of how postgresql allows lost update in read committed? Or just explain how? Or give a link to more info? – Dwayne Towell Mar 10 '23 at 17:28