Postgres тупик

Question

Я вижу некоторые необъяснимые тупики в нашей базе данных Postgres. Упрощая связанные запросы, одна из транзакций, вовлеченных в тупик:

BEGIN;
UPDATE A SET CHUNK_ID=1, STATUS='PROCESSING' WHERE ID IN (
    SELECT ID FROM A
    WHERE CHUNK_ID IS NULL
    ORDER BY O_ID
    LIMIT 1000
    FOR UPDATE 
);
COMMIT;

а другой это:

BEGIN;
UPDATE A SET STATUS='SENT' WHERE ID = 1; 
UPDATE A SET STATUS='SENT' WHERE ID = 2;
UPDATE A SET STATUS='SENT' WHERE ID = 3;
...
COMMIT; 

Мой вопрос: как здесь завести тупик? Я не могу придумать ни одного сценария, в котором 1-я транзакция может привести к тупику, независимо от того, выполняется ли одновременно какой-либо другой запрос.

Есть ли такой случай, то есть ОБНОВЛЕНИЕ, использующее вложенный SELECT ... ДЛЯ ОБНОВЛЕНИЯ может быть частью тупика?

Спасибо

Answer 1

(Это гипотеза, но, надеюсь, образованная.)

Все зависит от порядка, в котором строки заблокированы с помощью SELECT ... ORDER BY O_ID ... FOR UPDATE. Если порядок O_ID отличается от порядка ID, то вполне возможно иметь ситуацию, подобную этой:

ID    O_ID
--    ----
1     2
2     1

• Транзакция A блокирует строку с ID=2.
• Транзакция B блокирует строку с ID=1.
• Транзакция A пытается заблокировать строку с ID=1, но вынуждена ждать транзакции B.
• Транзакция B пытается заблокировать строку с ID=2, но вынуждена ждать транзакции A.

ВНИМАНИЕ: Даже если порядок O_ID совпадает с порядком идентификатора, возможно, что предложение ORDER BY фактически не гарантирует порядок блокировки (он просто гарантирует порядок, в котором возвращаются результаты). К сожалению, это кажется плохо документированным. Что бы это ни стоило, кажется, что Oracle (всегда) не соблюдает ORDER BY, когда дело доходит до блокировки, так что я был бы осторожен и с PostgreSQL.

Как правило, решение взаимоблокировки заключается в том, чтобы всегда блокировать в одном и том же порядке. Предполагая, что ORDER BY фактически гарантирует порядок блокировки, вы можете просто включить SELECT... ORDER BY O_ID... FOR UPDATE во второй транзакции. Или же используйте ORDER BY ID в первой транзакции.

Кстати, почему вы явно блокируете в первую очередь? Что именно вы пытаетесь сделать с этим?