mercredi 14 octobre 2015

Put pg_try_advisory_xact_lock, inside or outside the WHERE clause (Rails 4/postgresql 9.4)?

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