Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Oracle is so Oracle
Member

Откуда:
Сообщений: 10
Доброго времени суток, уважаемые форумчане. Вопрос такой.

Старый постгрес 9.6. Периодические возникают блокировки, хотелось бы отследить, какая сессия какой объект блокирует.

По форуму поискал - аналогичной темы не нашёл.

Находил запросы, которые показывают информацию, аналогичную pgAdmin (время, сессия, блокирующая сессия, запрос), но хотелось бы узнать, какой именно объект заблокирован.

Видимо, надо смотреть в pg_locks/relations. Но, может кто имеет готовый запрос, аккуратно показывающий, какая сессия какой объект блокирует?

Спасибо.
1 мар 21, 11:04    [22287771]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Павел Лузанов
Member

Откуда:
Сообщений: 786
Oracle is so Oracle,

Хороший запрос про дерево блокировок: запрос.

Сообщение было отредактировано: 1 мар 21, 16:13
1 мар 21, 16:19    [22287941]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Павел Лузанов
Member

Откуда:
Сообщений: 786
Перечитал вопрос и понял, что автору нужен другой запрос - какие объекты блокируются.

Это не отменяет того, что Виктор Егоров написал хороший запрос показывающий какие сеансы кого блокируют. Мне кажется что именно в таком виде удобно отслеживать периодически возникаемые блокировки.
1 мар 21, 22:59    [22288124]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
vyegorov
Member

Откуда: Баньоло-ин-Пьяно
Сообщений: 1232
Павел Лузанов,

Там можно на 26-й строчке убрать комментарий и будет видно объект блокировки, в виде ROW(pg_locks.*).
2 мар 21, 00:53    [22288137]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Oracle is so Oracle
Member

Откуда:
Сообщений: 10
Павел Лузанов, спасибо за ответ.
Но, или я что-то не понял, или этот запрос показывает не совсем то, что мне нужно.

В первой сессии я создаю таблицу lock1:

create table lock1 (c1 text, c2 numeric)
insert into lock1 values ('a', '1');
insert into lock1 values ('b', '2');


И беру строку на update:
update lock1 set c1='bbb' where c2='2'


Далее, из второй сессии беру эту же строку на изменение (возникает блокировка):
update lock1 set c1='ccc' where c2='2'

И из третьей сессии запускаю Ваш запрос, с НЕзакомментированной строкой w.obj wait_on_object.


Мой результат выглядит как-то так:

ts_age change_age datname usename client_addr wait_on_object pid state lvl blocked query
00:02:17 00:02:17 ИМЯ_БАЗЫ ИМЯ_ПОЛЬЗОВАТЕЛЯ IP PID idletx 0 1 update lock1 set c1='bbb' where c2='2'
00:01:40 00:01:40 ИМЯ_БАЗЫ ИМЯ_ПОЛЬЗОВАТЕЛЯ IP (transactionid.....137701111...) PID active 1 0 update lock1 set c1='ccc' where c2='2'



Я рассчитывал, что столбец wait_on_object покажет мне мою таблицу lock1.
А как-то можно получить имя таблицы или связать эту циферку 137701111 с именем таблицы?

Спасибо.
4 мар 21, 13:16    [22289558]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1280
Oracle is so Oracle,

так а мы и не ждём лок на таблицу. Ждём именно завершение транзакции - чтобы понять, если она commit - то берём новую версию строки, если rollback - то берём версию будто той транзакции не было вовсе.
4 мар 21, 13:21    [22289562]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Oracle is so Oracle
Member

Откуда:
Сообщений: 10
Запрос select * from pg_locks where transactionid='137701111' выдаёт мне:

locktype database relation page tuple virtualxid transationid classid objid objsubid virtualtransatioin pid mode granted fastpath
transactionid 137701111 5/123 14525 ShareLock f f
transactionid 137701111 4/227 14514 ExclusiveLock t f


И опять никакой информации об имени таблицы.
4 мар 21, 13:22    [22289563]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Oracle is so Oracle
Member

Откуда:
Сообщений: 10
Melkij,спасибо за ответ.
Ну меня интересует именно на чем висит моя сессия. В первом сообщении я попытался объяснить, что меня интересует именно имя таблицы, которая блокируется.
4 мар 21, 13:24    [22289564]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Павел Лузанов
Member

Откуда:
Сообщений: 786
Oracle is so Oracle,

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

В примере вторая сессия ждет окончания транзакции в первой, как и написал Melkij. А первая сессия в общем случае могла работать (и получить блокировку) с разными объектами. В запросе есть столбец pid, вот по значению этого столбца из блокирующей строки запроса можете поискать в pg_locks все объекты с которыми она работает:
select array_agg(relation::regclass) from pg_locks where pid = номер;

Этот запрос наверняка покажем вам lock1
4 мар 21, 13:46    [22289583]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
vyegorov
Member

Откуда: Баньоло-ин-Пьяно
Сообщений: 1232
Oracle is so Oracle,

