On 07/12/2012 04:17 AM, Steve Crawford wrote:
> The stickier issue is queuing. You have multiple users and need to
> ensure that you grab an unused check from the book but each concurrent
> user needs to get a different check. "Select from checkbook where not
> check_used order by check_no limit 1 for update" seems like a
> reasonable approach but if two users run it simultaneously the first
> user will get 1 check and succeed while the second user will attempt
> to lock same check record, block until the first user completes then
> recheck and find the selected record no longer
> meets the check_used criteria so the second user will see zero records
> returned.
In short-transaction systems where the blocking isn't an issue it's
often fine to just re-try when you don't get a result. Much simpler than
advisory locking tricks.
Using a SERIALIZABLE transaction should also work AFAIK, causing a
serialization failure and forcing the app to re-issue the transaction.
--
Craig Ringer