Транзакції і конкурентність

Навіщо це

На складі лишилась одна одиниця товару, і двоє покупців натискають «Купити» майже одночасно. Код кожного робить рівно те, що написано: читає залишок, бачить 1, пише 0 і створює замовлення. У журналі помилок порожньо, а замовлень на один товар два.

Для одного покупця цей код правильний. Ламається він лише тоді, коли між читанням і записом встигає втрутитися хтось інший. У лабораторній L5 такий код під навантаженням 16 паралельних покупців продав 48 одиниць товару, якого на складі було 20.

Модуль про те, як база не дає одночасним запитам заважати одне одному і чим вона за це платить: очікуванням, помилками, які доводиться повторювати, і місцем на диску.

Передумови. UPDATE і обмеження: модуль 4. Сторінка і рядок на диску: модуль 7. Мʼютекси й взаємоблокування: модуль 9 курсу «Операційні системи».

Транзакція: кілька команд як одна

Покупка в «Крамниці» складається з кількох команд: зменшити залишок у inventory, створити замовлення в orders, записати платіж у payments. Якщо сервер застосунку впаде після першої, товар зникне зі складу, а замовлення так і не буде.

Транзакція обʼєднує такі команди в одне ціле:

BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 39;
INSERT INTO orders (...) VALUES (...);
INSERT INTO payments (...) VALUES (...);
COMMIT;

Після COMMIT зміни бачать усі. Якщо щось пішло не так, ROLLBACK скасовує все, що транзакція встигла зробити, і база виглядає так, ніби її не було. Без BEGIN кожна команда є окремою транзакцією й комітиться одразу. Це називають автокомітом, і більшість драйверів працює так за замовчуванням: три команди покупки в автокоміті вже не є одним цілим.

Що саме обіцяє транзакція, описують чотири літери ACID:

Атомарність. Або всі зміни, або жодної.
Консистентність. База стежить за правилами, які їй описали: PRIMARY KEY, FOREIGN KEY, CHECK. Обмеження CHECK (reserved <= quantity) у inventory не порушить жодна транзакція. А правила на кшталт «у місті лишається хоча б один пункт видачі» база не знає, тож за ними стежить застосунок.
Ізоляція. Одночасні транзакції не повинні заважати одна одній. Наскільки суворо, вирішує рівень ізоляції, і це головна тема модуля.
Довговічність. Якщо COMMIT повернув успіх, дані переживуть аварію сервера. Як це влаштовано, розбирає модуль 11.

Коли дві транзакції працюють одночасно

Повернімося до покупців. Товар 39 має quantity = 2, і дві сесії psql на PostgreSQL 18.6 виконують той самий код: прочитати залишок, відняти одиницю в застосунку, записати результат.

Обидві сесії читають 2, кожна рахує «мінус один» у застосунку й пише 1. Друга перезаписує результат першої.

Крок	Сесія A	Сесія B
1	`BEGIN`	`BEGIN`
2	`SELECT quantity …` дає 2	`SELECT quantity …` дає 2
3	`UPDATE … SET quantity = 1`
4		`UPDATE … SET quantity = 1` чекає
5	`COMMIT`	`UPDATE` виконано
6		`COMMIT`

Продано дві одиниці, а на складі лишилась одна. Це втрачене оновлення: зміна першої транзакції пропала, бо друга записала значення, пораховане зі старих даних. Транзакції тут не допомогли: кожна окремо атомарна, і жодна не впала.

Найпростіший захист полягає в тому, щоб не забирати значення в застосунок, а дати базі змінити його однією командою:

UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 39 AND quantity > 0;

Друга сесія чекає, доки перша закомітить, а потім перевіряє умову вже на новому значенні. Якщо товару не лишилось, вона отримує UPDATE 0 і знає, що продавати нічого.

Так можна не завжди. Часто між читанням і записом стоїть логіка застосунку: перевірити знижку, ліміт покупця, наявність у пункті видачі. Тоді є два шляхи. Можна заблокувати рядок одразу під час читання (SELECT … FOR UPDATE, про нього нижче), а можна попросити базу суворіше ізолювати транзакції.