Сессия, которая что-то сделала и осталась висеть в `idle in transaction` никого не будет блокировать (сюрприз), пока этот кто-то не придёт, не захочет обратиться к объекту блокировки.
Просто смотреть список всего в pg_locks, что “придерживает” такая сессия, смысла не имеет.
4 мар 21, 19:37    [22289893]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
vyegorov
Member

Откуда: Баньоло-ин-Пьяно
Сообщений: 1232
Oracle is so Oracle,

Когда писал скрипт, пользовался такой комбинацией (во всех сессия явно открыть транзакцию и не завершать!):

CREATE TABLE a(a_id int CONSTRAINT p_a PRIMARY KEY, a_name text UNIQUE, a_created timestamptz);
CREATE TABLE b(b_id int CONSTRAINT p_b PRIMARY KEY, b_name text UNIQUE, a_id int REFERENCES a);
INSERT INTO a VALUES (1, 'One', (now()-INTERVAL '3 days')), (2, 'Two', now());
INSERT INTO b VALUES (1, 'Eins', 1), (2,'Zwei',2);

-- all sessions within transaction
S1: INSERT INTO a VALUES (3, 'Three', now());
S1: UPDATE b SET b_name='A One' WHERE b_id=1;

S2: INSERT INTO a VALUES (3, 'Drei', now()); -- transactionid

S3: UPDATE b SET b_name='The One' WHERE b_id=1; -- transactionid

S4: DELETE FROM a WHERE a_id=1; -- tuple

S5: ALTER TABLE a DROP CONSTRAINT p_a; -- relation

S6: DROP TABLE b; -- relation

Потом открываете седьмую сессию и смотрите на вывод скрипта.
4 мар 21, 19:40    [22289895]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки. Какой объект(таблицу) держит какая сессия?  [new]
Oracle is so Oracle
Member

Откуда:
Сообщений: 10
UP.

Спасибо всем, кто ответил. Но задача не решена. Давайте зайдем с другой стороны.

В Oracle, например, если мы создадим подобную блокировку:

create table test1 (c1 varchar(10), c2 number);
insert into test1 values ('a', '1');
insert into test1 values ('b', '2');

session 1: update test1 set c1='new' where c2=2;

session2: update test1 set c1='new2';


На объект, в котором возникла блокировка (только одной строки) можно посмотреть разными способами. Например запросом вида:

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.state,
   b.blocking_session,
   b.event,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;


Который покажет что-то такое:
owner object_name sid serial# status state blocking_session event osuser machine
MY_USER TEST1 139 4440 INACTIVE WAITING SQL*Net message from client MY_OSUSER MY_MACHINE
MY_USER TEST1 118 9899 ACTIVE WAITING 139 enq: TX - row lock contention MY_OSUSER MY_MACHINE

и сразу всё понятно. Сессия с sid`ом 118 ожидает сессию с sid`ом 139. Объект блокировки - таблица MY_USER.TEST1. Тип блокировки - блокировка строки. Проблема найдена, решение за нами. Можем убить сессию, можем пойти посмотреть на таблицу и т.д. и т.п.
Подобную же информацию можно получить и в Enterprise Manager`е.


Возвращаемся к нашим слонам, к Postgres`у.

В PgAdmin`е, OmniDB, EMS Studio ничего подобного не обнаружил. Пишут, какая сессия заблокирована, про объект ни слова.

Запрос, предложенный Павлом Лузановым

select array_agg(relation::regclass) from pg_locks where pid = номер;


Покажет ВСЕ блокировки сессии/транзакции с соответствующим pid`ом. И да, там будет перечень объектов. Но мне нужен объект, на котором эти сессии застряли (как в примере с Oracle`овым запросом). Мне не нужны ВСЕ блокировки, которая использует сессия/транзакция (в моей задаче, кстати, заблокированная и блокирующая сессии имеют > 600 блокировок).

Я понимаю, что механизм блокировок немного разный, и что в случае с Oracle`ом исходная строка после незакоммиченного апдейта лежит в undo, а в случае с Postgres`ом - в той же таблице создаётся новая версия строки. Повторный поиск в интернете ничего полезного не выдал. Единственная интересная инфа - вот тут https://stackoverflow.com/questions/62431485/when-and-how-does-postgres-use-transactionid-locks в частности написано, что в Postgres блокировки строк не хранятся в общей памяти постоянно, а в самой строке таблицы. ТАБЛИЦЫ. Но, тем не менее, как же мне найти ОБЪЕКТ Postgres`а, в котором создаётся эта строка, и, соответственно, на котором встали в ожидании транзакции? Хочу объект. По аналогии с Oracle`овым запросом.

Я надеюсь, достаточно ясно описал ситуацию. Знает ли кто-нибудь решение проблемы? Пожалуйста, откликнетесь.

P.S. Неужели, никто не встречался с подобным?
22 мар 21, 15:28    [22298279]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить