SELECT FOR UPDATE - как правильно использовать блокировку

В самом начале своего инженерного пути я работала с транзакциями в биллинге, и часто встречалась с дэдлоками при обновлении данных, но практически ничего не знала об устройстве процесса блокировок и тем более о том, как это можно отладить.

Тогда бы мне очень пригодилась эта статья, в которой супер понятно изложено в каких случаях какую блокировку использовать.

🤩При работе с транзакциями в PostgreSQL есть проблема «потерянного обновления» - это когда две транзакции понимают, что данные не изменились, читают одни и те же строки и обе пытаются их изменить одновременно. В итоге, одна перезаписывает изменения другой, и данные теряются.

Простой способ избежать этого - заблокировать нужные строки во время чтения, чтобы другие транзакции не могли их поменять или удалить. Для этого чаще всего используют SELECT FOR UPDATE:

sql
BEGIN;

SELECT * FROM orders WHERE order_id = 123 FOR UPDATE;

/* здесь логика обработки заказа */

UPDATE orders SET status = 'processed' WHERE order_id = 123;

COMMIT;

Таким образом гарантируется, что пока ваша транзакция не закончилась, никто другой не сможет обновить или удалить эти строки.

🤩 Но чаще всего операция SELECT FOR UPDATE избыточна и очень сильно влияет на производительность.

В PostgreSQL существует несколько режимов блокировок строк, которые влияют на параллелизм транзакций:

⭐️FOR KEY SHARE - самый мягкий режим, нужен для проверок ссылочной целостности (например, внешних ключей). Блокирует строку от удаления или изменения ключа, но позволяет обновлять обычные поля и разрешает другим транзакции читать данные под своей блокировкой.

⭐️FOR SHARE - блокирует строку для предотвращения изменений, позволяя читать с разделяемой блокировкой. Позволяет нескольким транзакциям одновременно читать данные, но никто не может их менять.

⭐FOR NO KEY UPDATE - блокирует строку так, чтобы никто не мог её удалить или изменить ключи, и дает эксклюзивное право обновлять неключевые поля. Почти всегда подходит для обычных обновлений.

⭐FOR UPDATE - самый жёсткий режим блокировки, блокирует строку полностью: эксклюзивное право на изменение или удаление строк. Использовать стоит только если вы меняете ключи или собираетесь удалять строки. При этом оставляет право читать ту же строку другим параллельным транзакциям, но только с помощью обычного оператора SELECT (без явных блокировок).

Что происходит, если использовать SELECT FOR UPDATE, когда достаточно FOR NO KEY UPDATE?

Получаются лишние блокировки, которые блокируют не только обновления, но и вставки в дочерние таблицы, если в базе есть внешние ключи. Это приводит к падению производительности, появлению тайм-аутов и дэдлоков.

🔥Ключевые конфликты:

🤩FOR UPDATE конфликтует абсолютно со всеми другими режимами, гарантируя эксклюзивный доступ для полного изменения или удаления строки.

🤩FOR NO KEY UPDATE конфликтует с другими FOR NO KEY UPDATE и с FOR SHARE, потому что FOR SHARE запрещает любые изменения строки, а FOR NO KEY UPDATE разрешает изменения неключевых полей. При этом, не конфликтует с FOR KEY SHARE - это ключевая особенность для повышения параллелизма.

🤩FOR SHARE конфликтует с режимами обновления (FOR UPDATE и FOR NO KEY UPDATE), поскольку его цель - гарантировать, что строка не изменится вообще.

🤩FOR KEY SHARE конфликтует только с FOR UPDATE, потому что FOR UPDATE может включать удаление или изменение ключевых полей, что запрещено режимом FOR KEY SHARE.

Также приложила к посту алгоритм выбора уровня блокировки от автора статьи.

©️что-то на инженерном