13 Блокировки строк

PostgreSQL 18 · Издание 2026 года · Китайский перевод доступен для PostgreSQL 18

简体中文 · PG14 English · PG14 简体中文 · PG18Русский · PG18

13.1. Устройство

Благодаря изоляции на основе снимков табличные строки не требуется блокировать при чтении. Но нельзя допустить, чтобы две транзакции изменяли одну и ту же строку в один момент времени. Строки нужно блокировать, вот только тяжелые блокировки плохо подходят для этого: каждая из них занимает место в разделяемой памяти сервера (сотни байт, не считая вспомогательной инфраструктуры), а внутренние механизмы не рассчитаны на работу с огромным количеством одновременно существующих блокировок.

В некоторых СУБД эта проблема решается повышением уровня блокировки: если блокировок уровня строк становится слишком много, они заменяются одной более общей блокировкой (например, уровня страницы или всей таблицы). Это упрощает реализацию, но может приводить к сильному снижению эффективности.

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

Обычно строка блокируется при изменении или удалении. В обоих случаях актуальная версия строки помечается как удаленная. Признаком служит номер транзакции в поле xmax, и этот же номер (в сочетании с дополнительными информационными битами) указывает на то, что строка заблокирована. Когда какая-либо транзакция собирается изменить строку, но видит в поле xmax актуальной версии номер незавершенной транзакции, она обязана дождаться ее завершения. После этого все блокировки будут сняты, и ожидающая транзакция сможет продолжить свою операцию над строкой. (с. 87)

Такое решение позволяет блокировать сколько угодно строк, не потребляя никаких ресурсов.

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

13.2. Режимы блокировки строки

Существует четыре режима, в которых можно заблокировать строку¹. Два режима представляют исключительные блокировки, которые одновременно может удерживать только одна транзакция, и еще два — разделяемые блокировки, которые могут удерживаться несколькими транзакциями.

Матрица конфликтов выглядит следующим образом:

Режим	Key Share	Share	No Key Update	Update
Key Share				×
Share			×	×
No Key Update		×	×	×
Update	×	×	×	×

Таблица 13.1 Матрица конфликтов блокировок строк

Исключительные режимы

Режим Update предполагает изменение любых полей строки или ее удаление, а режим No Key Update — изменение только тех полей, которые не входят в уникальные индексы (иными словами, такое изменение, которое не может затронуть внешние ключи).

Команда UPDATE сама выбирает минимальный подходящий режим блокировки; поскольку ключи обычно не меняются, чаще всего строки блокируются в режиме No Key Update.

Создадим функцию, которая с помощью расширения pageinspect покажет интересующую нас информацию о версиях строк, а именно поле xmax и некоторые информационные биты:

=> CREATE FUNCTION row_locks(relname text, pageno integer)
RETURNS TABLE(
  ctid tid, xmax text,
  lock_only text, is_multi text,
  keys_upd text, keyshr text, shr text
) AS $$
SELECT (pageno,lp)::text::tid,
  t_xmax,
  CASE WHEN t_infomask  & 128   = 128   THEN 't' END,
  CASE WHEN t_infomask  & 4096  = 4096  THEN 't' END,
  CASE WHEN t_infomask2 & 8192  = 8192  THEN 't' END,
  CASE WHEN t_infomask  & 16    = 16    THEN 't' END,
  CASE WHEN t_infomask  & 16+64 = 16+64 THEN 't' END
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE sql;

Начнем транзакцию и обновим сумму первого счета в таблице accounts (ключ не меняется) и номер второго счета (ключ меняется):

=> BEGIN;
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;
=> UPDATE accounts SET id = 20 WHERE id = 2;

Заглянем в страницу:

=> SELECT * FROM row_locks('accounts',0) LIMIT 2;
 ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr | shr
-------+--------+-----------+----------+----------+--------+-----
 (0,1) | 191451 |           |          |          |        |
 (0,2) | 191451 |           |          | t        |        |
(2 rows)

Режим блокировки определяется информационным битом keys_updated.

