Онлайн
Alvaros
.
- Регистрация
- 14.05.16
- Сообщения
- 21.452
- Реакции
- 101
- Репутация
- 204
В
Сначала немного доработаем запрос из исходной статьи, чтобы он стал чуть удобнее и нагляднее, а вся самая важная для восприятия информация оказывалась в одном месте:
мегазапрос
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:
id]
WHEN 'virtualxid' THEN
string_to_array(virtualxid::text, '/'):id[]
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', ''):id
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
--
, 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;
С помощью него мы рассмотрим некоторые примеры ситуаций, которые могут вам встретиться в реальной работе:
Но сначала посмотрим, какие вообще ресурсы могут вызывать конфликт, и чем они идентифицируются в
В большинстве случаев «в быту» блокировки возникают, конечно же, на таблицах и их записях. Давайте посмотрим на конфликты
А теперь посмотрим на реальных примерах.
Создание одноименных таблиц
-- tx1
BEGIN;
CREATE TABLE tbl(pk integer, val integer);
-- tx2
CREATE TABLE tbl(pk integer, val integer);
Профиль блокировок:
Таблица, как и любой «именованный» объект в PostgreSQL представлена как запись в
Но, поскольку запись с точки зрения нашей транзакции мониторинга еще «не существует», раскастовка ее oid в regclass не может пройти, и мы видим вместо имени только лишь число.
Также помимо записи о самой таблице в метаданные (в
SELECT NULL::tbl;
Пример состояния:
Создание одноименных индексов
-- tx1
BEGIN;
CREATE UNIQUE INDEX idx ON tbl(pk);
-- tx2
CREATE UNIQUE INDEX idx ON tbl(pk);
Профиль блокировок:
Тут мы снова видим точно такую же 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-запросы запускать нельзя, как и в любой транзакции?.. Конечно же, воспользуемся
DO $$
BEGIN
SELECT dblink_exec('dbname=' || current_database() || ' user=' || current_user, 'CREATE INDEX CONCURRENTLY idx_cic ON tbl(pk);');
END$$;
Профиль блокировок:
Вот вам маленькая демонстрация, почему с dblink надо обращаться очень аккуратно. Здесь мы видим классическую ситуацию «транзакция ждет транзакцию» — просто ее окончания. И не дождется никогда в нашем случае, поскольку сам DO-блок является транзакцией.
Пример состояния:
Блокировка по UNIQUE-индексу
Перейдем от работы с таблицами к работе с отдельными записями и попробуем вставить в разных транзакциях записи с одинаковым значением уникального ключа.
-- tx1
BEGIN;
INSERT INTO tbl(pk, val)VALUES(1, 1);
-- tx2
INSERT INTO tbl(pk, val)VALUES(1, 1);
Профиль блокировок:
Тут мы уже видим RowExclusive не на одной из системных таблиц, а на нашей. А в пару к ней — и на том индексе, который вызвал проблемы [не]уникальности.
Пример состояния:
Одновременное изменение записи
Попробуем теперь одну и ту же запись в нашей таблице UPDATE'нуть:
-- tx1
BEGIN;
UPDATE tbl SET val = val + 1 WHERE pk = 1;
-- tx2
UPDATE tbl SET val = val + 1 WHERE pk = 1;
Профиль блокировок:
Понятно, что оба запроса наложили RowExclusive на таблицу, раз хотят туда что-то записать. Но важнее, что что заблокированный запрос ждет tuple-блокировку на этой же таблице. Причем последние два числа в ID объекта блокировки (или поля page, tuple в исходной pg_locks) позволяют нам узнать, что именно за запись мы тут ждем:
SELECT * FROM WHERE ctid = '(
,)';
Пример состояния:
Обслуживание таблицы
Мы тут очень много чего в таблице уже намусорили — давайте ее зачистим. Но запись-то пока в нее идет:
-- tx1
BEGIN;
INSERT INTO tbl(pk, val)VALUES(2, 2);
-- tx2
TRUNCATE TABLE tbl;
Профиль блокировок:
Собственно, мы видим, что заблокированный хочет AccessExclusive — самый «тяжелый» режим, но не может получить из-за RowExclusive. Смотрим на картинку с таблицей конфликтов — и понимаем, что это кто-то из {TRUNCATE, VACUUM FULL, ...} пересекся c {INSERT, UPDATE, DELETE}, даже не заглядывая в
Пример состояния:
Надеюсь, если теперь вы встретитесь с какой-то блокировкой, всегда сможете вычислить ее причину, даже если исходные запросы нигде в логи не попались.
You must be registered for see links
мы научились снимать состояние блокировок на сервере 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:
id]WHEN 'virtualxid' THEN
string_to_array(virtualxid::text, '/'):
id[]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', ''):
idWHEN '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
--
You must be registered for see links
, 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;
С помощью него мы рассмотрим некоторые примеры ситуаций, которые могут вам встретиться в реальной работе:
-
You must be registered for see links
-
You must be registered for see links
-
You must be registered for see links
-
You must be registered for see links
-
You must be registered for see links
-
You must be registered for see links
Но сначала посмотрим, какие вообще ресурсы могут вызывать конфликт, и чем они идентифицируются в
You must be registered for see links
:locktype | описание | ID ресурса |
relation | отношение (таблица) | (relation) |
extend |
You must be registered for see links
) | (relation) |
page | страница (блок данных таблицы/индекса) | (relation, page) |
tuple | кортеж (запись таблицы/индекса) | (relation, page, tuple) |
transactionid | идентификатор транзакции | (transactionid) |
virtualxid | виртуальный идентификатор | (virtualxid) |
object | некоторый объект | (classid, objid, objsubid) |
userlock | пользовательская блокировка | (classid) |
advisory | рекомендательная блокировка | (classid, objid, objsubid) |
You must be registered for see links
как на матрицу мешающих друг другу запросов:
А теперь посмотрим на реальных примерах.
Создание одноименных таблиц
-- 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 |
You must be registered for see links
. Отличаются друг от друга записи объектов разных типов значением поля 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 не может пройти, и мы видим вместо имени только лишь число.
Также помимо записи о самой таблице в метаданные (в
You must be registered for see links
) добавляется табличный тип, благодаря которому мы можем кастовать что-то прямо в формат записи таблицы: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 индекса (число) |
Заметим, что Share между собой не конфликтуют, поэтому попробуем создать сразу два разных индекса на одной таблице:
-- tx1
BEGIN;
CREATE UNIQUE INDEX idx1 ON tbl(pk);
-- tx2
CREATE UNIQUE INDEX idx2 ON tbl(pk); -- работает, и никаких блокировок!
Пример состояния:
Самоблокировка (CONCURRENTLY vs транзакция)
Теперь давайте попробуем создать еще один индекс — но не просто так, а CONCURRENTLY, чтобы никому не блокировать доступ к таблице.
Ну а если мы хотим сделать это из PL-кода, где CONCURRENTLY-запросы запускать нельзя, как и в любой транзакции?.. Конечно же, воспользуемся
You must be registered for see links
для подключения к этой же БД!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-индексу
Перейдем от работы с таблицами к работе с отдельными записями и попробуем вставить в разных транзакциях записи с одинаковым значением уникального ключа.
-- 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 |
Пример состояния:
Одновременное изменение записи
Попробуем теперь одну и ту же запись в нашей таблице 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 |
SELECT * FROM WHERE ctid = '(
,)';
Пример состояния:
Обслуживание таблицы
Мы тут очень много чего в таблице уже намусорили — давайте ее зачистим. Но запись-то пока в нее идет:
-- 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 |
You must be registered for see links
.Пример состояния:
Надеюсь, если теперь вы встретитесь с какой-то блокировкой, всегда сможете вычислить ее причину, даже если исходные запросы нигде в логи не попались.