Работа с транзакциями для обеспечения целостности данных

Давайте представим, что вы пишете приложение для интернет-магазина, и во время оплаты заказа вам нужно:

1. Удержать деньги с карты клиента.
2. Уменьшить количество товара на складе.
3. Создать запись об успешной транзакции.

Что будет, если посреди этих действий что-то пойдет не так? Например, товар на складе закончился после того, как деньги были удержаны, но до создания записи о заказе? Всё пойдет кувырком: деньги "зависли", заказ не завершен, а ваш сервер получает тонны гневных писем (и, возможно, судебных исков).

Транзакции как раз помогают избежать таких ситуаций. Они позволяют сгруппировать несколько операций в одну "атомарную" единицу работы с базой данных. Это как кнопка "Отмена" в текстовом редакторе: если что-то пошло не так, просто откатитесь к началу.

Как транзакции обеспечивают целостность данных?

Транзакции основаны на концепции ACID:

• Атомарность (Atomicity) — Все операции внутри транзакции выполняются либо полностью, либо ничего не выполняется. "Всё или ничего".
• Согласованность (Consistency) — Данные остаются в согласованном состоянии до и после выполнения транзакции.
• Изоляция (Isolation) — Одна транзакция не мешает другим.
• Долговечность (Durability) — Когда транзакция завершена, её результат сохранен даже в случае сбоя системы.

Почему я это опять повторяю? Потому что это тот идеал, к которому все стремятся. И... который редко достижим. Когда мы второй раз вернемся к транзакциям в нашем курсе, вы поймете, что некоторыми ACID-принципами нам придется пожертвовать.

Так что наслаждайтесь временем, когда транзакции такие простые и красивые. И давайте уже перейдем к примерам!

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

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

Допустим, мы работаем с базой данных университета. У нас появились внештатные слушатели наших курсов. Если на курс есть место, тогда мы такого слушателя регистрируем как студента (временно) и добавляем на курс. Вот как это будет происходить.

При добавлении нового студента в базу и его регистрации на курс нам нужно:

1. Добавить запись в таблицу students.
2. Создать запись в таблице enrollments, связывающей студента с курсом.

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

-- Начало транзакции
BEGIN;

-- Шаг 1: Добавляем студента
INSERT INTO students (name, age, gender)
VALUES ('Otto Lin', 20, 'Male')
RETURNING id;

-- Допустим, вернулся id = 10

-- Шаг 2: Регистрируем его на курс
INSERT INTO enrollments (student_id, course_id)
VALUES (10, 5);

-- Всё прошло успешно? Фиксируем изменения
COMMIT;

Что происходит, если возникает ошибка?

Вдруг произошла ошибка при регистрации на курс: например, курс не существует. Если вы забудете про транзакцию, то запись о студенте в таблице students останется, а в таблице enrollments — нет. Это нарушает целостность данных. Чтобы этого избежать, мы можем использовать команду ROLLBACK.

-- Начало транзакции
BEGIN;

-- Шаг 1: Добавляем студента
INSERT INTO students (name, age, gender)
VALUES ('Otto Lin', 20, 'Male')
RETURNING id;

-- Шаг 2: Пытаемся зарегистрировать его на курс
INSERT INTO enrollments (student_id, course_id)
VALUES (10, 999); -- Ошибка: курса с id = 999 не существует!

-- Откатываем все изменения
ROLLBACK;

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

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

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

Давайте реализуем пошаговую регистрацию студента

-- Начало транзакции
BEGIN;

-- Добавляем студента
SAVEPOINT add_student; -- Создаём точку сохранения
INSERT INTO students (name, age, gender)
VALUES ('Anna Song', 22, 'Female');

-- Регистрируем её на первый курс
SAVEPOINT enroll_course_1; -- Ещё одна точка сохранения
INSERT INTO enrollments (student_id, course_id)
VALUES (11, 5);

-- Регистрируем её на второй курс (ошибка здесь)
INSERT INTO enrollments (student_id, course_id)
VALUES (11, 999); -- Ошибка!

-- Откатываемся только к последней точке сохранения
ROLLBACK TO enroll_course_1;

-- Восстанавливаем процесс
INSERT INTO enrollments (student_id, course_id)
VALUES (11, 6);

-- Фиксируем изменения
COMMIT;

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

Проверка на наличие изменений

Если SQL-запрос что-то меняет, то есть возможность проверить, были реально какие-то изменения или нет.

Может же быть ситуация, когда мы выполняли DELETE, но ни одна строка не попала под WHERE. Или выполняли UPDATE, а данные уже были изменены и по факту ничего не поменялось.

На этот случай есть специальная системная переменная FOUND. Которая указывает, были ли затронуты строки в последнем SQL-запросе:

• FOUND = TRUE — запрос что-то обновил/удалил;
• FOUND = FALSE — ничего не удалено или не изменено.

С обычным SELECT она не работает, только для отслеживания изменений.

Практическое применение: обработка платежей

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

-- Начало транзакции
BEGIN;

-- Шаг 1: Снимаем деньги с первого счета
UPDATE accounts
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;

-- Шаг 2: Проверяем, что операция успешна (строки были изменены)
IF NOT FOUND THEN
    ROLLBACK; -- Откат, если средств недостаточно
    RAISE EXCEPTION 'Недостаточно средств!'; -- Ошибка! Кидаем исключение
END IF;

-- Шаг 3: Добавляем деньги на второй счет
UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

-- Фиксируем транзакцию
COMMIT;

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

Особенности и типичные ошибки

Забыт COMMIT: если в конце транзакции забыть выполнить COMMIT, база данных будет "жить в ожидании", а изменения не сохранятся.

Забыт WHERE: обновление или удаление данных без условия может привести к катастрофическим последствиям. Например, команда DELETE FROM students без WHERE удалит всех студентов.

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

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

2

Задача

SQL SELF, 22 уровень, 2 лекция

Недоступна

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

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

Открыть