CODEWITHSUNDEEP
sqlruby-on-railspostgresqlruby-on-rails-4concurrency
Mathieu
Mathieu

Reputation: 4787

Advisory locks or NOWAIT to avoid waiting for locked rows?

In my Rails 4 app, I have this query to a Postgres 9.4 database:

@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"
)

Very much inspired by this related answer on dba.SE.

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 release of the previous lock as there are many concurrent calls that will use this query.

But I also saw the NOWAIT option to FOR UPDATE. I'm not sure I understand the difference between using pg_try_advisory_xact_lock() and the NOWAIT option, they seem to me to achieve the same goal:

Which one is better suited to my need?

Upvotes: 10

Views: 21354

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

FOR UPDATE NOWAIT is only a good idea if you insist on locking a particular row, which is not what you need. You just want any qualifying, available (unlocked) row. The important difference is, (quoting the manual:)

With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately.

Identical queries will very likely try to lock the same arbitrary pick. FOR UPDATE NOWAIT will just bail out with an exception (which will roll back the whole transaction unless you trap the error) and you have to retry.

The solution in my referenced answer on dba.SE uses a combination of plain FOR UPDATE in combination with pg_try_advisory_lock():

pg_try_advisory_lock is similar to pg_advisory_lock, except the function will not wait for the lock to become available. It will either obtain the lock immediately and return true, or return false if the lock cannot be acquired immediately.

So your best option is ... a third alternative: FOR UPDATE SKIP LOCKED (added with Postgres 9.5), which implements the same behavior without additional function call.

The manual explains the difference:

To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped.

With Postgres 9.4 or older your next best option is to use pg_try_advisory_xact_lock(id) in combination with FOR UPDATE like demonstrated in the referenced answer:

(Also with an implementation with FOR UPDATE SKIP LOCKED.)

Aside

Strictly speaking you get arbitrary, not truly random picks. That can be an important distinction.
An audited version of your query is in my answer to your other question.

Upvotes: 18

Related Questions