Сначала немного доработаем запрос из исходной статьи, чтобы он стал чуть удобнее и нагляднее, а вся самая важная для восприятия информация оказывалась в одном месте:
мегазапрос
WITH lm(ld, lr) AS ( VALUES ('AccessShareLock', '{AccessExclusiveLock}'::text[]) , ('RowShareLock', '{ExclusiveLock,AccessExclusiveLock}'::text[]) , ('RowExclusiveLock', '{ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[]) , ('ShareUpdateExclusiveLock', '{ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[]) , ('ShareLock', '{RowExclusiveLock,ShareUpdateExclusiveLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[]) , ('ShareRowExclusiveLock', '{RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[]) , ('ExclusiveLock', '{RowShareLock,RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[]) , ('AccessExclusiveLock', '{AccessShareLock,RowShareLock,RowExclusiveLock,ShareUpdateExclusiveLock,ShareLock,ShareRowExclusiveLock,ExclusiveLock,AccessExclusiveLock}'::text[])), locks AS ( SELECT ( locktype , database , relation , page , tuple , virtualxid , transactionid::text::bigint , classid , objid , objsubid ) target , * FROM pg_locks), ld AS ( SELECT * FROM locks WHERE NOT granted), lr AS ( SELECT * FROM locks WHERE target::text = ANY(ARRAY( SELECT DISTINCT target::text FROM ld )) AND granted), lcx AS ( SELECT lr.target , ld.pid ldp , ld.mode ldm , lr.pid lrp , lr.mode lrm FROM ld JOIN lr ON lr.pid <> ld.pid AND lr.target IS NOT DISTINCT FROM ld.target), cfl AS (SELECT lc.locktype "type", CASE lc.locktype WHEN 'relation' THEN ARRAY[relation] WHEN 'extend' THEN ARRAY[relation] WHEN 'page' THEN ARRAY[relation, page] WHEN 'tuple' THEN ARRAY[relation, page, tuple] WHEN 'transactionid' THEN ARRAY[transactionid::text::oid] WHEN 'virtualxid' THEN string_to_array(virtualxid::text, '/')::oid[] WHEN 'object' THEN ARRAY[classid, objid, objsubid] WHEN 'userlock' THEN ARRAY[classid] WHEN 'advisory' THEN ARRAY[classid, objid, objsubid] END target, nullif(lc.pid = lcx.ldp, FALSE) as locked, lc.pid, regexp_replace(lc.mode, 'Lock$', '') "mode", nullif(lc.granted, TRUE) "granted", nullif(lc.target IS NOT DISTINCT FROM lcx.target, FALSE) "conflict"FROM lcxJOIN locks lc ON lc.pid IN (lcx.ldp, lcx.lrp))SELECT cfl.*, CASE WHEN "type" NOT IN ('virtualxid', 'transactionid') THEN target[1]::regclass END relname, cl.relkindFROM cflLEFT JOIN LATERAL( SELECT * FROM pg_class WHERE cfl.type = 'relation' AND oid = target[1] LIMIT 1 ) cl ON TRUEORDER BY -- сортируем ... locked -- сначала кого блокируют, pid -- по принадлежности процессу (1 процесс = 1 транзакция), CASE "type" -- по приоритету типов блокировок WHEN 'virtualxid' THEN 0 WHEN 'transactionid' THEN 1 WHEN 'relation' THEN 2 WHEN 'tuple' THEN 3 WHEN 'object' THEN 4 WHEN 'advisory' THEN 5 END, CASE relkind -- по принадлежности объекта таблице WHEN 'r' THEN cl.oid WHEN 't' THEN regexp_replace(cl.relname, E'^.*\\D(\\d+)$', E'\\1', '')::oid WHEN 'i' THEN ( SELECT indrelid FROM pg_index WHERE indexrelid = cl.oid LIMIT 1 ) WHEN 'S' THEN ( SELECT ( SELECT adrelid FROM pg_attrdef WHERE oid = dp.objid ) FROM pg_depend dp WHERE (refclassid, refobjid) = ('pg_class'::regclass, cl.oid) AND (deptype, classid) = ('n', 'pg_attrdef'::regclass) LIMIT 1 ) END -- https://postgrespro.ru/docs/postgresql/12/catalog-pg-class, CASE relkind -- по типу объекта БД WHEN 'r' THEN 0 -- relation WHEN 'm' THEN 1 -- materialized view WHEN 'p' THEN 2 -- partitioned table WHEN 'f' THEN 3 -- foreign table WHEN 't' THEN 4 -- TOAST WHEN 'i' THEN 5 -- index WHEN 'I' THEN 6 -- partitioned index WHEN 'S' THEN 7 -- sequence WHEN 'c' THEN 8 -- composite type WHEN 'v' THEN 9 -- view END, CASE -- по типу индекса PK вперед WHEN relkind = 'i' THEN NOT ( SELECT indisprimary FROM pg_index WHERE indexrelid = cl.oid LIMIT 1 ) END, cl.relname -- по имени объекта, CASE mode -- по приоритету режима блокировки WHEN 'AccessExclusive' THEN 0 WHEN 'Exclusive' THEN 1 WHEN 'ShareRowExclusive' THEN 2 WHEN 'Share' THEN 3 WHEN 'ShareUpdateExclusive' THEN 4 WHEN 'RowExclusive' THEN 5 WHEN 'RowShare' THEN 6 WHEN 'AccessShare' THEN 7 END;
С помощью него мы рассмотрим некоторые примеры ситуаций, которые могут вам встретиться в реальной работе:
- создание одноименных таблиц
- создание одноименных индексов
- самоблокировка (CONCURRENTLY vs транзакция)
- блокировка по UNIQUE-индексу
- одновременное изменение записи
- обслуживание таблицы
Но сначала посмотрим, какие вообще ресурсы могут вызывать конфликт, и чем они идентифицируются в
pg_locks:| locktype | описание | ID ресурса |
relation |
отношение (таблица) |
(relation) |
extend |
расширение отношения (TOAST) |
(relation) |
page |
страница (блок данных таблицы/индекса) |
(relation, page) |
tuple |
кортеж (запись таблицы/индекса) |
(relation, page, tuple) |
transactionid |
идентификатор транзакции |
(transactionid) |
virtualxid |
виртуальный идентификатор |
(virtualxid) |
object |
некоторый объект |
(classid, objid, objsubid) |
userlock |
пользовательская блокировка |
(classid) |
advisory |
рекомендательная блокировка |
(classid, objid, objsubid) |
А теперь посмотрим на реальных примерах.
Создание одноименных таблиц
-- tx1BEGIN;CREATE TABLE tbl(pk integer, val integer); -- tx2 CREATE TABLE tbl(pk integer, val integer);
Профиль блокировок:
| type | mode | relname | relkind |
| ожидающий PID | |||
relation |
RowExclusive |
pg_type |
r |
relation |
RowExclusive |
pg_class |
r |
object |
AccessShare |
pg_namespace |
|
| блокирующий PID | |||
relation |
AccessExclusive |
oid таблицы (число) |
|
object |
AccessShare |
pg_namespace |
|
таблице pg_class.
Отличаются друг от друга записи объектов разных типов значением
поля pg_class.relkind:
- r = обычная таблица (Relation)
- i = индекс (Index)
- S = последовательность (Sequence)
- t = таблица TOAST
- v = представление (View)
- m = материализованное представление (Materialized view)
- c = составной тип (Composite type)
- f = сторонняя таблица (Foreign table)
- p = секционированная таблица (Partitioned table)
- I = секционированный индекс (partitioned Index)
Но, поскольку запись с точки зрения нашей транзакции мониторинга еще не существует, раскастовка ее
oid в
regclass не может пройти, и мы видим вместо имени
только лишь число.Также помимо записи о самой таблице в метаданные (в
pg_type) добавляется табличный тип,
благодаря которому мы можем кастовать что-то прямо в формат записи
таблицы:
SELECT NULL::tbl;
Пример состояния:
Создание одноименных индексов
-- tx1BEGIN;CREATE UNIQUE INDEX idx ON tbl(pk); -- tx2 CREATE UNIQUE INDEX idx ON tbl(pk);
Профиль блокировок:
| type | mode | relname | relkind |
| ожидающий PID | |||
relation |
RowExclusive |
pg_class |
r |
relation |
Share |
tbl |
r |
relation |
AccessExclusive |
oid индекса (число) |
|
| блокирующий PID | |||
relation |
Share |
tbl |
r |
relation |
AccessExclusive |
oid индекса (число) |
|
pg_class, которая не дает создать одноименные
объекты. Но уже никаких pg_type поскольку создание
индекса не порождает никаких спецтипов. Вместо этого мы видим
Share-блокировки, наложенные на обе таблицы (в нашем случае, она
одна и та же).Заметим, что Share между собой не конфликтуют, поэтому попробуем создать сразу два разных индекса на одной таблице:
-- tx1BEGIN;CREATE UNIQUE INDEX idx1 ON tbl(pk); -- tx2 CREATE UNIQUE INDEX idx2 ON tbl(pk); -- работает, и никаких блокировок!
Пример состояния:
Самоблокировка (CONCURRENTLY vs транзакция)
Теперь давайте попробуем создать еще один индекс но не просто так, а
CONCURRENTLY, чтобы никому не блокировать доступ к
таблице.Ну а если мы хотим сделать это из PL-кода, где
CONCURRENTLY-запросы запускать нельзя, как и в любой
транзакции?.. Конечно же, воспользуемся модулем dblink для
подключения к этой же БД!
DO $$BEGIN SELECT dblink_exec('dbname=' || current_database() || ' user=' || current_user, 'CREATE INDEX CONCURRENTLY idx_cic ON tbl(pk);');END$$;
Профиль блокировок:
| type | mode | relname | relkind |
| ожидающий PID | |||
virtualxid |
Share |
--- |
|
| блокирующий PID | |||
virtualxid |
Exclusive |
--- |
|
Пример состояния:
Блокировка по UNIQUE-индексу
Перейдем от работы с таблицами к работе с отдельными записями и попробуем вставить в разных транзакциях записи с одинаковым значением уникального ключа.
-- tx1BEGIN;INSERT INTO tbl(pk, val)VALUES(1, 1); -- tx2 INSERT INTO tbl(pk, val)VALUES(1, 1);
Профиль блокировок:
| type | mode | relname | relkind |
| ожидающий PID | |||
relation |
RowExclusive |
tbl |
r |
| блокирующий PID | |||
relation |
RowExclusive |
tbl |
r |
relation |
RowExclusive |
idx |
i |
Пример состояния:
Одновременное изменение записи
Попробуем теперь одну и ту же запись в нашей таблице
UPDATE'нуть:
-- tx1BEGIN;UPDATE tbl SET val = val + 1 WHERE pk = 1; -- tx2 UPDATE tbl SET val = val + 1 WHERE pk = 1;
Профиль блокировок:
| type | mode | relname | relkind |
| ожидающий PID | |||
tuple |
Exclusive |
tbl |
r |
relation |
RowExclusive |
tbl |
r |
| блокирующий PID | |||
relation |
RowExclusive |
tbl |
r |
pg_locks) позволяют нам узнать, что именно за
запись мы тут ждем:
SELECT * FROM <relation> WHERE ctid = '(<page>,<tuple>)';
Пример состояния:
Обслуживание таблицы
Мы тут очень много чего в таблице уже намусорили давайте ее зачистим. Но запись-то пока в нее идет:
-- tx1BEGIN;INSERT INTO tbl(pk, val)VALUES(2, 2); -- tx2 TRUNCATE TABLE tbl;
Профиль блокировок:
| type | mode | relname | relkind |
| ожидающий PID | |||
relation |
AccessExclusive |
tbl |
r |
| блокирующий PID | |||
relation |
RowExclusive |
tbl |
r |
{TRUNCATE, VACUUM FULL, ...} пересекся c
{INSERT, UPDATE, DELETE}, даже не заглядывая в
pg_stat_activity.Пример состояния:
Надеюсь, если теперь вы встретитесь с какой-то блокировкой, всегда сможете вычислить ее причину, даже если исходные запросы нигде в логи не попались.