Рівні ізоляції

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

Дивне тут має назву: аномалія, тобто результат, якого не могло б бути, якби транзакції йшли по черзі. На «Крамниці» їх пʼять:

Аномалія	Як це виглядає
брудне читання	бачите `quantity = 0` з чужої транзакції, яка ще не закомітила й потім відкотилась
неповторюване читання	двічі прочитали той самий рядок `inventory` і отримали різні значення
фантомне читання	двічі порахували пункти видачі міста й отримали різну кількість
втрачене оновлення	дві транзакції прочитали `quantity = 2`, обидві записали 1
write skew	дві транзакції закрили по різному пункту видачі й залишили місто без жодного

Стандарт описує рівні лише через перші три. Втрачене оновлення і write skew у ньому не згадано, хоча на практиці вони болять найбільше. На цю прогалину вказала стаття Беренсона та співавторів 1995 року.

Ось що дозволяє PostgreSQL на кожному рівні (перевірено на PostgreSQL 18.6):

Рівень	Брудне	Неповторюване	Фантомне	Втрачене оновлення	Write skew
`READ UNCOMMITTED`	ні	так	так	так	так
`READ COMMITTED` (типовий)	ні	так	так	так	так
`REPEATABLE READ`	ні	ні	ні	ні, помилка `40001`	так
`SERIALIZABLE`	ні	ні	ні	ні	ні, помилка `40001`

Брудного читання в PostgreSQL немає взагалі: READ UNCOMMITTED поводиться як READ COMMITTED. Наш приклад з покупцями працював на типовому рівні READ COMMITTED, і втрачене оновлення там дозволене.

Знімок даних

Щоб зрозуміти таблицю вище, треба знати одну ідею. Транзакція в PostgreSQL бачить дані не «наживо», а як знімок: стан бази на певний момент. Те, що інші закомітили після цього моменту, у знімок не потрапляє.

Рівні відрізняються тим, коли знімок береться:

На READ COMMITTED новий знімок береться перед кожним запитом. Тому два однакові SELECT в одній транзакції можуть повернути різне.
На REPEATABLE READ і SERIALIZABLE знімок береться на першому запиті й живе до кінця транзакції. Двічі прочитаний quantity не зміниться, навіть якщо хтось закомітив зміну між читаннями. Фантомів теж немає, хоча стандарт їх на цьому рівні дозволяє.

Такий підхід називають snapshot isolation: кожна транзакція працює зі своїм знімком. Тепер повторимо покупку на REPEATABLE READ. На кроці 5 сесія B хоче змінити рядок, який після її знімка вже змінила A. База відмовляє, і B відкочується:

ERROR:  could not serialize access due to concurrent update

Це не збій, а прохання повторити транзакцію з початку: з новим знімком B побачить quantity = 1 і порахує правильно. Помилки такого роду мають код 40001, і застосунок має бути до них готовий (розділ «Повтор транзакції» нижче).

Write skew

Буває аномалія хитріша. У Жмеринці два відкриті пункти видачі: 1556 (поштомат) і 1557 (відділення). Правило: у місті має лишатися хоча б один. Два оператори різних перевізників закривають кожен свій пункт і спершу перевіряють, що другий лишиться.

Крок	Сесія A (`REPEATABLE READ`)	Сесія B (`REPEATABLE READ`)
1	`BEGIN ISOLATION LEVEL REPEATABLE READ`	те саме
2	`SELECT count(*) … WHERE closed_at IS NULL` дає 2	те саме, 2
3	`UPDATE … SET closed_at = current_date WHERE pickup_point_id = 1556`	`… WHERE pickup_point_id = 1557`
4	`COMMIT`	`COMMIT` без помилок

Обидва пункти закрито, правило порушено. На REPEATABLE READ конфлікту немає: транзакції змінили різні рядки, кожна у своєму знімку чесно бачила два відкриті пункти. Це і є write skew: кожна транзакція окремо права, а разом вони порушують правило, яке залежить від кількох рядків.

