Засоби UPDATE WHERE Value є в підзапит, який має GROUP BY, так що немає Проблеми з гоночним станом?

Може бути, це моя наївність, мабуть, моя параноїда, але я думаю, що я шукаю рішення проблеми проблеми перегонів, яка, схоже, повинна бути такою загальною, там буде повені рішень, і я вже знайшов це зараз ... але у мене немає.

Спрощений сценарій - у мене є процес, який повинен захопити будь-які записи, де є більше ніж одне з певних типів. Я хотів би зробити систему/процес (-и) thread-/multiprocessing/reentrant-/buzzword-of-the-day-safe; якщо той самий процес стартує і вводить стан перегонів, намагаючись захопити цікаві рядки, я б хотів, щоб там були чітко визначені переможці/програли: успіх для одного, помилка для іншого; насправді, я віддаю перевагу бездоганній, мовчазній, граціозній "провалці" для другого, тому що він просто не побачить тих, які були б схоплені в першій інстанції.

Отже, моя дилема.

Мій запит виглядає так:

   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

Моя думка: я думаю, що немає блокування, тому немає гарантії "держави" між підзаписом та зовнішнім оновленням. Отже, як забезпечити, щоб будь-які кандидати ЦЕ отримували, ми захоплюємо , і вони не були захоплені іншим процесом у середньому?

Я думав про додавання " Для оновлення на my_table "в кінці підзапиту, але це не спрацює; не може мати цього і "GROUP BY" (що необхідно для з'ясування COUNT trans_nbr's). (Оскільки це призведе до блокування будь-яких блоків до нашого оновлення, це було б кращим рішенням, оскільки тоді це дозволило б уникнути помилки, викликаної раси-умовами [два процеси захоплення того ж ряду {s}] і дозволити ці інші процеси блаженно непередбачувані і просто отримують рядки, які більше не включають тих, котрі схопили перший процес. На жаль.)

Я думав про блокування таблиці, але (у Postgres, принаймні) столові замки випускаються тільки після COMMIT; для цілей тестування я не хочу COMMIT, тому під час тестування (так, попереднє тестування в живій базі даних після перевірки на тесті db) він не зробить, щоб піти на цей маршрут. (Плюс, навіть жити, це призведе до неприйнятного враження продуктивності, коли надається достатня кількість користувачів/процесів.)

Я думав про те, що оновлення залежить від того, яке значення для обробки було для нашого підзапиту, але знову ж таки це не спрацює: якщо підзапит, він порушить умова GROUP BY/HAVING ( як зараз буде підраховуватись підгрупи trans_nbr/processing_by, що не є тим, що я пізніше).

Я чекаю певного прохолоду в правильному напрямку, який насміхається над мною з таким очевидним запитанням, але це не було очевидним для мене (очевидно, o), і я запевняю вас, я досліджував це протягом буквально годин.

Велике спасибі за будь-які натяки, не кажучи вже про рішення!


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 мав подібну функцію SKIP LOCKED . Спеціально для черг по суті атомних рядків, які потрібно обробляти, не блокуючи іншу обробку. Але, на жаль, Можливо коли-небудь ...? < a href = "http://www.postgresql.org/message-id/[email protected]om" rel = "nofollow noreferrer"> Або "скоро"? :-)

Наразі можна додати 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

ПОВИННІ виконувати транзакцію, щоб випустити наші блокування, а дядько Боба.

СКАЗАТИ БЛОКИРОВАНО, все-таки буде супер-класно, хоча.

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...
1
Незначна точка: якщо trans_nbr іноді має значення NULL, ви, ймовірно, отримаєте кращі результати за допомогою COUNT (*) , а також COUNT (trans_nbr) , оскільки, як вважаю, це може бути краще оптимізованим .
додано Автор Flimzy, джерело
Ви думали про тестування цього в двох сесіях SQL?
додано Автор Mike Sherrill 'Cat Recall&, джерело
@ MikeSherrill'Catcall: Так, я думав про це. Почав працювати деякі тестові дані/запити, але отримав запрошення до інших завдань. (Я переглядаю це зараз, тому що хтось був досить люб'язним, щоб запропонувати рішення. :-) 1. Логічно, проблема, яку я згадую, - це "теоретично можливо", проблема видно чи ні. 2. Навіть при тестуванні подібні проблеми паралельності важко відтворити. Отже, "фактично бачачи це сталося" не завжди можливо. І без детальних знань про джерело ... (Так, FLOSS, але цей тип коду важкий для мене; -], щоб пройти через ... ESP, коли я не працюю над цим 24/7
додано Автор pythonlarry, джерело
@Flimzy: Я давно давав вам реквізити в іншому місці, але хотів тут вигукнути: ДЯКУЄ, щоб вказати це! :>
додано Автор pythonlarry, джерело

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
1
додано