Как выявлять и устранять взаимоблокировки и конкуренцию за блокировки в PostgreSQL

Взаимоблокировки и конкуренция за блокировки в PostgreSQL обычно проявляются как внезапное зависание приложения. Запросы накапливаются, рабочие процессы находятся в состоянии active или idle in transaction, а в самой базе данных может оставаться много свободного процессорного времени. Проблема не в нехватке ресурсов. Один сеанс ожидает, пока другой сеанс освободит блокировку, и иногда за ним выстраивается целая очередь.

Самый быстрый способ справиться с этим — разделить два случая. Конкуренция за блокировки означает, что сеанс ожидает, но, возможно, в конечном итоге продолжит работу. Взаимоблокировка означает, что два или более сеансов ожидают друг друга в цикле, поэтому PostgreSQL приходится отменять одну транзакцию. Вы отлаживаете оба случая с помощью одних и тех же базовых инструментов, но исправление часто бывает разным.

Основы блокировок в PostgreSQL

PostgreSQL использует блокировки для защиты таблиц, строк, транзакций и других внутренних объектов, пока одновременно работают много сеансов. Он также использует MVCC, поэтому обычные операции чтения и записи часто не блокируют друг друга. Именно поэтому PostgreSQL хорошо справляется с высокой степенью параллелизма, но это также может сбивать с толку при возникновении проблем с блокировками: проблема обычно заключается в конкретном шаблоне операторов, а не в «слишком большом количестве пользователей» в целом.

Типы блокировок

PostgreSQL использует различные уровни блокировок, каждый из которых обеспечивает разную степень защиты. Понимание этого является ключом к диагностике проблем:

AccessShareLock: Используется обычным SELECT. Конфликтует в основном с ACCESS EXCLUSIVE, поэтому множество операций чтения могут выполняться во время записи.
RowExclusiveLock: Обычен для INSERT, UPDATE и DELETE для таблицы. Название легко неправильно истолковать; это не означает, что каждая строка в таблице заблокирована исключительно.
ShareUpdateExclusiveLock: Используется такими операциями, как VACUUM без FULL, ANALYZE и некоторыми операциями с индексами. Разрешает обычные чтение и запись, но конфликтует с несколькими операциями обслуживания.
ShareLock / ShareRowExclusiveLock / ExclusiveLock: Более сильные режимы блокировки на уровне таблицы, используемые определенными операциями DDL и связанными с ограничениями.
AccessExclusiveLock: Самая ограничительная блокировка таблицы. ALTER TABLE, DROP TABLE, TRUNCATE и VACUUM FULL могут использовать этот тип блокировки. Он блокирует как обычные чтения, так и записи.

Блокировки на уровне строк отделены от режимов блокировки на уровне таблицы. UPDATE использует блокировку на уровне таблицы RowExclusiveLock и блокировки строк для изменяемых строк. Когда говорят «эта строка заблокирована», обычно имеют в виду, что другая транзакция изменила или выбрала эту строку FOR UPDATE и еще не зафиксировалась.

Режимы блокировок

Режимы блокировок указывают тип доступа, необходимый транзакции. Они часто представлены такими именами, как RowExclusiveLock, ShareLock, ExclusiveLock и т.д.

Выявление конкуренции за блокировки и блокирующих сеансов

Конкуренция за блокировки возникает, когда несколько транзакций ожидают блокировок, удерживаемых другими транзакциями. Это может значительно замедлить ваше приложение. Системное представление pg_locks является вашим основным инструментом для диагностики этих проблем.

Использование `pg_locks`

Представление pg_locks предоставляет информацию обо всех активных блокировках в системе базы данных. Оно бесценно для понимания того, какие сеансы удерживают блокировки, а какие ожидают.

Вот типичный запрос для выявления блокирующих сеансов:

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    blocking_activity.query AS blocking_statement
FROM
    pg_catalog.pg_locks blocked_locks
JOIN
    pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN
    pg_catalog.pg_locks blocking_locks
ON
    blocking_locks.locktype = blocked_locks.locktype AND
    blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND
    blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND
    blocking_locks.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

Пояснение к запросу:

Мы дважды присоединяем pg_locks к pg_stat_activity: один раз для заблокированного процесса и один раз для блокирующего процесса.
Предложение WHERE NOT blocked_locks.granted фильтрует блокировки, которые в данный момент ожидаются.
Условие blocking_locks.pid != blocked_locks.pid гарантирует, что мы не сообщаем о блокировке сеансом самого себя.
Условия соединения в pg_locks сопоставляют блокировки одного и того же ресурса.

