L5. Аномалії ізоляції

середнійспирається на модуль 10

Мета

Побачити на власні очі те, що в модулі 10 описано таблицями: як два коректні сеанси разом дають некоректний результат, і яка саме зміна в коді чи рівні ізоляції це виправляє. Після роботи ви вмієте написати покупку, яка витримує одночасних покупців, і знаєте, що робити з помилкою 40001.

Завдання

Результат: два SQL-файли в labs/l05-isolation/solution/.

buy.sql купує одну одиницю товару: зменшує залишок, створює замовлення, позицію й платіж.
close_point.sql закриває пункт видачі, якщо в його населеному пункті лишається хоча б один відкритий.

Готово, коли ./labs/l05-isolation/check.sh виводить «усе гаразд». Це навантаження: 16 паралельних покупців і 20 паралельних операторів на окремій копії датасету. Чекер перевіряє інваріанти: продано не більше, ніж було; залишок збігається з проданим; сума платежів дорівнює сумі замовлень; у кожному населеному пункті лишився відкритий пункт видачі.

Чого робити не треба: змінювати схему чи додавати обмеження, міняти hammer.sh і lib/. Лабораторна про ізоляцію, а не про обмеження.

Перед початком

Прочитайте модуль 10, принаймні розділи про втрачене оновлення, write skew і повтор транзакції. Підніміть базу з датасетом small (Датасет, див. setup/README.md) і переконайтеся, що docker context ls показує локальний Docker:

docker compose --profile postgres up -d --wait
docker compose exec postgres psql -U shop -d shop -c "SELECT count(*) FROM inventory"

Драйвер навантаження написано на bash і psql: він працює всередині контейнера postgres, тож Node.js чи Python на хості не потрібні. Нічого, крім Docker і bash (Git Bash на Windows, WSL, Linux, macOS), не треба. Якщо ви запускаєте compose під власною назвою проєкту, передайте її: COMPOSE_PROJECT_NAME=моя-назва ./labs/l05-isolation/check.sh.

Вам знадобляться два термінали. У кожному відкрийте сеанс:

docker compose exec postgres psql -U shop -d shop

Етапи

Втрачене оновлення вручну. Приведіть товар 39 до двох одиниць:
```
UPDATE inventory SET quantity = 2, reserved = 0 WHERE product_id = 39;
```
Далі по черзі в терміналах A і B (рівень READ COMMITTED, типовий):
```
-- A і B: BEGIN;
-- A і B: SELECT quantity FROM inventory WHERE product_id = 39;   -- обидві бачать 2
-- A: UPDATE inventory SET quantity = 1 WHERE product_id = 39;
-- B: UPDATE inventory SET quantity = 1 WHERE product_id = 39;   -- чекає
-- A: COMMIT;
-- B: COMMIT;
```
Застосунок тут читає 2, віднімає одиницю у своєму коді й пише 1. Перевірте: quantity дорівнює 1, хоча два покупці «купили» по одиниці. Повторіть із BEGIN ISOLATION LEVEL REPEATABLE READ в обох сесіях: що отримала B, і що тепер має зробити застосунок?
Write skew вручну. У Жмеринці (settlement_id = 687116) два відкриті пункти видачі, 1556 і 1557. Перед дослідом: UPDATE pickup_points SET closed_at = NULL WHERE settlement_id = 687116;. В обох терміналах BEGIN ISOLATION LEVEL REPEATABLE READ, потім в обох SELECT count(*) FROM pickup_points WHERE settlement_id = 687116 AND closed_at IS NULL (обидві бачать 2). Тоді A закриває 1556, B закриває 1557 (UPDATE … SET closed_at = current_date), обидві роблять COMMIT. Перевірте, що відкритих пунктів у місті немає. Повторіть на SERIALIZABLE: хто отримав помилку, з яким кодом і що в її HINT?
Наївні скрипти під навантаженням. У labs/l05-isolation/starter/ лежать наївні версії обох скриптів: читання, перевірка, запис без блокувань, рівень READ COMMITTED. Запустіть їх:
Terminal window
```
./labs/l05-isolation/hammer.sh buy   labs/l05-isolation/starter/buy.sql
./labs/l05-isolation/hammer.sh close labs/l05-isolation/starter/close_point.sql
```
Драйвер повідомляє, скільки одиниць продано понад залишок і в скількох населених пунктах не лишилось жодного відкритого пункту. Запишіть числа: вони мають бути ненульові в кожному з запусків, бо чекер додає до UPDATE паузу 25 мс (імітація повільного застосунку), і вікно гонитви з мікросекунд стає помітним.
Виправлення. Створіть labs/l05-isolation/solution/ і скопіюйте туди обидва файли зі starter/. Виправте buy.sql трьома способами, по одному за раз, і після кожного запускайте ./labs/l05-isolation/hammer.sh buy labs/l05-isolation/solution/buy.sql:
- атомарний UPDATE … SET quantity = quantity - 1 WHERE … AND quantity > 0; замовлення, позиція й платіж мають лишитися частиною тієї самої покупки;
- SELECT … FOR UPDATE усередині BEGIN … COMMIT;
- BEGIN ISOLATION LEVEL SERIALIZABLE … COMMIT: драйвер грає роль застосунку й повторює весь скрипт після 40001 та 40P01.
Для close_point.sql спробуйте «атомарний UPDATE з підзапитом» (він має провалитися: чому?), FOR UPDATE по всіх відкритих пунктах міста (з однаковим ORDER BY), блокування рядка settlements, SERIALIZABLE. У solution/ лишіть той спосіб, який вважаєте найкращим для кожного скрипта, і коротко запишіть у коментарі, чому.
Здача. Запустіть ./labs/l05-isolation/check.sh. Кожен пункт, що не пройшов, пояснює, який інваріант порушено.

