Consulting locks or NOWAIT to avoid waiting for locked rows?

Question

In my Rails 4 application, I have this query on 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 inspired by this related answer to dba.SE .

I just want my query to find and update the first line (randomly, from LIMIT), where I available = trueupdated it to available = false, and I need to block the line at the same time, but without creating new requests, waiting for the previous lock to exit, since there are many simultaneous calls , who will use this request.

NOWAIT FOR UPDATE. , pg_try_advisory_xact_lock() NOWAIT, , :

• NOWAIT :

  , NOWAIT.

• pg_try_advisory_xact_lock
  , , , " ".

?

Answer 1

FOR UPDATE NOWAIT - , , , . , () . ( Postgres 9.4):

NOWAIT , , .

, , . FOR UPDATE NOWAIT ( , ), .

dba.SE FOR UPDATE pg_try_advisory_lock():

pg_try_advisory_lock pg_advisory_lock, . , true, false, .

, - : FOR UPDATE SKIP LOCKED Postgres 9.5, . p >

Postgres 9.5 , :

commit, NOWAIT SKIP LOCKED. NOWAIT , , . SKIP LOCKED, , , .

Postgres 9.4 pg_try_advisory_xact_lock(id) FOR UPDATE, , :

• Postgres UPDATE... LIMIT 1

( FOR UPDATE SKIP LOCKED.)

, , - . .
.