Уровень 3.0

Предусловия:

Изучена лекция «Блокировки»

Анализ блокировок

Откройте терминал и подключитесь в psql к базе данных postgres c ролью postgres:

[student@pkles-gt0040964 ~]$ sudo -iu postgres

-bash-4.4$ psql

psql (15.5)
Введите "help", чтобы получить справку.

Создайте базу данных locks_db и подключитесь к ней:

postgres=# CREATE DATABASE locks_db;

CREATE DATABASE

postgres=# \c locks_db

Вы подключены к базе данных "locks_db" как пользователь "postgres".

Создайте расширение pageinspect:
```
locks_db=# CREATE EXTENSION pageinspect;
```
```
CREATE EXTENSION
```
Вспомним, что расширение pageinspect позволяет обратиться к содержимому страниц данных.
Создайте расширение pgrowlocks:
```
locks_db=# CREATE EXTENSION pgrowlocks;
```
```
CREATE EXTENSION
```
Расширение pgrowlocks позволяет получить информацию о блокировках строк в удобном виде.

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

locks_db=# CREATE OR REPLACE FUNCTION get_tuples_info(relname text, pagenum bigint)
RETURNS TABLE(ctid text, xmin xid, xmax xid,
    is_multi boolean, lock_only boolean,
    keys_updated boolean, key_share boolean, share boolean)
AS $$
SELECT '(' || $2 || ', ' || lp || ')', t_xmin, t_xmax,
    'HEAP_XMAX_IS_MULTI' = ANY(raw_flags),
    'HEAP_XMAX_LOCK_ONLY' = ANY(raw_flags),
    'HEAP_KEYS_UPDATED' = ANY(raw_flags),
    'HEAP_XMAX_KEYSHR_LOCK' = ANY(raw_flags),
    'HEAP_XMAX_SHR_LOCK' = ANY(raw_flags)
         FROM heap_page_items(get_raw_page($1, 'main', $2)),
           LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
         WHERE t_infomask IS NOT NULL OR t_infomask2 IS NOT NULL
    ORDER BY lp;
$$ LANGUAGE SQL;

CREATE FUNCTION

Похожая функция уже использовалась в предыдущих лабораторных работах.

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

Создайте простую таблицу:

locks_db=# CREATE TABLE some_table(id integer, value integer);

CREATE TABLE

Настройте приглашение в psql с указанием номера обслуживающего процесса:
```
locks_db=# \set PROMPT1 '%p%R%x%# '
```
```
4180=# \set PROMPT2 '%p%R%x%# '
```
Теперь в приглашении указан pid обслуживающего процесса. Это необходимо для различения разных сеансов psql.
Откройте второй терминал и перейдите в режим выполнения команд от имени пользователя postgres:
```
[student@pkles-gt0041359 ~]$ sudo -iu postgres
```

Во втором сеансе подключитесь к базе данных locks_db и настройте приглашение:

-bash-4.4$ psql -d locks_db

psql (15.5)
Введите "help", чтобы получить справку.

locks_db=# \set PROMPT1 '%p%R%x%# '

67993=# \set PROMPT2 '%p%R%x%# '

Откройте третий терминал и перейдите в режим выполнения команд от имени пользователя postgres:
```
[student@pkles-gt0041359 ~]$ sudo -iu postgres
```

В третьем сеансе подключитесь к базе данных locks_db и настройте приглашение:

-bash-4.4$ psql -d locks_db

psql (15.5)
Введите "help", чтобы получить справку.

locks_db=# \set PROMPT1 '%p%R%x%# '

68008=# \set PROMPT2 '%p%R%x%# '

Блокировки объектов

