23.08.2025 15:12
OlegIct 22 18000 Источник

Разбирая взаимоблокировки у клиента, я вспомнил, насколько опасным может быть использование 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 в дочерние таблицы строк, которые ссылаются на заблокированную строку в родительской таблице.

Комментарии (22)


  1. ahdenchik
    23.08.2025 16:49
    #28744834

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

    Или ввести необязательное слово KEY таким образом:

    FOR [KEY] UPDATE

    те, кому важно не путаться, начнут его использовать


  1. Valerdos_UA
    23.08.2025 16:49
    #28744962

    Чтоб ничего не блокировать - из документации:

    To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option.


    1. ahdenchik
      23.08.2025 16:49
      #28745016

      Это подходит не всегда:

      С NOWAIT оператор выдаёт ошибку, а не ждёт, если выбранную строку нельзя заблокировать немедленно. С указанием SKIP LOCKED выбранные строки, которые нельзя заблокировать немедленно, пропускаются.

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


    1. OlegIct Автор
      23.08.2025 16:49
      #28746046

      есть третий, более лучший вариант. В Oracle Database есть третья опция "WAIT n" - ждать n секунд, кроме NOWAIT. В PostgreSQL у команды нет опции, но есть более функциональный аналог в виде параметра lock_timeout:

      /* первая сессия */
