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