Во втором сеансе начните транзакцию:
```
67993=# BEGIN;
```
```
BEGIN
```
В первом сеансе посмотрите, какие блокировки запрошены обслуживающим процессом второго сеанса:
```
4180=# SELECT pid, locktype, relation, virtualxid, transactionid, mode, granted FROM pg_locks WHERE pid=67993;
```
```
  pid  |  locktype  | relation | virtualxid | transactionid |     mode      | granted
-------+------------+----------+------------+---------------+---------------+---------
 67993 | virtualxid |          | 7/1646     |               | ExclusiveLock | t
(1 строка)
```
Представление pg_locks отображает все текущие блокировки объектов.

При старте транзакции обслуживающий процесс сразу захватил блокировку виртуального номера транзакции (locktype=virtualxid) в исключительном режиме (mode=ExclusiveLock).

Значение поля granted говорит о том, что захват блокировки был успешным.
Во втором сеансе вставьте одну строку в таблицу some_table:
```
67993=*# INSERT INTO some_table VALUES(1,100);
```
```
INSERT 0 1
```
В первом сеансе снова посмотрите, какие блокировки запрошены обслуживающим процессом второго сеанса:
```
4180=# SELECT pid, locktype, relation, virtualxid, transactionid, mode, granted FROM pg_locks WHERE pid=67993;
```
```
  pid  |   locktype    | relation | virtualxid | transactionid |       mode       | granted
-------+---------------+----------+------------+---------------+------------------+---------
 67993 | relation      |    16388 |            |               | RowExclusiveLock | t
 67993 | virtualxid    |          | 7/1646     |               | ExclusiveLock    | t
 67993 | transactionid |          |            |           792 | ExclusiveLock    | t
(3 строки)
```
Теперь при первом изменении внутри транзакции также была захвачена блокировка настоящего номера транзакции (locktype=transactionid).

Помимо этого при вставке строки была захвачена блокировка типа relation в режиме RowExclusiveLock.

Вспомним, что как при изменении, так и при выполнении запросов на таблицу накладывается блокировка отношений (relation). Указанная блокировка накладывается на всю таблицу, а не на отдельные строки.
Во втором сеансе выполните запрос к таблице some_table:
```
67993=*# SELECT * FROM some_table;
```
```
 id | value
----+-------
  1 |   100
(1 строка)
```

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

4180=# SELECT pid, locktype, relation, virtualxid, transactionid, mode, granted FROM pg_locks WHERE pid=67993;

  pid  |   locktype    | relation | virtualxid | transactionid |       mode       | granted
-------+---------------+----------+------------+---------------+------------------+---------
 67993 | relation      |    16388 |            |               | AccessShareLock  | t
 67993 | relation      |    16388 |            |               | RowExclusiveLock | t
 67993 | virtualxid    |          | 7/1646     |               | ExclusiveLock    | t
 67993 | transactionid |          |            |           792 | ExclusiveLock    | t
(4 строки)

При выполнении запроса также была захвачена блокировка отношения в самом слабом режиме AccessShareLock.

В третьем сеансе очистите таблицу some_table командой TRUNCATE:
```
68008=# TRUNCATE some_table;
```
Выполнение команды приостановлено, поскольку команда TRUNCATE требует блокировки в несовместимом режиме с уже имеющимися блокировками.
В первом сеансе посмотрите, какие блокировки запрошены обслуживающими процессами второго и третьего сеансов:
```
4180=# SELECT pid, locktype, relation, virtualxid, transactionid, mode, granted FROM pg_locks WHERE pid IN (67993, 68008) ORDER BY pid;
```
```
  pid  |   locktype    | relation | virtualxid | transactionid |        mode         | granted
-------+---------------+----------+------------+---------------+---------------------+---------
 67993 | relation      |    16388 |            |               | AccessShareLock     | t
 67993 | virtualxid    |          | 7/1646     |               | ExclusiveLock       | t
 67993 | relation      |    16388 |            |               | RowExclusiveLock    | t
 67993 | transactionid |          |            |           792 | ExclusiveLock       | t
 68008 | virtualxid    |          | 5/101025   |               | ExclusiveLock       | t
 68008 | transactionid |          |            |           793 | ExclusiveLock       | t
 68008 | relation      |    16388 |            |               | AccessExclusiveLock | f
(7 строк)
```
Действительно, команда TRUNCATE запросила блокировку отношения в самом строгом режиме AccessExclusiveLock, несовместимом ни с одним из режимов блокировок отношений.

