postgresql - 意味着要更新WHERE值是在具有GROUP BY的IN子查询中，因此没有种族状况问题？

Question

也许这是我的天真，也许是我的偏执，但我想我正在寻找一个解决种族状况问题的办法，这似乎应该是如此普遍，会有大量的解决办法，我现在已经找到了一个。。。但我没有。
最简单的情况是，我有一个进程，它应该获取任何记录，其中有一个以上的特定类型。我想使系统/进程的线程/多进程/可重入/当前流行语安全；如果同一进程启动并引入一个竞争条件试图获取感兴趣的行，我希望有明确的赢家/输家：一个成功，另一个错误；实际上，我更喜欢无缝、无声、优雅的“失败”第二种情况是它根本看不到那些会被一审抓住的人。
这就是我的困境。
我的问题是这样的：

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(trans_nbr) > 1
                           LIMIT our_limit_to_have_single_process_grab
                       )
RETURNING row_id

我的想法是：我认为子查询和外部更新之间没有锁，因此不能保证“状态”。那么，如何确保这个过程得到的任何候选人，我们抓住，而他们在同一时间内没有被另一个过程抓住？
我考虑过在子查询的末尾添加一个“ FOR UPDATE”在我的表上，但这不起作用；不能有这个和一个“GROUP BY”（这是计算trans-nbr的计数所必需的）。（由于这将强制在我们的更新之前阻塞任何rans，这将是一个首选的解决方案，因为这样既可以避免由竞争条件引起的错误[两个进程捕获同一行{s}]，又可以允许这些其他进程在不知不觉中很愉快地获得不再包含第一个进程捕获的行的行。唉。）
我已经考虑过锁定表，但是（至少在Postgres中）表锁只在提交之后才被释放；出于测试目的，我不想提交，因此在测试期间（是的，在测试数据库上测试之后在实时数据库上的预实时测试），不需要走这条路。（另外，即使是live，如果有足够的用户/进程，这也会给性能带来不可接受的影响。）
我考虑过让更新依赖于子查询的processing_by值是什么，但是，同样的，这是行不通的：如果在子查询中，将按/有条件（因为现在会有trans_nbr/processing_的子组被计数，这不是我要的）。
我期待着一些尖锐的指向正确的方向嘲笑我问这样一个明显的问题，但这对我来说并不明显（显然；o），我向你保证，我已经研究了几个小时了。
非常感谢您的提示，更不用说解决方案了！
更新：非常感谢 Chris Travers！
我想起了那句关于“ Forrest for the Trees”的台词！:>
这是查询的修改版本，考虑到了这个建议，并添加了另一个“双重检查”。应该是这个。

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                SELECT trans_nbr
                  FROM my_table
                 WHERE trans_nbr IN (
                           SELECT trans_nbr
                             FROM my_table
                         GROUP BY trans_nbr
                           HAVING COUNT(*) > 1 -- Thanks for the suggestion, Flimzy
                            LIMIT our_limit_to_have_single_process_grab
                                    )
                   AND processing_by IS NULL
                       /* Or some other logic that says "not currently being
                          processed".  This way, we ALSO verify we're not
                          grabbing one that might have been UPDATEd/grabbed
                          during our sub-SELECT, while it was being
                          blocked/waiting.

                          This COULD go in our UPDATE/top-level, but unnecessary
                          rows could be locked by this lower-level in that case.
                       */
            FOR UPDATE /* Will block/wait for rows this finds to be unlocked by
                          any prior transaction that had a lock on them.

                          NOTE: Which _could_ allow the prior trans to change
                                our desired rows in the mean time, thus the
                                secondary WHERE clause.
                       */
                       )
RETURNING row_id