=> ROLLBACK;

То же самое поле xmax используется как признак блокирования командой SELECT FOR, но в этом случае проставляется дополнительный информационный бит xmax_lock_only. Он говорит о том, что версия строки только заблокирована, но не удалена и по-прежнему является актуальной:

=> BEGIN;
=> SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;
=> SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
=> SELECT * FROM row_locks('accounts',0) LIMIT 2;
 ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr | shr
-------+--------+-----------+----------+----------+--------+-----
 (0,1) | 191452 | t         |          |          |        |
 (0,2) | 191452 | t         |          | t        |        |
(2 rows)
=> ROLLBACK;

Разделяемые режимы

Режим Share гарантирует, что прочитанная строка не будет изменена другой транзакцией. Режим Key Share допускает изменение любых полей строки, кроме ключевых.

Из разделяемых режимов само ядро PostgreSQL использует только Key Share при проверке внешних ключей. Он совместим с исключительным режимом No Key Update, то есть проверка внешних ключей не мешает одновременному обновлению любых неключевых полей. Конечно, приложения могут использовать любой подходящий режим.

Подчеркну еще раз, что при чтении не используются никакие блокировки уровня строки.

=> BEGIN;
=> SELECT * FROM accounts WHERE id = 1 FOR KEY SHARE;
=> SELECT * FROM accounts WHERE id = 2 FOR SHARE;

В версиях строк видим:

=> SELECT * FROM row_locks('accounts',0) LIMIT 2;
 ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr | shr
-------+--------+-----------+----------+----------+--------+-----
 (0,1) | 191453 | t         |          |          | t      |
 (0,2) | 191453 | t         |          |          | t      | t
(2 rows)

В обоих случаях установлен бит xmax_keyshr_lock, а режим Share можно распознать, посмотрев на другие информационные биты².

13.3. Мультитранзакции

Признак блокировки представлен номером блокирующей транзакции в поле xmax, но разделяемые блокировки могут удерживаться несколькими транзакциями одновременно. Как в одно поле записать сразу несколько номеров?

Для разделяемых блокировок применяются так называемые мультитранзакции ³ (MultiXact). Мультитранзакция — это группа транзакций, которой присвоен отдельный номер. Детальная информация об участниках такой группы и режимах их блокировок хранится в файлах в каталоге PGDATA/pg_multixact. Поскольку количество участников мультитранзакций может быть произвольным, работа со страницами в памяти организована с помощью двух SLRU-кешей: один для состава мультитранзакций, а второй для смещений, по которым следует искать информацию в первом кеше. Размер кешей настраивается параметрами multixact_member_buffers и multixact_offset_buffers. (с. 309) (по умолчанию: 32) (по умолчанию: 16)

Номер мультитранзакции имеет ту же размерность, что и обычный номер транзакции (32 бита), но номера выделяются независимо, то есть номера транзакций и мультитранзакций могут пересекаться. Чтобы отличить один вид транзакций от другого, используется дополнительный информационный бит xmax_is_multi.

Добавим к имеющимся блокировкам еще одну исключительную, выполненную другой транзакцией (это можно сделать, поскольку режимы Key Share и No Key Update совместимы между собой):

=> BEGIN;
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;

=> SELECT * FROM row_locks('accounts',0) LIMIT 2;
 ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr | shr
-------+--------+-----------+----------+----------+--------+-----
 (0,1) | 1      |           | t        |          |        |
 (0,2) | 191453 | t         |          |          | t      | t
(2 rows)

В первой строке обычный номер заменен на номер мультитранзакции — об этом говорит бит xmax_is_multi.

Чтобы не вникать в детали реализации мультитранзакций, можно воспользоваться расширением pgrowlocks, которое позволяет увидеть всю информацию о всех типах блокировок строк в удобном виде:

