Разбирая взаимоблокировки у клиента, я вспомнил, насколько опасным может быть использование SELECT FOR UPDATE при конкурентном доступе. В этом нет ничего нового, но я заметил, что многие не знают о режимах блокировки строк в PostgreSQL. Я решил подробно описать, когда следует избегать SELECT FOR UPDATE.

SELECT FOR UPDATE используют, чтобы избежать потерянных обновлений

Команды UPDATE и DELETE блокируют строки, чтобы в других сессиях их одновременно не меняли. На уровне изоляции транзакций READ COMMITED, который используется по умолчанию, возникает гонка (race condition): вторая транзакция может изменить строку между её чтением и обновлением в первой транзакции. В этом случае, результат изменения из второй транзакции будет затёрт обновлением из первой транзакции. Это называют аномалией потерянного обновления (lost update).

Если вы не хотите использовать более высокий уровень изоляции транзакций (и обрабатывать ошибки сериализации), то можете устранить "гонку", заблокировав строку во время её чтения:

START TRANSACTION;
 /* блокирование строки, чтобы другие транзакции не могли её обновить или удалить*/
SELECT data FROM tab WHERE key = 42 FOR UPDATE;
 /* здесь могут быть команды обработки данных */
 /* обновление строки новыми данными */
UPDATE tab SET data = 'new' WHERE key = 42;
 COMMIT;

Этот код плохой и его не стоит использовать! Чтобы понять, чем он плох, посмотрим детали блокирования строк.

Как PostgreSQL использует блокировки для поддержки внешних ключей

Рассмотрим, как PostgreSQL обеспечивает ссылочную целостность на примере таблиц:

CREATE TABLE parent (
   p_id bigint PRIMARY KEY,
   p_val integer NOT NULL
);
INSERT INTO parent VALUES (1, 42);
CREATE TABLE child (
   c_id bigint PRIMARY KEY,
   p_id bigint REFERENCES parent
);

Начнем транзакцию, в которой вставим строку в дочернюю таблицу. Строка будет ссылаться на строку родительской таблицы:

START TRANSACTION;
INSERT INTO child VALUES (100, 1);

На этом этапе вставленная строка ещё не видна другим сессиям, так как транзакция ещё не зафиксирована. Если другая транзакция удалит строку в родительской таблице, а потом наша транзакция зафиксируется, то будет нарушено ссылочное ограничение целостности. PostgreSQL должен это предотвратить!

Чтобы защитить строку родительской таблицы, команда INSERT INTO child блокирует строку таблицы parent (на которую ссылается вставляемая строка) в режиме FOR KEY SHARE. Попытка удалить заблокированную строку не удастся, так как DELETE столкнется с блокировкой FOR KEY SHARE. Если наша транзакция (которая вставляет строку в child) откатится, то DELETE сможет удалить строку. Если наша транзакция зафиксируется, то DELETE получит ошибку нарушения ограничения целостности.

Блокировки строк, которые устанавливают UPDATE и DELETE

Мы рассмотрели, как PostgreSQL использует блокировку строк FOR KEY SHARE. В таблице совместимости блокировок есть ещё три типа блокировок: FOR UPDATE, FOR NO KEY UPDATE и FOR SHARE. Посмотрим, когда PostgreSQL использует эти блокировки:

• PostgreSQL устанавливает FOR UPDATE перед выполнением команды DELETE или перед командой UPDATE, которая изменяет значение в столбце, входящим в уникальный индекс, который не содержит выражений и не является частичным индексом

• PostgreSQL устанавливает FOR NO KEY UPDATE перед всеми остальными UPDATE

• PostgreSQL не использует блокировку FOR SHARE для обслуживания команд.

Другими словами, PostgreSQL использует FOR UPDATE если меняется значение в столбце, который может быть частью первичного или уникального ключа, на который может ссылаться внешний ключ. Только такие изменения потенциально могут конфликтовать со вставками строк в дочернюю таблицу. UPDATE, не меняющие ключевые столбцы, не конфликтуют с вставками строк в дочернюю таблицу. Такие UPDATE не блокируются, так как блокировка FOR NO KEY UPDATE не конфликтует с блокировкой FOR KEY SHARE.

Проблема с SELECT FOR UPDATE в PostgreSQL

Проблема SELECT FOR UPDATE в том, что блокировка излишне сильная. Вопреки интуиции, большинству команд UPDATE не требуется блокировка FOR UPDATE, так как они не конфликтуют с вставками в дочерние таблицы. Если вы используете SELECT FOR UPDATE по таблице, на которую ссылается внешний ключ, вы заблокируете INSERTстрок в дочернюю таблицу, которые будут относиться к заблокированной строке родительской таблицы:

/* первая сессия */
START TRANSACTION;
 SELECT p_val FROM parent WHERE p_id = 1 FOR UPDATE;
 
                        /* вторая сессия подвиснет */
                        INSERT INTO child VALUES (100, 1);

Использование SELECT FOR UPDATE плохо сказывается на конкурентном доступе, так как приводит к ненужным блокировкам работы сессий. Если вы не планируете удалить строку или изменять значение в ключевом столбце, всегда используйте SELECT FOR NO KEY UPDATE.

SELECT FOR UPDATE не является правильной блокировкой для UPDATE?

Да, всё верно. Этот сбивающий с толку факт - историческое наследие. В старые недобрые времена в PostgreSQL было всего два режима блокирования строк: FOR SHARE и FOR UPDATE. FOR UPDATE - для изменения данных, FOR SHARE - блокировка строк, на которые ссылается внешний ключ. В те времена, блокировка строки родительской таблицы командой UPDATE (если на таблицу ссылался внешний ключ) всегда конфликтовала с блокировкой, устанавливаемой при вставке строк в дочернюю таблицу.

Коммит 0ac5ad5134 улучшил конкурентность, введя уровни блокировки строк FOR KEY SHARE и FOR NO KEY UPDATE.

Возможно, было бы лучше переименовать FOR UPDATE в FOR KEY UPDATE и переназначить команду FOR UPDATE на более слабый режим FOR NO KEY UPDATE. Однако этот корабль давно ушёл.

Заключение

Если вы не планируете удалять строку или изменять ключевой столбец, используйте SELECT FOR NO KEY UPDATE. Тем самым вы не заблокируете команды INSERT в дочерние таблицы строк, которые ссылаются на заблокированную строку в родительской таблице.