Русский
Русский
English
Статистика
Реклама

DBA кто скрывается за блокировкой

В предыдущей статье мы научились снимать состояние блокировок на сервере 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  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;


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

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



А теперь посмотрим на реальных примерах.

Создание одноименных таблиц


-- 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
Таблица, как и любой именованный объект в 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;

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



Создание одноименных индексов


-- 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 индекса (число)
Тут мы снова видим точно такую же RowExclusive-блокировку на 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 ---
Вот вам маленькая демонстрация, почему с dblink надо обращаться очень аккуратно. Здесь мы видим классическую ситуацию транзакция ждет транзакцию просто ее окончания. И не дождется никогда в нашем случае, поскольку сам DO-блок является транзакцией.

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



Блокировка по 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
Тут мы уже видим RowExclusive не на одной из системных таблиц, а на нашей. А в пару к ней и на том индексе, который вызвал проблемы [не]уникальности.

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



Одновременное изменение записи


Попробуем теперь одну и ту же запись в нашей таблице 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
Понятно, что оба запроса наложили RowExclusive на таблицу, раз хотят туда что-то записать. Но важнее, что что заблокированный запрос ждет tuple-блокировку на этой же таблице. Причем последние два числа в ID объекта блокировки (или поля page, tuple в исходной 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
Собственно, мы видим, что заблокированный хочет AccessExclusive самый тяжелый режим, но не может получить из-за RowExclusive. Смотрим на картинку с таблицей конфликтов и понимаем, что это кто-то из {TRUNCATE, VACUUM FULL, ...} пересекся c {INSERT, UPDATE, DELETE}, даже не заглядывая в pg_stat_activity.

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



Надеюсь, если теперь вы встретитесь с какой-то блокировкой, всегда сможете вычислить ее причину, даже если исходные запросы нигде в логи не попались.
Источник: habr.com
К списку статей
Опубликовано: 15.06.2020 20:17:21
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании тензор

Системное администрирование

Postgresql

Sql

Администрирование баз данных

Dba

Sql tips and tricks

Блокировки

Категории

Последние комментарии

© 2006-2020, personeltest.ru