Re: choosing the right locking mode

rihad wrote:
> Given this type query:
>
> UPDATE bw_pool
> SET user_id=?
> WHERE bw_id=
> (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
> RETURNING bw_id

Can you use a SERIALIZABLE transaction and avoid the explicit lock?

If I'm not mistaken, using the SERIALIZABLE isolation level should
ensure that the following cannot occur:

UPDATE begins
UPDATE begins
Subquery finds free row id 1
Subquery finds free row id 1

Update completes

Update completes, overwriting
changes from the other update.

You'd have to be prepared to retry failed updates, but I doubt that's a
big deal in this situation.

See:

http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html

--
Craig Ringer