ACID и как модель влияет на блокировки и конкурентность

OLTP-система обрабатывает тысячи параллельных транзакций, и многие из них пересекаются по данным. Как при этом не получить хаос — деньги, списанные дважды, заказы, потерянные между двумя пользователями? Ответ — ACID: набор гарантий, которые СУБД даёт транзакциям. А расплачивается СУБД за эти гарантии блокировками (locks) — и вот здесь модель данных начинает играть прямую роль.

В этом уроке мы разберём четыре свойства ACID, посмотрим, что такое изоляция и блокировки, и покажем главное для модельера: структура схемы напрямую влияет на то, сколько конфликтов конкурентности возникнет.

Транзакция и четыре свойства ACID

Транзакция — это группа операций, которые СУБД выполняет как единое неделимое целое. Перевод денег — классическая транзакция: «списать со счёта A» и «зачислить на счёт B» обязаны произойти вместе или не произойти вовсе. Промежуточное состояние, где деньги списаны, но не зачислены, недопустимо.

ACID — четыре свойства, которые СУБД гарантирует транзакции. Аббревиатура от Atomicity, Consistency, Isolation, Durability.

Разберём по очереди.

Atomicity. Транзакция неделима: либо применяются все её изменения, либо ни одного. Если посреди перевода случился сбой, СУБД откатит уже сделанное списание — база не останется в состоянии «деньги пропали».

Consistency. Транзакция не может оставить базу в состоянии, нарушающем правила целостности. Все ограничения из прошлого урока — PK, FK, CHECK, UNIQUE — проверяются, и транзакция, которая их нарушила бы, откатывается. ACID-Consistency опирается ровно на те constraints, которые мы заложили в модель.

Isolation. Параллельные транзакции выполняются так, будто они не мешают друг другу: транзакция не видит незавершённые изменения другой. Без изоляции две транзакции, читающие и пишущие одни строки, испортили бы данные друг другу.

Durability. Как только СУБД подтвердила транзакцию (COMMIT), её результат на диске и переживёт отключение питания, перезапуск, сбой.

Аномалии конкурентности и зачем нужна изоляция

Изоляция нужна, потому что без неё параллельные транзакции порождают аномалии конкурентности — некорректные результаты от пересечения. Главная и самая наглядная — lost update (потерянное обновление).

Сценарий: на складе товара 10 штук. Два заказа оформляются одновременно, каждый покупает 1 штуку.

Транзакция 1                        Транзакция 2
читает stock = 10
                                    читает stock = 10
вычисляет 10 - 1 = 9
                                    вычисляет 10 - 1 = 9
пишет stock = 9
                                    пишет stock = 9
COMMIT                              COMMIT

Продано 2 штуки, а остаток стал 9 вместо 8. Обновление транзакции 1 потеряно — транзакция 2 затёрла его, потому что читала старое значение. Это lost update, и в OLTP с конкурентной записью он возникает постоянно, если не защититься.

Есть и другие аномалии: dirty read (чтение незакоммиченных данных другой транзакции), non-repeatable read (повторное чтение той же строки даёт другое значение), phantom read (повторный запрос возвращает другой набор строк). От того, какие аномалии допускаются, зависит уровень изоляции транзакции.

Стандарт SQL определяет четыре уровня — от слабого к строгому: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Чем выше уровень — тем больше аномалий запрещено, тем строже гарантия, но тем дороже она обходится по конкурентности. SERIALIZABLE — самый строгий: транзакции выполняются так, будто строго по очереди, аномалий нет совсем.

Блокировки — цена изоляции

Чтобы обеспечить изоляцию, СУБД использует блокировки (locks). Когда транзакция собирается изменить строку, она берёт на эту строку блокировку. Пока блокировка держится (до конца транзакции — COMMIT или ROLLBACK), другая транзакция, желающая изменить ту же строку, ждёт.

Блокировка на нашем примере со складом решает lost update: транзакция 1, изменяя stock, блокирует строку товара; транзакция 2 при попытке изменить ту же строку ждёт, пока транзакция 1 закоммитится; затем читает уже актуальное значение 9 и пишет 8. Корректно.

Блокировки бывают разной гранулярности: на строку (row-level — самая частая и щадящая в OLTP), на страницу, на всю таблицу (table-level — грубая, останавливает многих). Чем мельче гранулярность, тем больше параллелизма: блокировка одной строки мешает только тем, кому нужна именно эта строка.

Цена блокировок — contention (конкуренция за ресурс) и риск deadlock (взаимоблокировки). Contention: если много транзакций рвутся к одной и той же строке, они выстраиваются в очередь, и throughput падает. Deadlock: транзакция 1 держит строку X и ждёт строку Y, транзакция 2 держит Y и ждёт X — обе застряли навсегда; СУБД обнаруживает такой цикл и принудительно откатывает одну из транзакций.

Две стратегии: пессимистичная и оптимистичная

Защититься от lost update можно двумя принципиально разными способами. Понимать оба полезно, потому что выбор между ними отчасти определяется моделью данных.