=> CREATE EXTENSION pgrowlocks;
=> SELECT * FROM pgrowlocks('accounts') \gx
-[ RECORD 1 ]---------------------------------
locked_row | (0,1)
locker     | 1
multi      | t
xids       | {191453,191454}
modes      | {"For Key Share","No Key Update"}
pids       | {65884,66190}
-[ RECORD 2 ]---------------------------------
locked_row | (0,2)
locker     | 191453
multi      | f
xids       | {191453}
modes      | {"For Share"}
pids       | {65884}

Такой запрос внешне похож на обращение к представлению pg_locks, но функция pgrowlocks читает табличные страницы, так как в оперативной памяти нет информации о блокировках уровня строки.

=> COMMIT;

=> ROLLBACK;

Поскольку для мультитранзакций выделяются 32-битные номера, из-за ограничения разрядности счетчика с ними возникает та же проблема переполнения (xid wraparound), что и с обычным номером. Поэтому для номеров мультитранзакций необходимо выполнять аналог заморозки — заменять старые номера на новые (или на обычный номер транзакции, если в момент заморозки блокировка удерживается уже только одной транзакцией)⁴. (с. 134)

Но если заморозка обычных номеров транзакций выполняется только для поля xmin (так как версии с непустым полем xmax неактуальны и будут очищены), то для мультитранзакций, наоборот, замораживается поле xmax: актуальная версия строки может постоянно блокироваться все новыми транзакциями в разделяемом режиме.

За заморозку мультитранзакций отвечают параметры сервера, аналогичные тем, что управляют обычной заморозкой: vacuum_multixact_freeze_min_age, vacuum_multixact_freeze_table_age, autovacuum_multixact_freeze_max_age, а также vacuum_multixact_failsafe_age. (v. 14)

13.4. Очередь ожидания

Исключительные режимы

Из-за того, что блокировка строки — просто признак, очередь организована весьма нетривиально. Когда транзакция собирается изменить строку, она выполняет следующую последовательность действий ⁵:

если поле xmax и информационные биты версии строки указывают на то, что строка заблокирована в несовместимом режиме, захватывает исключительную тяжелую блокировку изменяемой версии строки;
при необходимости дожидается освобождения несовместимых блокировок, запрашивая блокировку номера транзакции xmax (или нескольких транзакций, если xmax — мультитранзакция);
прописывает в версию строки свой номер (в поле xmax) и устанавливает необходимые информационные биты;
освобождает блокировку версии строки, если она захватывалась в п. 1.

Блокировка версии строки (tuple lock) является еще одним видом тяжелых блокировок; она имеет тип tuple. Не путайте ее с блокировкой собственно строки (row lock).

Может показаться, что шаги 1 и 4 избыточны и достаточно ограничиться только ожиданием блокирующих транзакций. Но если строку одновременно пытаются обновить несколько транзакций, все они будут ждать завершения транзакции, работающей над строкой в данный момент. При завершении этой транзакции между ожидающими возникнет состояние гонки за право обладания строкой, а это может привести к неопределенно долгому ожиданию для отдельных «невезучих» транзакций. Такая ситуация называется ресурсным голоданием (starvation).

Блокировка версии строки выделяет первую в очереди транзакцию и гарантирует, что именно она получит блокировку следующей.

Однако лучше один раз увидеть. Поскольку в процессе работы возникает довольно много разных блокировок, и каждой соответствует отдельная строка в таблице pg_locks, я создам еще одно представление над pg_locks. Оно показывает информацию компактно и оставляет только интересные нам сейчас блокировки (относящиеся к таблице accounts и к самой транзакции, но без блокировки виртуальных номеров):

=> CREATE VIEW locks_accounts AS
SELECT pid,
  locktype,
  CASE locktype
    WHEN 'relation' THEN relation::regclass::text
    WHEN 'transactionid' THEN transactionid::text
    WHEN 'tuple' THEN relation::regclass||'('||page||','||tuple||')'
  END AS lockid,
  mode,
  granted
FROM pg_locks
WHERE locktype in ('relation','transactionid','tuple')
  AND (locktype != 'relation' OR relation = 'accounts'::regclass)
ORDER BY 1, 2, 3;

Начнем первую транзакцию и обновим строку:

