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

Введение

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

В рамках практического занятия рассматриваются следующие аспекты: начало транзакции с помощью команды BEGIN, вставка данных и фиксация (коммит) транзакции для сохранения изменений навсегда, откат неудачного обновления для отмены изменений и использование точек сохранения (savepoints) для частичного отката внутри транзакции. Вы подключитесь к серверу MySQL, создадите базу данных и таблицу, а затем потренируетесь в выполнении операций, связанных с транзакциями.

Skills Graph

Начало транзакции с помощью команды BEGIN

На этом этапе вы узнаете, как начать транзакцию в MySQL с использованием оператора BEGIN. Транзакции - это фундаментальное понятие в управлении базами данных, обеспечивающее целостность и согласованность данных. Транзакция представляет собой последовательность одной или нескольких SQL-операций, рассматриваемую как единый блок работы. Либо все операции в рамках транзакции завершаются успешно, либо ни одна из них не выполняется.

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

Для начала транзакции в MySQL используется оператор BEGIN (или его синоним START TRANSACTION). Этот оператор сигнализирует о начале новой транзакции. Любые последующие SQL-операторы станут частью этой транзакции до тех пор, пока вы не COMMIT (фиксируете) изменения (сделаете их постоянными) или не ROLLBACK (откатываете) изменения (отбросите их).

Начнем с подключения к серверу MySQL. Откройте терминал и выполните следующую команду:

mysql -u root -p

Вам будет предложено ввести пароль для пользователя root. Введите пароль, который вы установили при установке MySQL. Если вы не установили пароль, просто нажмите Enter.

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

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;
CREATE TABLE IF NOT EXISTS accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_number VARCHAR(50) NOT NULL,
    balance DECIMAL(10, 2) NOT NULL
);

Эти команды создают базу данных с именем labex_db и таблицу с именем accounts в этой базе данных. Таблица accounts имеет три столбца: id, account_number и balance.

Далее, вставим некоторые начальные данные в таблицу accounts:

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

Теперь начнем транзакцию. Выполните следующий оператор:

BEGIN;

Эта команда запускает новую транзакцию. Любые последующие SQL-операторы станут частью этой транзакции.

Для проверки того, что транзакция началась, можно выполнить простой оператор обновления. Например, уберем 100 с счета 'ACC001':

UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';

Теперь проверим баланс счета 'ACC001':

SELECT * FROM accounts WHERE account_number = 'ACC001';

Вы должны увидеть, что баланс счета 'ACC001' был обновлен до 900.00. Однако эти изменения еще не стали постоянными. Они видны только в рамках текущей транзакции. Чтобы сделать изменения постоянными, вам нужно будет COMMIT (фиксировать) транзакцию, о чем вы узнаете на следующем этапе. Если вы закроете соединение с MySQL сейчас, не зафиксировав или не откатив изменения, они будут потеряны.

Вставка данных и фиксация (коммит) транзакции

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

Продолжая с того места, где вы остановились на предыдущем этапе, вы должны быть по-прежнему подключены к серверу MySQL и иметь активную транзакцию. Если это не так, подключитесь к серверу MySQL снова с помощью следующей команды:

mysql -u root -p

Введите пароль, если вас об этом попросят, а затем выберите базу данных labex_db:

USE labex_db;

Если у вас нет активной транзакции, начните новую:

BEGIN;

Теперь вставим новую запись в таблицу accounts в рамках текущей транзакции:

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

Эта команда вставляет новый счет с номером 'ACC003' и балансом 200.00.

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

SELECT * FROM accounts WHERE account_number = 'ACC003';

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

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

COMMIT;

Эта команда фиксирует транзакцию, делая вставленные данные постоянными.

Для проверки того, что изменения были зафиксированы, выполните тот же запрос еще раз:

SELECT * FROM accounts WHERE account_number = 'ACC003';

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

Вы также можете проверить изменения, выбрав все записи из таблицы accounts:

SELECT * FROM accounts;

Вы должны увидеть все три счета: 'ACC001', 'ACC002' и 'ACC003' с соответствующими балансами. Баланс счета 'ACC001' должен быть 900.00 (если вы выполнили оператор обновления на предыдущем этапе и не откатили изменения).

