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

非常受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.

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

解决方法

我用更多的解释和链接更新了我的参考答案.
在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是一个布尔列,您可以将opportunity_available = true简化为opportunity_available
>您不需要从子查询返回*,只需id即可.

通常,这是按原样工作的.说明如下.

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

可以肯定的是,在下一个查询级别应用pg_try_advisory_xact_lock()之前,您可以使用OFFSET 0 hack(较少的开销)将所有谓词封装在CTE或子查询中：

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)将成为索引或位图索引扫描的过滤条件,并且仅开始测试(和锁定)合格行,因此您可以使用简单形式而无需额外嵌套.同时,您的查询性能已得到优化.我会这样做的.

即使一些不相关的行应该偶尔得到一个咨询锁,这通常也无关紧要.咨询锁只与实际使用咨询锁的查询相关.或者你真的有其他并发事务也使用咨询锁并瞄准同一个表的其他行？真？

唯一的另一个有问题的情况是,如果大量不相关的行获得咨询锁定,这只能通过顺序扫描发生,即便如此也是如此.

sql – 将pg_try_advisory_xact_lock()放在嵌套的子查询中？

解决方法

猜你在找的MsSQL相关文章