МДК.11.01 - 14 - Введение в транзакции

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

Как правило, для поддержания целостности данных в реляционных СУБД используют механизм транзакций. Транзакция в базе данных — это одна или несколько последовательных операций на языке SQL, представляющих собой единую логическую задачу. Транзакция представляет собой неделимое действие, то есть она должна быть выполнена как единое целое и результат должен быть либо записан в базу данных целиком, либо не должен быть записан ни один из его компонентов. Каждая транзакция рассматривается как внутренне связный, надежный и независимый от других транзакций элемент.

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

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

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

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

3. Изолированность (Isolation): Каждая транзакция должна быть выполнена изолированно от других транзакций. Это означает, что результаты одной транзакции не должны быть видны другим транзакциям до тех пор, пока первая транзакция не будет завершена.

4. Долговечность (Durability): Результаты успешно завершенной транзакции должны быть постоянными и доступными даже в случае сбоя системы. Это достигается путем записи изменений в постоянное хранилище, такое как жесткий диск.

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

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

Примеры использования транзакций

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

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;

Пример без использования транзакций

Пример работы в данной БД для ситуаций, когда Иванов переводит Петрову деньги с основного счета на основной:

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

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

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

Пример работы с транзакциями в данной БД для ситуаций, когда Иванов переводит Петрову деньги:

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

START TRANSACTION;

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

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

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

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

4. Выполняем перевод средств от Иванова к Петрову с основного счета на основной:

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

5. Если всё прошло по плану то фиксируем изменения:

COMMIT;

Но если необходимо откатить транзакцию в случае ошибки или недостатка средств:

ROLLBACK;

Примечание: В данном примере предполагается, что все операции выполняются в одном соединении с базой данных.