Создание транзакций

Если вы попытаетесь перевести 1000 долларов с вашего сберегательного счёта на текущий и вдруг обнаружите, что деньги были списаны, но не зачислены на текущий счёт, вы, скорее всего, расстроитесь. 😿

Для защиты от такого рода ошибок программа, обрабатывающая ваш запрос на перевод денег, сначала начинает транзакцию, затем запускает SQL-запросы, необходимые для перевода денег с одного счёта на другой, и, если все проходит успешно, завершает транзакцию, выполнив команду COMMIT — фиксации изменений.

Однако, если возникнут какие-либо проблемы, будет выполнена команда ROLLBACK, которая указывает серверу отменить все действия, совершённые с начала транзакции.

Процесс может выглядеть следующим образом:

MySQL
-- Начало транзакции
START TRANSACTION;

-- Проверка наличия достаточного баланса у отправителя
SELECT @balance := user_balance FROM accounts WHERE user_id = 1;

-- Если средств недостаточно, отмена транзакции
IF @balance < 1000 THEN
ROLLBACK;
END IF;

-- Проверка на существование получателя
SELECT @exists := COUNT(*) FROM accounts WHERE user_id = 2;
IF @exists = 0 THEN
ROLLBACK;
END IF;

-- Обновление баланса счетов, если все проверки пройдены
UPDATE accounts SET user_balance = user_balance - 1000 WHERE user_id = 1;
UPDATE accounts SET user_balance = user_balance + 1000 WHERE user_id = 2;

-- Применение изменений
COMMIT;

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

Запуск и завершение транзакций

Каждая явная транзакция в MySQL начинается с использования оператора START TRANSACTION.

Завершение же транзакции возможно:

• с помощью команды COMMIT, которая даёт указание серверу пометить изменения как постоянные и освободить все ресурсы (т.е. блокировки строк), использовавшиеся во время транзакции
• с помощью команды ROLLBACK, которая требует от сервера вернуть данные в состояние до начала транзакции. После завершения отката также любые ресурсы, используемые транзакцией, освобождаются.

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

Точки сохранения транзакции

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

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

MySQL
SAVEPOINT my_savepoint;

Для отката к определённой точке сохранения просто вводится команда ROLLBACK, за которой следуют ключевые слова TO SAVEPOINT и имя точки сохранения, например:

MySQL
START TRANSACTION;

-- Создаем точку сохранения перед изменением баланса первого пользователя
SAVEPOINT before_updating_user_1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;

-- Проверка условия для первого пользователя
-- например, проверяем логику бизнес-правил

-- Здесь мы предполагаем, что условие не выполнилось, и нам нужно отменить изменение баланса
ROLLBACK TO SAVEPOINT before_updating_user_1;

-- Обновляем баланс для второго пользователя
UPDATE accounts SET balance = balance + 200 WHERE user_id = 2;

-- Завершаем транзакцию
COMMIT;

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

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

• Несмотря на название, при создании точки сохранения ничего не сохраняется. Чтобы ваши изменения в рамках транзакции стали постоянными, необходимо выполнить команду COMMIT.
• При выполнении отката транзакции без указания конкретной точки сохранения, все ранее установленные точки сохранения будут проигнорированы, и будет произведён откат всей транзакции.