=> BEGIN;
=> SELECT txid_current(), pg_backend_pid();
 txid_current | pg_backend_pid
--------------+----------------
       191456 |          66190
(1 row)
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;

Транзакция успешно выполняет четыре шага последовательности и теперь удерживает блокировку таблицы:

=> SELECT * FROM locks_accounts WHERE pid = 66190;
  pid  |   locktype    |  lockid  |       mode       | granted
-------+---------------+----------+------------------+---------
 66190 | relation      | accounts | RowExclusiveLock | t
 66190 | transactionid | 191456   | ExclusiveLock    | t
(2 rows)

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

=> BEGIN;
=> SELECT txid_current(), pg_backend_pid();
 txid_current | pg_backend_pid
--------------+----------------
       191457 |          66267
(1 row)
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;

Рисунок 13.1 Исключительные режимы

Вторая транзакция дошла только до второго шага. Поэтому, помимо блокировки таблицы и собственного номера, она добавляет в pg_locks еще две строки: захваченную на первом шаге блокировку типа tuple и запрошенную на втором шаге блокировку номера первой транзакции:

=> SELECT * FROM locks_accounts WHERE pid = 66267;
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 66267 | relation      | accounts      | RowExclusiveLock | t
 66267 | transactionid | 191456        | ShareLock        | f
 66267 | transactionid | 191457        | ExclusiveLock    | t
 66267 | tuple         | accounts(0,1) | ExclusiveLock    | t
(4 rows)

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

=> BEGIN;
=> SELECT txid_current(), pg_backend_pid();
 txid_current | pg_backend_pid
--------------+----------------
       191458 |          66344
(1 row)
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;

=> SELECT * FROM locks_accounts WHERE pid = 66344;
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 66344 | relation      | accounts      | RowExclusiveLock | t
 66344 | transactionid | 191458        | ExclusiveLock    | t
 66344 | tuple         | accounts(0,1) | ExclusiveLock    | f
(3 rows)

Четвертая и последующие транзакции, желающие обновить ту же самую строку, ничем не будут отличаться от третьей — все они будут ожидать одну и ту же блокировку версии строки.

       => BEGIN;
       => SELECT txid_current(), pg_backend_pid();
        txid_current | pg_backend_pid
       --------------+----------------
              191459 |          66421
       (1 row)
       => UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;
=> SELECT * FROM locks_accounts WHERE pid = 66344;
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 66344 | relation      | accounts      | RowExclusiveLock | t
 66344 | transactionid | 191458        | ExclusiveLock    | t
 66344 | tuple         | accounts(0,1) | ExclusiveLock    | f
(3 rows)

Рисунок 13.2 Исключительные режимы

Общую картину текущих ожиданий можно посмотреть в представлении pg_stat_activity, добавив информацию о блокирующих процессах:

=> SELECT pid,
  wait_event_type,
  wait_event,
  pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE pid IN (66190,66267,66344,66421);
  pid  | wait_event_type |  wait_event   | pg_blocking_pids
-------+-----------------+---------------+------------------
 66190 | Client          | ClientRead    | {}
 66267 | Lock            | transactionid | {66190}
 66344 | Lock            | tuple         | {66267}
 66421 | Lock            | tuple         | {66267,66344}
(4 rows)

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

Но все-таки более вероятно, что первая транзакция завершится фиксацией. На уровне изоляции Repeatable Read или Serializable это приведет к обрыву второй транзакции с ошибкой сериализации ⁶ (а затем и остальных, стоящих в очереди). Но на уровне Read Committed измененная строка перечитывается, и возобновляется попытка ее обновления.

Итак, первая транзакция завершается:

=> COMMIT;

Вторая транзакция пробуждается и успешно выполняет третий и четвертый шаги последовательности:

UPDATE 1

=> SELECT * FROM locks_accounts WHERE pid = 66267;
  pid  |   locktype    |  lockid  |       mode       | granted
-------+---------------+----------+------------------+---------
 66267 | relation      | accounts | RowExclusiveLock | t
 66267 | transactionid | 191457   | ExclusiveLock    | t