Интерпретация вывода

blocked_pid / blocking_pid: Идентификаторы процессов (PID) вовлеченных сеансов.
blocked_user / blocking_user: Пользователи, связанные с этими PID.
blocked_statement / blocking_statement: SQL-запросы, которые в данный момент выполняются или ожидают.
blocked_mode / blocking_mode: Запрашиваемые и удерживаемые режимы блокировок.

Если этот запрос возвращает строки, значит, у вас есть конкуренция за блокировки. blocking_pid удерживает блокировку, которую ожидает blocked_pid.

Понимание и устранение взаимоблокировок

Взаимоблокировка возникает, когда две или более транзакций ожидают блокировку, удерживаемую другой транзакцией в цикле, создавая круговую зависимость, которую ни одна из них не может разрешить самостоятельно. PostgreSQL обнаруживает взаимоблокировки и автоматически разрешает их, прерывая одну из транзакций, обычно ту, которая вызвала взаимоблокировку и выполнила наименьший объем работы.

Типичные сценарии взаимоблокировок

Две транзакции обновляют разные строки в разных таблицах в обратном порядке:
- Транзакция A: Обновляет строку X в Таблице 1, затем пытается обновить строку Y в Таблице 2.
- Транзакция B: Обновляет строку Y в Таблице 2, затем пытается обновить строку X в Таблице 1. Если транзакция A блокирует строку X, а транзакция B блокирует строку Y, они взаимоблокируются, когда попытаются получить блокировку, удерживаемую другой.
UPDATE с последующим SELECT ... FOR UPDATE:
- Транзакция A: Обновляет строку.
- Транзакция B: Выполняет SELECT ... FOR UPDATE для той же строки. Если UPDATE все еще удерживает эксклюзивную блокировку строки, когда SELECT FOR UPDATE пытается получить разделяемую блокировку, и существуют другие зависимости, может возникнуть взаимоблокировка.

Обнаружение взаимоблокировок

PostgreSQL записывает информацию о взаимоблокировках в свой серверный журнал. Вы обычно увидите сообщения, подобные этим:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

PostgreSQL автоматически выбирает одну транзакцию для прерывания, чтобы остальные могли продолжить работу. Не полагайтесь на pg_stat_activity после факта для полной картины; к тому времени, когда вы посмотрите, отмененный оператор может исчезнуть. Серверный журнал обычно является лучшей записью цикла.

Устранение взаимоблокировок

Когда обнаружена взаимоблокировка и PostgreSQL разрешает ее, прерывая транзакцию:

Определите жертву: Проверьте журналы PostgreSQL на наличие сообщения deadlock detected. В нем будет указано, какой процесс был прерван.
Повторите прерванную транзакцию: Приложение, получившее SQLSTATE 40P01 (deadlock_detected), должно повторить всю транзакцию, если операцию безопасно повторить. Повтор только последнего оператора может привести к несогласованному состоянию приложения.
Проанализируйте причину: Ключ к решению — предотвращение будущих взаимоблокировок. Это включает понимание того, почему произошла взаимоблокировка (как описано в типичных сценариях), и корректировку логики приложения или дизайна базы данных.

Методы предотвращения конкуренции за блокировки и взаимоблокировок

Профилактика всегда лучше лечения. Внедрение стратегий по минимизации конкуренции за блокировки и предотвращению ситуаций взаимоблокировок имеет решающее значение для высокопроизводительной базы данных PostgreSQL.

1. Согласованный порядок транзакций

Правило: Всегда обращайтесь к ресурсам (таблицам, строкам) и изменяйте их в одном и том же порядке во всех транзакциях. Если нескольким транзакциям необходимо обновить TableA и TableB, убедитесь, что они всегда обновляют TableA перед TableB, или наоборот, согласованным образом.
Пример: Если транзакции необходимо обновить записи в users и orders, всегда сначала выполняйте операции с users, затем с orders. Избегайте сценариев, когда одна транзакция обновляет users, затем orders, а другая — orders, затем users.

2. Минимизируйте продолжительность транзакций

Правило: Делайте транзакции как можно короче. Чем дольше открыта транзакция, тем больше блокировок она удерживает, увеличивая вероятность конкуренции.
Действие: Выполняйте только необходимые операции с базой данных в рамках транзакции. Выносите работу, не связанную с базой данных (например, внешние API-вызовы, сложные вычисления, не зависящие от состояния транзакции), за пределы транзакции.