Однако запрошенная блокировка предоставлена не была, о чем свидетельствует значение granted=f. Процесс 68008 встал в очередь ожидания снятия блокировок.

Помимо этого, поскольку была начата еще одна транзакция, процесс 68008 также захватил блокировки своих номеров виртуальной и настоящей транзакций.
В первом сеансе посмотрите, снятия блокировок каких процессов ожидает обслуживающий процесс третьего сеанса:
```
4180=# SELECT pg_blocking_pids(68008);
```
```
 pg_blocking_pids
------------------
 {67993}
(1 строка)
```
Функция pg_blocking_pids позволяет по номеру процесса получить список процессов с несовместимыми блокировками в очереди (или уже захвативших блокировку).

В данном случае процесс с номером 68008 ожидает освобождения блокировки процессом с номером 67993.
В первом сеансе с использованием представления pg_stat_activity получите информацию об обслуживающих процессах второго и третьего сеансов:

4180=# SELECT pid, wait_event_type, wait_event, state, query, backend_type FROM pg_stat_activity WHERE pid IN (67993, 68008) ORDER BY pid;

  pid  | wait_event_type | wait_event |        state        |           query           |  backend_type
-------+-----------------+------------+---------------------+---------------------------+----------------
67993 | Client          | ClientRead | idle in transaction | SELECT * FROM some_table; | client backend
68008 | Lock            | relation   | active              | TRUNCATE some_table;      | client backend
(2 строки)

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

В данном случае блокирующий процесс с номером 67993 находится в состоянии ожидания новой команды в транзакции (state = idle in transaction). При этом он своей предыдущей командой, которая отображена в поле query, захватил блокировку, препятствующую продолжению работы процесса 68008.

О том, что процесс 68008 заблокирован, говорит значение поля wait_event_type = Lock. В свою очередь тип блокировки, снятия которой ожидает процесс, указан в поле wait_event.

Во втором сеансе зафиксируйте транзакцию:

67993=*# COMMIT;

COMMIT

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

4180=# SELECT pid, locktype, relation, virtualxid, transactionid, mode, granted FROM pg_locks WHERE pid IN (67993, 68008) ORDER BY pid;

 pid | locktype | relation | virtualxid | transactionid | mode | granted
-----+----------+----------+------------+---------------+------+---------
(0 строк)

После фиксации транзакции все блокировки объектов были сняты.

Вспомним, что время жизни блокировок объектов, как правило, ограничено временем жизни транзакции.

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

В первом сеансе создайте первичный ключ в таблице some_table:
```
4180=# ALTER TABLE some_table ADD PRIMARY KEY (id);
```
```
ALTER TABLE
```
В первом сеансе вставьте одну строку в таблицу some_table и посмотрите содержание табличной страницы:
```
4180=# INSERT INTO some_table VALUES(1,100);
```
```
INSERT 0 1
```
```
4180=# SELECT * FROM get_tuples_info('some_table', 0);
```
```
  ctid  | xmin | xmax | is_multi | lock_only | keys_updated | key_share | share
--------+------+------+----------+-----------+--------------+-----------+-------
 (0, 1) |  819 |    0 | f        | f         | f            | f         | f
(1 строка)
```
Вспомним, что признаком блокировки строки является ненулевое значение поля xmax в ее заголовке, соответствующее активной транзакции.

