Блокировки в PostgreSQL
Чтобы рассказать о тонких моментах pg_repack, мне понадобится немного углубиться в
тему блокировок в PostgreSQL. Конечно, лучше всего начать читать про них в официальной документации. Для этой статьи достаточно понимать, что
эксклюзивная блокировка (ACCESS EXCLUSIVE LOCK) препятствует выполнению всех операций, включая SELECT, и она нужна для операции
ALTER TABLE.
Ситуация, с которой периодически приходится сталкиваться в «бою», можно приблизительно повторить следующим образом.
Вначале получим блокировку любого уровня на какую-то таблицу. В данном примере я заблокирую ее с помощью SELECT FOR UPDATE, потому что сделать длинный SELECT в домашних условиях достаточно сложно. Я изменил pid’ы процессов, чтобы они
легче воспринимались визуально.
mc=# select pg_backend_pid();
pg_backend_pid
----------------
2
mc=# begin;
BEGIN
mc=# select * from a where id = 6637 for update;
id | first_name
------+------------
6637 | Р
Во второй сессии мы добавим новую колонку к той же таблице.
mc=# select pg_backend_pid();
pg_backend_pid
----------------
3
mc=# alter table a add c int;
Как мы видим, ALTER TABLE «завис», то есть ждет завершение транзакции, которую начал процесс с pid 2.
Что же, запустим еще одну сессию, в которой попробуем выбрать что-то из БД.
mc=# select pg_backend_pid();
pg_backend_pid
----------------
4
mc=# select * from a limit 10;
# здесь запрос тоже висит
Этот запрос тоже повис. А теперь представьте себе на мгновение, что таблица a — это одна из самых важных таблиц в вашем web-проекте, и все запросы к ней, даже на чтение, теперь блокируются, это будет означать, что сайт лег.
Ситуация понятная, pid 3 ждет pid 2, а pid 4 ждет pid 3. Посмотрим более внимательно, как это выглядит, с помощью системного представления PostgreSQL pg_locks.
mc=# SELECT c.relname, l.mode, l.granted, l.pid
FROM pg_locks as l JOIN pg_class as c on c.oid = l.relation;
relname | mode | granted | pid
----------------------------+---------------------+---------+-------
a | AccessShareLock | f | 4
a | AccessExclusiveLock | f | 3
a | RowShareLock | t | 2
Добавлю еще, как я и говорил выше, pid 2 может просто делать выборку (SELECT), которая длится достаточно долго. Дважды
за последние пару месяцев я наблюдал такую ситуацию в «бою». В первый раз это была финальная стадия работы pg_repack,
которая пришлась на тот момент, когда делался обычный sql dump с помощью утилиты pg_dump. В финальной стадии
pg_repack переименовывает новые объекты в старые, а для этого выполняет ALTER TABLE new RENAME TO old.
Второй случай встретить можно даже в обычной жизни — выполнить rails миграцию с командой ALTER TABLE tablename ADD COLUMN
в тот момент, когда на БД выполнялся достаточно длинный аналитический отчет.
Что можно делать, чтобы избежать такой ситуации, зависит от специфики конкретного проекта. В первом случае мы убрали
pg_dump, потому что уже использовался непрерывный backup. Второй случай показателен,
потому что это известный факт, что для аналитики стоит иметь отдельный сервер БД по многим другим причинам, помимо вероятности
возникновения подобной ситуации. Но это уже совсем другая история.
В подобных ситуациях всегда спасает следующий запрос из системной таблицы pg_stat_activity, который возвращает все выполняющиеся в данный момент операции, отсортированные по времени выполнения. Внизу вывода обычно находятся те из них, которые нас больше всего интересуют.
SELECT now() - query_start, procpid, current_query
FROM pg_stat_activity
WHERE current_query <> '<IDLE>' order by 1;
?column? | pid | query
------------------------+-------+--------------------------------------------------------------------------------------------------
00:00:00 | 56167 | select now() - query_start, pid, query from pg_stat_activity where query <> '<IDLE>' order by 1;
01:11:43.353052 | 58745 | SELECT "groups".* FROM "groups" ORDER BY id DESC LIMIT 10
01:35:15.182291 | 58762 | COMMIT
7 days 09:59:50.269023 | 61889 | select * from words where word like 'а';