Транзакции и изоляция — Примеры

Пример 1 — Счётчик, потерявший обновление

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

сеанс A                            сеанс B
begin;                             begin;
select value from counters
 where id = 1;                     -> 10
                                   (сеанс B читает те же 10)
update counters set value = 11;
                                   update counters set value = 11;
commit;                            commit;   -> итоговое значение 11

Решение

update counters set value = value + 1 where id = 1;

-- одна инструкция и читает, и пишет, поэтому движок делает её атомарной

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

Пример 2 — Две транзакции, берущие блокировки в противоположном порядке

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

сеанс A: update accounts set balance = balance - 10 where id = 1;
         update accounts set balance = balance + 10 where id = 2;

сеанс B: update accounts set balance = balance - 5  where id = 2;
         update accounts set balance = balance + 5  where id = 1;

Решение

-- правило для любого перевода: сначала трогаем счёт с меньшим id
begin;
update accounts set balance = balance - 10 where id = 1;
update accounts set balance = balance + 10 where id = 2;
commit;

begin;
update accounts set balance = balance + 5 where id = 1;
update accounts set balance = balance - 5 where id = 2;
commit;

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