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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