3. Используйте соответствующие уровни изоляции

Правило: Понимайте и выбирайте правильный уровень изоляции транзакций. PostgreSQL предлагает:
- READ UNCOMMITTED (имитируется READ COMMITTED в PostgreSQL)
- READ COMMITTED (по умолчанию)
- REPEATABLE READ
- SERIALIZABLE
Действие: Уровень по умолчанию READ COMMITTED обеспечивает хорошую производительность, предотвращая «грязное» чтение. REPEATABLE READ и SERIALIZABLE обеспечивают более строгую согласованность, но могут привести к большему количеству ошибок serialization_failure (которые по сути являются взаимоблокировками для изоляции снимков) и потенциально к большей конкуренции за блокировки. Используйте их только при крайней необходимости.

4. Оптимизируйте запросы и индексы

Правило: Медленные запросы дольше удерживают блокировки. Убедитесь, что ваши запросы эффективны и хорошо проиндексированы.
Действие: Используйте EXPLAIN ANALYZE для выявления медленных запросов. Добавьте соответствующие индексы для ускорения извлечения данных, особенно для предложений WHERE и условий JOIN.

5. Используйте `SELECT ... FOR UPDATE` экономно

Правило: SELECT ... FOR UPDATE блокирует строки на время транзакции. Это мощный инструмент для предотвращения состояний гонки, но также может быть основным источником конкуренции.
Действие: Используйте его только тогда, когда вам действительно нужно заблокировать строки, чтобы предотвратить их изменение другими транзакциями до завершения вашей транзакции. Подумайте, не могут ли советующие блокировки лучше подойти для определенных сценариев.

6. Советующие блокировки

Правило: Для блокировок на уровне приложения или более сложных потребностей синхронизации, которые не отображаются напрямую на блокировки объектов базы данных, советующие блокировки PostgreSQL могут быть мощным инструментом.
Действие: Используйте такие функции, как pg_advisory_lock(), pg_advisory_lock_shared() и pg_advisory_unlock() для реализации пользовательских механизмов блокировки. Эти блокировки не обнаруживаются автоматически механизмом обнаружения взаимоблокировок, поэтому логика приложения должна управлять ими осторожно.

7. Пакетная обработка операций

Правило: Вместо выполнения множества отдельных операторов UPDATE или DELETE рассмотрите возможность их пакетной обработки в одном операторе или использования COPY для массовой загрузки/обновления, где это возможно.
Действие: Один оператор UPDATE может приобретать блокировки более эффективно, чем цикл отдельных UPDATE. Проанализируйте поведение блокировок ваших пакетных операций.

Практический порядок действий при инциденте

Когда инцидент активен, начинайте с ожидающих сеансов, а не с самой старой теории в вашей голове:

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

Если один блокирующий PID появляется снова и снова, проверьте его:

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

Фраза, на которую стоит обратить внимание, — idle in transaction. Этот сеанс не выполняет активно полезную работу с базой данных, но все еще может удерживать блокировки. Это часто происходит из-за кода приложения, который открывает транзакцию, выполняет запрос, вызывает внешний API и фиксирует транзакцию только после возврата API. По возможности вынесите внешний вызов за пределы транзакции.

Отменяйте осторожно. SELECT pg_cancel_backend(pid) просит текущий запрос остановиться. SELECT pg_terminate_backend(pid) убивает сеанс и откатывает его открытую транзакцию. В производственном инциденте завершение блокирующего процесса может быть правильным решением, но сначала запишите запрос и возраст транзакции, чтобы позже исправить путь в коде.

Еще одна полезная привычка после инцидента: сохраните блокирующий запрос, заблокированный запрос и границы транзакций из журналов приложения. Одного SQL-оператора часто недостаточно. Безобидный UPDATE users SET last_seen_at = now() может стать блокирующим, если он находится внутри транзакции, которая также ожидает платежный API. Исправления взаимоблокировок обычно находятся на уровне потока транзакций, а не внутри одного изолированного оператора.

Надежные исправления обычно просты: делайте транзакции короткими, обращайтесь к общим ресурсам в согласованном порядке, добавляйте индексы, которые не позволяют обновлениям сканировать слишком много, и настройте повторные попытки в приложении для корректной обработки 40P01 и ошибок сериализации. PostgreSQL может обнаружить взаимоблокировку, но не может перепроектировать шаблон транзакций за вас. Эта часть остается на рассмотрение приложения и схемы после того, как инцидент утихнет.