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

专打抬杠狗

在我的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吗？怎么做？

解决方案:


            我更新了参考答案，并提供了更多解释和链接。
在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)将成为索引或位图索引扫描的筛选条件，并且仅将对合格行进行测试（并锁定），因此您可以使用简单的表单而无需其他嵌套。同时，您的查询性能得到了优化。我会做
那。
即使几个不相关的行 _ 偶尔_
会获得咨询锁，通常也没关系。咨询锁仅与实际使用咨询锁的查询有关。还是您真的有其他并发事务也使用咨询锁并定位同一表的其他行？真的吗？
唯一有问题的情况是，如果大量不相关的行获得了咨询锁，那么只有在顺序扫描时才会发生这种情况，即使在那时也不太可能。

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

专打抬杠狗 LV1