# Занятие 13: Блокировки / Блокировки строк

> Исходная страница в нативном Markdown: Блокировки / Блокировки строк

---

Индекс LLMS: [llms.txt](/ru/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> Эта страница извлечена из официального PDF в нативный Markdown. Текст слайдов, код и комментарии сохранены в порядке чтения; исходные PDF и HTML остаются эталоном макета.
> **HTML source:** [dba2_13_locks_rows.html](https://edu.postgrespro.ru/16/dba2-16/dba2_13_locks_rows.html)
> **Archive:** [DBA2-handouts-16.zip](https://edu.postgrespro.ru/16/DBA2-handouts-16.zip)
> **Страниц PDF:** 32

## Page 1: Блокировки / Блокировки строк {#source-page-001}

### Авторские права

© Postgres Professional, 2016–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
### Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.
### Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru
### Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

## Page 2: Темы {#source-page-002}

- Исключительные и разделяемые блокировки строк
- Мультитранзакции и заморозка
- Реализация очереди ожидания
- Взаимоблокировки

## Page 3: Блокировки строк {#source-page-003}

![Source slide page 3: Блокировки строк](images/page-003.png){#source-figure-003 caption="Source slide page 3: Блокировки строк"}


### Комментарии преподавателя

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

## Page 4: Устройство {#source-page-004}

**Информация только в страницах данных**
  - поле xmax заголовка версии строки + информационные биты
  - в оперативной памяти ничего не хранится
- Неограниченное количество
  - большое число не влияет на производительность
- Инфраструктура
  - «очередь» ожидания организована с помощью блокировок объектов

### Комментарии преподавателя

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

Если в случае блокировок объектов каждый ресурс представлен собственной блокировкой в оперативной памяти, то со строками так не получается: отдельная блокировка для каждой табличной строки (которых могут быть миллионы и миллиарды) потребует непомерных накладных расходов и огромного объема оперативной памяти.

Один из вариантов решения — повышение уровня (эскалация): если уже заблокировано N строк таблицы, то при блокировке еще одной блокируется вся таблица целиком, а блокировки на уровне строк снимаются. Но в этом случае страдает пропускная способность.

Поэтому в PostgreSQL сделано иначе. Информация о том, что строка заблокирована, хранится исключительно в заголовке версии строки внутри страницы данных. Там она представлена номером блокирующей транзакции (xmax) и дополнительными информационными битами.

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

Обратная сторона такого подхода — сложность организации очереди ожидания. Для этого все-таки приходится использовать блокировки уровня объектов, но удается обойтись очень небольшим их количеством (пропорциональным числу процессов, а не числу заблокированных строк).

## Page 5: Исключительные режимы {#source-page-005}

Update No Key Update
  - удаление строки
  - изменение любых полей,
  - или изменение всех полей
  - кроме ключевых
```sql
SELECT FOR UPDATE
SELECT FOR NO KEY UPDATE
```
  - UPDATE
  - UPDATE
  - (с изменением ключевых полей)
  - (без изменения ключевых полей)
```sql
DELETE
```

### Комментарии преподавателя

Всего существует 4 режима, в которых можно заблокировать строку (в версии строки режим проставляется с помощью дополнительных информационных битов).

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

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

<https://postgrespro.ru/docs/postgresql/16/explicit-locking#LOCKING-ROWS>

## Page 6: Исключительные режимы {#source-page-006}

![Source slide page 6: Исключительные режимы](images/page-006.png){#source-figure-006 caption="Source slide page 6: Исключительные режимы"}


### Комментарии преподавателя

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

То же самое происходит и при явном блокировании строки командой SELECT FOR UPDATE, но еще проставляется дополнительный информационный бит (xmax lock only), который говорит о том, что версия строки по-прежнему актуальна, хоть и заблокирована.

Режим блокировки определяется еще одним информационным битом (keys updated).

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

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

## Page 7: Разделяемые режимы {#source-page-007}

Share Key Share
  - запрет изменения
  - запрет изменения
  - любых полей строки
  - ключевых полей строки
  - SELECT FOR SHARE
  - SELECT FOR KEY SHARE
  - и проверка внешних ключей
- Матрица совместимости режимов
  - Key
  - No Key
  - Share
  - Share
  - Update
  - Update
- Key Share × Share × × No Key Update × × × Update × × × ×

### Комментарии преподавателя

Еще два режима представляют разделяемые (shared) блокировки, которые могут удерживаться несколькими транзакциями.

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

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

Общая матрица совместимости режимов приведена внизу слайда. Из нее видно, что:

● исключительные режимы конфликтуют между собой;

● разделяемые режимы совместимы между собой;

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

<https://postgrespro.ru/docs/postgresql/16/explicit-locking#LOCKING-ROWS>

## Page 8: Разделяемые режимы {#source-page-008}

![Source slide page 8: Разделяемые режимы](images/page-008.png){#source-figure-008 caption="Source slide page 8: Разделяемые режимы"}


### Комментарии преподавателя

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

Поэтому для разделяемых блокировок применяются так называемые мультитранзакции (MultiXact). Им выделяются отдельные номера, которые соответствуют не одной транзакции, а целой группе. Чтобы отличить мультитранзакцию от обычной, используется еще один информационный бит (xmax is multi), а детальная информация об участниках такой группы и режимах блокировки находится в каталоге PGDATA/pg\_multixact/. Естественно, последние использованные данные хранятся в буферах в общей памяти сервера для ускорения доступа.

## Page 9: Настройка заморозки {#source-page-009}

**Параметры для мультитранзакций**
  - vacuum\_multixact\_freeze\_min\_age
  - = 5 000 000
  - vacuum\_multixact\_freeze\_table\_age
  - = 150 000 000
  - autovacuum\_multixact\_freeze\_max\_age = 400 000 000
  - vacuum\_multixact\_failsafe\_age
  - = 1 600 000 000
- Параметры хранения таблиц
  - autovacuum\_multixact\_freeze\_min\_age
  - toast.autovacuum\_multixact\_freeze\_min\_age
  - autovacuum\_multixact\_freeze\_table\_age
  - toast.autovacuum\_multixact\_freeze\_table\_age
  - autovacuum\_multixact\_freeze\_max\_age
  - toast.autovacuum\_multixact\_freeze\_max\_age

### Комментарии преподавателя

Поскольку для мультитранзакций выделяются отдельные номера, которые записываются в поле xmax версий строк, из-за ограничения разрядности счетчика с ними возникают такие же сложности, как и с обычным номером транзакции. Речь идет о переполнении счетчика транзакций, которое рассматривалось в теме «Заморозка» модуля «Многоверсионность».

Поэтому для номеров мультитранзакций тоже необходимо выполнять аналог заморозки — старые номера multixact id заменяются на новые (или на обычный номер, если в текущий момент блокировка уже удерживается только одной транзакцией).

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

За заморозку мультитранзакций отвечают параметры, аналогичные параметрам обычной заморозки.

## Page 10: Блокировки строк {#source-page-010}

Наиболее частый случай блокировок — блокировки, возникающие на уровне строк.
Создадим таблицу счетов, как в прошлой теме.
```sql
=> CREATE DATABASE locks_rows;
```
```sql
CREATE DATABASE
```
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```sql
=> CREATE TABLE accounts(acc_no integer PRIMARY KEY, amount numeric);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO accounts VALUES (1,1000.00),(2,2000.00),(3,3000.00);
```
```sql
INSERT 0 3
```
Поскольку информация о блокировке строк хранится только в самих версиях строк, воспользуемся знакомым расширением pageinspect.
```sql
=> CREATE EXTENSION pageinspect;
```
```sql
CREATE EXTENSION
```
Для удобства создадим представление, расшифровывающее интересующие нас информационные биты в первых трех версиях строк.
```sql
=> CREATE VIEW accounts_v AS
SELECT '(0,'||lp||')' AS ctid,
       t_xmax as xmax,
       CASE WHEN (t_infomask & 1024) > 0  THEN 't' END AS committed,
       CASE WHEN (t_infomask & 2048) > 0  THEN 't' END AS aborted,
       CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,
       CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,
       CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd
FROM heap_page_items(get_raw_page('accounts',0))
WHERE lp <= 3
ORDER BY lp;
```
```sql
CREATE VIEW
```
Обновляем сумму первого счета (ключ не меняется) и номер второго счета (ключ меняется):
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
```sql
=> UPDATE accounts SET acc_no = 20 WHERE acc_no = 2;
```
```sql
UPDATE 1
```
Заглянем в представление. Напомним, что оно показывает только первые три (то есть исходные) версии строк.
```sql
=> SELECT * FROM accounts_v;
```
```text
 ctid  | xmax | committed | aborted | lock_only | is_multi | keys_upd
-------+------+-----------+---------+-----------+----------+----------
 (0,1) |  748 |           |         |           |          |
 (0,2) |  748 |           |         |           |          | t
 (0,3) |    0 |           | t       |           |          |
(3 rows)
```
Строки, соответствующие первому и второму счету, заблокированы — в поле xmax записан номер блокирующей транзакции. Биты в столбце keys\_upd показывают, что строки заблокированы в разных режимах.
Теперь в другом сеансе запросим разделяемые блокировки первого и третьего счетов:

## Page 11: Page 11 {#source-page-011}

![Source slide page 11: Page 11](images/page-011.png){#source-figure-011 caption="Source slide page 11: Page 11"}

```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM accounts WHERE acc_no = 1 FOR KEY SHARE;
```
```text
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)
```
```sql
=> SELECT * FROM accounts WHERE acc_no = 3 FOR SHARE;
```
```text
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)
```
Все запрошенные блокировки совместимы друг с другом. В версиях строк видим:
```sql
=> SELECT * FROM accounts_v;
```
```text
 ctid  | xmax | committed | aborted | lock_only | is_multi | keys_upd
-------+------+-----------+---------+-----------+----------+----------
 (0,1) |    1 |           |         |           | t        |
 (0,2) |  748 |           |         |           |          | t
 (0,3) |  749 |           |         | t         |          |
(3 rows)
```
Столбец lock\_only позволяет отличить просто блокировку от обновления или удаления. Видим также, что в первой строке обычный номер в столбце xmax заменен на номер мультитранзакции — об этом говорит столбец is\_multi.
Чтобы не вникать в детали информационных битов и реализацию мультитранзакций, можно воспользоваться еще одним расширением, которое позволяет увидеть всю информацию о блокировках строк в удобном виде.
```sql
=> CREATE EXTENSION pgrowlocks;
```
```sql
CREATE EXTENSION
```
```sql
=> SELECT * FROM pgrowlocks('accounts') \gx
```
```sql
-[ RECORD 1 ]-----------------------------
locked_row | (0,1)
locker     | 1
multi      | t
xids       | {748,749}
modes      | {"No Key Update","Key Share"}
pids       | {27939,28128}
-[ RECORD 2 ]-----------------------------
locked_row | (0,2)
locker     | 748
multi      | f
xids       | {748}
modes      | {Update}
pids       | {27939}
-[ RECORD 3 ]-----------------------------
locked_row | (0,3)
locker     | 749
multi      | f
xids       | {749}
modes      | {"For Share"}
pids       | {28128}
```
Расширение дает информацию о номерах транзакций, мультитранзакций и режимах всех блокировок.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```

## Page 12: «Очередь» ожидания {#source-page-012}

![Source slide page 12: «Очередь» ожидания](images/page-012.png){#source-figure-012 caption="Source slide page 12: «Очередь» ожидания"}


### Комментарии преподавателя

Для организации очереди ожидания дополнительно используется механизм блокировок объектов, при этом информация хранится в оперативной памяти и видна в pg\_locks.

Допустим, транзакции с номером 101 (пример на слайде) удалось заблокировать строку в разделяемом режиме Share. Информация о блокировке записывается в заголовок версии строки — в поле xmax появляется номер этой транзакции.

## Page 13: «Очередь» ожидания {#source-page-013}

![Source slide page 13: «Очередь» ожидания](images/page-013.png){#source-figure-013 caption="Source slide page 13: «Очередь» ожидания"}


### Комментарии преподавателя

Другая транзакция 102, желая получить блокировку, обращается к строке и видит, что строка заблокирована. Если режимы блокировок конфликтуют, она должна каким-то образом встать в очередь, чтобы система «разбудила» ее, когда блокировка освободится. Но блокировки на уровне строк не предоставляют такой возможности — они никак не представлены в оперативной памяти, это просто байты внутри страницы данных.

Как мы видели в теме «Блокировки объектов», каждая транзакция удерживает исключительную блокировку своего номера. Поскольку транзакция 102 фактически должна дождаться завершения транзакции 101 (ведь блокировка строки освобождается только при завершении транзакции), она запрашивает блокировку номера 101.

Когда транзакция 101 завершится, заблокированный ресурс освободится (при фиксации — просто исчезнет), транзакция 102 будет разбужена и сможет заблокировать строку (установить xmax = 102 в соответствующей версии строки).

Кроме того, ожидающая транзакция удерживает блокировку ресурса типа tuple (версия строки), показывая, что она первая в «очереди».

## Page 14: «Очередь» ожидания {#source-page-014}

![Source slide page 14: «Очередь» ожидания](images/page-014.png){#source-figure-014 caption="Source slide page 14: «Очередь» ожидания"}


### Комментарии преподавателя

Если появляются другие транзакции, конфликтующие с текущей блокировкой версии строки (в нашем примере — 112, 122, 132), первым делом они пытаются захватить блокировку типа tuple для этой версии.

Поскольку блокировка tuple уже удерживается транзакцией 102, другие транзакции ждут освобождения этой блокировки. Получается своеобразная «очередь», в которой есть первый и все остальные.

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

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

## Page 15: «Очередь» ожидания {#source-page-015}

Для удобства создадим представление над pg\_locks, «свернув» в одно поле идентификаторы разных типов блокировок:
```sql
=> CREATE VIEW locks AS
SELECT pid,
       locktype,
       CASE locktype
         WHEN 'relation' THEN relation::regclass::text
         WHEN 'virtualxid' THEN virtualxid::text
         WHEN 'transactionid' THEN transactionid::text
         WHEN 'tuple' THEN relation::regclass::text||':'||page::text||','||tuple::text
       END AS lockid,
       mode,
       granted
FROM pg_locks;
```
```sql
CREATE VIEW
```
Пусть одна транзакция заблокирует строку в разделяемом режиме...
```text
S1=> BEGIN;
```
```text
BEGIN
```
```sql
S1=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                752 |          27780
(1 row)
```
```sql
S1=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;
```
```text
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)
```
...а другая попробует выполнить обновление:
```text
U1=> BEGIN;
```
```text
BEGIN
```
```sql
U1=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                753 |          27939
(1 row)
```
```sql
U1=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
В представлении pg\_locks можно увидеть, что вторая транзакция ожидает завершения первой (granted = f), удерживая при этом блокировку версии строки (locktype = tuple):
```sql
=> SELECT * FROM locks WHERE pid = 27939; -- U1
```
```text
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 27939 | relation      | accounts_pkey | RowExclusiveLock | t
 27939 | relation      | accounts      | RowExclusiveLock | t
 27939 | virtualxid    | 3/13          | ExclusiveLock    | t
 27939 | transactionid | 752           | ShareLock        | f
 27939 | tuple         | accounts:0,1  | ExclusiveLock    | t
 27939 | transactionid | 753           | ExclusiveLock    | t
(6 rows)
```
Чтобы не разбираться, кто кого блокирует, по представлению pg\_locks, можно узнать номер (или номера) процесса блокирующего сеанса с помощью функции:
```sql
=> SELECT pg_blocking_pids(27939); -- U1
```

## Page 16: Page 16 {#source-page-016}

![Source slide page 16: Page 16](images/page-016.png){#source-figure-016 caption="Source slide page 16: Page 16"}

```text
 pg_blocking_pids
------------------
 {27780}
(1 row)
```
Теперь появляется транзакция, желающая получить несовместимую блокировку.
```text
U2=> BEGIN;
```
```text
BEGIN
```
```sql
U2=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                754 |          28128
(1 row)
```
```sql
U2=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
```
Она встает в очередь за транзакцией, удерживающей блокировку версии строки (locktype = tuple, granted = f):
```sql
=> SELECT * FROM locks WHERE pid = 28128; -- U2
```
```text
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 28128 | relation      | accounts_pkey | RowExclusiveLock | t
 28128 | relation      | accounts      | RowExclusiveLock | t
 28128 | virtualxid    | 4/6           | ExclusiveLock    | t
 28128 | tuple         | accounts:0,1  | ExclusiveLock    | f
 28128 | transactionid | 754           | ExclusiveLock    | t
(5 rows)
```
```sql
=> SELECT pg_blocking_pids(28128); -- U2
```
```text
 pg_blocking_pids
------------------
 {27939}
(1 row)
```

## Page 17: «Очередь» ожидания {#source-page-017}

![Source slide page 17: «Очередь» ожидания](images/page-017.png){#source-figure-017 caption="Source slide page 17: «Очередь» ожидания"}


### Комментарии преподавателя

В нашем примере транзакция 101 заблокировала строку в разделяемом (Share) режиме. Пока транзакция 102 ожидает завершения транзакции 101, может появиться еще транзакция 133, желающая получить блокировку строки в разделяемом режиме, совместимом с текущей блокировкой транзакции 101.

Такая транзакция не стоит в очереди, она сразу получает блокировку. В этом случае из списка номеров блокирующих транзакций 101 и 133 формируется мультитранзакция 2, номер которой запишется в поле xmax строки.

## Page 18: «Очередь» ожидания {#source-page-018}

![Source slide page 18: «Очередь» ожидания](images/page-018.png){#source-figure-018 caption="Source slide page 18: «Очередь» ожидания"}


### Комментарии преподавателя

Когда транзакция 101 завершится, транзакция 102 будет разбужена, но не сможет захватить блокировку строки, поскольку строка все еще заблокирована транзакцией 133. Транзакция 102 будет вынуждена снова «заснуть».

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

Заметим, что такой проблемы в принципе не возникает при блокировках объектов (таких, как отношения). В этом случае каждый ресурс представлен собственной блокировкой в оперативной памяти, и все ждущие процессы выстраиваются в «честную» очередь.

<https://git.postgresql.org/gitweb/?p=postgresql.git;f=src/backend/access/hea> p/README.tuplock;hb=HEAD

## Page 19: «Очередь» ожидания {#source-page-019}

![Source slide page 19: «Очередь» ожидания](images/page-019.png){#source-figure-019 caption="Source slide page 19: «Очередь» ожидания"}


### Комментарии преподавателя

Когда транзакция 133 завершится, транзакция 102 получит возможность первой записать свой номер в поле xmax, после чего она освободит блокировку tuple. Тогда одна случайная транзакция из всех остальных успеет захватить блокировку tuple и станет первой в «очереди».

## Page 20: Page 20 {#source-page-020}

![Source slide page 20: Page 20](images/page-020.png){#source-figure-020 caption="Source slide page 20: Page 20"}

Транзакция, желающая заблокировать строку в разделяемом режиме, проходит в нашем примере без очереди.
```sql
S2=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
S2=> BEGIN;
```
```text
BEGIN
```
```sql
S2=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                755 |          29079
(1 row)
```
```sql
S2=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;
```
```text
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)
```
В версии строки теперь мультитранзакция:
```sql
=> SELECT * FROM pgrowlocks('accounts') \gx
```
```text
-[ RECORD 1 ]-------------
locked_row | (0,1)
locker     | 2
multi      | t
xids       | {752,755}
modes      | {Share,Share}
pids       | {27780,29079}
```
После того как одна из транзакций, удерживающих строку в разделяемом режиме, завершится, другая продолжит удерживать блокировку.
```text
S1=> COMMIT;
```
```text
COMMIT
```
Транзакция, стоящая первой в очереди, теперь ждет завершения оставшейся транзакции.
```sql
=> SELECT * FROM locks WHERE pid = 27939; -- U1
```
```text
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 27939 | relation      | accounts_pkey | RowExclusiveLock | t
 27939 | relation      | accounts      | RowExclusiveLock | t
 27939 | virtualxid    | 3/13          | ExclusiveLock    | t
 27939 | tuple         | accounts:0,1  | ExclusiveLock    | t
 27939 | transactionid | 755           | ShareLock        | f
 27939 | transactionid | 753           | ExclusiveLock    | t
(6 rows)
```
Обратите внимание, что в поле xmax остался номер мультитранзакции, хотя одна из входящих в нее транзакций уже завершилась. Этот номер может быть заменен на другой (новой мультитранзакции или обычной транзакции) при очистке.
```sql
=> SELECT * FROM pgrowlocks('accounts') \gx
```
```text
-[ RECORD 1 ]-------------
locked_row | (0,1)
locker     | 2
multi      | t
xids       | {752,755}
modes      | {Share,Share}
pids       | {0,29079}
```
Теперь завершается и вторая транзакция, удерживавшая разделяемую блокировку.
```text
S2=> COMMIT;
```
```text
COMMIT
```

## Page 21: Page 21 {#source-page-021}

![Source slide page 21: Page 21](images/page-021.png){#source-figure-021 caption="Source slide page 21: Page 21"}

Транзакция, стоявшая первой в очереди, получает доступ к версии строки:
```sql
UPDATE 1
```
```sql
=> SELECT * FROM pgrowlocks('accounts') \gx
```
```sql
-[ RECORD 1 ]-----------------
locked_row | (0,1)
locker     | 753
multi      | f
xids       | {753}
modes      | {"No Key Update"}
pids       | {27939}
```
Оставшаяся транзакция захватывает блокировку tuple версии строки и становится первой в очереди:
```sql
=> SELECT * FROM locks WHERE pid = 28128; -- U2
```
```text
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 28128 | relation      | accounts_pkey | RowExclusiveLock | t
 28128 | relation      | accounts      | RowExclusiveLock | t
 28128 | virtualxid    | 4/6           | ExclusiveLock    | t
 28128 | tuple         | accounts:0,1  | ExclusiveLock    | t
 28128 | transactionid | 754           | ExclusiveLock    | t
 28128 | transactionid | 753           | ShareLock        | f
(6 rows)
```
Отменим изменения.
```text
U1=> ROLLBACK;
```
```text
ROLLBACK
```
```sql
UPDATE 1
```
```text
U2=> ROLLBACK;
```
```text
ROLLBACK
```
### Как не ждать блокировку?
Иногда удобно не ждать освобождения блокировки, а сразу получить ошибку, если необходимый ресурс занят. Приложение может перехватить и обработать такую ошибку.
Для этого ряд команд SQL (такие, как SELECT и некоторые варианты ALTER) позволяют указать ключевое слово NOWAIT. Заблокируем таблицу, обновив первую строку:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount + 1 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
```text
=> BEGIN;
```
```text
BEGIN
```
```text
=> LOCK TABLE accounts NOWAIT; -- IN ACCESS EXCLUSIVE MODE
```
```text
ERROR:  could not obtain lock on relation "accounts"
```
Транзакция сразу же получает ошибку.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
А для рекомендательных блокировок есть функции, позволяющие либо сразу захватить блокировку, либо вернуть false в случае неудачи:
```text
=> \df pg_try_advisory*
```

## Page 22: Page 22 {#source-page-022}

```text
                                       List of functions
   Schema   |               Name               | Result data type | Argument data types |
Type
------------+----------------------------------+------------------+---------------------+-
-----
 pg_catalog | pg_try_advisory_lock             | boolean          | bigint              |
func
 pg_catalog | pg_try_advisory_lock             | boolean          | integer, integer    |
func
 pg_catalog | pg_try_advisory_lock_shared      | boolean          | bigint              |
func
 pg_catalog | pg_try_advisory_lock_shared      | boolean          | integer, integer    |
func
 pg_catalog | pg_try_advisory_xact_lock        | boolean          | bigint              |
func
 pg_catalog | pg_try_advisory_xact_lock        | boolean          | integer, integer    |
func
 pg_catalog | pg_try_advisory_xact_lock_shared | boolean          | bigint              |
func
 pg_catalog | pg_try_advisory_xact_lock_shared | boolean          | integer, integer    |
func
(8 rows)
```
Команды UPDATE и DELETE не позволяют указать NOWAIT. Но можно сначала выполнить команду
```sql
SELECT ... FOR UPDATE NOWAIT; -- или FOR NO KEY UPDATE NOWAIT
```
а затем, если строки успешно заблокированы, изменить или удалить их. Например:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM accounts WHERE acc_no = 1 FOR UPDATE NOWAIT;
```
```text
ERROR:  could not obtain lock on row in relation "accounts"
```
Тут же получаем ошибку и не пытаемся вызывать DELETE или UPDATE.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
Другой способ не ждать снятия блокировки строк предоставляет предложение SKIP LOCKED. Заблокируем одну строку, но без указания конкретного номера счета:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM accounts ORDER BY acc_no
FOR UPDATE SKIP LOCKED LIMIT 1;
```
```text
 acc_no | amount
--------+---------
      2 | 2000.00
(1 row)
```
В этом случае команда пропускает заблокированную первую строку и мы немедленно получаем блокировку уже второй строки.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
Для команд, не связанных с блокировкой строк, использовать NOWAIT не получится. В этом случае можно установить небольшой тайм-аут ожидания. (По умолчанию его значение нулевое, что означает бесконечное ожидание):
```text
=> SET lock_timeout = '1s';
```
```text
SET
```
```sql
=> ALTER TABLE accounts DROP COLUMN amount;
```
```text
ERROR:  canceling statement due to lock timeout
```
Получаем ошибку без длительного ожидания освобождения ресурса.
```text
=> RESET lock_timeout;
```
```text
RESET
```

## Page 23: Page 23 {#source-page-023}

А при выполнении очистки можно указать, что она должна пропускать обработку таблицы, если ее блокировку не удалось получить немедленно. Это может оказаться особенно актуальным, когда выполняется очистка всей базы:
```sql
=> VACUUM (skip_locked);
```
```sql
VACUUM
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```

## Page 24: Взаимоблокировки {#source-page-024}

![Source slide page 24: Взаимоблокировки](images/page-024.png){#source-figure-024 caption="Source slide page 24: Взаимоблокировки"}

**Обнаруживаются поиском контуров в графе ожиданий**
  - проверка выполняется после ожидания deadlock\_timeout
- Одна из транзакций обрывается, остальные продолжают

### Комментарии преподавателя

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

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

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

Если взаимоблокировка возникла, участвующие транзакции уже не могут ничего с этим сделать — они будут ждать бесконечно. Поэтому PostgreSQL автоматически отслеживает взаимоблокировки. Проверка выполняется, если какая-либо транзакция ожидает освобождения ресурса дольше, чем указано в параметре deadlock\_timeout. Если выявлена взаимоблокировка, одна из транзакций принудительно прерывается, чтобы остальные могли продолжить работу.

Взаимоблокировки обычно означают, что приложение спроектировано неправильно. Сообщения в журнале сервера или увеличивающееся значение pg\_stat\_database.deadlocks — повод задуматься о причинах.

<https://postgrespro.ru/docs/postgresql/16/explicit-locking#LOCKING-DEADL> OCKS

## Page 25: Взаимоблокировка {#source-page-025}

![Source slide page 25: Взаимоблокировка](images/page-025.png){#source-figure-025 caption="Source slide page 25: Взаимоблокировка"}

Обычная причина возникновения взаимоблокировок — разный порядок блокирования строк таблиц.
Первая транзакция намерена перенести 100 рублей с первого счета на второй. Для этого она сначала уменьшает первый счет:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
В это же время вторая транзакция намерена перенести 10 рублей со второго счета на первый. Она начинает с того, что уменьшает второй счет:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 10.00 WHERE acc_no = 2;
```
```sql
UPDATE 1
```
Теперь первая транзакция пытается увеличить второй счет, но обнаруживает, что строка заблокирована.
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
```
Затем вторая транзакция пытается увеличить первый счет, но тоже блокируется.
```sql
=> UPDATE accounts SET amount = amount + 10.00 WHERE acc_no = 1;
```
Возникает циклическое ожидание, которое никогда не завершится само по себе. Поэтому сервер, обнаружив такой цикл, прерывает одну из транзакций.
```text
ERROR:  deadlock detected
DETAIL:  Process 27939 waits for ShareLock on transaction 761; blocked by process 28128.
Process 28128 waits for ShareLock on transaction 760; blocked by process 27939.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "accounts"
```
```sql
UPDATE 1
```
```text
=> COMMIT;
```
```text
ROLLBACK
```
```text
=> COMMIT;
```
```text
COMMIT
```
Правильный способ выполнения таких операций — блокирование ресурсов в одном и том же порядке. Например, в данном случае можно блокировать счета в порядке возрастания их номеров.

## Page 26: Итоги {#source-page-026}

**Блокировки строк хранятся в страницах данных**
  - из-за потенциально большого количества
- Очереди и обнаружение взаимоблокировок обеспечиваются блокировками объектов
  - приходится прибегать к сложным схемам блокирования

## Page 27: Практика {#source-page-027}

1. Смоделируйте ситуацию обновления одной и той же строки тремя командами UPDATE в разных сеансах. Изучите возникшие блокировки в представлении pg\_locks и убедитесь, что все они понятны.
- 2. Воспроизведите взаимоблокировку трех транзакций. Можно ли разобраться в ситуации постфактум, изучая журнал сообщений?
- 3. Могут ли две транзакции, выполняющие единственную команду UPDATE одной и той же таблицы, заблокировать друг друга? Попробуйте воспроизвести такую ситуацию.

## Page 28: 1. Блокировки при нескольких обновлениях строки {#source-page-028}

Для простоты создадим таблицу без первичного ключа.
```sql
=> CREATE DATABASE locks_rows;
```
```sql
CREATE DATABASE
```
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```sql
=> CREATE TABLE accounts(acc_no integer, amount numeric);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO accounts VALUES (1,1000.00),(2,2000.00),(3,3000.00);
```
```sql
INSERT 0 3
```
Создадим представление над pg\_locks как в демонстрации:
```sql
=> CREATE VIEW locks AS
SELECT pid,
       locktype,
       CASE locktype
         WHEN 'relation' THEN relation::REGCLASS::text
         WHEN 'virtualxid' THEN virtualxid::text
         WHEN 'transactionid' THEN transactionid::text
         WHEN 'tuple' THEN relation::REGCLASS::text||':'||page::text||','||tuple::text
       END AS lockid,
       mode,
       granted
FROM pg_locks;
```
```sql
CREATE VIEW
```
Первая транзакция обновляет и, соответственно, блокирует строку:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                747 |          61482
(1 row)
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
Вторая транзакция делает то же самое:
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                748 |          61684
(1 row)
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
И третья:
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
=> BEGIN;
```

## Page 29: Page 29 {#source-page-029}

```text
BEGIN
```
```sql
=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                749 |          61805
(1 row)
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
Блокировки для первой транзакции:
```sql
=> SELECT * FROM locks WHERE pid = 61482;
```
```text
  pid  |   locktype    |  lockid  |       mode       | granted
-------+---------------+----------+------------------+---------
 61482 | relation      | pg_locks | AccessShareLock  | t
 61482 | relation      | locks    | AccessShareLock  | t
 61482 | relation      | accounts | RowExclusiveLock | t
 61482 | virtualxid    | 6/6      | ExclusiveLock    | t
 61482 | transactionid | 747      | ExclusiveLock    | t
(5 rows)
```
Тип relation для pg\_locks и locks в режиме AccessShareLock — устанавливаются на читаемые отношения. Тип relation для accounts в режиме RowExclusiveLock — устанавливается на изменяемое отношение. Типы virtualxid и transactionid в режиме ExclusiveLock — удерживаются транзакцией для самой себя.
Блокировки для второй транзакции:
```sql
=> SELECT * FROM locks WHERE pid = 61684;
```
```text
  pid  |   locktype    |    lockid    |       mode       | granted
-------+---------------+--------------+------------------+---------
 61684 | relation      | accounts     | RowExclusiveLock | t
 61684 | virtualxid    | 3/12         | ExclusiveLock    | t
 61684 | transactionid | 747          | ShareLock        | f
 61684 | transactionid | 748          | ExclusiveLock    | t
 61684 | tuple         | accounts:0,1 | ExclusiveLock    | t
(5 rows)
```
По сравнению с первой транзакцией:
Блокировки для pg\_locks и locks отсутствуют, так как вторая транзакция не обращалась к этим отношениям. Транзакция ожидает получение блокировки типа transactionid в режиме ShareLock для первой транзакции. Удерживается блокировка типа tuple для обновляемой строки.
Блокировки для третьей транзакции:
```sql
=> SELECT * FROM locks WHERE pid = 61805;
```
```text
  pid  |   locktype    |    lockid    |       mode       | granted
-------+---------------+--------------+------------------+---------
 61805 | relation      | accounts     | RowExclusiveLock | t
 61805 | virtualxid    | 4/5          | ExclusiveLock    | t
 61805 | tuple         | accounts:0,1 | ExclusiveLock    | f
 61805 | transactionid | 749          | ExclusiveLock    | t
(4 rows)
```
Транзакция ожидает получение блокировки типа tuple для обновляемой строки.
Общую картину текущих ожиданий можно увидеть в представлении pg\_stat\_activity. Для удобства можно добавить и информацию о блокирующих процессах:
```sql
=> SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE backend_type = 'client backend';
```
```text
  pid  | wait_event_type |  wait_event   | pg_blocking_pids
-------+-----------------+---------------+------------------
 61684 | Lock            | transactionid | {61482}
 61805 | Lock            | tuple         | {61684}
 61482 |                 |               | {}
(3 rows)
```
```text
=> ROLLBACK;
```

## Page 30: Page 30 {#source-page-030}

![Source slide page 30: Page 30](images/page-030.png){#source-figure-030 caption="Source slide page 30: Page 30"}

```text
ROLLBACK
```
```sql
UPDATE 1
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
```sql
UPDATE 1
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
### 2. Взаимоблокировка трех транзакций
Воспроизведем взаимоблокировку трех транзакций.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 2;
```
```sql
UPDATE 1
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 3;
```
```sql
UPDATE 1
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 3;
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
```text
ERROR:  deadlock detected
DETAIL:  Process 61482 waits for ShareLock on transaction 751; blocked by process 61684.
Process 61684 waits for ShareLock on transaction 752; blocked by process 61805.
Process 61805 waits for ShareLock on transaction 750; blocked by process 61482.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "accounts"
```
```sql
UPDATE 1
```
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
UPDATE 1
```
```text
=> COMMIT;
```
```text
COMMIT
```
```text
=> COMMIT;
```
```text
ROLLBACK
```
Вот какую информацию о взаимоблокировке можно получить из журнала:
```sh
student$ tail -n 10 /var/log/postgresql/postgresql-16-main.log
```
```sql
2026-06-15 13:28:26.684 MSK [61482] student@locks_rows ERROR:  deadlock detected
2026-06-15 13:28:26.684 MSK [61482] student@locks_rows DETAIL:  Process 61482 waits for
ShareLock on transaction 751; blocked by process 61684.
Process 61684 waits for ShareLock on transaction 752; blocked by process 61805.
Process 61805 waits for ShareLock on transaction 750; blocked by process 61482.
Process 61482: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
Process 61684: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 3;
Process 61805: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```

## Page 31: 3. Взаимоблокировка двух операций UPDATE {#source-page-031}

![Source slide page 31: 3. Взаимоблокировка двух операций UPDATE](images/page-031.png){#source-figure-031 caption="Source slide page 31: 3. Взаимоблокировка двух операций UPDATE"}

```sql
2026-06-15 13:28:26.684 MSK [61482] student@locks_rows HINT:  See server log for query
details.
2026-06-15 13:28:26.684 MSK [61482] student@locks_rows CONTEXT:  while updating tuple
(0,2) in relation "accounts"
2026-06-15 13:28:26.684 MSK [61482] student@locks_rows STATEMENT:  UPDATE accounts SET
amount = amount + 100.00 WHERE acc_no = 2;
```
Команда UPDATE блокирует строки по мере их обновления. Это происходит не одномоментно.
Поэтому если одна команда будет обновлять строки в одном порядке, а другая — в другом, они могут взаимозаблокироваться. Это может произойти, если для команд будут построены разные планы выполнения, например, одна будет читать таблицу последовательно, а другая — по индексу.
Получить такую ситуацию непросто даже специально, в реальной работе она маловероятна. Проиллюстрировать ее проще всего с помощью курсоров, поскольку это дает возможность управлять порядком чтения.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> DECLARE c1 CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no
FOR UPDATE;
```
```text
DECLARE CURSOR
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> DECLARE c2 CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no DESC -- в обратную сторону
FOR UPDATE;
```
```text
DECLARE CURSOR
```
```text
=> FETCH c1;
```
```text
 acc_no | amount
--------+---------
      1 | 1100.00
(1 row)
```
```text
=> FETCH c2;
```
```text
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)
```
```text
=> FETCH c1;
```
```text
 acc_no | amount
--------+---------
      2 | 1900.00
(1 row)
```
```text
=> FETCH c2;
```
Вторая команда ожидает блокировку...
```text
=> FETCH c1;
```
Произошла взаимоблокировка. И через некоторое время:
```text
ERROR:  deadlock detected
DETAIL:  Process 61805 waits for ShareLock on transaction 753; blocked by process 61684.
Process 61684 waits for ShareLock on transaction 754; blocked by process 61805.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,8) in relation "accounts"
```
```text
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)
```
```text
=> COMMIT;
```

## Page 32: Page 32 {#source-page-032}

```text
COMMIT
```
```text
=> COMMIT;
```
```text
ROLLBACK
```