Перейти к основному содержимому

Блокировки и конкурентный доступ в PostgreSQL

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ
Разработчику Архитектору Инженеру

MVCC и блокировки​

PostgreSQL использует MVCC: читатели обычно не блокируют писателей и наоборот. Физические блокировки строк возникают при конфликте записи двух транзакций над одной версией строки.

Классическая теория (Shared / Exclusive) на уровне строк реализована через видимость версий; явные блокировки задаются командами SELECT … FOR UPDATE и LOCK TABLE.

Блокировки таблиц (уровни)​

УровеньТипичная операцияБлокирует SELECT?Блокирует UPDATE?
ACCESS SHARESELECTНетНет
ROW SHARESELECT FOR UPDATE/SHAREНетНет
ROW EXCLUSIVEINSERT, UPDATE, DELETEНетДругие ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVEVACUUM, ANALYZEНетДа
ACCESS EXCLUSIVEDROP TABLE, ALTER TABLEДаДа

Явные блокировки строк​

BEGIN;

-- Эксклюзивная блокировка выбранных строк до COMMIT
SELECT * FROM inventory
WHERE item_id = 1
FOR UPDATE;

UPDATE inventory SET quantity = quantity - 1 WHERE item_id = 1;

COMMIT;
ВариантПоведение
FOR SHAREРазделяемая блокировка: чтение FOR SHARE разрешено, запись — нет
FOR UPDATE NOWAITОшибка сразу, если строка занята
FOR UPDATE SKIP LOCKEDПропуск занятых строк (очереди задач)

Пример очереди заказов:

SELECT order_id
FROM orders
WHERE status = 'new'
ORDER BY order_date
FOR UPDATE SKIP LOCKED
LIMIT 1;

Блокировки действуют до COMMIT или ROLLBACK.

Взаимоблокировки (deadlock)​

Цикл: транзакция A ждёт ресурс B, B ждёт A. PostgreSQL обнаруживает цикл процессом deadlock detector (интервал deadlock_timeout, по умолчанию 1 с) и откатывает одну транзакцию.

Профилактика:

  • блокировать таблицы/строки в одинаковом порядке;
  • держать транзакции короткими;
  • использовать NOWAIT / SKIP LOCKED в очередях;
  • не повышать изоляцию без необходимости.

Диагностика​

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
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.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

SHOW deadlock_timeout;

Advisory-блокировки​

Логические замки приложения без привязки к строке таблицы:

SELECT pg_advisory_lock(42);
-- критическая секция
SELECT pg_advisory_unlock(42);

Контрольные вопросы​

  1. Почему обычный SELECT не блокирует UPDATE в PostgreSQL?
  2. Чем FOR UPDATE отличается от FOR SHARE?
  3. Для чего нужен SKIP LOCKED?
  4. Как СУБД разрешает deadlock?

См. также​

См. также

Другие статьи этого же раздела в боковом меню (как на странице «О разделе»).

Ещё 24 статей в разделе

Освоение главы0%