UPDATE WHERE値はINですGROUP BYを持つサブクエリーなので、競合条件の問題はありませんか？

おそらくそれは私の無邪気な、おそらく私のパラノイアだが、私は競争条件問題の解決策を探していると思うが、それは普遍的でなければならない解決策の洪水と私は今見つけた...しかし、私はしていない。

シンプルなシナリオでは、特定のタイプのレコードが複数あるレコードを取得するプロセスがあります。私はシステム/プロセスをスレッド/マルチプロセッシング/リエントラント/流行語の安全にしたいと思っています。同じプロセスが開始され、興味のある行を獲得しようとする競合状態が発生した場合、私は明確な勝者/敗者があることを望んでいます。実際には、私はシームレスで静かで優雅な "失敗"を第二のものにしたいと思っています。それは、第一のインスタンスが掴んだものを見ていないだけです。

したがって、私のジレンマ。

私が持っている質問は次のようなものです：

   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 on my_table "サブクエリの最後に、それは動作しません。これと "GROUP BY"（これはtrans_nbrのCOUNTを計算するのに必要です）を持つことはできません。 （これにより、更新が保留されている間に他のトランスがブロックされることが強制されるため、これは好ましい解決策となり、競合条件に起因するエラー[同じ行{2}を取得する2つのプロセス]を回避し、これらの他のプロセスは、幸せに邪魔されず、単に最初のプロセスを含む行を含まない行を取得するだけです。

私はテーブルをロックすることを考えましたが、（少なくともPostgresでは）テーブルロックはCOMMITの後にのみリリースされます。テスト目的のために、私はコミットしたくないので、テスト中に（はい、テストDBでテストした後のライブデータベースのプリブラブテスト）、このルートには行かないでしょう。 （さらに、ライブであっても、十分なユーザー/プロセスが与えられれば、これは許容できないパフォーマンス・ヒットになるでしょう）。

私は、更新をサブクエリのprocessing_byの値に依存すると考えましたが、動作しません：サブクエリの in がGROUP BY/HAVING条件を破る場合現在はtrans_nbr/processing_byのサブグループがカウントされていますが、これは私が後にしたものではありません）。

私は右方向のいくつかの鋭い点が私にそのような明白な質問を嘲笑することを期待していますが、明らかに私には明らかではありません（私はあなたに保証します）。

おかげさまで、何のヒントもありません。

UPDATE: Thanks SO MUCH Chris Travers!

That ol' line about "Forrest for the Trees" comes to mind! :>

ここでは、この提案を考慮し、別の「ダブルチェック」を追加して、クエリの修正バージョンを示します。これは必要です。

   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がスキップロックのような機能を持つことを望みます。特に、他の処理をブロックせずに処理する必要のある本質的に原子的な行のキューの場合。 しかし、悲しいです。 いつか...？ < a href = "http://www.postgresql.org/message-id/[email protected]om" rel = "nofollow noreferrer">または "soon"？ :-)

今のところ、 NOWAIT は他のトランザクションによってブロックされないように注意してください。ただ単にエラーでダンプするだけです。成功するか、あきらめるまでクエリを試し続けなければなりません。 NOWAITを指定しないと、他のトランザクションがロックを解除するまで、またはクエリがタイムアウトするまでクエリがブロックされます。

UPDATE 2: SO, after re-re-re-reading this and thinking about it, again "Forrest for the Trees" moment. I can simply do like this:

   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

OURロックとBobのyer叔父を解放するトランザクションをコミットします。

SKIP LOCKEDはまだまだ超クールです。

A CAVEATE: If one was to have workers pulling a limited (like LIMIT 1) number of rows and/or items must be grabbed in a certain order (e.g.: FIFO, either ORDER BY and/or by function like Min(id)), there can be cases of starved workers: a worker waits and waits, and when the row(s) they were waiting for unblocks, turns out none of them meet its final criteria. There are a number of ways to try to get around this, like having workers jumping around via OFFSET, but most are either complex or slow. (Usually both. BONUS!)

MY functionailty expects multiple rows returned, or none is A-OK - nothing to do for now; sleep for a bit and recheck, so this isn't a problem for me. It may be for you. If so, you'll want to consider a...

NON-BLOCKING VERSION: I found a great article working with this very problem, turns out, and it introduced me to Pg's Advisory Locks. (This one was quite informative, too.)

したがって、私自身の問題に対するノンブロッキングの解決策は次のようになります。

   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

注釈：

• It's up to the applications to do/respect Advisory Locks, so this is no pancea, but nor is it a placebo. Again, SKIP LOCKED would be very handy because of this.
• pg_try_advisory_lock, since v 8.2, does not auto-unlock, (thus) may (MUST) be explicitly unlocked
• pg_try_advisory_xact_lock, since v 9.1, auto-unlocks at end of transaction, may NOT be explicitly unlocked
• I HAVE NOT TESTED THIS YET! I'll edit/update when I have...