Перевірка

./labs/l05-isolation/check.sh            # ваш розвʼязок з labs/l05-isolation/solution/
./labs/l05-isolation/check.sh інший/каталог
ROUNDS=5 ./labs/l05-isolation/check.sh  # більше прогонів, якщо сумніваєтесь

Чекер створює у вашому контейнері допоміжні бази l05_base (копія датасету small, один раз) і l05_run (свіжа копія на кожен прогін) і нічого не змінює у вашій базі shop. Кожен сценарій проганяється три рази. Порушення інваріанта в будь-якому прогоні означає «не пройдено», тому розвʼязок, що ламається раз на десять запусків, не пройде. Перевірка триває близько хвилини.

Як драйвер запускає ваш скрипт: по одному процесу psql на покупця, зі спільним моментом старту (pg_sleep_until), щоб усі вдарили одночасно. Змінні :product_id, :customer_id, :pickup_point_id (для buy.sql) і :point_id (для close_point.sql) передаються як psql -v. Якщо скрипт завершився помилкою 40001 чи 40P01, драйвер запускає його знову, до ста разів із короткою випадковою паузою. Будь-яка інша помилка записується й рахується як збій скрипта.

Часті помилки

Скрипт без BEGIN і COMMIT. Кожна команда тоді окрема транзакція. Повтор після 40001 повторить усе спочатку, і частина покупки, що встигла закомітитись, задвоїться або лишиться без платежу: чекер покаже «замовлень без повної оплати».

SERIALIZABLE без повтору. Саме тому драйвер повторює скрипт за вас. У справжньому застосунку цикл повтору пишете ви (приклад у модулі 10).

SELECT count(*) … FOR UPDATE. PostgreSQL не дозволяє FOR UPDATE з агрегатними функціями. Блокуйте рядки у підзапиті, а рахуйте зовні.

FOR UPDATE без ORDER BY. Дві сесії беруть ті самі рядки в різному порядку, і зʼявляються взаємоблокування (40P01). Драйвер їх повторить, але повторів буде багато, і це видно у виводі hammer.sh.

SET TRANSACTION ISOLATION LEVEL після першої команди. Рівень задають першою командою транзакції: BEGIN ISOLATION LEVEL … або SET TRANSACTION … одразу після BEGIN.

Docker дивиться на віддалену машину. Якщо docker context ls показує зірочку біля SSH-адреси, docker compose працює там, а не на вашому компʼютері. docker context use desktop-linux (Docker Desktop) або default (Linux).

Далі, якщо цікаво

Черга задач на FOR UPDATE SKIP LOCKED: n працівників беруть замовлення зі статусом confirmed і позначають їх shipped; перевірте, що жодне не обробили двічі. Подивіться під час навантаження на pg_locks і pg_stat_activity: які сесії чекають і на що. Повторіть buy.sql на REPEATABLE READ із повтором: чим відрізняється кількість повторів від SERIALIZABLE?