我希望Postgres有一个类似于 SKIP LOCKED的特性。尤其是对于基本上是原子行的队列，这些队列需要在不阻塞其他处理的情况下进行处理。 But alas. Maybe someday...? Or "soon"?：-）
现在，您可以添加 NOWAIT以不被任何其他事务阻止，但是请记住，它只会返回一个错误-您必须继续尝试查询，直到查询成功（或放弃）。如果没有NOWAIT，查询将阻塞，直到其他事务释放其锁，或者查询超时。
更新2：所以，在重新阅读并思考之后，再次“Forrest for the Trees”时刻。我可以这样做：

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                        -- This query MAY pull ones we don't want to mess with (already "grabbed")
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(*) > 1
                           LIMIT our_limit_to_have_single_process_grab
                             AND processing_by IS NULL -- only "ungrabbed" ones (at this point)
                       )
      AND processing_by IS NULL -- But THIS will drop out any "bogus" ones that changed between subquery and here
RETURNING row_id

提交事务以释放我们的锁，以及鲍勃的叔叔。
不过，SKIP LOCKED还是很酷的。
一个警告：如果要让工人拉一个有限的（类似于限制1）行和/或项目的数量必须以特定的顺序（例如：FIFO，order BY和/或BY function like Min（id））进行抓取，则可能会出现饿死工人的情况：工人等待并等待，当他们等待的行被解锁时，结果没有一个符合最终标准。有很多方法可以绕过这一点，比如让工人通过补偿跳来跳去，但大多数方法要么复杂，要么缓慢。（通常两者都有。奖金！）
我的功能性要求返回多行，或者没有一行是A-OK的—暂时不做任何事情；请稍睡片刻，然后重新检查，所以这对我来说不是问题。可能是给你的。如果是的话，你会考虑。。。
非阻塞版本：我发现一个 great article正在处理这个问题，结果，它向我介绍了Pg的 Advisory Locks。（ This one也提供了很多信息。）
所以，解决我自己问题的非阻塞解决方案应该是这样的：

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
            -- This query MAY pull ones we don't want to mess with (already "grabbed")
              SELECT trans_nbr
                FROM my_table AS inner_my_table_1
            GROUP BY trans_nbr
              HAVING Count(*) > 1
                 AND Count(*) in ( -- For MY query, since I'm grouping-by, I want "all or none" of trans_nbr rows
                       SELECT Count(*)
                         FROM my_table AS inner_my_table_2
                        WHERE inner_my_table_2.trans_nbr = inner_my_table_1.trans_nbr
                          AND pg_try_advisory_xact_lock(id) -- INT that will uniquely ID this row
                                 )
/* Note also that this will still lock all non-locked rows with this
   trans_nbr, even though we won't use them unless we can grab ALL of the
   rows with same trans_nbr... the rest of our query should be made
   quick-enough to accept this reality and not tie up the server unduly.

   See linked info for more-simple queries not doing group-by's.
*/
               LIMIT our_limit_to_have_single_process_grab
                 AND processing_by IS NULL -- only "ungrabbed" ones (at this point)
                       )
      AND processing_by IS NULL -- But THIS will drop out any "bogus" ones that changed between subquery and here
RETURNING row_id

笔记：
由应用程序来做/尊重咨询锁，所以这不是pancea，但也不是安慰剂。同样，SKIP LOCKED也很方便。
pg_try_advisory_lock，因为v 8.2没有自动解锁，（因此）可以（必须）显式解锁
pg_try_advisory_xact_lock，因为v 9.1，在事务结束时自动解锁，可能不会显式解锁
我还没试过呢！我会编辑/更新当我有。。。

Answer 1

为锁添加一个额外的子查询层怎么样？

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this instance
    WHERE trans_nbr IN (
                    SELECT trans_nbr
                      FROM my_table
                     WHERE trans_nbr IN (
                                 SELECT trans_nbr
                                   FROM my_table
                               GROUP BY trans_nbr
                                 HAVING COUNT(trans_nbr) > 1
                                  LIMIT our_limit_to_have_single_process_grab
                                 )
                        FOR UPDATE
                       )
RETURNING row_id