В данный момент единственная строка в таблице не заблокирована.
Во втором сеансе начните транзакцию и обновите неключевое поле вставленной строки:
```
67993=# BEGIN;
```
```
BEGIN
```
```
67993=*# UPDATE some_table SET value = 150 WHERE id = 1;
```
```
UPDATE 1
```
В первом сеансе посмотрите содержание табличной страницы:
```
4180=# SELECT * FROM get_tuples_info('some_table', 0);
```
```
  ctid  | xmin | xmax | is_multi | lock_only | keys_updated | key_share | share
--------+------+------+----------+-----------+--------------+-----------+-------
 (0, 1) |  819 |  820 | f        | f         | f            | f         | f
 (0, 2) |  820 |    0 | f        | f         | f            | f         | f
(2 строки)
```
Теперь на первую строку была наложена блокировка, о чем говорит значение xmax = 820.

При этом никакие информационные биты выставлены не были. Это означает, что блокировка была наложена в режиме FOR NO KEY UPDATE.

Действительно, строка была обновлена без затрагивания ключевых полей.

В третьем сеансе начните транзакцию и выполните запрос:

68008=# BEGIN;

BEGIN

68008=*# SELECT * FROM some_table WHERE id=1;

 id | value
----+-------
  1 |   100
(1 строка)

В первом сеансе посмотрите содержание табличной страницы:
```
4180=# SELECT * FROM get_tuples_info('some_table', 0);
```
```
  ctid  | xmin | xmax | is_multi | lock_only | keys_updated | key_share | share
--------+------+------+----------+-----------+--------------+-----------+-------
 (0, 1) |  819 |  820 | f        | f         | f            | f         | f
 (0, 2) |  820 |    0 | f        | f         | f            | f         | f
(2 строки)
```
После выполнения запроса в заголовках версий строк изменений не произошло.

Вспомним, что выполнение запроса без явного указания режима блокирования к блокировке строк не приводит.
В третьем сеансе выполните запрос с захватом блокировки FOR KEY SHARE:
```
68008=*# SELECT * FROM some_table WHERE id=1 FOR KEY SHARE;
```
```
 id | value
----+-------
  1 |   100
(1 строка)
```
Вспомним, что режим FOR KEY SHARE запрещает изменение ключевых полей параллельным транзакциям.
В первом сеансе посмотрите содержание табличной страницы:
```
4180=# SELECT * FROM get_tuples_info('some_table', 0);
```
```
  ctid  | xmin | xmax | is_multi | lock_only | keys_updated | key_share | share
--------+------+------+----------+-----------+--------------+-----------+-------
 (0, 1) |  819 |    6 | t        | f         | f            | f         | f
 (0, 2) |  820 |  821 | f        | t         | f            | t         | f
(2 строки)
```
Запрошенная явно блокировка в режиме FOR KEY SHARE совместима с режимом FOR NO KEY UPDATE блокировки, захваченной командой UPDATE в параллельной транзакции.

Поэтому на единственную строку сейчас наложено сразу две совместимых блокировки.

Поскольку сразу два номера транзакции не могут быть записаны в поле xmax, для первой версии строки была создана мультитранзакция с номером 6, о чем свидетельствует проставленный информационный бит is_multi.

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

При этом, поскольку изменения версии строки не произошло, а была только наложена блокировка, проставлен информационный бит lock_only. Также проставлен информационный бит key share, свидетельствующий о том, что блокировка наложена в режиме FOR KEY SHARE.
В первом сеансе получите информацию о блокировках строк с использованием расширения pgrowlocks:
```
4180=# SELECT * FROM pgrowlocks('some_table');
```
```
 locked_row | locker | multi |   xids    |             modes             |     pids
------------+--------+-------+-----------+-------------------------------+---------------
 (0,1)      |      6 | t     | {820,821} | {"No Key Update","Key Share"} | {67993,68008}
(1 строка)
```
С использованием расширения pgrowlocks можно получить в удобочитаемом виде информацию о блокировках строк. При этом расширение также отображает информацию о разделяемых блокировках, для которых используются мультитранзакции.

