Scott Marlowe wrote: > On Thu, Apr 3, 2008 at 11:45 AM, Craig Ringer > <craig@xxxxxxxxxxxxxxxxxxxxx> 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
