Сначала немного доработаем запрос из исходной статьи, чтобы он стал чуть удобнее и нагляднее, а вся самая важная для восприятия информация оказывалась в одном месте:
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 lcx JOIN 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.relkind FROM cfl LEFT JOIN LATERAL( SELECT * FROM pg_class WHERE cfl.type = 'relation' AND oid = target[1] LIMIT 1 ) cl ON TRUE ORDER 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) |
В большинстве случаев «в быту» блокировки возникают, конечно же, на таблицах и их записях. Давайте посмотрим на конфликты режимов блокировок как на матрицу мешающих друг другу запросов:
А теперь посмотрим на реальных примерах.
Создание одноименных таблиц
-- tx1 BEGIN; 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 |
|
Таблица, как и любой «именованный» объект в PostgreSQL представлена как запись в таблице 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;Пример состояния:
Создание одноименных индексов
-- tx1 BEGIN; 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 индекса (число) |
|
Тут мы снова видим точно такую же RowExclusive-блокировку на pg_class, которая не дает создать одноименные объекты. Но уже никаких pg_type — поскольку создание индекса не порождает никаких спецтипов. Вместо этого мы видим Share-блокировки, наложенные на обе таблицы (в нашем случае, она одна и та же).
Заметим, что Share между собой не конфликтуют, поэтому попробуем создать сразу два разных индекса на одной таблице:
-- tx1 BEGIN; 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 |
--- |
|
Вот вам маленькая демонстрация, почему с dblink надо обращаться очень аккуратно. Здесь мы видим классическую ситуацию «транзакция ждет транзакцию» — просто ее окончания. И не дождется никогда в нашем случае, поскольку сам DO-блок является транзакцией.
Пример состояния:
Блокировка по UNIQUE-индексу
Перейдем от работы с таблицами к работе с отдельными записями и попробуем вставить в разных транзакциях записи с одинаковым значением уникального ключа.
-- tx1 BEGIN; 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 |
Тут мы уже видим RowExclusive не на одной из системных таблиц, а на нашей. А в пару к ней — и на том индексе, который вызвал проблемы [не]уникальности.
Пример состояния:
Одновременное изменение записи
Попробуем теперь одну и ту же запись в нашей таблице UPDATE‘нуть:
-- tx1 BEGIN; 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 |
Понятно, что оба запроса наложили RowExclusive на таблицу, раз хотят туда что-то записать. Но важнее, что что заблокированный запрос ждет tuple-блокировку на этой же таблице. Причем последние два числа в ID объекта блокировки (или поля page, tuple в исходной pg_locks) позволяют нам узнать, что именно за запись мы тут ждем:
SELECT * FROM <relation> WHERE ctid = '(<page>,<tuple>)';Пример состояния:
Обслуживание таблицы
Мы тут очень много чего в таблице уже намусорили — давайте ее зачистим. Но запись-то пока в нее идет:
-- tx1 BEGIN; 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 |
Собственно, мы видим, что заблокированный хочет AccessExclusive — самый «тяжелый» режим, но не может получить из-за RowExclusive. Смотрим на картинку с таблицей конфликтов — и понимаем, что это кто-то из {TRUNCATE, VACUUM FULL, ...} пересекся c {INSERT, UPDATE, DELETE}, даже не заглядывая в pg_stat_activity.
Пример состояния:
Надеюсь, если теперь вы встретитесь с какой-то блокировкой, всегда сможете вычислить ее причину, даже если исходные запросы нигде в логи не попались.
ссылка на оригинал статьи https://habr.com/ru/company/tensor/blog/506360/
Добавить комментарий