Ловить таке лише SERIALIZABLE. Той самий розклад на ньому закінчується на кроці 4 в сесії B:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

Атомарний UPDATE тут не рятує: перевірка в підзапиті бачить знімок, де другий пункт ще відкритий. Працюють інші два способи. Перший: SELECT … FOR UPDATE по всіх відкритих пунктах міста, бажано з однаковим ORDER BY в обох сесіях. Якщо блокувати прочитане незручно, можна блокувати один спільний рядок, наприклад рядок цього міста в settlements: через нього проходить кожен, хто закриває пункт. Другий спосіб: SERIALIZABLE з повтором, і тоді код перевірки взагалі не змінюється.

Повтор транзакції

Помилку 40001 база повертає не тому, що щось зламалось, а тому, що інакше результат був би неправильним. Виправляє її повтор, і робить його застосунок: база не знає, що відбувалося до BEGIN. Схема однакова для 40001 і 40P01 (взаємоблокування): відкотитися, трохи почекати й виконати всю транзакцію заново.

for (let attempt = 1; attempt <= 5; attempt++) {
  try {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
    // уся покупка: перевірка залишку, UPDATE, замовлення, платіж
    await client.query('COMMIT');
    return;
  } catch (e) {
    await client.query('ROLLBACK');
    if (e.code !== '40001' && e.code !== '40P01') throw e;
    await sleep(10 * attempt + Math.random() * 20);
  }
}

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

Явні блокування і взаємоблокування

SELECT … FOR UPDATE блокує вибрані рядки до кінця транзакції: хто захоче їх змінити або теж заблокувати, чекатиме. Є мʼякші варіанти (FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE), але найчастіше потрібен саме FOR UPDATE.

Добрий приклад — черга задач. Працівники складу беруть на пакування замовлення зі статусом confirmed. Зі звичайним FOR UPDATE усі стоятимуть за першим, а SKIP LOCKED пропускає рядки, які вже заблокував хтось інший:

SELECT order_id
FROM orders
WHERE status = 'confirmed'
ORDER BY order_id
LIMIT 2
FOR UPDATE SKIP LOCKED;

Дві сесії з цим запитом одночасно отримали 21728, 21858 і 21880, 21881: жодного перетину й жодного очікування. Зворотний бік у тому, що чергу видно неповною, тож SKIP LOCKED годиться для черг, але не для звітів.

Блокування приносять знайому з курсу ОС проблему. Сесія A тримає рядок товару 39 і просить 55, а сесія B тримає 55 і просить 39: обидві чекатимуть вічно. PostgreSQL шукає такий цикл не одразу, а лише коли сесія чекає довше за deadlock_timeout (типово 1 с): перевірка недешева, а більшість очікувань закінчується сама. Знайшовши цикл, він скасовує одну з транзакцій:

ERROR:  deadlock detected
DETAIL:  Process 897 waits for ShareLock on transaction 914; blocked by process 898.
Process 898 waits for ShareLock on transaction 913; blocked by process 897.

Запобігає цьому, як і в задачі про філософів, єдиний порядок захоплення: завжди спершу менший product_id. Решту випадків ловить повтор.

Як база тримає кілька версій рядка

Досі ми дивились на поведінку ззовні. Тепер про те, як вона влаштована.

Класичний спосіб ізолювати транзакції — двофазне блокування (2PL): транзакція блокує все, що читає й пише, і відпускає лише після коміту. Результат правильний, але читач чекає на того, хто пише, а той, хто пише, на читача. Так працює рівень SERIALIZABLE в InnoDB: з вимкненим автокомітом він перетворює звичайні SELECT на SELECT … FOR SHARE.

PostgreSQL робить інакше. Його підхід називають MVCC (multiversion concurrency control). UPDATE не змінює рядок на місці, а створює його нову версію, стара лишається поруч. Кожна транзакція читає ту версію, яка належить до її знімка, тож читачі не заважають тим, хто пише, і навпаки. Чекають одне на одного лише дві транзакції, що змінюють той самий рядок.

Дві версії рядка після UPDATE. Числа з виконання в Docker. Знімок, зроблений до коміту транзакції 5086, бачить стару версію, зроблений після — нову.

