Транзакции*

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

Транзакции имеют четыре основных свойства, известны как ACID:

• A (Atomicity) – атомарность: транзакция должна быть выполнена полностью или не быть выполнена вовсе; нельзя выполнить только часть операций.

• C (Consistency) – согласованность: после выполнения транзакции, база данных должна быть в консистентном состоянии; все данные должны быть корректными в соответствии с правилами.

• I (Isolation) – изолированность: для любых двух транзакций, которые выполняются одновременно, результаты должны быть такими же, как если бы они выполнялись последовательно.

• D (Durability) – устойчивость: если транзакция завершена успешно, ее изменения должны быть постоянными и не исчезать после завершения транзакции.

Использование транзакций

Для обработки транзакций используются следующие SQL-запросы:

• BEGIN – начать новую транзакцию.

• COMMIT – завершить транзакцию и подтвердить все изменения данных.

• ROLLBACK – отменить все изменения, произведенные транзакцией, и вернуться к состоянию базы данных до начала транзакции.

Пример использования транзакции для перевода денежных средств между двумя аккаунтами:

BEGIN;

-- Забрать деньги со счета A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Проверить, что баланс аккаунта A не отрицателен
SELECT balance FROM accounts WHERE id = 1;
-- Если баланс отрицателен, отменить транзакцию
ROLLBACK; 

-- Добавить деньги на счет B
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Если все операции успешны, подтвердить транзакцию
COMMIT;

Изоляция

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

Аномалии

Аномалии в SQL возникают из-за плохой изолированности между параллельно выполняющимися транзакциями. Давайте рассмотрим три основных типа аномалий и предоставим примеры для каждого из них.

1. Грязное чтение (Dirty Read)

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

Пример:

Транзакция A:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; // Списание средств со счета 1

Транзакция B:

BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; // Чтение текущего баланса счета 1 (грязное чтение, т.к. транзакция A не завершилась)

2. Неповторяемое чтение (Non-repeatable Read)

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

Пример:

Транзакция A:

BEGIN TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; // Получение баланса счета 1

Транзакция B:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; // Списание средств со счета 1
COMMIT;

Продолжение транзакции A:

SELECT balance FROM accounts WHERE id = 1; // Получение баланса счета снова (получаем другой результат)
COMMIT;

В данном примере результаты двух SELECT запросов в Транзакции A отличаются, то есть чтение стало "неповторяемым".

3. Фантомное чтение (Phantom Read)

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

Пример:

Транзакция A:

BEGIN TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000; // Получение всех счетов с балансом выше 1000

Транзакция B:

BEGIN TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (3, 1500); // Добавление нового счета с балансом выше 1000
COMMIT;

Транзакция A:

SELECT * FROM accounts WHERE balance > 1000; // Получение всех счетов с балансом выше 1000 снова (получаем другой результат)
COMMIT;

В этом случае последующий SELECT запрос в Транзакции A возвращает дополнительную строку, несмотря на то, что условия запроса не изменялись. Это и называется "фантомное чтение".

Уровни изоляций

В SQL-стандарте определены следующие четыре уровня изолированности транзакций:

• Read Uncommitted (неподтвержденное чтение): на этом уровне каждая транзакция может видеть результаты выполнения других незавершенных транзакций. Это самый низкий уровень изолированности, и он допускает все виды аномалий транзакций.

• Read Committed (подтвержденное чтение): на этом уровне транзакция может видеть только те изменения, которые были зафиксированы до ее начала. Данный уровень предотвращает грязное чтение, но неповторяемое чтение и фантомное чтение все еще возможны.

• Repeatable Read (повторяемое чтение): на этом уровне транзакция может видеть только те изменения, которые были сделаны до ее начала, и повторяющиеся запросы будут возвращать одинаковые результаты. Этот уровень предотвращает грязное чтение и неповторяемое чтение, но фантомное чтение все еще возможно.

• Serializable (сериализуемое): это самый высокий уровень изолированности, который гарантирует, что транзакции будут выполняться так, как будто они осуществляются последовательно. На этом уровне изолированности отсутствуют все виды аномалий.

Дополнительный материал

• Транзакции

• ACID