МДК.11.01 - 15 - Расширенные транзакции

Примеры данной темы используют учебную БД:

SET foreign_key_checks = 0;

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS accounts;

CREATE TABLE `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
);

LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES (1,'Иванов Иван Иванович'),(2,'Петров Петр Петрович'),(3,'Сидоров Сидор Сидорович');
UNLOCK TABLES;

CREATE TABLE `accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) DEFAULT NULL,
  `user_id` INT NOT NULL,
  `balance` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

LOCK TABLES `accounts` WRITE;
INSERT INTO `accounts` VALUES (1,'Основной',1,10500.00),(2,'Резервный',1,3412.57),(3,'Основной',2,20750.00),(4,'Основной',3,25000.00),(5,'Накопительный',2,5401.75);
UNLOCK TABLES;

SET foreign_key_checks = 1;

Уровни изоляции транзакций

В MySQL 8 существует четыре уровня изоляции транзакций: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ и SERIALIZABLE.

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

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

START TRANSACTION;
UPDATE accounts SET balance = balance + 1000 WHERE id = 1;

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

START TRANSACTION;
UPDATE accounts SET balance = balance + 250 WHERE id = 1;
COMMIT;

Уровни изоляции позволяют настроить баланс между производительностью и надежностью транзакций в зависимости от требований приложения.

1. READ UNCOMMITTED (неподтвержденное чтение): Этот уровень позволяет одной транзакции читать неподтвержденные изменения, внесенные другой транзакцией.

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Если транзакция 1 выполняется в режиме READ UNCOMMITTED, она сможет прочитать изменения, внесенные транзакцией 2, даже до ее подтверждения.

2. READ COMMITTED (подтвержденное чтение): Этот уровень позволяет транзакции читать только подтвержденные изменения других транзакций.

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

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Если транзакция 1 выполняется в режиме READ COMMITTED, она не сможет прочитать изменения, внесенные транзакцией 2, до ее подтверждения.

3. REPEATABLE READ (повторяемое чтение): Этот уровень гарантирует, что транзакция будет видеть одни и те же данные при повторном чтении в рамках одной транзакции.

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

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Если транзакция 1 выполняется в режиме REPEATABLE READ, она будет видеть исходные данные при повторном чтении, даже после подтверждения изменений транзакцией 2.

4. SERIALIZABLE (сериализуемое чтение): Этот уровень гарантирует полную изоляцию транзакций, предотвращая конфликты и аномалии.

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

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

Возможные аномалии по уровням

• Потерянное обновление — Ситуация, когда при одновременном изменении одного блока данных разными транзакциями одно из изменений теряется.

• «Грязное» чтение — Чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится).

• Неповторяющееся чтение — Ситуация, когда при повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.

• Чтение «фантомов» — Ситуация, когда при повторном чтении в рамках одной транзакции одна и та же выборка дает разные множества строк.

Уровень изоляции по-умолчанию

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

SELECT @@global.transaction_isolation;

Этот запрос вернет текущее значение переменной transaction_isolation, которая указывает на уровень изоляции транзакций, используемый по умолчанию. Возможные значения переменной transaction_isolation включают READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE и NONE.

Работа с точками восстановления

Точка восстановления, или SAVEPOINT — это зафиксированное состояние БД к которому можно откатиться в рамках команды ROLLBACK.

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

1. Начинаем транзакцию:

START TRANSACTION;

2. Получаем текущий баланс Иванова и номер его основного счета:

SELECT id, balance FROM accounts WHERE user_id = 1 AND title = 'Основной';

3. Фиксируем состояния до того как начали перевод средств:

SAVEPOINT SavePoint1;

4. Переводим 5000 рублей с основного счёта Иванова на основной счёт Петрова:

UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 3;

5. Фиксируем состояние в этой точке:

SAVEPOINT SavePoint2;

6. Переводим 1500 рублей с основного счёта Иванова на основной счёт Сидорова:

UPDATE accounts SET balance = balance - 1500 WHERE id = 1;
UPDATE accounts SET balance = balance + 1500 WHERE id = 4;

7. Фиксируем состояние в этой точке:

SAVEPOINT SavePoint3;

8. Посмотрим сколько денег осталось на основном счёте Иванова:

SELECT id, balance FROM accounts WHERE user_id = 1 AND title = 'Основной';

На данный момент у нас есть возможность произвести следующие действия:

• Если всё прошло хорошо, и результаты всех действий нас устроили, можем зафиксировать результаты при помощи COMMIT;.
• Если нам нужно откатиться на состояние после того как мы перевели деньги Сидорову, можем выполнить ROLLBACK TO SavePoint3;.
• Если нам нужно откатиться на состояние после того как мы перевели деньги Петрову, но до того как мы начали переводить деньги Сидорову, можем выполнить ROLLBACK TO SavePoint2;.
• Если нам нужно откатиться на состояние до того как мы начали переводить деньги, можем выполнить ROLLBACK TO SavePoint1;.
• Если нужно вернуть БД в состояние как будто целой транзакции не было, используем просто ROLLBACK;, при этом промежуточные состояния точек восстановления учтены не будут.
• Если какая-либо из точек восстановления уже не нужна, очистить её из памяти можно при помощи команды RELEASE SAVEPOINT SavePoint1;.