Звідки транзакція знає, яку версію бачити? У заголовку кожної версії є два номери: xmin (транзакція, що її створила) і xmax (транзакція, що її видалила або замінила). Версію видно, якщо транзакція xmin закомітила до знімка, а xmax порожній або належить транзакції, якої знімок ще не бачить.

Звідси ж дешевий відкат. Коміт у PostgreSQL зводиться до того, що статус транзакції в pg_xact міняється на «закомічено» і про це пишеться запис у WAL. Нові версії лежать у таблиці від першого INSERT чи UPDATE, але нікому не видимі, доки статус не зміниться. Якщо транзакція відкотилась, її версії так і лишаються невидимими, а прибирає їх VACUUM.

SERIALIZABLE у PostgreSQL зроблено як SSI (serializable snapshot isolation). Транзакція працює так само зі знімком, як на REPEATABLE READ, але база ще стежить, хто прочитав дані, які інша одночасна транзакція потім змінила. Коли такі залежності утворюють небезпечний ланцюжок, база скасовує одну з транзакцій, не чекаючи, чи цикл справді замкнеться. Стеження нікого не блокує, а платою є 40001, інколи навіть там, де порушення насправді не було. Тому документація вимагає від застосунків на цьому рівні повторювати транзакції цілком.

PostgreSQL
MySQL

Старі версії лежать у самій таблиці, поруч із новими, і їх прибирає VACUUM. REPEATABLE READ у прикладі з втраченим оновленням скасовує другу транзакцію помилкою 40001.

InnoDB змінює рядок на місці, а попередні значення відкладає в undo log. Читач, якому потрібна стара версія, відновлює її з undo, а непотрібне чистить процес purge. REPEATABLE READ тут типовий рівень, але з нашим розкладом втраченого оновлення помилки немає: друга сесія чекає, а потім мовчки записує свою 1 (перевірено на MySQL 8.4 в Docker). Захищають SELECT … FOR UPDATE або атомарний UPDATE.

Ціна версій: VACUUM, bloat і wraparound

Старі версії після UPDATE і DELETE нікуди не зникають самі. Їх прибирає VACUUM: знаходить версії, які вже не потрібні жодному знімку, і позначає їхнє місце вільним для нових. Зазвичай це робить автоматичний autovacuum.

Якщо прибирання не встигає, таблиця й індекси розростаються: займають більше місця, ніж потребують живі дані, а скани читають напівпорожні сторінки. Це називають bloat. Звичайний VACUUM місце операційній системі не повертає (хіба що вільний хвіст файлу), він лише дозволяє використати його знову. Повертає місце VACUUM FULL, але він переписує таблицю під ексклюзивним блокуванням.

Найчастіша причина, чому VACUUM не прибирає, — давня відкрита транзакція. Її знімок може ще знадобитися, тож усі версії, які вона могла б побачити, лишаються. Одна сесія, що застрягла в стані idle in transaction, гальмує прибирання в усіх таблицях бази.

У VACUUM є й друга, менш помітна робота. Номер транзакції має 32 біти, тож порівняння «раніше чи пізніше» працює лише в межах приблизно двох мільярдів транзакцій. Коли лічильник обертається по колу (це називають wraparound), старі рядки почали б здаватися «з майбутнього», тобто невидимими. Щоб цього не сталося, VACUUM заморожує достатньо старі версії: позначає їх видимими для всіх, і порівнювати номер більше не треба.

За документацією PostgreSQL 18 autovacuum береться за таблицю примусово, коли її вік перевищує autovacuum_freeze_max_age (типово 200 млн транзакцій). Якщо він не встигає, база попереджає, коли до межі лишається 40 млн транзакцій, а за 3 млн перестає приймати команди, яким потрібен новий номер транзакції, тобто будь-який запис. Виходить із цього VACUUM по всій базі після того, як прибрано причини: давні відкриті й підготовлені транзакції та непотрібні слоти реплікації. На великій базі він іде довго.

Як це насправді

