Допустим, вы делаете что-то глупое, например, выполняете запрос, который зависает и не возвращается -- или открываете транзакцию, но не фиксируете ее. Подобные вещи могут вызвать блокировку таблицы базы данных в PostgreSQL. Вам нужно иметь возможность найти эти блокировки, процессы, которые их вызвали, и отключить их.
Обнаружение блокировки
Первое, что может дать вам подсказку о наличии блокировки на таблице, это то, что вы не можете сделать простые вещи, которые вы обычно можете сделать, например, удалить строку из таблицы:
| 1 2 3 4 | system=> delete from search_hit where id = 154193; ^CCancel request sent ERROR: canceling statement due to user request CONTEXT: while deleting tuple (22286,5) in relation "search_hit" |
Перечисление блокировок
Postgres содержит тонну отличной статистической информации в таблицах с метаданными о том, как работает сама система Postgres. Одной из таких таблиц является pg_locks, где мы можем объединить ее с pg_class, чтобы иметь возможность искать по имени таблицы. Так, с именем таблицы search_hit я могу сделать запрос:
| 1 2 3 4 5 | select pid from pg_locks l join pg_class t on l.relation = t.oid where t.relkind = 'r' and t.relname = 'search_hit'; |
И получите:
| 1 2 3 4 5 6 7 | pid ------- 11337 16389 16389 11929 (4 rows) |
И конечно, у меня есть несколько pids, или идентификаторов процессов. Это те процессы, которые создали блокировки на этой таблице. Вероятно, я делаю некоторые глупые вещи, о которых я говорил выше.
Сопоставление запросов с блокировками
Я даже могу увидеть запросы, которые вызвали эти блокировки, если посмотрю на таблицу pg_stat_activity, фильтруя по этим pids:
| 1 2 3 4 5 6 7 8 | select pid, state, usename, query, query_start from pg_stat_activity where pid in ( select pid from pg_locks l join pg_class t on l.relation = t.oid and t.relkind = 'r' where t.relname = 'search_hit' ); |
И получите что-то вроде этого:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | pid | state | usename | query | query_start -------+---------------------+-------------------+-------------------------------------------------------------------------------------------+------------------------------- 16389 | idle in transaction | appuser | +| 2018-12-12 16:30:32.933695+00 | | | select count(r.id) as repos_count +| | | | , count(case when r.time_search is null then 1 end) as repos_unsearched_count +| | | | , count(case when sh.contents is null then 1 end) as search_hit_no_contents_count+| | | | , s.time_complete +| | | | from search s +| | | | left join repo r on s.id = r.search_id +| | | | left join search_hit sh on s.id = sh.search_id +| | | | group by s.time_complete +| | | | , s.time_start +| | | | order by s.time_start desc +| | | | limit 1 +| | | | | 11929 | active | appuser | +| 2018-12-12 16:30:31.11427+00 | | | update search +| | | | set time_complete = now() +| | | | where id = $1 +| | | | | 11337 | active | appuser | +| 2018-12-12 16:31:01.764946+00 | | | update search +| | | | set time_complete = now() +| | | | where id = $1 +| | | | | 13098 | active | | autovacuum: VACUUM public.search_hit | 2018-12-12 18:32:36.714039+00 (4 rows) |
Когда у меня есть запрос, я могу вернуться к своему коду, найти запрос и навсегда решить проблему.
Вы можете обнаружить, что ваша блокировка возникла из-за долго выполняющегося запроса, которому вы просто не хватает терпения дать завершиться (или практики, чтобы сделать его более производительным).
Но в этот момент может возникнуть процесс, который просто нужно убить.
Удаление блокировок
Есть несколько способов уничтожить процессы, вызывающие блокировки. Если вы выполняете запрос в интерактивном режиме, просто остановите его с помощью отмены запроса пользователем (например, используя ctrl-c из psql cli).
Для этих более назойливых процессов мы будем использовать pids, которые мы нашли в предыдущих запросах:
| 1 2 3 4 5 | system=> SELECT pg_cancel_backend(11929); pg_cancel_backend ------------------- t (1 row) |
Эта обратная связь, к сожалению, не указывает точно на успех или неудачу. Вместо этого, скорее всего, вам придется выполнить предыдущие запросы, чтобы определить, активен ли еще процесс.
Это был вежливый способ спросить (т.е. pg_cancel_backend). Более силовым методом является:
| 1 2 3 4 5 | system=> SELECT pg_terminate_backend(11929); pg_terminate_backend ---------------------- t (1 row) |
Проверка снятых блокировок
После отмены или прекращения действия существовавших блокировок вы должны иметь возможность запросить pg_locks, соединить с pg_class и создать фильтр для pids в pg_stat_activity, как показано выше, чтобы еще раз убедиться, что блокировки сняты.
А теперь та простая вещь, которую я хотел сделать:
| 1 2 | design_system=> delete from search_hit where id = 154193; DELETE 1 |
будет действительно работать. Ваши блокировки навсегда останутся в страхе перед вашей силой.