in my Ruby on Rails app, I'm using a postgresql query that is very much inspired by http://ift.tt/XxuZpD :
@chosen_opportunity = Opportunity.find_by_sql(
" UPDATE \"opportunities\" s
SET opportunity_available = false
FROM (
SELECT \"opportunities\".*
FROM \"opportunities\"
WHERE ( deal_id = #{@deal.id}
AND opportunity_available = true
AND pg_try_advisory_xact_lock(id) )
LIMIT 1
FOR UPDATE
) sub
WHERE s.id = sub.id
RETURNING sub.prize_id, sub.id"
)
But here (Postgres pg_try_advisory_lock blocks all records), they say, if I'm not mistaken, that I should not use pg_advisory_lock inside the WHERE clause because I would be calling pg_try_advisory_lock() once per row in the entire set that gets scanned (as part of the filtering that occurs in the where clause).
I just want my query to find and update the first (randomly, with 'LIMIT') row where available= true and update it to available= false, and I need to lock the row while doing this (but without making new requests waiting for the unlock => so I added advisory locks like advice here http://ift.tt/XxuZpD) .
Should I place pg_try_advisory_lock outside the WHERE clause? how to do it?
Aucun commentaire:
Enregistrer un commentaire