Аномалии конкурентного доступа27.03.2025 15:01

В распределённых базах данных YDB, CockroachDB по умолчанию используется уровень изоляции SERIALIZABLE. В PostgreSQL, Oracle Database, MySQL по умолчанию используется READ COMMITED. В стандарте SQL указаны только три аномалии. В статье приводится пример аномалии потерянного обновления в задаче «списания средств» и рассматривается, почему уровень READ COMMITED и ограничения целостности достаточны для решения задачи.

Если с данными (строками таблиц) работает одна сессия в базе данных, то разницы между уровнями изоляции транзакций нет, так как нет транзакций, от которых нужно изолироваться. Если есть несколько транзакций, которые пересекаются во времени, то нужно обращать внимание на то, что параллельно с теми же данными  работают другие транзакции. Другими  словами, учитывать особенности (феномены, аномалии) «конкурентного доступа».

Аномалией считается неожидаемый результат. Аномалии субъективны, так как один человек ожидает одно, а другой другое. Объективны те аномалии, которые указаны в стандартах. В стандарте SQL 2016 в таблице 9 (SQL-transaction isolation levels and the three phenomena) описаны только три феномена: P1 — грязное чтение («Dirty read»), P2 — неповторяемость по чтению («Non-repeatable read» или fuzzy read), P3 — фантомное чтение («Phantom»). Эта таблица представлена в разделе «Уровни изоляции транзакций» документации PostgreSQL. Иногда аномалии называют race condition, но этот термин не используется в стандарте SQL. Пример описания большого числа аномалий с примерами SQL-команд: https://github.com/ept/hermitage

Об аномалиях можно ничего не знать, на практике важно, чтобы транзакция давала результат, который ожидается постановкой задачи и ни в одном из случаев не давала не предусмотренный заданием (неожиданный, аномальный) результат.

Пример, когда аномалии привели к убыткам

В 2011 году компания Poloniex потерпела убытки. Официальное описание проблемы: https://bitcointalk.org/index.php? topic=499580 За несколько дней до этого вторая компания Flexcoin получила убытки и обанкротилась. В программных системах двух компаний была одна и та же ошибка в реализации типичной задачи, решаемой транзакционной логикой.