В резюме, на этом этапе показано, как вставить данные в рамках транзакции и затем зафиксировать транзакцию, чтобы изменения стали постоянными. Оператор COMMIT является ключевым для того, чтобы все операции в рамках транзакции были применены к базе данных.

Откат неудачного обновления

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

Продолжая с предыдущего этапа, вы должны быть по-прежнему подключены к серверу MySQL. Если это не так, подключитесь снова с помощью следующей команды:

mysql -u root -p

Введите пароль, если вас об этом попросят, а затем выберите базу данных labex_db:

USE labex_db;

Теперь начнем новую транзакцию:

BEGIN;

Проведем имитацию неудачного обновления. Предположим, что вы хотите перевести 500 с счета 'ACC001' на счет 'ACC004', но счет 'ACC004' не существует. Сначала попробуем списать 500 с счета 'ACC001':

UPDATE accounts SET balance = balance - 500 WHERE account_number = 'ACC001';

Теперь попробуем добавить 500 на счет 'ACC004':

UPDATE accounts SET balance = balance + 500 WHERE account_number = 'ACC004';

Вероятно, это обновление не повлияет на ни одну строку, так как счет 'ACC004' не существует. Вы можете проверить это, посмотрев количество затронутых строк:

SELECT ROW_COUNT();

Результат должен быть равен 0, что означает, что ни одна строка не была обновлена.

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

Выполните следующую команду:

ROLLBACK;

Эта команда откатывает транзакцию, отменяя списание 500 с счета 'ACC001'.

Для проверки того, что откат был успешным, проверьте баланс счета 'ACC001':

SELECT * FROM accounts WHERE account_number = 'ACC001';

Баланс счета 'ACC001' должен вернуться к исходному значению (1000, если вы начали с самого начала, или 900, если вы только зафиксировали первое обновление на первом этапе).

Вы также можете проверить, что счет 'ACC003' по-прежнему существует и имеет баланс 200.00 (если вы завершили второй этап).

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

Использование точек сохранения (сэйвпоинтов) для частичного отката

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

Продолжая с предыдущего этапа, вы должны быть по-прежнему подключены к серверу MySQL. Если это не так, подключитесь снова с помощью следующей команды:

mysql -u root -p

Введите пароль, если вас об этом попросят, а затем выберите базу данных labex_db:

USE labex_db;

Теперь начнем новую транзакцию:

BEGIN;

Сначала создадим точку сохранения с именем savepoint1:

SAVEPOINT savepoint1;

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

Теперь обновим баланс счета 'ACC001':

UPDATE accounts SET balance = balance - 200 WHERE account_number = 'ACC001';

Далее создадим еще одну точку сохранения с именем savepoint2:

SAVEPOINT savepoint2;

Теперь вставим новую запись в таблицу accounts:

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

Предположим, что вы решили отменить вставку записи о счете 'ACC004', но сохранить обновление баланса счета 'ACC001'. Вы можете откатиться к точке сохранения savepoint2:

ROLLBACK TO savepoint2;

Эта команда откатывает транзакцию до состояния, которое было на момент создания точки сохранения savepoint2, тем самым отменяя вставку записи о счете 'ACC004'.

Для проверки того, что откат до точки сохранения savepoint2 был успешным, проверим, существует ли счет 'ACC004':

SELECT * FROM accounts WHERE account_number = 'ACC004';

Этот запрос не должен вернуть ни одной строки, что означает, что запись о счете 'ACC004' была удалена.

Теперь проверим баланс счета 'ACC001':

SELECT * FROM accounts WHERE account_number = 'ACC001';

Баланс счета 'ACC001' должен быть обновлен (уменьшен на 200).

Если вы удовлетворены оставшимися изменениями (обновлением баланса счета 'ACC001'), вы можете зафиксировать транзакцию:

COMMIT;

Эта команда фиксирует транзакцию, делая обновление баланса счета 'ACC001' постоянным.

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

Резюме

В этом практическом занятии вы изучили основы транзакций в MySQL и их важность для обеспечения целостности данных. Практикум начался с демонстрации того, как начать транзакцию с использованием оператора BEGIN (или START TRANSACTION). Затем вы создали базу данных с именем labex_db и таблицу accounts в ней, чтобы подготовиться к последующим операциям с транзакциями.

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