sql - 将 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"
)

非常受 this related answer on dba.SE 启发.

但在这里 ( Postgres pg_try_advisory_lock blocks all records ) 他们说，如果我没记错的话，我不应该在 WHERE 子句中使用 pg_try_advisory_lock() 因为我会在扫描的整个集合中将其称为每行一次(作为发生在 where 子句中的过滤的一部分)。

我只想让我的查询找到并更新第一个(随机地，使用 LIMIT)行，其中 available = true 并将其更新为 available = false，我需要在执行此操作时锁定该行，但不会发出新请求等待释放前一个锁，因此我添加了咨询锁 like suggested here .

我应该将 pg_try_advisory_lock() 放在 WHERE 子句之外吗？怎么做？

Answer 1

我用更多的解释和链接更新了我的引用答案。
在 Postgres 9.5(目前是测试版)中，新的 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;

• 所有双引号都只是您合法的小写名称的杂音。
• 由于 opportunity_available 是一个 bool 列，您可以将 opportunity_available = true 简化为 opportunity_available
• 子查询不需要返回*，id就够了。

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

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

可以肯定的是，您可以使用 OFFSET 0 hack(更少的开销)将所有谓词封装在 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) 将成为索引或位图索引扫描的过滤条件，并且只有符合条件的行才会被测试(并锁定)，因此您可以使用简单的形式没有额外的嵌套。同时，您的查询性能得到了优化。我会那样做。

即使一些不相关的行应该偶尔获得建议锁，这通常也无关紧要。咨询锁只与实际使用咨询锁的查询相关。或者您是否真的有其他并发事务也使用建议锁并针对同一表的其他行？真的吗？

唯一的其他问题情况是如果大量不相关的行获得咨询锁，这只能通过顺序扫描发生，即使那样也不太可能发生。