У PostgreSQL 18.6 з нашого docker compose версії рядків видно прямо в запиті: системні колонки xmin, xmax і ctid є в кожній таблиці.

SELECT xmin, xmax, ctid, product_id, quantity FROM inventory WHERE product_id = 39;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 39;
SELECT xmin, xmax, ctid, product_id, quantity FROM inventory WHERE product_id = 39;

 xmin | xmax |  ctid   | product_id | quantity
------+------+---------+------------+----------
 5049 |    0 | (22,92) |         39 |        2
 5086 |    0 | (22,93) |         39 |        1

Після UPDATE змінились і xmin, і адреса: нова версія лягла на ту саму сторінку 22, у слот 93. Стара нікуди не ділась. Розширення pageinspect показує обидві (у контейнері для нього потрібен користувач postgres: docker compose exec postgres psql -U postgres -d shop):

SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('inventory', 22)) WHERE lp >= 92;

 lp | t_xmin | t_xmax | t_ctid
----+--------+--------+---------
 92 |   5049 |   5086 | (22,93)
 93 |   5086 |      0 | (22,93)

t_xmax = 5086 на старій версії означає «замінено транзакцією 5086», а t_ctid вказує на нову. У пісочниці номери будуть інші.

СпробуйPostgreSQLCtrl+Enter — виконати

Тепер VACUUM і давня транзакція. У сесії A відкрито BEGIN ISOLATION LEVEL REPEATABLE READ і виконано один SELECT. Сесія B двічі оновлює всі 3500 рядків копії inventory і запускає VACUUM (VERBOSE). Копія виросла з 256 до 600 кБ, а VACUUM звітує:

tuples: 0 removed, 10500 remain, 7000 are dead but not yet removable

Після COMMIT у сесії A той самий VACUUM пише 7000 removed, 3500 remain, 0 are dead but not yet removable. Файл при цьому лишається 600 кБ: місце знову можна використати, але операційній системі його не повернуто.

Як близько база до межі wraparound, показує вік найстарішого незамороженого номера транзакції:

SELECT datname, age(datfrozenxid) FROM pg_database WHERE datname = current_database();
SHOW autovacuum_freeze_max_age;

На щойно завантаженій базі вік вимірюється тисячами, а autovacuum_freeze_max_age дорівнює 200000000. За першим числом має стежити моніторинг (модуль 21).

Розбір: Mandrill, лютий 2019

Mandrill, сервіс транзакційної пошти Mailchimp, зберігав дані в PostgreSQL, розбитому на шарди. 4 лютого 2019 року зʼясувалося, що один із шардів зупинився через wraparound номерів транзакцій. Хешування розкладало записи нерівномірно, і цей шард отримував їх більше за інші. За постмортемом Mailchimp, autovacuum на ньому, імовірно, відстав від навантаження, хоча міг і зовсім не працювати.

Перші оцінки тривалості VACUUM були від днів до тижнів. Паралельно команда почала переносити дані дампом в іншу базу без двох найбільших таблиць, Search і Url: вони займали терабайти. 5 лютого ці таблиці очистили (truncate), і VACUUM завершився приблизно за годину. Відправку листів із черги відновили того ж дня о 21:36 UTC.

Схожу відмову в липні 2015 року мав Sentry: його хмарний сервіс не працював більшу частину робочого дня в США, і причиною теж був wraparound.

Урок: стежити треба не лише за вільним місцем, а й за age(datfrozenxid) і найстарішою відкритою транзакцією. Обидва числа ростуть тихо, а зупиняється база раптово.

Типові помилки розуміння

«READ COMMITTED — безпечний рівень». Кожен запит бачить свіжий знімок, тож між двома читаннями в одній транзакції дані можуть змінитися. Код «прочитав, перевірив, записав» на цьому рівні ненадійний без блокування чи атомарного UPDATE.

«SERIALIZABLE просто повільніше». Він не стільки чекає, скільки скасовує: частину транзакцій доводиться виконувати наново, і без повтору на 40001 покупки губляться.