Задача проверить есть ли на счету положительный баланс средств, вычесть и оформить расходную операцию. Использовался ли SQL или реляционная СУБД неизвестно. В статье The race condition that led to Flexcoin bankruptcy (https://vladmihalcea.com/race-condition/) приводится пример команд SQL в транзакциях, которые могли бы привести к аномалии. Последовательность действий, которая по официальному объяснению, приводила к неожиданному результату:

1. Input validation.
2. Your balance is checked to see if you have enough funds.
3. If you do, your balance is deducted.
4. The withdrawal is inserted into the database.
5. The confirmation email is sent.
6. After you confirm the withdrawal, the withdrawal daemon picks it up and processes the withdrawal.

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

drop table if exists account;
create table account (id numeric primary key, balance numeric not null);
insert into account values (1,1);
select * from account;
 id | balance 
----+---------
  1 |       1
(1 row)

Пример того, как не стоит реализовывать задачу списания средств:

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

Ограничения целостности

После прочтения вышеупомянутой статьи Therace condition that led to Flexcoin bankruptcy, возникает впечатление, что только REPATABLE READ, а ещё лучше SERIALIZABLE не допустит непредсказуемых результатов. Но проблема кроется не в уровне изоляции, а написании команд SQL. Более того, даже при использовании этих команд можно не допустить появления отрицательного баланса на всех уровнях изоляции. Для этого достаточно указать, что требуется проверять  (задекларировать бизнес-правило) в виде ограничения целостности:

alter table account add check (balance>=0);

СУБД гарантируют целостность (consistency) и даже неверный код SQL и ошибки в логике не приведут к появлению строк, нарушающих ограничение целостности. Посмотрим пример, есть ли нарушение логики при наличии ограничения целостности:

drop table if exists account;
create table account (id numeric primary key, balance numeric not null);
insert into account values (1,1);
alter table account add check (balance>=0);
select * from account;
 id | balance 
----+---------
  1 |       1
(1 row)

На уровне изоляции READ COMMITED получен корректный и ожидаемый результат. Использовать более сильные уровни изоляции транзакций не потребовалось.

Ограничения целостности работают одинаково на всех уровнях изоляции транзакций, независимо от уровней. Транзакции обеспечивают свойства ACID (атомарность, согласованность, изоляция, отказоустойчивость). Согласованность обеспечивается ограничениями целостности, изоляция — уровнями.

При написании кода транзакций и использовании ограничений целостности, уровень изоляции READ COMMITED достаточен для большинства задач. Уровень изоляции REPEATABLE READ используется, если сложно реализовать запрос (только чтение) одной командой, а хочется получить согласованный результат (на один момент). По возможности, стоит избегать использовать команды, меняющие данные, в транзакциях REPEATABLE READ, так как, есть вероятность получить ошибку «could not serialize access» (не могу сериализовать доступ) при фиксации транзакции. В Oracle Database уровня REPEATABLE READ нет, но есть аналог, в котором можно только читать данные.

Особенности выполнения команд SQL

 1. Одиночные читающие команды (SELECT) выдают данные на один момент времени (выдают согласованные данные; гарантируют целостность по чтению).

2. На уровне изоляции READ COMMITED команды SELECT выдают данные на момент начала своего выполнения. Один и тот же SELECT может выдать разные данные, это называется феноменом неповторяемости по чтению (non-repeatable read или fuzzy read). Пример:

begin;
select * from account;
select * from account;
commit;

Две одинаковых команды, данные «очень быстро» одна за другой могут дать разные результаты. Это произойдёт, если пока работает первая команда SELECT, другая транзакция изменит строки таблицы и зафиксирует изменения. Чем дольше работает SELECT, тем больше вероятность.

Для того, чтобы команды видели данные на один момент (момент выполнения первой команды в транзакции) можно использовать уровень изоляции REPEATABLE READ (повторяемость по чтению) или SERIALIZABLE.

Уровень SERIALIZABLE на репликах PostgreSQLне работает, так как на репликах данные нельзя менять, то он не отличается от REPEATABLE READ, который на репликах может использоваться:

postgres=# begin isolation level serializable;
ERROR:  cannot use serializable mode in a hot standby
HINT:  You can use REPEATABLE READ instead.

3. В отличие от читающих команд, изменяющие данные команды (UPDATE, DELETE, MERGE) могут выдавать данные для разных строк на разные моменты времени. Для этих команд целостность по чтению не гарантируется.

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

drop table if exists account;
create table account (id bigserial primary key, balance numeric not null);
insert into account values (DEFAULT,1);
select * from account;
 id | balance 
----+---------
  1 |       1
(1 row)

Баланс не ушел в минус, а значит, результат соответствует задаче безопасного списания средств со счета. Результат такой же, как если бы вторая транзакция началась после фиксации первой транзакции.

Если бы транзакции использовали REPEATABLE READ, то вторая транзакция после снятия блокировки получила бы ошибку «ERROR:   could not serialize access due to concurrent update».

Сняв блокировку с первой строки, вторая транзакция прочтет вторую строку (точнее будет читать версии строк, которые найдёт в блоках таблицы), применит правила видимости, исключив версии строк, которые не должна видеть. Если версия строки видна, то прочтет ее и если значения попадают под условие, то попытается заблокировать её. Эта строка может быть также заблокирована транзакциями, начавшимися позже. Транзакция будет ждать получения блокировки и как только получит её, перечитает поля, перепроверит условия предиката и обновит строку, если строка удовлетворит предикату.

Таким образом, UPDATE натыкаясь на заблокированные строки, будет ждать получения блокировки по каждой строке, перечитывать их поля и продвигаться дальше. Получится, что команда UPDATE в процессе продвижения по строкам видит значения в столбце balance на разные моменты времени. Целостности по чтению нет.

Особенность: если транзакции, начавшиеся позже, вставят новые строки, эти строки не будут видны транзакции, так как вставлены позже и по правилам видимости не должны быть видны.

Аномалия потерянного обновления на уроне изоляции транзакций READ COMMITED будет присутствовать, если в команде UPDATE нет обращения к полям строки. Например:

update account SET balance = число WHERE id = 1 returning *;

В этом случае, если такую команду дать в двух транзакциях, то значение поля balance будет установлено той транзакцией, которая завершилась позже. Это считается аномалией потерянного изменения: теряется изменение, сделанное первой транзакцией. В стандарте SQL такого феномена нет, то есть аномалия субъективна. Кто-то может считать, что позднюю транзакцию надо уведомить о том, что значения полей строки изменились. Как уведомить, если команда не интересуется этими полями? Путем перевода транзакции в режим сбоя. Кто-то может считать, что если команда в поздней транзакцией не поинтересовалась значениями полей, а просто их перезаписывает, то зачем настаивать — значит разработчик знает, что делает.

Для кого-то важным является: что с полями, которые не читаются и не меняются поздней транзакцией, но меняются ранней транзакцией, какие значения эти поля примут? Проверим:

drop table if exists account;
create table account (id numeric primary key, balance numeric not null, third numeric);
insert into account values (1,1,1);
select * from account;

 id | balance | third 
----+---------+-------
  1 |       1 |     1
(1 row)

Поле столбца third, измененное в первой сессии не читалось и не менялось во второй сессии. Значение поля сохранилось и не было потеряно. Вторая сессия заменила значение поля balance.

Можно ли считать, что СУБД потеряло обновление какого-либо поля? Можно точно сказать, что значение поля third не было потеряно.

В PostgreSQL есть разделяемые блокировки на уровне строк (FOR NO KEY UPDATE). Блокировка может устанавливаться командой UPDATE. Также есть привилегии на обновление и выборку отдельных столбцов таблицы. Для увеличения гранулярности блокировок можно было пойти дальше и дать возможность транзакциям, меняющим разные столбцы, обновлять совместно строку, но это не реализовано и вряд ли будет реализовано. Почему? Это бы привело к аномалиям. Первая транзакция меняет одно поле. Вторая транзакция начинается позже, меняет второе поле и фиксируется. Первая транзакция не должна видеть изменения второй транзакции, так как вторая транзакция началась позже. Но первая транзакция не должна потерять изменения второй транзакции. Более того, два поля могут быть логически связаны и их нельзя менять  отдельно друг от друга. В настоящее время аномалий нет по той причине, что вторая транзакция не может зафиксироваться раньше первой, так как она не может получить блокировку: блокировка FOR NO KEY UPDATE совместима только с блокировкой FOR KEY SHARE.

Уровень изоляции SERIALIZABLE можно представить так: при написании команд в транзакции можно считать, что кроме этой транзакции данные никто не меняет — как будто нет параллельных сессий. Недостаток этого уровня в том, что вероятность того, что на любую из команд в транзакции этого уровня (даже SELECT) будет выдана ошибка «could not serialize access» довольно высока. Транзакция переходит в состоянии сбоя, нужно закрыть транзакцию и повторить заново надеясь, что с какой-то попытки транзакция сможет зафиксироваться. Второе неудобство этого уровня изоляции: все транзакции в базе данных, работающие с данными, к которым получает доступ (читает и меняет) транзакция должны работать на уровне SERIALIZABLE. В противном случае, отсутствие аномалий не гарантируется. Гарантировать, что во всех сессиях используется этот уровень сложно.

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

Заключение

В статье рассмотрен пример задачи «списания средств». Задача примечательна тем, что легко совершить ошибку при написании кода транзакций из-за аномалии потерянного обновления. Задача простая, но как решение часто предлагают использовать строгие уровни изоляции транзакций. В статье рассмотрен пример использования ограничений целостности на примере CHECK, использование которого позволяет решать задачу на уровне изоляции READ COMMITED.