Транзакции

Понятие транзакции и ее свойства

Транзакции - это фундаментальная концепция баз данных, обеспечивающая целостность данных.

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

Стандартно принято выделять 4 базовых свойства транзакций, сокращенно ACID-свойства:

  • Атомарность (Atomicity) - транзакция выполняется полностью или не выполняется вообще
  • Согласованность (Consistency) - транзакция переводит БД из одного согласованного состояния в другое
  • Изолированность (Isolation) - параллельные транзакции не мешают друг другу
  • Долговечность (Durability) - результаты завершенной транзакции сохраняются даже после сбоев

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

CREATE TABLE ACCOUNTS (
   ACCOUNT_ID VARCHAR(20) PRIMARY KEY,
   OWNER_NAME VARCHAR(100) NOT NULL,
   BALANCE DECIMAL(15,2) NOT NULL CHECK (BALANCE >= 0)
);

-- Инициализируем тестовые данные
INSERT INTO ACCOUNTS VALUES ('10000001', 'Иванов И.И.', 5000.00);
INSERT INTO ACCOUNTS VALUES ('10000002', 'Петров П.П.', 3000.00);

CREATE PROCEDURE TRANSFER (
    FROM_ACCOUNT VARCHAR(20),
    TO_ACCOUNT VARCHAR(20),
    AMOUNT DECIMAL(15,2))
RETURNS (RES BOOLEAN)
AS
BEGIN
    RES = FALSE;
    UPDATE ACCOUNTS 
        SET BALANCE = BALANCE - :AMOUNT 
        WHERE ACCOUNT_ID = :FROM_ACCOUNT AND BALANCE >= :AMOUNT;
    IF (ROW_COUNT = 0) THEN
    BEGIN
        SUSPEND;
        EXIT;
    END
    -- а что если здесь отключат свет?
    UPDATE ACCOUNTS SET BALANCE = BALANCE + :AMOUNT WHERE ACCOUNT_ID = :TO_ACCOUNT;
    RES = TRUE;
    SUSPEND;
END;

SELECT * FROM TRANSFER('10000001', '10000002', 10000.00);

SELECT * FROM ACCOUNTS;

В примере мы видим комментарий. А что случится, если в этом месте действительно отключат электричество, произойдет какой-либо сбой, аппаратная ошибка? В промышленных транзакционных СУБД эти ситуации и обрабатывает сервер. Если в этом месте произойдет сбой, то в дальнейшем, когда БД снова станет доступна, она будет содержать данные, как если бы процедуру TRANSFER не вызывали.

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

Аномалии параллельного доступа

  1. Потерянное обновление. Возникает при одновременном изменении одного блока данных разными транзакциями. При этом теряются все изменения, кроме последнего.
Транзакция 1Транзакция 2
UPDATE T SET F=F+10 WHERE K=1UPDATE T SET F=F+15 WHERE K=1

При последовательном выполнении транзакций, значение поля F изменилось бы на 25. Но при параллельном доступе, обе транзакции могут прочитать старое значение F, добавить свое слагаемое и записать новый результат. В результате, новое значение F будет увеличено или на 10 или на 15, в зависимости от того, какая из транзакций будет подтверждена последней и затрет изменения параллельной транзакции.

  1. Грязное чтение (Dirty Read). Чтение незафиксированных изменений другой транзакции.
Транзакция 1Транзакция 2
UPDATE T SET F=F+1 WHERE K=1
SELECT F FROM T WHERE K=1
ROLLBACK

Транзакция 1 изменяет данные поля F, после транзакция 2 его читает, использует далее в своей работе, а после транзакция 1 не подтверждается и получится, что прочтенные транзакцией 1 незафиксированные данные никогда не существовали, но были использованы при работе.

  1. Неповторяющееся чтение (Non-repeatable Read). Разные значения при повторном чтении одной строки.
Транзакция 1Транзакция 2
SELECT F FROM T WHERE K=1
UPDATE T SET F=F+2 WHERE K=1
COMMIT
SELECT F FROM T WHERE K=1

В транзакции 2 выбирается значение поля F, затем в транзакции 1 изменяется значение поля F. При повторной попытке выбора значения из поля F в транзакции 2 будет получен другой результат.

  1. Фантомное чтение (Phantom Read). Появление новых строк при повторном чтении диапазона.
Транзакция 1Транзакция 2
SELECT SUM(F) FROM T
INSERT INTO T (K, F) VALUES (2, 30)
COMMIT
SELECT SUM(F) FROM T

