sql - Advisory locks or NOWAIT to avoid waiting for locked rows?

Question

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:

• NOWAIT's goal:

  To prevent the operation from waiting for other transactions to commit, use the NOWAIT option.

• pg_try_advisory_xact_lock's goal
  is not to wait for the previous transaction to release the lock and still be able to do another transaction and only operate the next select for update the 'not yet locked' rows.

Which one is better suited to my need?

Answer 1

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 this (quoting the manual for Postgres 9.4):

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 ... the third alternative: the new FOR UPDATE SKIP LOCKED in Postgres 9.5, which implements the same behavior without additional function call.

The manual for Postgres 9.5 compares the two options, explaining the difference some more:

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.

On 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:

• Postgres UPDATE … LIMIT 1

(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.