Пессимистичная стратегия (pessimistic). Транзакция заранее блокирует строку, которую собирается изменить, — ещё на этапе чтения. В SQL это SELECT ... FOR UPDATE: строка читается и сразу блокируется до конца транзакции.

BEGIN;
SELECT stock FROM products WHERE product_id = 'P-7' FOR UPDATE;  -- читаем И блокируем
-- ... вычисляем новое значение ...
UPDATE products SET stock = stock - 1 WHERE product_id = 'P-7';
COMMIT;

Вторая транзакция, дойдя до своего SELECT ... FOR UPDATE на ту же строку, ждёт. Логика «исходит из худшего»: считаем, что конфликт вероятен, и предотвращаем его блокировкой заранее. Цена — ожидание: при высокой конкуренции за строку транзакции выстраиваются в очередь.

Оптимистичная стратегия (optimistic). Транзакция не блокирует строку при чтении, а при записи проверяет, не изменил ли её кто-то за это время. Реализуется через столбец-версию: читаем значение и его version, а в UPDATE добавляем условие «версия всё ещё та же».

-- прочитали: stock = 10, version = 7
UPDATE products SET stock = 9, version = 8
    WHERE product_id = 'P-7' AND version = 7;   -- условие на версию
-- если затронуто 0 строк — кто-то изменил строку первым; транзакция повторяется

Если другая транзакция успела изменить строку, её version уже не 7, UPDATE затронет 0 строк — конфликт обнаружен, и транзакция повторяется с новыми данными. Логика «исходит из лучшего»: считаем конфликт редким, не платим за блокировки, но обязаны уметь обнаружить и повторить.

Связь с моделью данных прямая: оптимистичная стратегия требует, чтобы в таблице был столбец-версия (или timestamp последнего изменения). Это решение принимается при моделировании — если предполагается оптимистичный контроль конкурентности, столбец version закладывают в схему заранее. Так конкурентная стратегия становится частью модели, а не надстройкой над ней.

Как модель данных влияет на конкурентность

Вот главное для модельера. Сколько блокировок и конфликтов возникнет — зависит не только от уровня изоляции, но и от структуры схемы. Хорошая модель снижает contention; плохая — создаёт «горячие точки».

1. Нормализация уменьшает зону блокировки. В денормализованной схеме один факт размазан по многим строкам — изменение факта блокирует их все. В нормализованной схеме факт в одной строке — изменение блокирует одну строку. Меньше заблокированных строк -> меньше транзакций ждут -> выше параллелизм. Это ещё один аргумент за нормализацию OLTP, теперь со стороны конкурентности.

2. «Горячая строка» — узкое место. Если в модели есть строка, которую обновляет почти каждая транзакция, она становится точкой contention. Классический антипаттерн — счётчик в одной строке: таблица counters со строкой orders_total, которую инкрементит каждый заказ. Все транзакции выстраиваются в очередь за блокировкой этой одной строки, и весь параллелизм исчезает. Моделирование должно избегать таких единых точек: разнести счётчик на несколько строк-«сегментов», агрегировать отдельно, не хранить часто меняющийся агрегат в горячей строке.

3. Узкие таблицы -> мельче блокировки. Нормализованные узкие таблицы означают, что транзакция блокирует короткие строки в нескольких таблицах, а не широкую строку, которую ждут и те, кому нужны совсем другие её столбцы.

4. Порядок доступа к строкам и deadlock. Deadlock возникает, когда транзакции берут блокировки в разном порядке. Структура схемы и принятые соглашения влияют на это: если все транзакции всегда обращаются к таблицам и строкам в одном согласованном порядке (например, всегда сначала accounts, потом transactions, строки — по возрастанию id), циклов ожидания не возникает. Это соглашение проектируется вместе с моделью.

Вывод: ACID-гарантии бесплатны для корректности, но не бесплатны для производительности — за них платят блокировками. И счёт по этой плате во многом выписывает модель данных. Нормализованная схема без горячих точек и с предсказуемым порядком доступа позволяет СУБД держать высокий параллелизм; денормализованная схема с единым счётчиком его убивает.

Попробуй сам

Дана OLTP-система билетов на мероприятия:

EVENTS(event_id PK, name, seats_available)
TICKETS(ticket_id PK, event_id, user_id, seat_number)

Каждая покупка билета уменьшает EVENTS.seats_available на 1 и вставляет строку в TICKETS.

Выполните на бумаге:

1. Опишите конкретный lost update, который возникнет, если два пользователя одновременно покупают билет на одно мероприятие и оба читают seats_available до того, как кто-то его обновил.
2. Объясните, как блокировка строки EVENTS при обновлении seats_available устраняет этот lost update. Какая транзакция и сколько будет ждать?
3. Найдите «горячую строку» в этой модели. Почему при популярном мероприятии она становится узким местом для конкурентности?
4. Предложите изменение модели, которое снизит contention на популярных мероприятиях. Подсказка: подумайте, обязательно ли хранить seats_available как единый счётчик в одной строке, или его можно вычислять/сегментировать иначе.