将pg_try_advisory_xact_lock（）放在嵌套子查询中？ [英] Put pg_try_advisory_xact_lock() in a nested subquery?

问题描述

在我的Ruby on Rails 4应用程序，我有这个查询到Postgres 9.4数据库：

  @chosen_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 
和pg_try_advisory_xact_lock（id））
 LIMIT 1 
 UPDATE 
）sub 
 WHERE s.id = sub.id 
 RETURNING sub.prize_id，sub.id
）
  

很多灵感来自关于dba.SE的相关答案。

但这里（ Postgres pg_try_advisory_lock阻止所有记录）他们说，如果我没有错误，我不应该使用 pg_try_advisory_lock（） WHERE 子句，因为我将在整个集合中每行调用一次（作为过滤的一部分在 LIMIT

我只想让我的查询查找和更新第一个）row其中 available = true 并将其更新为 available = false ，我需要锁定行，但没有发出新的请求，等待上次锁定的发布，因此我添加了咨询锁 like here here 。

我应该在 WHERE 子句之外放置 pg_try_advisory_lock（）如何做？

解决方案

我更新了我的参考答案与更多的解释和链接。

在Postgres 9.5（目前为测试版）新的 SKIP LOCKED 是一个优越的解决方案：

• < a href =http://dba.stackexchange.com/a/69497/3684> Postgres UPDATE ... LIMIT 1

---

让我先简化查询中的一些事情：

直接查询

<$ c $ p> 更新机会s
SET opportunity_available = false
FROM（
SELECT id
FROM opportunities
WHERE deal_id = #{@deal.id}
AND opportunity_available
和pg_try_advisory_xact_lock（id）
LIMIT 1
FOR UPDATE
）sub
WHERE s.id = sub.id
RETURNING s.prize_id，s.id;


• 所有的双引号只是噪音与您的法律，小写 c $ c
  
• 由于opportunity_available是一个布尔列，您可以将 opportunity_available = true 简化为 opportunity_available


• 您不需要从子查询返回 * ，只需 id 就足够了。



通常情况下，

避免对不相关行进行建议性锁定

可以将所有谓词CUT或具有 OFFSET 0 hack（减少开销）的子查询 之前应用 pg_try_advisory_xact_lock c>在下一个查询级别：

 更新机会s 
 SET opportunity_available = false 
 FROM b $ b SELECT id 
 FROM（
 SELECT id 
 FROM opportunities 
 WHERE deal_id = #{@deal.id} 
 AND opportunity_available 
和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; 
  

但，通常会更昂贵。

您可能不需要这个

如果您以查询为基础，就不会有任何「抵押」在覆盖所有谓词的索引上，例如此部分索引：

  CREATE INDEX opportunities_deal_id ON机会（deal_id）
 WHERE opportunity_available ; 
  

使用 EXPLAIN 检查以验证Postgres实际使用索引。这样， pg_try_advisory_xact_lock（id）将是对索引或位图索引扫描的过滤条件，只有合格行将被测试（和锁定）您可以使用简单的形式而无需额外的嵌套。同时，您的查询性能得到优化。

。

一个咨询锁一会儿，通常只是没有关系。咨询锁只与实际使用咨询锁的查询相关。或者你真的有其他并发事务，也使用咨询锁和目标其他行相同的表？真的吗？

唯一另一个有问题的情况是，如果大量的不相关的行获得咨询锁，这只能发生在顺序扫描，并且是不可能的，即使这样。 / p>

In my Ruby on 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.

But here (Postgres pg_try_advisory_lock blocks all records) they say, if I'm not mistaken, that I should not use pg_try_advisory_lock() inside the WHERE clause because I would be calling it 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 release of the previous lock so I added advisory locks like suggested here.

Should I place pg_try_advisory_lock() outside the WHERE clause? How to do it?

解决方案

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.

这篇关于将pg_try_advisory_xact_lock（）放在嵌套子查询中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！