Hi All,

I'm stumbling a bit over a concurrent behaviour in postgresql that I
didn't expect, and I wonder if there is a nice workaround to it.

I have a table that represents a token pool. A token is either free or
occupied, and I need to be able to concurrently occupy and release
tokens without overwriting already occupied (leased) tokens.

Heres my pool with (f.x) two available tokens:

create table pool (id serial, token text);
insert into pool (token) values (null);
insert into pool (token) values (null);

Now, clever me thought I could just do:

begin;
update pool set token = 'a'
where id = (select id from pool where token = null limit 1);
commit;

....and rest assured that the subquery would lock the row, and not allow
any other transaction to alter the row until done.

Appears I'm not too clever after all. Postgresql does exactly what the
documentation states: When I execute 2 concurrent transactions, the second
one retrieves the same column that the first transaction already got, but
waits for the first one to commit (row lock). Now since the second one
executed the subquery while the first one is still not commited, they will
both get the same id, and once the first transaction is commited, the
second one will overwrite the newly updated value a second time.

Result, both transactions believes they've succecfully occupied a unique
token, but the database only shows a single token being occupied.

The basic problem here must have been solved a hundred times over, so I'm
hopefull that someone in this group can show me the error of my path, and
perhaps a better solution.

--
Fredrik Jonson

On 2010-09-08, Fredrik Jonson <fredrik (AT) jonson (DOT) org> wrote:
Easy: check for null again.

update pool set token = 'a'
where id = (select id from pool where token = null limit 1) and
token=null;

(if zero rows were updated then try again)

you won't be able to tell the instance when there are no free tokens
from the instance when there was a clash.

Better: select for update.

update pool set token = 'a'
where id = (select id from pool where token = null limit 1 for update)

you still need to check rows_affected as the select may not find a
free row.

See the documentation for select in the "sql commands" chapter of
the "Reference" section of the on-line documentation.

--
¡spuɐɥ ou 'ɐꟽ ʞooꞀ

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

Jasen Betts wrote:
Now that IS clever! Thanks!

Tried that already. It isn't allowed on postgresql 8.4.4:

db=> update pool set token = 'a'
db-> where id = (select id from pool where token = null limit 1 for update);
ERROR: SELECT FOR UPDATE/SHARE is not allowed in subqueries

--
Fredrik Jonson

Jasen Betts wrote:

Obviously in real life it turns out that this solution is trivial to DOS. Any
time you have more than one concurrent transaction the last transaction in is
guaranteed to get nothing. The subquery select will return the same results to
both transactions until the first transaction commits.

Retrying again on the application level only increases the risk of contention.
As a temporary workaround I've added a "LOCK TABLE pool" before the query. It
works, but I'm not too happy about it. If anyone got another less drastic
alternative, I'm very interested.

--
Fredrik Jonson

Fredrik Jonson, 08.03.2011 01:46:
What about using an advisory lock the the ID prior to updating?

SELECT pg_try_advisory_lock(t.id)
FROM (
SELECT id
FROM pool
WHERE token IS NULL
LIMIT 1
) t

If that completes the ID will be locked and other threads trying the same will wait. Once the SELECT returns it should be safe to run the UPDATE (at least that's how I understand the advisory locks - maybe I'm missing something here...)

Most probably you should use the two value version of the function in order to have a "namespace" for the locks.

Regards
Thomas