Транзакция 2 выполняет запрос, использующий все значения поля F. Затем транзакция 1 вставляет новую строку. Повторное выполнение запроса в транзакции 2 выдаст другой результат. От неповторяющегося чтения оно отличается тем, что данные, к которым было обращение, не изменились, но изменилось количество строк с данными.

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

Уровень изоляции определяет, насколько транзакции изолированы друг от друга и какие аномалии возможны при параллельном выполнении.

Стандарт SQL определяет 4 основные уровни изоляции:

  1. READ UNCOMMITTED (Чтение незафиксированных данных)

    • Самый низкий уровень изоляции
    • Возможны все типы аномалий:

  2. READ COMMITTED (Чтение зафиксированных данных)

    • По умолчанию в большинстве СУБД
    • Запрещает:
      • Потерянное обновление
      • Грязное чтение
    • Разрешает:
      • Неповторяющееся чтение
      • Фантомное чтение

  3. REPEATABLE READ (Повторяемое чтение)

    • Запрещает:
      • Грязное чтение
      • Неповторяющееся чтение
      • Потерянное обновление
    • Разрешает:
      • Фантомное чтение

  4. SERIALIZABLE (Упорядочиваемый)

    • Самый строгий уровень
    • Запрещает все аномалии
    • Эквивалентен последовательному выполнению транзакций

Исторически Firebird и РЕД База Данных реализует следующие уровни изоляции:

  1. READ COMMITTED. В полном объеме соответствует стандартному.

  2. SNAPSHOT. Практически аналог REPEATABLE READ, но запрещает также и фантомное чтение. Все операции видят согласованный снимок данных на момент начала транзакции.

  3. SNAPSHOT TABLE STABILITY. Практически аналог SERIALIZABLE. Блокирует читаемые таблицы от изменений другими транзакциями.

Старт транзакции

Для старта транзакции с необходимыми параметрами используется оператор SET TRANSACTION.

Запуск транзакций на выполнение осуществляется только клиентскими приложениями, но не сервером.

SET TRANSACTION
    [READ WRITE | READ ONLY]
    [ISOLATION LEVEL] [SNAPSHOT [TABLE STABILITY] | READ COMMITTED]

Все предложения в операторе SET TRANSACTION являются необязательными. Если в операторе запуска транзакции на выполнение не задано никакого предложения, то предполагается старт транзакции со значениями всех характеристик по умолчанию.

SET TRANSACTION
    READ WRITE
    ISOLATION LEVEL SNAPSHOT

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

При старте транзакции сервер передает клиенту дескриптор транзакции (целое число). Значение этого дескриптора средствами SQL можно получить, используя контекстную переменную CURRENT_TRANSACTION.

Для транзакций существует два режима доступа к данным базы данных: READ WRITE (по умолчанию) и READ ONLY.

В режиме READ ONLY в контексте данной транзакции могут выполняться только операции выборки данных SELECT. Любая попытка изменения данных в контексте такой транзакции приведет к исключениям базы данных.

Уровень изоляции запускаемой транзакции задается необязательным предложением ISOLATION LEVEL. Это самая важная характеристика транзакции, которая определяет ее основное поведение по отношению к другим одновременно выполняющимся транзакциям.

Подтверждение транзакции

Чтобы подтвердить текущую транзакцию используется оператор COMMIT.

COMMIT;

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

Отмена транзакции

Для отмены всех изменений, выполненных в контексте текущей транзакции, используется оператор ROLLBACK.

ROLLBACK;

При выполнении оператора отменяются все изменения данных базы данных (добавление, изменение, удаление), выполненные под управлением этой транзакции. Оператор ROLLBACK никогда не вызывает ошибок.

Контрольные вопросы

  1. Что такое транзакция?
  2. Назовите свойства транзакций?
  3. Назовите аномалии параллельного доступа к данным?
  4. Чем фантомное чтение отличается от неповторяющегося чтения?
  5. Назовите уровни изоляции транзакций?
  6. Какие уровни изоляции транзакций поддерживает РЕД База Данных?
  7. Какие аномалии параллельного доступа разрешены на каждом из них?
  8. Какие аномалии параллельного доступа запрещены на каждом из них?
  9. Какие параметры транзакций допустимы?
  10. Что произойдет, если при подтверждении транзакции возникнут ошибки?
  11. Что произойдет, если при отмене транзакции возникнут ошибки?