(2 rows)

Как только вторая транзакция освобождает блокировку версии строки, просыпается и третья транзакция, но она обнаруживает в поле xmax новой версии строки уже другой номер. На этом последовательность блокирования, приведенная выше, завершается неуспехом. На уровне изоляции Read Committed выполняется повторная попытка заблокировать строку⁷, но эта попытка уже не следует приведенной последовательности. Третья транзакция будет ожидать завершения второй без попыток захватить блокировку версии строки:

=> SELECT * FROM locks_accounts WHERE pid = 66344;
  pid  |   locktype    |  lockid  |       mode       | granted
-------+---------------+----------+------------------+---------
 66344 | relation      | accounts | RowExclusiveLock | t
 66344 | transactionid | 191457   | ShareLock        | f
 66344 | transactionid | 191458   | ExclusiveLock    | t
(3 rows)

То же самое происходит и с четвертой транзакцией:

=> SELECT * FROM locks_accounts WHERE pid = 66421;
  pid  |   locktype    |  lockid  |       mode       | granted
-------+---------------+----------+------------------+---------
 66421 | relation      | accounts | RowExclusiveLock | t
 66421 | transactionid | 191457   | ShareLock        | f
 66421 | transactionid | 191459   | ExclusiveLock    | t
(3 rows)

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

Рисунок 13.3 Исключительные режимы

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

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

Завершим все начатые транзакции.

=> COMMIT;

UPDATE 1
=> COMMIT;

UPDATE 1
=> COMMIT;

Разделяемые режимы

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

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

если поле xmax и информационные биты версии строки указывают на то, что строка заблокирована в несовместимом режиме, захватывает исключительную тяжелую блокировку изменяемой версии строки;
при необходимости дожидается освобождения несовместимых блокировок, запрашивая блокировку номера транзакции xmax (или нескольких транзакций, если xmax — мультитранзакция);
прописывает в версию строки свой номер (в поле xmax) и устанавливает необходимые информационные биты;
освобождает блокировку версии строки, если она захватывалась в п. 1.

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

Повторим наш эксперимент с чистого листа.

=> TRUNCATE accounts;
=> INSERT INTO accounts(id, client, amount)
VALUES (1,'alice',100.00),
       (2,'bob',200.00),
       (3,'charlie',300.00);

Начинаем первую транзакцию:

=> BEGIN;
=> SELECT txid_current(), pg_backend_pid();
 txid_current | pg_backend_pid
--------------+----------------
       191462 |          66190
(1 row)

Первая транзакция блокирует строку в разделяемом режиме:

=> SELECT * FROM accounts WHERE id = 1 FOR SHARE;

Вторая транзакция пытается обновить ту же строку, но не может — режимы Share и No Key Update несовместимы:

=> BEGIN;
=> SELECT txid_current(), pg_backend_pid();
 txid_current | pg_backend_pid
--------------+----------------
       191463 |          66267
(1 row)
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;

Вторая транзакция ждет завершения первой и удерживает блокировку версии строки — как и в прошлом примере:

=> SELECT * FROM locks_accounts WHERE pid = 66267;
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 66267 | relation      | accounts      | RowExclusiveLock | t
 66267 | transactionid | 191462        | ShareLock        | f
 66267 | transactionid | 191463        | ExclusiveLock    | t
 66267 | tuple         | accounts(0,1) | ExclusiveLock    | t
(4 rows)

Рисунок 13.4 Разделяемые режимы

Пусть теперь третья транзакция блокирует строку в разделяемом режиме. Ее блокировка совместима с уже имеющейся блокировкой, и поэтому третья транзакция проходит без очереди:

=> BEGIN;
=> SELECT txid_current(), pg_backend_pid();
 txid_current | pg_backend_pid
--------------+----------------
       191464 |          66344
(1 row)
=> SELECT * FROM accounts WHERE id = 1 FOR SHARE;

И вот уже две транзакции блокируют строку:

=> SELECT * FROM pgrowlocks('accounts') \gx
-[ RECORD 1 ]-------------------------
locked_row | (0,1)
locker     | 2
multi      | t
xids       | {191462,191464}
modes      | {"For Share","For Share"}
pids       | {66190,66344}

Рисунок 13.5 Разделяемые режимы

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

=> COMMIT;

=> SELECT * FROM locks_accounts WHERE pid = 66267;
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 66267 | relation      | accounts      | RowExclusiveLock | t
 66267 | transactionid | 191463        | ExclusiveLock    | t
 66267 | transactionid | 191464        | ShareLock        | f
 66267 | tuple         | accounts(0,1) | ExclusiveLock    | t
(4 rows)

И только когда третья транзакция завершится (и если за это время не появятся другие разделяемые блокировки), вторая сможет выполнить обновление.

=> COMMIT;

UPDATE 1
=> COMMIT;

Проверка внешних ключей вряд ли создаст проблемы, поскольку ключевые поля обычно не меняются, а режимы Key Share и No Key Update совместимы. Но от идеи использования разделяемых режимов блокировок строк в приложении в большинстве случаев лучше отказаться.

13.5. Блокировка без ожидания

Обычно команды SQL ожидают освобождения необходимых им ресурсов. Но иногда хочется отказаться от выполнения команды, если блокировку не удалось получить сразу же. Для этого такие команды, как SELECT, LOCK и ALTER, позволяют использовать предложение NOWAIT.

Заблокируем строку:

=> BEGIN;
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;

Команда с предложением NOWAIT немедленно завершается ошибкой, если ресурс оказался занят:

=> SELECT * FROM accounts
FOR UPDATE NOWAIT;
ERROR:  could not obtain lock on row in relation "accounts"

В прикладном коде такую ошибку можно перехватить и обработать.

У команд UPDATE и DELETE предложение NOWAIT не предусмотрено. Стандартный прием состоит в том, чтобы сначала попробовать заблокировать строку командой SELECT FOR UPDATE NOWAIT, а затем — если получилось — обновить или удалить ее.

В редких случаях бывает удобно пропускать уже заблокированные строки и сразу же получать свободные для обработки. Так работает команда SELECT FOR с предложением SKIP LOCKED:

=> SELECT * FROM accounts
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
 id | client | amount
----+--------+--------
  2 | bob    | 200.00
(1 row)

В этом примере первая — заблокированная — строка была пропущена, и запрос заблокировал и вернул вторую.

Такой подход позволяет организовать многопоточную обработку очередей или пакетную обработку групп строк. Но не стоит искать для этой команды другие применения — большинство задач решаются более простыми средствами. (с. 179)

Наконец, есть способ не допустить длительного ожидания с помощью установки тайм-аута:

=> SET lock_timeout = '1s';
=> ALTER TABLE accounts DROP COLUMN amount;
ERROR:  canceling statement due to lock timeout

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

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

Примечание

Параметр lock_timeout отличается от параметра statement_timeout тем, что ограничивает время ожидания блокировки, а не общее время выполнения оператора.

=> ROLLBACK;

13.6. Взаимоблокировки

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

Но возможна ситуация, когда транзакции, за которой стоит очередь, понадобится еще один ресурс, и ей самой потребуется встать за ним в ту же самую очередь. Возникнет взаимоблокировка ⁸: очередь замкнется в круг и не сможет уже рассосаться сама собой.

Чтобы наглядно представить себе картину блокировок, построим граф ожиданий. Вершины этого графа — выполняющиеся процессы. Его ребра — стрелки, проведенные от процессов, ожидающих получения блокировок, к процессам, которые удерживают эти блокировки. Если в графе есть контур, то есть из какой-либо вершины можно по стрелкам добраться до нее же самой, — значит, произошла взаимоблокировка. (с. 301)

Примечание

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

Рисунок 13.6 Взаимоблокировки

Если взаимоблокировка возникла и ни один участник не установил тайм-аут ожидания, транзакции будут ждать друг друга бесконечно. Поэтому менеджер блокировок ⁹ автоматически отслеживает такие ситуации.

