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

Подсказки:

• Рассмотрите использование уровней изоляции READ COMMITTED или REPEATABLE READ.
• Возможно, потребуется реализовать блокировку строк с помощью выражений SELECT FOR UPDATE.
• Подумайте о том, как обнаруживать и разрешать дедлоки, которые могут возникнуть.
• Помните, что PostgreSQL автоматически обнаруживает дедлоки и прерывает одну из транзакций с ошибкой.

Выше ожиданий:

• Понимание внутренних механизмов MVCC и как они влияют на видимость конкурирующих транзакций.
• Знание функций транзакционных рекомендательных блокировок (advisory locks) как альтернативного механизма блокировки.
• В курсе о несоответствиях при сериализации и о том, когда уровень изоляции SERIALIZABLE является уместным.

Понимание уровней изоляции транзакций

PostgreSQL использует MVCC (Multi-Version Concurrency Control) для обработки одновременного доступа к данным. Это позволяет читателям не блокировать писателей и наоборот.

Четыре стандартных уровня изоляции в PostgreSQL:

1. READ UNCOMMITTED (ведет себя как READ COMMITTED в PostgreSQL)
2. READ COMMITTED - Каждое оператор видит только данные, подтверждённые до его начала
3. REPEATABLE READ - Транзакция видит только данные, подтверждённые до её начала
4. SERIALIZABLE - Обеспечивает самую жёсткую изоляцию, предотвращая все аномалии

Пример транзакции с разными уровнями изоляции

-- Транзакция 1: Использование READ COMMITTED (по умолчанию)
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Обновление баланса пользователя
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- В READ COMMITTED этот SELECT может увидеть изменения от других 
-- подтверждённых транзакций, которые не были видны в начале
SELECT * FROM accounts WHERE user_id = 1;

COMMIT;

-- Транзакция 2: Использование REPEATABLE READ
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Блокировка строки для обновления, чтобы предотвратить одновременные изменения
SELECT * FROM accounts WHERE user_id = 2 FOR UPDATE;

-- Внесение изменений
UPDATE accounts SET balance = balance - 200 WHERE user_id = 2;

-- Это всегда увидит те же данные в течение всей транзакции
-- независимо от одновременных подтверждённых изменений
SELECT * FROM accounts WHERE user_id = 2;

COMMIT;

Обработка конфликтов блокировки строк

Используйте SELECT FOR UPDATE, чтобы явно заблокировать строки для изменения:

BEGIN;
-- Получение блокировок на строки, которые мы намерены изменить
SELECT * FROM accounts 
WHERE user_id IN (1, 2) 
FOR UPDATE;
-- Теперь у нас есть эксклюзивные блокировки на этих строках

-- Безопасное выполнение обновлений
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

COMMIT;

Используйте FOR UPDATE NOWAIT, чтобы немедленно завершиться, если строка заблокирована:

BEGIN;
-- Немедленно выдаст ошибку, если строки заблокированы
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE NOWAIT;
-- Логика обработки...
COMMIT;

Или используйте таймаут, чтобы подождать ограниченное время:

BEGIN;
-- Подождите до 5 секунд, чтобы получить блокировку
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE WAIT 5;
-- Логика обработки...
COMMIT;

Обнаружение и обработка дедлока

PostgreSQL автоматически обнаруживает дедлоки и завершает одну транзакцию с кодом ошибки 40P01.

-- Правильная обработка ошибок тупиков
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

DO $$
BEGIN
    BEGIN
        -- Попытка получить блокировки и выполнить операции
        SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE;
        -- Симулируем некоторое время обработки
        PERFORM pg_sleep(0.1); 
        UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
        
        -- Дополнительные операции, которые могут привести к тупикам
        SELECT * FROM transactions WHERE account_id = 1 FOR UPDATE;
        
        -- Если мы дошли до этого, подтверждаем транзакцию
        COMMIT;
    EXCEPTION
        WHEN deadlock_detected THEN
            -- Логирование ошибки
            RAISE NOTICE 'Тупик обнаружен, повторяем транзакцию';
            -- Транзакция уже откачена PostgreSQL
            -- Логика повторного выполнения транзакции будет здесь
        WHEN OTHERS THEN
            -- Обработка других ошибок
            RAISE NOTICE 'Ошибка: %', SQLERRM;
            ROLLBACK;
    END;
END;
$$;

Дополнительно: Использование функций управления рекомендательными блокировками

Рекомендательные блокировки (advisory locks) обеспечивают блокировку на уровне приложения без фактического блокирования строк таблиц:

BEGIN;
-- Получение рекомендательной блокировки на ресурс приложения 1001
SELECT pg_advisory_xact_lock(1001);

-- Выполнение операций, требующих эксклюзивного доступа
-- Эти операции не используют блокировки базы данных на строках
UPDATE accounts SET status = 'processing' WHERE user_id = 5;

-- Блокировка автоматически освобождается при завершении транзакции
COMMIT;