Put Pg_Try_Advisory_Xact_Lock() in a Nested Subquery

Put pg_try_advisory_xact_lock() in a nested subquery?

I updated my referenced answer with more explanation and links.

In Postgres 9.5 (currently beta) the new SKIP LOCKED is a superior solution:

• Postgres UPDATE … LIMIT 1

---

Let me simplify a few things in your query first:

Straight query

UPDATE opportunities s
SET    opportunity_available = false
FROM  (
   SELECT id
   FROM   opportunities
   WHERE  deal_id = #{@deal.id}
   AND    opportunity_available
   AND    pg_try_advisory_xact_lock(id)
   LIMIT  1
   FOR    UPDATE
   ) sub
WHERE     s.id = sub.id
RETURNING s.prize_id, s.id;

• All the double quotes were just noise with your legal, lower-case names.
• Since opportunity_available is a boolean column you can simplify opportunity_available = true to just opportunity_available
• You don't need to return * from the subquery, just id is enough.

Typically, this works as is. Explanation below.

Avoid advisory lock on unrelated rows

To be sure, you could encapsulate all predicates in a CTE or a subquery with the OFFSET 0 hack (less overhead) before you apply pg_try_advisory_xact_lock() in the next query level:

UPDATE opportunities s
SET    opportunity_available = false
FROM (
   SELECT id
   FROM  ( 
      SELECT id
      FROM   opportunities
      WHERE  deal_id = #{@deal.id}
      AND    opportunity_available
      AND    pg_try_advisory_xact_lock(id)
      OFFSET 0
      ) sub1
   WHERE  pg_try_advisory_xact_lock(id)
   LIMIT  1
   FOR    UPDATE
   ) sub2
WHERE     s.id = sub.id
RETURNING s.prize_id, s.id;

However, this is typically much more expensive.

You probably don't need this

There aren't going to be any "collateral" advisory locks if you base your query on an index covering all predicates, like this partial index:

CREATE INDEX opportunities_deal_id ON opportunities (deal_id)
WHERE opportunity_available;

Check with EXPLAIN to verify Postgres actually uses the index. This way, pg_try_advisory_xact_lock(id) will be a filter condition to the index or bitmap index scan and only qualifying rows are going to be tested (and locked) to begin with, so you can use the simple form without additional nesting. At the same time, your query performance is optimized. I would do that.

Even if a couple of unrelated rows should get an advisory lock once in a while, that typically just doesn't matter. Advisory locks are only relevant to queries that actually use advisory locks. Or do you really have other concurrent transactions that also use advisory locks and target other rows of the same table? Really?

The only other problematic case would be if massive amounts of unrelated rows get advisory locks, which can only happen with a sequential scan and is very unlikely even then.

Postgres pg_try_advisory_lock blocks all records

You're 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), whereas you only want it called once per row in table1 returned by the query.

You could try using a subquery or a CTE instead:

with rows as (
SELECT a.id
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE table2.id = 1
)
select rows.*
from rows
where pg_try_advisory_lock('table1'::regclass::integer, rows.id);

But don't rely on that to necessarily work as expected either: Postgres should be tempted to rewrite it the way your initial query was.

Another possibility is this, since the select part of a statement is evaluated very late in the query:

with rows as (
SELECT a.id,
       pg_try_advisory_lock('table1'::regclass::integer, a.id) as locked
FROM table1 a
JOIN table2 b ON a.table1_id = b.id
WHERE table2.id = 1
)
select rows.id
from rows
where rows.locked;

The real issue in practice is that pg_try_advisory_lock() is something you'd normally find in app land or in a function, rather than in a query like you're doing. Speaking of which, depending on what you're doing, are you sure you shouldn't be using select … for update?

---

Regarding your update:

postgres scans the very first row then stops?

Yes. Due to the limit 1, it's going to find a match and immediately stop. What is probably happening, though, is that it's not evaluating the where clause in the same order depending on your queries. SQL offers no guarantee that the a <> 0 part in a <> 0 and b / a > c gets evaluated first. Applied to your case, it offers no guarantee that the advisory lock is obtained after the row from a is joined with b.

Advisory locks or NOWAIT to avoid waiting for locked rows?

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.

postgresql 9.4 - prevent app selecting always the latest updated rows

Just an idea: instead of calling random() use it as default value for a column(which can be indexed) A similar way could use a serial with an increment of about 0.7 * INT_MAX.

\i tmp.sql

CREATE TABLE opportunities
    ( id SERIAL NOT NULL PRIMARY KEY
    , deal_id INTEGER NOT NULL DEFAULT 0
    , prize_id INTEGER
    , opportunity_available boolean NOT NULL DEFAULT False
            -- ----------------------------------------
            -- precomputed random() , (could be indexed)
    , magic DOUBLE precision NOT NULL default RANDOM()
    );

INSERT INTO opportunities(deal_id)
SELECT 341
FROM generate_series(1,20) gs
    ;
VACUUM ANALYZE opportunities;

PREPARE add_three (integer) AS (
WITH zzz AS (
  UPDATE opportunities
  SET prize_id = 21
    , opportunity_available = True
    -- updating magic is not *really* needed here ...
    , magic = random()
  WHERE opportunities.id
  IN (
    SELECT opportunities.id
    FROM opportunities
    WHERE (deal_id = $1 AND prize_id IS NULL)
    -- ORDER BY RANDOM()
    ORDER BY magic
    LIMIT 3)
RETURNING id, magic
    ) -- 
SELECT * FROM zzz
    );

PREPARE draw_one (integer) AS (
  WITH upd AS (
  UPDATE opportunities s
  SET    opportunity_available = false
  FROM  (
     SELECT id
     FROM   opportunities
     WHERE  deal_id = $1
     AND    opportunity_available
     AND    pg_try_advisory_xact_lock(id)
     ORDER BY magic
     LIMIT  1

     FOR    UPDATE
     ) sub
  WHERE     s.id = sub.id
  RETURNING s.prize_id, s.id, magic
    )
SELECT * FROM upd
    );

SELECT * FROM opportunities;

\echo add3
EXECUTE add_three(341);
SELECT * FROM opportunities;

\echo add3 more
EXECUTE add_three(341);
SELECT * FROM opportunities;

\echo draw1
EXECUTE draw_one(341);
SELECT * FROM opportunities;

\echo draw2
EXECUTE draw_one(341);
SELECT * FROM opportunities;

VACUUM ANALYZE opportunities;

\echo draw3
EXECUTE draw_one(341);
SELECT * FROM opportunities;

\echo draw4
EXECUTE draw_one(341);
SELECT * FROM opportunities;

Rails 4/postgresql - Update model attribute AFTER Read query has been performed on the row

You can set and save the new status directly in the controller in your show_opportunities method, after the record is read, but before the data is send to the user.

If you want to make sure that at the same time no other is getting the same opportunity, you need to lock the database row when you read it. See "pessimistic locking"

---

Related Topics