Однако проверка требует определенных усилий, которые не хочется прилагать всякий раз, когда запрашивается новая блокировка (все-таки взаимоблокировки достаточно редки). Поэтому когда процесс пытается захватить блокировку и не может, он просто встает в очередь и засыпает, но при этом автоматически устанавливается таймер на время, указанное в параметре deadlock_timeout ¹⁰. Если ресурс освободится раньше — отлично, на проверке удалось сэкономить. Но если по истечении deadlock_timeout единиц времени ожидание продолжается, ожидающий процесс будет разбужен и инициирует проверку¹¹. (по умолчанию: 1s)

По сути, проверка и состоит в построении графа ожиданий и поиска в нем контуров¹². На все время проверки останавливается любая работа с тяжелыми блокировками, чтобы «заморозить» текущее состояние графа.

Если проверка не выявила взаимоблокировок, процесс снова засыпает; рано или поздно до него дойдет очередь.

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

Взаимоблокировки обычно означают, что приложение спроектировано неправильно. Обнаружить такие ситуации можно двумя способами: во-первых, будут появляться сообщения в журнале сервера, а во-вторых, будет увеличиваться значение deadlocks в таблице pg_stat_database.

Взаимоблокировка при обновлении строк

Хотя в конечном счете к взаимоблокировкам приводят тяжелые блокировки, наиболее частая причина их возникновения — разный порядок блокирования табличных строк.

Пусть первая транзакция собирается перевести 100 ₽ с первого счета на второй. Для этого она сначала уменьшает первый счет:

=> BEGIN;
=> UPDATE accounts SET amount = amount - 100.00 WHERE id = 1;
UPDATE 1

В это же время вторая транзакция собирается перевести 10 ₽ со второго счета на первый. Она начинает с того, что уменьшает второй счет:

=> BEGIN;
=> UPDATE accounts SET amount = amount - 10.00 WHERE id = 2;
UPDATE 1

Теперь первая транзакция пытается увеличить второй счет, но обнаруживает, что строка заблокирована:

=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 2;

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

=> UPDATE accounts SET amount = amount + 10.00 WHERE id = 1;

Возникает циклическое ожидание, которое никогда не завершится само по себе. Через секунду первая транзакция, не получив доступа к ресурсу, инициирует проверку взаимоблокировки и обрывается сервером:

ERROR:  deadlock detected
DETAIL:  Process 65884 waits for ShareLock on transaction 191470;
blocked by process 66190.
Process 66190 waits for ShareLock on transaction 191469; blocked by
process 65884.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "accounts"

Теперь вторая транзакция может продолжить работу. Она пробуждается и выполняет обновление:

UPDATE 1

Завершим транзакции.

=> ROLLBACK;

=> ROLLBACK;

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

Взаимоблокировка двух команд UPDATE

Бывают случаи, когда взаимоблокировка кажется невозможной, но все-таки возникает.

Удобно и привычно воспринимать команды SQL как атомарные. Но возьмем UPDATE: команда блокирует строки по мере их обновления (а не все сразу), и это происходит не одномоментно. Поэтому если одна команда UPDATE обновляет несколько строк в одном порядке, а другая — в другом, они могут взаимозаблокироваться.

Для воспроизведения создадим индекс по столбцу amount, построенный по убыванию суммы:

=> CREATE INDEX ON accounts(amount DESC);

Чтобы успеть увидеть происходящее, напишем замедляющую функцию:

=> CREATE FUNCTION inc_slow(n numeric)
RETURNS numeric
AS $$
  SELECT pg_sleep(1);
  SELECT n + 100.00;
$$ LANGUAGE sql;

Первая команда UPDATE будет обновлять всю таблицу. План выполнения — последовательный просмотр всей таблицы:

=> EXPLAIN (costs off)
  UPDATE accounts SET amount = inc_slow(amount);
        QUERY PLAN
---------------------------
 Update on accounts
   -> Seq Scan on accounts
(2 rows)