Расширение выдает информацию о блокировках для каждой строки, но не версии строки, как в табличных страницах.

В данном случае единственная строка заблокирована двумя процессами в совместимых режимах (modes = "No Key Update" и "Key Share").
Во втором сеансе обновите ключевое поле вставленной строки:

67993=*# UPDATE some_table SET id = 10 WHERE id = 1;

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

В первом сеансе посмотрите текущие блокировки объектов, запрошенные обслуживающим процессом второго сеанса:

4180=# SELECT pid, locktype, relation, virtualxid, transactionid, mode, granted FROM pg_locks WHERE pid = 67993;

  pid  |   locktype    | relation | virtualxid | transactionid |        mode         | granted
-------+---------------+----------+------------+---------------+---------------------+---------
 67993 | relation      |    16454 |            |               | RowExclusiveLock    | t
 67993 | relation      |    16388 |            |               | RowExclusiveLock    | t
 67993 | virtualxid    |          | 7/1653     |               | ExclusiveLock       | t
 67993 | tuple         |    16388 |            |               | AccessExclusiveLock | t
 67993 | transactionid |          |            |           820 | ExclusiveLock       | t
 67993 | transactionid |          |            |           821 | ShareLock           | f
(6 строк)

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

Вспомним, что для организации очереди блокировок строк используются блокировки объектов типов transactionid и tuple.

В данном случае процесс запросил блокировку номера транзакции 821, которая ему не была предоставлена (granted=f). О снятии блокировки строки процесс узнает, когда будет снята блокировка номера транзакции, ее заблокировавшей в несовместимом режиме.

Также для частичного упорядочивания очереди процесс захватил блокировку типа tuple.

В третьем сеансе завершите транзакцию:

68008=*# ROLLBACK;

ROLLBACK

Во втором сеансе завершите транзакцию:

67993=*# ROLLBACK;

ROLLBACK

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

В первом сеансе вставьте вторую строку и сделайте запрос к таблице some_table:

4180=# INSERT INTO some_table VALUES(2,200);

INSERT 0 1

4180=# SELECT * FROM some_table;

 id | value
----+-------
  1 |   100
  2 |   200
(2 строки)

Во втором сеансе начните транзакцию и обновите первую строку таблицы some_table:
```
67993=# BEGIN;
```
```
BEGIN
```
```
67993=*# UPDATE some_table SET value = 150 WHERE id = 1;
```
```
UPDATE 1
```
В третьем сеансе начните транзакцию и обновите вторую строку таблицы some_table:
```
68008=# BEGIN;
```
```
BEGIN
```
```
68008=*# UPDATE some_table SET value = 250 WHERE id = 2;
```
```
UPDATE 1
```
Во втором сеансе обновите вторую строку таблицы some_table:
```
67993=*# UPDATE some_table SET value = 270 WHERE id = 2;
```
Выполнение команды приостановлено, поскольку вторая строка уже заблокирована в третьем сеансе.
В третьем сеансе обновите первую строку таблицы some_table:
```
68008=*# UPDATE some_table SET value = 170 WHERE id = 1;
```
```
ERROR:  deadlock detected
ПОДРОБНОСТИ:  Process 68008 waits for ShareLock on transaction 824; blocked by process 67993.
Process 67993 waits for ShareLock on transaction 825; blocked by process 68008.
ПОДСКАЗКА:  See server log for query details.
КОНТЕКСТ:  while updating tuple (0,1) in relation "some_table"
```
Первая строка уже заблокирована во втором сеансе.

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

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

Такие ситуации, как правило, возникают при неправильном проектировании приложения и связаны с различным порядком изменения строк параллельными транзакциями.
В первом сеансе посмотрите значение параметра deadlock_timeout:
```
4180=# SHOW deadlock_timeout;
```
```
 deadlock_timeout
------------------
 1s
(1 строка)
```
По умолчанию процедура обнаружения взаимоблокировок запускается через 1 секунду ожидания.

