read_committed_1.sql

-- Read Committed

-- 1. Отсутствие грязного чтения.

-- Легко убедиться в том, что грязные данные прочитать невозможно.
-- Начнем транзакцию.
-- По умолчанию она использует уровень изоляции Read Committed

-- Шаг 1.1. Проверяем уровень изоляции транзакции по умолчанию
SHOW default_transaction_isolation;

-- Шаг 1.2. Начинаем т1
BEGIN;

-- Шаг 1.3. Проверяем уровень изоляции нашей транзакции
SHOW transaction_isolation;

-- Шаг 1.4. Проверяем уровень изоляции нашей транзакции
-- В открытой транзакции снимаем средства со счета, но не фиксируем изменения.

UPDATE accounts
SET amount = amount - 200
WHERE id = 1;

-- Шаг 1.5 =>
-- Свои собственные изменения транзакция всегда видит
SELECT *
FROM accounts
WHERE client = 'alice';

------------------------------------------------------------------------------------------------------------------------

-- 2. Неповторяющееся чтение.
-- Пусть теперь первая транзакция зафиксирует изменения, а вторая повторно выполнит тот же самый запрос
-- Шаг 2.1 =>
COMMIT;

-- Пример из жизни как нарваться на неповторяющееся чтение (классический антипаттерн)
DO
$$
    BEGIN
        IF (SELECT amount FROM accounts WHERE id = 1) >= 1000 THEN
            -- Представим, что параллельно кто-то сделал вот такой запрос:
            -- UPDATE accounts SET amount = amount - 200 WHERE id = 1;
            -- COMMIT;
            UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
        END IF;
    END
$$;
-- За время, которое проходит между проверкой и обновлением, другие транзакции могут как угодно изменить состояние счета,
-- так что такая «проверка» ни от чего не спасает.

-- Как написать код корректно? Есть несколько возможностей:
-- 1. Добавить ограничение целостности, например
ALTER TABLE accounts ADD CHECK (amount >= 0);
-- В таком случае при отрицательном балансе мы получим исключение и обработаем его в коде

-- 2. Использовать один оператор SQL.
--    Проблемы с согласованностью возникают из-за того, что в промежутке между операторами может завершиться
--    другая транзакция, и видимые данные изменятся. Если оператор один, то и промежутков никаких нет.
--    Можно использовать CTE, для вставки можно использовать INSERT ... ON CONFLICT
--    А можно ещё проще:
UPDATE accounts
SET amount = amount - 1000
WHERE id = 1 AND amount >= 1000;

-- 3. Задействовать пользовательские блокировки.
--    Последнее средство — вручную установить исключительную блокировку на все нужные строки (SELECT FOR UPDATE)
--    или вообще на всю таблицу (LOCK TABLE). Это всегда работает, но сводит на нет преимущества многоверсионности:
--    вместо одновременного выполнения часть операций будет выполняться последовательно.
DO
$$
    BEGIN
        IF (SELECT amount FROM accounts WHERE id = 1 FOR UPDATE) >= 1000 THEN
            UPDATE accounts SET amount = amount - 1000 WHERE id = 1;
        END IF;
    END
$$;

------------------------------------------------------------------------------------------------------------------------

-- 3. Несогласованное чтение.
--    Реализация PostgreSQL такова, что допускает другие, менее известные аномалии,
--    которые не регламентируются стандартом.
--    Допустим, первая транзакция начала перевод средств с одного счета Боба на другой:
-- Шаг 3.1 =>
BEGIN;
    UPDATE accounts
    SET amount = amount - 100
    WHERE id = 2;

    -- Шаг 3.3 =>
    -- В этот момент первая транзакция успешно завершается:
    UPDATE accounts
    SET amount = amount + 100
    WHERE id = 3;
COMMIT;

-- Видимость данных может поменяться только между операторами, но так ли это очевидно?
-- А если запрос выполняется долго, может ли он увидеть часть данных в одном состоянии, а часть — уже в другом?
-- Проверим. Удобный способ для этого — вставить в оператор искусственную задержку, вызвав функцию pg_sleep.
-- Первая строка будет прочитана сразу, а вторая — через 5 секунд:

-- Шаг 3.5 =>
SELECT amount, pg_sleep(5) -- 5 секунд
FROM accounts
WHERE client = 'bob';
-- Результат показывает, что оператор видит данные в таком состоянии, в каком они находились на момент
-- начала его выполнения, что, безусловно, правильно.

