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

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


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Режимы блокировок таблиц​

Задание 1: Эксперименты с разными режимами LOCK TABLE

⏱️ Примерное время: 15-30 минут

Проверь конфликты и совместимость разных режимов LOCK TABLE в двух сессиях.


Требования:

  • открой две сессии psql (или два окна)
  • в сессии 1 выполни LOCK TABLE в одном из режимов
  • в сессии 2 попробуй разные операции (SELECT, UPDATE, INSERT, ALTER, VACUUM и т.д.)
  • запиши, какие операции блокируются, а какие проходят
  • объясни результат, опираясь на матрицу совместимости блокировок

Пример:

-- Сессия 1
BEGIN;
LOCK TABLE accounts IN SHARE MODE;
-- Сессия 2
UPDATE accounts SET balance = 0 WHERE id = 1;
-- Результат: блокируется (SHARE конфликтует с ROW EXCLUSIVE)

Задание 2 — Блокировки строк + FOR UPDATE / SHARE / SKIP LOCKED​

Задание 2: Эксперименты с блокировками строк

⏱️ Примерное время: 25-45 минут

Проверь поведение FOR UPDATE, FOR SHARE, NOWAIT, SKIP LOCKED.


Требования:

  • создай тестовую таблицу (accounts, tasks, job_queue и т.д.)
  • в двух сессиях проведи эксперименты
  • покажи, что блокируется, а что нет
  • реализуй простой worker с SKIP LOCKED для очереди задач
  • попробуй NOWAIT и обработай ошибку lock_not_available

Пример worker’а:

-- Worker
BEGIN;
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY priority DESC, created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- Если вернулась строка → обработать, обновить status
COMMIT;

Задание 3 — Deadlocks: воспроизведение и предотвращение​

Задание 3: Воспроизведение и устранение deadlocks

⏱️ Примерное время: 30-50 минут

Создай классический deadlock и устрани его.


Требования:

  • воспроизведи deadlock в двух сессиях (с pg_sleep для синхронизации)
  • запиши сообщение об ошибке из лога / клиента
  • исправь двумя способами:
    1. упорядочивание блокировки (всегда меньший ID первым)
    2. использование advisory lock или retry-логики
  • реализуй безопасную функцию (например, transfer_money_safe)

Пример:

-- Плохо (deadlock возможен)
BEGIN; UPDATE accounts SET ... WHERE id = 1; pg_sleep(2); UPDATE ... WHERE id = 2;
BEGIN; UPDATE accounts SET ... WHERE id = 2; pg_sleep(2); UPDATE ... WHERE id = 1;

Задание 4 — Advisory Locks и распределённые задачи​

Задание 4: Advisory Locks для координации

⏱️ Примерное время: 30-50 минут

Реализуй сценарии с advisory locks.


Требования:

  • реализуй функцию с pg_try_advisory_lock / pg_advisory_xact_lock
  • добавь обработку случая, когда блокировка не получена
  • протестируй в двух сессиях (одна получает lock, вторая — нет)
  • (бонус) реализуй retry-логику или очередь ожидания

Пример:

IF NOT pg_try_advisory_lock(12345) THEN
    RAISE NOTICE 'Задача уже выполняется';
    RETURN;
END IF;
-- Критическая секция
PERFORM pg_advisory_unlock(12345);

Задание 5 — Мониторинг и диагностика блокировок (итоговое)​

Задание 5: Полный мониторинг и диагностика блокировок

⏱️ Примерное время: 60-120 минут

Создай инструменты для обнаружения и анализа проблем с блокировками.


Требования:

  • создай 2–3 представления / функции для мониторинга:
    • текущие блокировки + кто кого ждёт
    • долгие ожидания (> 5–30 сек)
    • deadlocks за последние сутки
  • реализуй функцию kill_long_blocking_queries(max_duration)
  • протестируй под нагрузкой: запусти 5–10 параллельных сессий с UPDATE/SELECT FOR UPDATE
  • воспроизведи deadlock → поймай его в логе / таблице
  • предложи 5–8 рекомендаций по устранению проблем (упорядочивание, SKIP LOCKED, advisory, короткие транзакции и т.д.)

Это задание — полноценный кейс для роли DBA / Senior Backend / Performance Engineer.

подсказка

Блокировки — это не враг, а инструмент.
Главные правила:

  1. Короткие транзакции — лучший друг
  2. Одинаковый порядок доступа к ресурсам — спасает от deadlocks
  3. SKIP LOCKED + NOWAIT — идеально для очередей и конкурентных задач
  4. Advisory Locks — когда нужно координировать приложение
  5. Мониторинг — pg_stat_activity + pg_locks — твои глаза в проде