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



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

мегазапрос
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;


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

  1. создание одноименных таблиц
  2. создание одноименных индексов
  3. самоблокировка (CONCURRENTLY vs транзакция)
  4. блокировка по UNIQUE-индексу
  5. одновременное изменение записи
  6. обслуживание таблицы

Но сначала посмотрим, какие вообще ресурсы могут вызывать конфликт, и чем они идентифицируются в 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.

Пример состояния:



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