将pg_try_advisory_xact_lock（）放在嵌套的子查询中？

Question

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

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

但是在这里Postgrespg_try_advisory_lock阻止了所有记录他们说，如果我没记错的话，我不
应该pg_try_advisory_lock()在WHERE子句中使用，因为我将在被扫描的整个集合中的 每一行 调用 一次
（作为过滤的一部分）出现在where子句中）。

我只希望查询在其中找到并更新第一行（随机地LIMIT），available = true并将其更新为available = false，并且我需要在执行此操作时锁定该行，但无需发出新请求来等待先前锁的释放，因此我添加了像这里建议的咨询锁。

我应该放在条款pg_try_advisory_lock()之外WHERE吗？怎么做？

Answer 1

我更新了参考答案，并提供了更多解释和链接。
在Postgres 9.5（目前为beta）中，新功能SKIP LOCKED是一种出色的解决方案：

• Postgres UPDATE-LIMIT 1

首先让我简化一下查询中的几件事：

直接查询

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;

• 所有的双引号都带有合法的小写字母名称。
• 由于career_available是一个布尔列，因此您可以简化opportunity_available = true为opportunity_available
• 您不需要*从子查询返回，就id足够了。

通常，它 按原样 工作。解释如下。

避免对不相关的行进行咨询性锁定

可以肯定的是， 在 应用到下一个查询级别 之前 ，您可以将所有谓词封装在带有OFFSET 0黑客的CTE或子查询中（减少开销）：
__pg_try_advisory_xact_lock()

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;

但是 ，这通常要贵​​得多。

你可能不需要这个

如果您将查询基于覆盖所有谓词的索引（例如，部分索引），则不会有任何“附带”咨询锁：

CREATE INDEX opportunities_deal_id ON opportunities (deal_id)
WHERE opportunity_available;

检查EXPLAIN以验证Postgres实际使用该索引。这样，pg_try_advisory_xact_lock(id)将成为索引或位图索引扫描的筛选条件，并且仅将对合格行进行测试（并锁定），因此您可以使用简单的表单而无需其他嵌套。同时，您的查询性能得到了优化。我会做
那 。

即使 几个不相关的行 _ 偶尔_
会获得咨询锁，通常也没关系。咨询锁仅与实际使用咨询锁的查询有关。还是您真的有其他并发事务也使用咨询锁并定位同一表的其他行？真的吗？

唯一有问题的情况是，如果大量不相关的行获得了咨询锁，那么只有在顺序扫描时才会发生这种情况，即使在那时也不太可能。