Расположим версии строк на табличной странице в порядке возрастания суммы. Для этого вставим их заново в опустошенную таблицу:

=> TRUNCATE accounts;
=> INSERT INTO accounts(id, client, amount)
VALUES (1,'alice',100.00),
       (2,'bob',200.00),
       (3,'charlie',300.00);
=> ANALYZE accounts;
=> SELECT ctid, * FROM accounts;
 ctid  | id | client  | amount
-------+----+---------+--------
 (0,1) |  1 | alice   | 100.00
 (0,2) |  2 | bob     | 200.00
 (0,3) |  3 | charlie | 300.00
(3 rows)

При последовательном сканировании строки будут обновляться в том же порядке (для больших таблиц это не всегда верно). (с. 198)

Запускаем обновление:

=> UPDATE accounts SET amount = inc_slow(amount);

А пока оно работает, в другом сеансе запретим использование последовательного сканирования:

=> SET enable_seqscan = off;

В этом случае для следующего оператора UPDATE планировщик решает использовать сканирование индекса:

=> EXPLAIN (costs off)
  UPDATE accounts SET amount = inc_slow(amount)
  WHERE amount > 100.00;
                      QUERY PLAN
-------------------------------------------------------
 Update on accounts
   -> Index Scan using accounts_amount_idx on accounts
       Index Cond: (amount > 100.00)
(3 rows)

Под условие попадают вторая и третья строки, причем, поскольку индекс построен по убыванию суммы, строки будут обновляться в обратном порядке.

Запускаем следующее обновление:

=> UPDATE accounts SET amount = inc_slow(amount)
WHERE amount > 100.00;

Заглянув в табличную страницу с помощью расширения pgrowlocks, мы увидим, что первый оператор успел обновить первую строку (0,1), а второй — последнюю (0,3):

=> SELECT locked_row, locker, modes FROM pgrowlocks('accounts');
 locked_row | locker |       modes
------------+--------+-------------------
 (0,1)      | 191476 | {"No Key Update"}    ← первый
 (0,3)      | 191477 | {"No Key Update"}    ← второй
(2 rows)

Проходит еще секунда. Первый оператор обновил вторую строку, а второй хотел бы это сделать, но не может.

=> SELECT locked_row, locker, modes FROM pgrowlocks('accounts');
 locked_row | locker |       modes
------------+--------+-------------------
 (0,1)      | 191476 | {"No Key Update"}
 (0,2)      | 191476 | {"No Key Update"}    ← первый успел раньше
 (0,3)      | 191477 | {"No Key Update"}
(3 rows)

Теперь первый оператор хотел бы обновить последнюю строку таблицы, но она уже заблокирована вторым. Произошла взаимоблокировка.

Одна из транзакций прерывается:

ERROR:  deadlock detected
DETAIL:  Process 66267 waits for ShareLock on transaction 191476;
blocked by process 66190.
Process 66190 waits for ShareLock on transaction 191477; blocked by
process 66267.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "accounts"

А другая завершает выполнение:

UPDATE 3

Несмотря на кажущуюся невероятность, такие ситуации реально возникают в нагруженных системах, обновляющих пакеты строк.

postgrespro.ru/docs/postgresql/18/explicit-locking#LOCKING-ROWS. ↩︎
include/access/htup_details.h. ↩︎
backend/access/transam/multixact.c. ↩︎
backend/access/heap/heapam.c, функция FreezeMultiXactId. ↩︎
backend/access/heap/README.tuplock. ↩︎
backend/executor/nodeModifyTable.c, функция ExecUpdate. ↩︎
backend/access/heap/heapam_handler.c, функция heapam_tuple_lock. ↩︎
postgrespro.ru/docs/postgresql/18/explicit-locking#LOCKING-DEADLOCKS. ↩︎
backend/storage/lmgr/README. ↩︎
backend/storage/lmgr/proc.c, функция ProcSleep. ↩︎
backend/storage/lmgr/proc.c, функция CheckDeadLock. ↩︎
backend/storage/lmgr/deadlock.c. ↩︎