read_committed_2.sql

-- Read Committed

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

-- Шаг 1.6
BEGIN;

-- Шаг 1.7 =>
SELECT * FROM accounts WHERE client = 'alice';

-- Шаг 2.2
SELECT * FROM accounts WHERE client = 'alice';
-- Запрос получает уже новые данные — это и есть аномалия неповторяющегося чтения,
-- которая допускается на уровне Read Committed.

-- Практический вывод: в транзакции нельзя принимать решения на основании данных,
-- прочитанных предыдущим оператором ведь за время между выполнением операторов все может измениться.

-- Шаг 2.3 =>
COMMIT;


-- 3. Несогласованное чтение.
-- Шаг 3.2 =>
-- В это время другая транзакция подсчитывает баланс Боба, причем подсчет выполняется в цикле по всем счетам Боба.
-- Фактически транзакция начинает с первого счета (и, разумеется, видит прежнее состояние):
BEGIN;
SELECT amount FROM accounts WHERE id = 2; -- представляем, что этот amount мы в переменную в приложении записали

-- Шаг 3.4 =>
-- А другая читает состояние второго счета (и видит уже новое значение):
SELECT amount FROM accounts WHERE id = 3;
COMMIT;
-- В итоге вторая транзакция получила в сумме 1100 ₽, то есть прочитала некорректные данные.
-- Такая аномалия называется несогласованным чтением.
-- Как избежать этой аномалии, оставаясь на уровне Read Committed? Конечно, использовать один оператор. Например, так:
SELECT sum(amount) FROM accounts WHERE client = 'bob';

-- Шаг 3.6 =>
-- Пока запрос выполняется, переведём средства обратно
BEGIN;
UPDATE accounts SET amount = amount + 100 WHERE id = 2;
UPDATE accounts SET amount = amount - 100 WHERE id = 3;
COMMIT;

-- Шаг 3.8 =>
-- И снова переведем деньги между счетами, пока запрос с задержкой выполняется
BEGIN;
UPDATE accounts SET amount = amount + 100 WHERE id = 2;
UPDATE accounts SET amount = amount - 100 WHERE id = 3;
COMMIT;

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

-- 4. Несогласованное чтение вместо потерянного обновления.

-- Шаг 4.3 =>
-- В это же время другая транзакция начисляет проценты на все счета с общим балансом, равным или превышающим 1000 ₽
UPDATE accounts
SET amount = amount * 1.01
WHERE client IN (SELECT client
                 FROM accounts
                 GROUP BY client
                 HAVING sum(amount) >= 1000
);
-- Выполнение оператора UPDATE состоит из двух частей. Сначала фактически выполняется оператор SELECT,
-- который отбирает для обновления строки, соответствующие условию. Поскольку изменение первой транзакции
-- не зафиксировано, вторая транзакция не может его видеть, и оно никак не влияет на выбор строк
-- для начисления процентов. Таким образом, счета Боба попадают под условие, и после выполнения обновления
-- его баланс должен увеличиться на 10 ₽.
-- На втором этапе выполнения выбранные строки обновляются одна за другой. Вторая транзакция вынуждена подождать,
-- поскольку строка id = 3 в настоящий момент изменяется первой транзакцией и поэтому заблокирована.

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

-- 5. Потерянное обновление.
-- Шаг 5.2 =>
-- В это время другая транзакция действует так же
BEGIN;
SELECT amount FROM accounts WHERE id = 1; -- и как бы сохранили это значение на уровне приложения

-- Шаг 5.4 =>
-- В это время другая транзакция действует так же
UPDATE accounts SET amount = 800.00 + 100 WHERE id = 1 RETURNING amount;
COMMIT;

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

-- Шаг 6.2 =>
BEGIN;
SELECT * FROM accounts WHERE id = 1;

UPDATE accounts SET amount = 1200, ts = now() WHERE id = 1 AND ts = '2023-10-27 13:00:00';
-- Ни одна запись не была изменена, как мы видим в консоли

SELECT * FROM accounts WHERE id = 1;
COMMIT;