«Мені досить SELECT … FOR UPDATE на одному рядку». Від втраченого оновлення досить. Але write skew пише різні рядки, тож блокувати треба все, що читає перевірка правила, або один спільний рядок.

«VACUUM зменшує файл». Він звільняє місце для повторного використання. Файл зменшує VACUUM FULL, але під ексклюзивним блокуванням таблиці.

Перевір себе

1. Дві транзакції на `READ COMMITTED` прочитали `quantity = 3`, кожна записала `quantity = 2` і закомітила. Яка це аномалія і який найнижчий рівень PostgreSQL її не допустить?Неповторюване читання, `REPEATABLE READ`Write skew, `SERIALIZABLE`Фантомне читання, `SERIALIZABLE`Втрачене оновлення, `REPEATABLE READ`: друга транзакція отримає `40001`

2. Два оператори на `REPEATABLE READ` перевірили, що в місті два відкриті пункти видачі, закрили кожен свій і закомітили. Правило «лишається хоча б один» порушено. Що з переліченого це виправить?Перейти на `SERIALIZABLE` з повтором після `40001` або блокувати `FOR UPDATE` усі відкриті пункти містаПерейти на `READ COMMITTED`Замінити перевірку атомарним `UPDATE … WHERE (SELECT count(*) …) >= 2`Додати `FOR UPDATE` лише до рядка пункту, який закривають

3. Запит завершився помилкою `40001`. Що має зробити застосунок?Повторити лише той запит, який впавВідкотити транзакцію і виконати її всю знову, бажано з невеликою випадковою паузоюПерейти на нижчий рівень ізоляціїПоказати користувачеві помилку: база так вирішила

4. Сесія A оновила товар 39, потім товар 55. Сесія B оновила 55, потім 39. Через секунду B отримала `deadlock detected`. Чому саме через секунду і як цьому запобігти?Секунда — час життя блокування рядка; допоможе `SERIALIZABLE`PostgreSQL перевіряє цикли на кожен `UPDATE`; допоможе `FOR UPDATE SKIP LOCKED`Перевірка запускається лише для сесії, що чекає довше за `deadlock_timeout`; допоможе єдиний порядок оновлення, наприклад за `product_id`Секунда — затримка мережі; допоможе збільшити `max_connections`

5. `VACUUM (VERBOSE)` пише `7000 are dead but not yet removable`, хоча таблицю щойно двічі оновили й більше нічого не чіпали. Що шукати?Вимкнений autovacuumНестачу памʼяті в `maintenance_work_mem`Потрібен `VACUUM FULL`Давню відкриту транзакцію, знімок якої ще може бачити ці версії

6. На одному з шардів autovacuum роками не встигав заморожувати рядки. Що станеться, коли вік найстарішого незамороженого номера транзакції наблизиться до двох мільярдів?Спершу зʼявляться попередження, а потім сервер перестане приймати команди, яким потрібен новий номер транзакції, тобто будь-який записСтарі рядки тихо зникнуть із таблицьЗапити сповільняться, але працюватимутьБаза автоматично перейде на `READ UNCOMMITTED`

Лабораторна

L5. Аномалії ізоляції: відтворити втрачене оновлення й write skew на живому PostgreSQL у двох сесіях, виправити кожну аномалію трьома способами й здати скрипти, які не зламає навантаження з багатьох паралельних покупців.

Джерела

PostgreSQL 18, документація: розділ 13 «Concurrency Control» (рівні ізоляції, явні блокування), розділ «Routine Vacuuming» (wraparound).
MySQL 8.4 Reference Manual, InnoDB Transaction Isolation Levels.
H. Berenson, P. Bernstein, J. Gray, J. Melton, E. O’Neil, P. O’Neil, A Critique of ANSI SQL Isolation Levels, SIGMOD 1995.
D. Ports, K. Grittner, Serializable Snapshot Isolation in PostgreSQL, VLDB 2012.
M. Kleppmann, Designing Data-Intensive Applications, розділ 7 «Transactions».
Mailchimp, What We Learned from the Recent Mandrill Outage, 2019.
Sentry, Transaction ID Wraparound in Postgres, 2015.