Scott Marlowe wrote:
> On Thu, Apr 3, 2008 at 11:45 AM, Craig Ringer
> <craig@postnewspapers.com.au> wrote:
>> 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?
>
> I'm pretty sure serializable won't fix this.
I'm far from sure myself, but if it won't I'd be very interested in
knowing how it can go wrong. A quick test suggested that it did the job,
and according to:
http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html
it should work.
Given the language:
----------
UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave
the same as SELECT in terms of searching for target rows: they will only
find target rows that were committed as of the transaction start time.
However, such a target row may have already been updated (or deleted or
locked) by another concurrent transaction by the time it is found. In
this case, the serializable transaction will wait for the first updating
transaction to commit or roll back (if it is still in progress). If the
first updater rolls back, then its effects are negated and the
serializable transaction can proceed with updating the originally found
row. But if the first updater commits (and actually updated or deleted
the row, not just locked it) then the serializable transaction will be
rolled back with the message "ERROR: could not serialize access due to
concurrent update" because a serializable transaction cannot modify or
lock rows changed by other transactions after the serializable
transaction began.
---------
Say two updates are begun at the same time. Both run their subqueries
and both pick the same free id. One then acquires a ROW EXCLUSIVE lock
on the record being updated and the other blocks trying to acquire that
lock. The update that successfully grabbed the lock makes its changes
and the transaction commits successfully, releasing the lock. The second
update is now free to continue, but because the row it was attempting to
modify has just been changed under it it'll abort with a serialization
error.
It seems safe to me.
--
Craig Ringer