START TRANSACTION;
SELECT p_val FROM parent WHERE p_id = 1 FOR UPDATE;
 
                        /* вторая сессия подвисает до снятия блокировки или таймаута */
                        set lock_timeout = '1s';
                        delete from parent where p_id=1;
                        ERROR:  canceling statement due to lock timeout
                        CONTEXT:  while deleting tuple (0,1) in relation "parent"

      Его не указали на странице документации к команде SELECT, где описаны NOWAIT и SKIP LOCKED, поэтому он малоизвестен.

      Смысл статьи в том, что вместо FOR UPDATE в PostgreSQL почти всегда стоит использовать FOR NO KEY UPDATE. То есть при переносе кода с других баз данных или при аудите древнего кода, если есть FOR UPDATE, то стоит проверить - можно ли заменить на FOR NO KEY UPDATE. Обычно такое и делают после переноса кода, так как если FOR UPDATE создаёт излишние блокировки другим сессиям, то обычно возникают взаимоблокировки: в начале статьи поводом к разбирательству были жалобы на взаимоблокировки.


  1. Valerdos_UA
    23.08.2025 16:49
    #28745046

    for update без этих опций - будет ждать разблокировки. Это то, о чем в статье сказали "плохо". С ними - не будет. Значит все будет "хорошо".


    1. Avas_Ton
      23.08.2025 16:49
      #28746028

      Автор про теплое - вы про мягкое.

      В статье написано про избыточность блокировок for update, которые удалось установить. Skip locked или nowait это никак не решают


  1. LaRN
    23.08.2025 16:49
    #28746122

    Часто встречается использование этой конструкции для блокировки select в получении некоторого somefield, когда это поле меняется в параллельной транзакции, чтобы получить актуальное значение, когда поле используется как сквозной счетчик например (всякие кейсы со счетчиком лайков в соц сети или тому подобное) . Т.е. для конкурентного доступа к счетчику.


  1. rikert
    23.08.2025 16:49
    #28746214

    Ну вот, сделали функционал, а он вредным оказался. И так у нас всегда.


    1. OlegIct Автор
      23.08.2025 16:49
      #28746576

      немного не так. Сначала сделали автомобили (FOR UPDATE), потом обнаружили, что они загрязняют окружающую среду, сделали катлизатор (FOR NO KEY UPDATE), но заставлять ставить катализаторы не стали - не переименовали FOR UPDATE в FOR KEY UPDATE, а FOR NO KEY UPDATE в FOR [NO KEY] UPDATE, так как реляционные базы консервативны и в них без спросу не переводят на "лучший тариф".


      1. tbl
        23.08.2025 16:49
        #28753116

        В постгресе еще есть такие же места - катализаторы, когда не стали менять старое поведение для сохранения обратной совместимости, но в новом коде лучше применять их, как, например отсутствие принудительного создания индекса на колонке, на которую навешен foreign key constraint. Можно буквально по руке с двумя пальцами пересчитать ситуации, когда этот индекс не нужен. Но postgresql не создает индекс, и нужно не забыть его создать, и если колонка nullable, то вполне применим и условный индекс с where column_name is not null


        1. OlegIct Автор
          23.08.2025 16:49
          #28753532

          да! не только в PostgreSQL, в Oracle и Sql Server индексы на FK не создаются автоматически. При этом рекомендуют создавать


  1. Pusk1
    23.08.2025 16:49
    #28747936

    Ну это же история про оптимистичные и пессимистичные блокировки. И про то, что foreign key приводит к каскадным блокировкам в рамках SQL стандарта (вот про стандарт могу приврать).
    Правильный подход - foreign key не использовать, select for update использовать для того, чтобы проверить, что с момента последнего чтения запись (иногда несколько да ещё и в связанных сущностях) не изменилась и если не изменилась, то записать. Если блокировать на время проверки не будете, то между чтением и записью может кто-нибудь вклиниться. Этот кейс особенно актуален для программ, связанным с финансами, материальными ценностями, ключевыми справочниками. Мало актуален для большинства транзакций в e-commerce, но и там тоже обязательно встречается.


    1. OlegIct Автор
      23.08.2025 16:49
      #28753560

      констрейнты всегда лучше создавать. Чтобы не было каскадных блокировок, нужно создавать индекс по столбцам FK, выше об этом написали. Оптимистичные блокировки по большей части - зло, их можно использовать в случае, если они помогают не держать открытой простаивающую транзакцию. FOR NO KEY UPDATE - хорошо и может использоваться, где надо


  1. zVlad909
    23.08.2025 16:49
    #28748694

    "Напрасно ты винишь в непостоянстве рок;

    Что не внакладе ты, тебе и невдомек.

    Когда б он в милостях своих был постоянен,

    Ты б очереди ждать своей до смерти мог."

    Омар Хайям

    Такая цитата была на стартовой страницы диалоговой программы Примус задолго до появления сколько-нибудь вменяемых программ на ПК.

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

    Любые попытки что-либо выкруживать заканчиваются еще большими проблемами чем просто ждать и уметь реагировать на то что ожидание может закончится отказом, timeout-ом.

    Не знаю что там в PostgreSQL, но в DB2 есть только один исход кроме просто успешного завершения транзакции после возможного ожидания в очереди - это системная авария после установленного периода ожидания ресурса - timeout.

    Это SQLCODE -911

    Кстати такой же код имеется и в Оракл. Да и многоие другие тоже совпадают. А почему? Да потому что Лари Элистон списывал их с DB2. Вот он из Оракле дока (https://www.databasestar.com/oracle-sqlcode-list/):

    -911 The current unit of work has been rolled back due to deadlock or timeout. reason reason-code, type of resource resource-type, and resource name resource-name

    А вот из DB2 (https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=codes-sql):

    -911 THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR
    TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

    Берём еще пару кодов из DB2:

    -901 UNSUCCESSFUL EXECUTION CAUSED BY A SYSTEM ERROR THAT DOES NOT PRECLUDE THE SUCCESSFUL EXECUTION OF SUBSEQUENT SQL STATEMENTS

    -206 object-name IS NOT VALID IN THE CONTEXT WHERE IT IS USED

    Из Оракл:

    -901 Unsuccessful execution caused by a system error that does not preclude the successful execution of subsequent SQL statements

    -206 column-name is not a column of an inserted table, updated table, or any table identified in a from clause

    А вот интересный код -805. В DB2 это:

    -805 PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason-code

    В Оракл:

    -805 Dbrm or package name location-name.collection-id.dbrm-name.consistency-token not found in plan plan-name. reason reason

    И там и там есть некий dbrm. Но если поискать что это такое, то в DB2 это будет:

    The main output from the precompiler is a database request module (DBRM). A DBRM is a data set that contains SQL statements and host variable information that is extracted from the source program during program preparation. The purpose of a DBRM is to communicate your SQL requests to DB2 during the bind process.

    А в Орал (гугл дает) это:

    .... Oracle database resource manager (DBRM) 

    И это никак не матчится с объяснением SQLCODE -805 в Оракл. Плагиат.

    Потому что Оракл скопировал эти коды с DB2 без осмысления. Некоторым он придал смысл совпадающий с смыслом в DB2, а другие (-805) просто оставил (ссылка на Оракл коды датирована Jan 27, 2016. Да это не официальная документация, но откуда это взялось?).