Транзакции MySQL и целостность данных

Введение

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

Вы попрактикуетесь в запуске транзакции с помощью BEGIN, фиксации изменений с помощью COMMIT, отмене изменений с помощью ROLLBACK и выполнении частичных откатов с использованием SAVEPOINT. К концу этой лабораторной работы вы поймете, как безопасно и надежно управлять операциями с базой данных.

Настройка базы данных и таблиц

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

Сначала откройте терминал на вашем рабочем столе.

Подключитесь к серверу MySQL от имени пользователя root. В этой лабораторной среде пароль для пользователя root не требуется.

sudo mysql -u root

После подключения вы увидите приглашение MySQL (mysql>), которое означает, что вы можете выполнять SQL-команды.

Создайте базу данных bank_db и переключитесь на нее для текущей сессии. Предложение IF NOT EXISTS предотвращает ошибку, если база данных уже существует.

CREATE DATABASE IF NOT EXISTS bank_db;
USE bank_db;

Далее создайте таблицу accounts. Эта таблица будет хранить информацию об учетных записях, включая идентификатор, номер счета и баланс.

CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

Теперь вставьте две примерные учетные записи в таблицу accounts. Они послужат исходными данными.

INSERT INTO accounts (account_number, balance) VALUES
('ACC001', 1000.00),
('ACC002', 500.00);

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

SELECT * FROM accounts;

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

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         | 1000.00 |
|  2 | ACC002         |  500.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

После подготовки базы данных и таблицы вы готовы приступить к изучению транзакций.

Создание успешной транзакции с помощью BEGIN и COMMIT

Транзакция — это последовательность операций, выполняемых как единое логическое целое. Чтобы сделать изменения постоянными, вы должны их COMMIT (зафиксировать). На этом шаге вы смоделируете успешный банковский перевод с ACC001 на ACC002.

Вы должны все еще находиться в оболочке MySQL из предыдущего шага.

Сначала начните новую транзакцию с помощью команды BEGIN. Все последующие команды будут частью этой транзакции, пока вы не выполните COMMIT или ROLLBACK.

BEGIN;

Теперь выполните две операции для перевода: спишите 100 долларов с ACC001 и добавьте 100 долларов на ACC002.

UPDATE accounts SET balance = balance - 100.00 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 100.00 WHERE account_number = 'ACC002';

На данном этапе изменения видны только в вашей текущей сессии. Вы можете проверить временное состояние учетных записей:

SELECT * FROM accounts;

Вывод покажет обновленные балансы, но они еще не сохранены постоянно.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

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

COMMIT;

Команда COMMIT сохраняет все изменения, сделанные во время транзакции. Перевод теперь завершен и виден всем другим подключениям к базе данных. Вы можете проверить это, снова выполнив запрос к таблице. Балансы останутся на уровне 900 и 600 долларов.

Отмена транзакции с помощью ROLLBACK

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

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

BEGIN;

Теперь вставьте новую учетную запись ACC003 в таблицу accounts.

INSERT INTO accounts (account_number, balance) VALUES ('ACC003', 150.00);

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

SELECT * FROM accounts;

Вы увидите три перечисленные учетные записи.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
|  3 | ACC003         |  150.00 |
+----+----------------+---------+
3 rows in set (0.00 sec)

Теперь представьте, что вы поняли, что эту учетную запись не следовало добавлять. Вместо фиксации (COMMIT) вы можете отменить всю транзакцию с помощью ROLLBACK.

ROLLBACK;

Эта команда отменяет операцию INSERT. Чтобы проверить это, снова выполните запрос к таблице accounts.

SELECT * FROM accounts;

Учетная запись ACC003 исчезнет, и таблица вернется в состояние, которое было до ввода команды BEGIN.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  900.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

ROLLBACK является критически важным инструментом для поддержания целостности данных при возникновении ошибок.

Использование SAVEPOINT для частичных откатов

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

Давайте начнем новую транзакцию.

BEGIN;

Сначала обновите баланс ACC001, увеличив его на 50 долларов.

UPDATE accounts SET balance = balance + 50.00 WHERE account_number = 'ACC001';

Теперь создайте точку сохранения (savepoint). Это действует как закладка для текущего состояния транзакции.

SAVEPOINT after_update;

Далее выполните еще одну операцию: вставьте новую учетную запись ACC004.

INSERT INTO accounts (account_number, balance) VALUES ('ACC004', 300.00);

Предположим, что вставка ACC004 была ошибкой, но обновление ACC001 было правильным. Вы можете откатиться к точке сохранения (savepoint), чтобы отменить только оператор INSERT.

ROLLBACK TO after_update;

Эта команда возвращает транзакцию к состоянию, в котором она находилась при создании точки сохранения after_update. Вставка ACC004 отменяется, но обновление ACC001 остается.

Выполните запрос к таблице для подтверждения. ACC004 должен исчезнуть, а баланс ACC001 должен быть обновлен.

SELECT * FROM accounts;

Вывод должен показать ACC001 с балансом 950.00 долларов и без ACC004.

+----+----------------+---------+
| id | account_number | balance |
+----+----------------+---------+
|  1 | ACC001         |  950.00 |
|  2 | ACC002         |  600.00 |
+----+----------------+---------+
2 rows in set (0.00 sec)

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

COMMIT;

Вы успешно использовали точку сохранения (savepoint) для выполнения частичного отката. Теперь вы можете выйти из оболочки MySQL.

exit;

Резюме

В этой лабораторной работе вы изучили основы транзакций MySQL для поддержания целостности данных. Вы отработали полный жизненный цикл транзакции: от ее начала с помощью BEGIN до завершения с помощью COMMIT или отмены с помощью ROLLBACK.

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