Означает ОБНОВИТЬ, ГДЕ Значение 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's). (Так как это приведет к блокировке любых-тоже-rans в ожидании нашего обновления, это было бы предпочтительным решением, так как тогда и то, и другое избежало бы ошибки, вызванной условием гонки [два процесса захватили одну и ту же строку {s}], и позволило бы те другие процессы, которые блаженно не подозревают и просто получают строки, которые больше не включают те, которые захватил первый процесс. Увы.)
Я думал о блокировке таблицы, но (по крайней мере, в Postgres) блокировки таблиц снимаются только после COMMIT; для целей тестирования я не хочу COMMIT, поэтому во время тестирования (да, перед живым тестированием на действующей базе данных ПОСЛЕ тестирования на тестовой базе данных) не пойдет этот путь. (Кроме того, даже в режиме реального времени это может привести к недопустимому снижению производительности при достаточном количестве пользователей / процессов.)
Я думал о том, чтобы сделать обновление зависимым от того, какое значение processing_by было для нашего подзапроса, но, опять же, это не сработает: если в подзапросе будет нарушено условие GROUP BY/HAVING (как сейчас будет подсчитываются группы trans_nbr/processing_by, а это не то, что мне нужно).
Я ожидаю, что какой-то пронзительный момент в "Правильном направлении" будет насмехаться над мной, задавая такой очевидный вопрос, но он не был очевиден для меня (очевидно; о), и, уверяю вас, я изучал это буквально часами.
Большое спасибо за любые подсказки, не говоря уже о решениях!
ОБНОВЛЕНИЕ: Большое спасибо Крис Трэверс!
Эта старая линия о " Форресте для деревьев " приходит на ум!:>
Вот модифицированная версия запроса, учитывающая это предложение и добавляющая еще одну "двойную проверку". Это должно быть ОДИН.
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. Особенно для очередей с атомарными строками, которые нужно обрабатывать, не блокируя другую обработку. Но увы. Возможно когда-нибудь...? Или "скоро"?:-)
На данный момент можно добавить NOWAIT, чтобы НЕ блокироваться какой-либо другой транзакцией (-ами), однако имейте в виду, что он просто возвращает обратно с ошибкой - вам придется продолжать пытаться выполнить свой запрос до тех пор, пока он не завершится успешно (или не откажется). Без NOWAIT запрос блокируется до тех пор, пока другие транзакции не снимают свои блокировки или не истекает время ожидания запроса.
ОБНОВЛЕНИЕ 2: ТАК, после перечитывания этого и размышления об этом, снова "Форрест для Деревьев". Я могу просто сделать так:
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 все равно будет супер-круто.
A CAVEATE: если нужно, чтобы работники тянули ограниченное (например, LIMIT 1) количество строк и / или элементов, они должны быть захвачены в определенном порядке (например: FIFO, ORDER BY и / или с помощью функции, такой как Min(id)) Могут быть случаи голодающих работников: работник ждет и ждет, и когда ряды, которых они ожидали, разблокируют, оказывается, что ни один из них не отвечает его окончательным критериям. Есть несколько способов обойти это, например, заставить работников прыгать через OFFSET, но большинство из них либо сложные, либо медленные. (Обычно оба. БОНУС!)
Моя функция ожидает, что будет возвращено несколько строк, или ни одна из них не в порядке - пока ничего не поделаешь; поспать немного и перепроверить, так что это не проблема для меня. Это может быть для вас. Если это так, вы хотите рассмотреть...
НЕ БЛОКИРУЮЩАЯ ВЕРСИЯ: Оказалось, что я нашел отличную статью, работающую с этой самой проблемой, и познакомил меня с консультативными блокировками Pg. ( Этот тоже был довольно информативным.)
Итак, неблокирующее решение моей проблемы должно выглядеть так:
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
ЗАМЕТКИ:
- Приложения должны выполнять / уважать консультативные блокировки, так что это не панча, но и не плацебо. Опять же, SKIP LOCKED будет очень удобно из-за этого.
- pg_try_advisory_lock, начиная с версии 8.2, не выполняет автоматическую разблокировку, (таким образом) может (ДОЛЖНО) быть явно разблокирована
- pg_try_advisory_xact_lock, поскольку v 9.1, автоматическая разблокировка в конце транзакции, НЕ может быть явно разблокирована
- Я НЕ ПРОВЕРИЛ ЭТОГО! Я буду редактировать / обновлять, когда у меня будет...
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