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