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

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

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

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

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