-- Но если в запросе вызывается изменчивая функция (с категорией изменчивости VOLATILE)
-- и в этой функции выполняется другой запрос, то этот вложенный запрос будет видеть данные,
-- не согласованные с данными основного запроса.
-- Проверим состояние счетов Боба, используя функцию:
CREATE FUNCTION get_amount(id integer) RETURNS numeric
AS
$$
SELECT amount
FROM accounts a
WHERE a.id = get_amount.id;
$$ VOLATILE LANGUAGE sql;

-- Шаг 3.7 =>
SELECT get_amount(id), pg_sleep(5)
FROM accounts
WHERE client = 'bob';
-- В этом случае получим несогласованные данные — 100 ₽ пропали.
-- Такой эффект возможен только на уровне изоляции Read Committed и только с категорией изменчивости VOLATILE.
-- Беда в том, что по умолчанию используется именно этот уровень изоляции и именно эта категория изменчивости,
-- так что остается признать — грабли лежат очень удачно.

------------------------------------------------------------------------------------------------------------------------

-- 4. Несогласованное чтение вместо потерянного обновления.
-- Аномалию несогласованного чтения в рамках одного оператора можно получить и при обновлении.

-- Шаг 4.1
-- Сейчас у Боба 1000 ₽ на двух счетах
SELECT *
FROM accounts
WHERE client = 'bob';

-- Начинаем транзакцию, которая уменьшает баланс Боба
-- Шаг 4.2 =>
BEGIN;
UPDATE accounts SET amount = amount - 100 WHERE id = 3;

-- Шаг 4.4
-- Между тем первая транзакция фиксирует изменения
COMMIT;
-- Шаг 4.5
-- После снятия блокировки оператор UPDATE перечитывает строку, которую пытается обновить (но только ее одну!).
-- В результате получается, что Бобу начислено 9 ₽, исходя из суммы 900 ₽. Но если бы у Боба было 900 ₽,
-- его счета вообще не должны были попасть в выборку.
-- Таким образом, транзакция прочитала некорректные данные: часть строк — на один момент времени, часть — на другой.
-- Взамен потерянного обновления мы снова получаем аномалию несогласованного чтения.
SELECT *
FROM accounts
WHERE client = 'bob';

------------------------------------------------------------------------------------------------------------------------

-- 5. Потерянное обновление.
-- Впрочем, хитрость с перечитыванием заблокированной строки не спасает от потери изменений,
-- если обновление происходит не в одном операторе SQL.

-- Шаг 5.1 =>
-- Приложение читает и запоминает (вне базы данных) текущий баланс счета Алисы
BEGIN;
SELECT amount FROM accounts WHERE id = 1; -- и как бы сохранили это значение 800 на уровне приложения

-- Шаг 5.3 =>
-- Первая транзакция увеличивает запомненное ранее значение на 100 ₽ и записывает в базу
UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1 RETURNING amount;
COMMIT;

-- К сожалению, Алиса недосчиталась 100 ₽. СУБД ничего не знает о том, что запомненное значение 800 ₽
-- как-то связано с accounts.amount, и допускает аномалию потерянного изменения. На уровне изоляции Read Committed
-- такой код некорректен.

------------------------------------------------------------------------------------------------------------------------

SELECT * FROM accounts;

-- 6. Реализация оптимистичной блокировки
-- Оптимистическая блокировка запрещает запись объекта в базу данных, если после считывания объекта
-- он был изменен в базе данных. Строго говоря, оптимистическая блокировка представляет собой проверку,
-- которая выполняется перед записью объекта в базу данных.

-- Добавим новый столбец с timestamp
ALTER TABLE accounts ADD COLUMN ts timestamp with time zone default '2023-10-27 13:00:00';

-- Шаг 6.1 =>
BEGIN;
SELECT * FROM accounts WHERE id = 1;
UPDATE accounts SET amount = 1000, ts = now() WHERE id = 1 AND ts = '2023-10-27 13:00:00';

SELECT * FROM accounts WHERE id = 1;

COMMIT;

-- Вернём всё как было
ALTER TABLE accounts DROP COLUMN ts;
UPDATE accounts SET amount = 900 WHERE id = 1;