Завершение

Откатите транзакцию и завершите второй сеанс psql:
```
67993=*# ROLLBACK;
```
```
ROLLBACK
```
```
67993=# \q
```
Откатите транзакцию и завершите третий сеанс psql:
```
68008=*# ROLLBACK;
```
```
ROLLBACK
```
```
68008=# \q
```

В первом сеансе подключитесь к базе данных postgres, удалите базу данных locks_db и завершите сеанс psql:

4180=# \c postgres

Вы подключены к базе данных "postgres" как пользователь "postgres".

71666=# DROP DATABASE locks_db;

DROP DATABASE

71666=# \q

Самопроверка

Вопрос 1

Состояние блокировок объектов, полученное с использованием представления pg_locks, представлено ниже:

pid  |   locktype    | relation | virtualxid | transactionid |        mode         | granted
-------+---------------+----------+------------+---------------+---------------------+---------
80147 | transactionid |          |            |           883 | ExclusiveLock       | t
80147 | relation      |    16524 |            |               | AccessExclusiveLock | f
80147 | virtualxid    |          | 6/310      |               | ExclusiveLock       | t
80291 | relation      |    16524 |            |               | AccessShareLock     | f
80291 | virtualxid    |          | 7/1960     |               | ExclusiveLock       | t
80512 | relation      |    16524 |            |               | RowExclusiveLock    | t
80512 | relation      |    12073 |            |               | AccessShareLock     | t
80512 | virtualxid    |          | 5/112537   |               | ExclusiveLock       | t
80512 | transactionid |          |            |           882 | ExclusiveLock       | t

Процесс с каким номером, выполняя команду SELECT, не смог захватить блокировку отношения?

Вопрос 2

Состояние заголовков версий строк табличной страницы с соответствующими идентификаторами ctid представлено ниже:

ctid  | xmin | xmax | is_multi | lock_only | keys_updated | key_share | share
--------+------+------+----------+-----------+--------------+-----------+-------
(0, 1) |  878 |    1 | t        | t         | f            | t         | f
(0, 2) |  879 |   15 | t        | f         | f            | f         | f
(0, 3) |  880 |  881 | f        | t         | f            | t         | f
(0, 4) |  879 |  880 | f        | f         | f            | f         | f
(0, 5) |  880 |    0 | f        | f         | f            | f         | f

На какие версии строк наложено несколько совместимых блокировок?

Вопрос 3

Состояние блокировок объектов, полученное с использованием представления pg_locks, представлено ниже:

pid  |   locktype    | relation | virtualxid | transactionid |       mode       | granted
-------+---------------+----------+------------+---------------+------------------+---------
80147 | relation      |    16524 |            |               | RowShareLock     | t
80147 | relation      |    16524 |            |               | RowExclusiveLock | t
80147 | virtualxid    |          | 6/309      |               | ExclusiveLock    | t
80147 | transactionid |          |            |           880 | ExclusiveLock    | t
80291 | relation      |    16524 |            |               | RowShareLock     | t
80291 | relation      |    16524 |            |               | RowExclusiveLock | t
80291 | virtualxid    |          | 7/1959     |               | ExclusiveLock    | t
80291 | transactionid |          |            |           880 | ShareLock        | f
80291 | relation      |    16524 |            |               | AccessShareLock  | t
80291 | tuple         |    16524 |            |               | ExclusiveLock    | t
80291 | transactionid |          |            |           881 | ExclusiveLock    | t
80512 | relation      |    12073 |            |               | AccessShareLock  | t
80512 | virtualxid    |          | 5/112534   |               | ExclusiveLock    | t

Процессы с какими номерами ожидают снятия блокировки строк?

80147

80291

80512

Анализ блокировок​

Блокировки объектов​

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

Взаимоблокировки​

Завершение​

Самопроверка​