Postgres запрос SELECT не видит данных из закомиченной транзакции при изоляции Read Commited
Столкнулся со странным поведением Postgres при параллельном выполнении запроса в разных транзакциях. Postgres 15.3, уровень изоляции транзакций на БД - Read Commited. Запрос выглядит так:
with inserting as (
insert into device(device_guid, platform, created_at)
values (
'5f0ca10f-a257-44ac-940e-a808c63a9914'::uuid,
'WEB',
NOW()
)
ON CONFLICT (device_guid) DO NOTHING
returning *
)
select id, device_guid, platform, created_at, 'INSERT' as db_operation from inserting
UNION ALL
select id, device_guid, platform, created_at, 'GET' as db_operation from device where device_guid = '5f0ca10f-a257-44ac-940e-a808c63a9914'::uuid
(на device_guid стоит unique index)
Ожидалось следующее поведение:
- На вход приходят device_guid и некоторые данные (в примере '5f0ca10f-a257-44ac-940e-a808c63a9914', 'WEB', NOW())
- В CTE происходит попытка вставки переданного device_guid. Если такой device_guid уже есть, то просто выходим из CTE к следующему шагу (select). Если такого device_guid нет в таблице, то вставляем его в таблицу и так же вставляем его в CTE inserting
- Далее делаем селект из физической таблицы device и временной CTE таблицы inserting. Здесь db_operation подскажет, откуда взяли строку, из CTE (INSERT) или строка уже была в таблице device (GET).
- Если выполнить дважды запрос, то все ожидаемо отрабатывает: первый раз появляется одна строка с db_operation = 'INSERT', а второй раз одна строка с db_operation = 'GET'.
Но все ломается, когда две транзакции одновременно начинают выполнять этот запрос c одинаковым device_guid.. Я рассчитывал, что правильным будет следующее поведение:
- Транзакция А стартует чуть раньше транзакции Б
- Тр-я А пытается вставить строку в таблицу, в которой есть уникальный индекс. А это значит, что тр-я А возмет эксклюзивную блокировку на строку и share блокировку на индекс до тех пор, пока транзакция не закоммитится.
- Тр-я Б видит, что тр-я А взяла блокировку и просто ждет, пока блокировка отпустится.
- Тр-я А делает успешную вставку и переходит к двум select. Но т.к. транзакция еще не закомичена, то блокировка не снимается.
- Тр-я А завершает свои селекты и весь скрипт запроса и коммитит транзакцию с отпуском блокировки.
- Тр-я Б видит, что блокировка снята, пытается вставить строку в device с тем же device_guid, получает конфликт, который спокойно пропускает и переходит к блоку с select
- На этом этапе уже должна была быть закомичена тр-я А (а точнее уже в п.5), а значит select из таблицы device уже должен увидеть строку, которую вставила тр-я А. Это гарантирует нам уровень изоляции Read commited.
- Здесь блок select-ов с union all должен был вернуть одну строку с db_operation = 'GET', т.е. строку уже лежащую в физической таблице device, которую туда положила тр-я А
Но по-факту, 8 пункт возвращает 0 строк..
Ошибка обнаружилась изначально в spring data jdbc и jooq, НО затем я многократно воспроизвел ее в стандартном psql. Сделать это можно обернув sql-скрипт в begin; ... commit; и выполнив его в двух отдельных консолях psql. но только в одной прописать begin; ... (без commit;), а затем во второй консоли весь скрипт с begin; ... commit; Если глянуть локи, то видно, что вторая транзакция будет заблокирована первой и виден лок на юник индексе поля device_guid. Но как только ввести commit; в первой консоли, то вторая завершит свою транз-ю и вернет 0 строк..
Такое ощущение, что происходит это потому что, когда готовится коммит транз-ии А, то сначала отпускается лок на индексе и строке, а только затем происходит коммит всей транзакции А. И за это время между отпуском лока и комитом, транзакция Б успевает выполнить свой код, так и не увидев закомиченную строку от транзакции А.. Третий день пытаюсь понять, это я ожидаю не того поведения, либо это постгрес ведет себя неожиданно. Пересматривал доки по блокировкам, CTE, уровням изоляции. Вроде бы все описано так, как я ожидаю. Буду благодарен любой информации!