Блокирующие запросы.
Это продолжение статьи Какие бывают блокировки в PostgreSQL на простом примере. Часть 1
И вот наступило время, когда Боб тоже хочет поиграть машинкой (обычное дело для детей). Боб делает следующий запрос:
[Bob] sandbox=# UPDATE toys SET usage = usage + 1 WHERE id = 1;
Но его желание не может быть сейчас исполнено, придется подождать пока Алиса завершит свою транзакцию. Снова посмотрим в таблицу блокировок:
Теперь у Боба тоже есть собственный transactionid и он настойчиво просит выдать ему ShareLock на transactionid Алисы — «Эй, я хочу поиграть с этой машинкой». Две блокировки конфликтуют друг c другом, а так как Алиса первая взяла машинку, то запрос Боба не удовлетворён (is not granted). Он будет находиться в таком состоянии до тех пор, пока Алиса не снимет ExclusiveLock, завершив (commit) свою транзакцию.
pg_stats_activity
pg_stat_activity ещё одно занимательное представление (view) из pg_catalog. С его помощью можно увидеть запросы, выполняющиеся в данный момент:
select query, state, pid
from pg_stat_activity where datname = 'sandbox' and not (state = 'idle' or pid = pg_backend_pid());
Запрос Алисы ждёт подтверждения (фиксации) транзакции (idle in transaction), в то время как запрос Боба активен и тоже ждёт (is waiting). Кто кого заблокировал? Посмотрим. Объединим два запроса в один:
select bda.pid as blocked_pid, bda.query as blocked_query,
bga.pid as blocking_pid, bga.query as blocking_query
from pg_catalog.pg_locks bdl
join pg_stat_activity bda on bda.pid = bdl.pid
join pg_catalog.pg_locks bgl on bgl.pid != bdl.pid and bgl.transactionid = bdl.transactionid
join pg_stat_activity bga on bga.pid = bgl.pid
where not bdl.granted and bga.datname = 'sandbox';
Всё-таки Алиса передумала играть с машинкой и решила откатить свою транзакцию, блокировка ExclusiveLock была снята и Боб получил долгожданный ShareLock. После этого он может без проблем зафиксировать свою транзакцию и обновить запись в таблице. То же самое произошло бы если Алиса зафиксировала свою транзакцию, т.е. поиграла бы с машинкой и вернула бы её.
[Alice] sandbox=# ROLLBACK; ROLLBACK
[Bob] sandbox=# COMMIT; COMMIT [Bob] sandbox=# SELECT * FROM toys;
Естественно, если бы Алиса и Боб решили поиграть разными игрушками, то столкновений интересов между ними не возникло бы вообще.
Явные блокировки.
Обычная ситуация для детей, когда кто-то хочет забрать все игрушки, хотя играть во все сразу у него всё-равно не получится. Алиса берёт себе всё:
[Alice] sandbox=# BEGIN; BEGIN [Alice] sandbox=# LOCK TABLE toys IN ACCESS EXCLUSIVE MODE; LOCK TABLE
Хотя Алиса ничего не делает ни с одной игрушкой, Боб всё равно должен ждать. Он все-таки пробует взять:
[Bob] sandbox=# BEGIN; UPDATE toys SET usage = usage+1 WHERE id = 2; BEGIN
Таблица блокировок теперь выглядит так:
Алиса удерживает AccessExclusiveLock без изменения состояния базы данных, поэтому у неё нет transactionid. Боб его тоже не получил, так как у него нет RowExclusiveLock на таблицу toys. В этой ситуации, запрос для отображения блокировок который мы использовали ранее, нам не поможет, т. к. он использует объединение по transactionid и может показаться что всё хорошо и никто ни кому не мешает.
Но следующий запрос показывает обратное:
select pid, query, now() - query_start as waiting_duration
from pg_catalog.pg_stat_activity
where datname = 'sandbox' and waiting;
Столбец waiting_duration рассчитывается как разница между текущим временем now() и временем старта query_start. Благодаря этому, видно сколько времени запрос уже висит. Сделав объединение (join) по столбцам relation и locktype, мы снова видим кто кого блокирует
select bgl.relation::regclass, bda.pid as blocked_pid, bda.query as blocked_query, bdl.mode as blocked_mode, bga.pid as blocking_pid, bga.query as blocking_query, bgl.mode as blocking_mode
from pg_catalog.pg_locks bdl
join pg_stat_activity bda on bda.pid = bdl.pid
join pg_catalog.pg_locks bgl on bdl.pid != bgl.pid and bgl.relation = bdl.relation and bgl.locktype = bdl.locktype
join pg_stat_activity bga on bga.pid = bgl.pid
where not bdl.granted and bga.datname = 'sandbox';
Алисе сказали, что не хорошо делать явную блокировку без веской на то причины. Она соглашается и фиксирует свою транзакцию без каких-либо изменений и теперь радостный Боб может взять игрушку.
[Alice] sandbox=# COMMIT; COMMIT
UPDATE 1 [Bob] sandbox=#
Транзакция Боба ещё открыта. Посмотрев в таблицу блокировок увидим:
Только после того как Боб получил долгожданный RowExclusiveLock, к его транзакции был добавлен transactionid. Боб доволен и фиксирует транзакцию:
[Bob] sandbox=# COMMIT; COMMIT
RowExclusiveLock
Алиса хочет видеть все игрушки и иметь возможность в любой момент взять любую, но пока не знает какую. А до тех пор никто не должен прикасался к ним. Но ставить явную блокировку ей запретили и она идёт другим путём.
[Alice] sandbox=# BEGIN; SELECT * FROM toys FOR UPDATE; BEGIN
А Боб как раз хочет взять лопатку и конечно не может этого сделать, опять он натыкается на блокировку и его транзакция подвисает:
[Bob] sandbox=# UPDATE toys SET usage = usage+1 WHERE id = 2;
Тем временем Ева видит такую таблицу:
Боб очень хочет получить лопатку (изменить состояние базы данных) и поэтому он получил transactionid равный 19310, но ему опять приходится ждать свой заветный ShareLock на транзакцию Алисы с номером 19309.
Объединяем таблицы блокировок и активности.
Теперь самое время объединить таблицу блокировок и таблицу активности так, чтобы всегда видеть кто кого заблокировал:
select
coalesce(bgl.relation::regclass::text, bgl.locktype) as locked_item, now() - bda.query_start as waiting_duration, bda.pid as blocked_pid, bda.query as blocked_query, bdl.mode as blocked_mode, bga.pid as blocking_pid, bga.query as blocking_query, bgl.mode as blocking_mode
from pg_catalog.pg_locks bdl
join pg_stat_activity bda on bda.pid = bdl.pid
join pg_catalog.pg_locks bgl on bgl.pid != bdl.pid and (bgl.transactionid = bdl.transactionid or bgl.relation = bdl.relation and bgl.locktype = bdl.locktype) join pg_stat_activity bga on bga.pid = bgl.pid and bga.datid = bda.datid
where not bdl.granted and bga.datname = current_database();
По столбцу waiting_duration удобно видеть сколько прошло времени блокирования запроса. Функция current_database(), используемая в условии, фильтрует блокировки и активности только текущей базы.
Более подробно о блокировках можно почитать в руководстве по явным блокировкам в постгресе.