Транзакция/transaktsioonid – это последовательность операций, выполняемых в логическом порядке пользователем, либо программой, которая работает с БД.

Транзакция – это распространение изменений в БД. Например, если мы создаём, изменяем или удаляем запись, то мы выполняем транзакцию. Крайне важно контролировать транзакции для гарантирования.

Основные концепции транзакции описываются аббревиатурой ACID –

  • Atomicity – Атомарность
  • Consistency – Согласованность
  • Isolation – Изолированность
  • Durability – Долговечность

Для управления транзакциями используются следующие команды:

  • COMMIT
    Сохраняет изменения
  • ROLLBACK
    Откатывает (отменяет) изменения
  • SAVEPOINT
    Создаёт точку к которой группа транзакций может откатиться
  • SET TRANSACTION
    Размещает имя транзакции.

Команды управление транзакциями используются только для DML команд: INSERT, UPDATE, DELETE. Они не могут быть использованы во время создания, изменения или удаления таблицы.

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

Таким образом, SQL транзакция может также рассматриваться в качестве элемента восстановления.

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

Некоторые СУБД (SQL-сервер, MySQL/InnoDB, PostgreSQL) работают в режиме AUTOCOMMIT по умолчанию.

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

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

create database transactionTARgv24;
use transactionTARgv24;

create table T(
id int not null primary key,
s varchar(40),
si smallint)

insert into T(id, s) values(1, 'fisrt');
insert into T(id, s) values(2, 'second');
insert into T(id, s) values(3, 'third');
select * from T;

--tagasi votmine
rollback;


--alustame transaction
begin transaction; --xampp start transaction
insert into T(id, s) values(4, 'fourth');
select * from T;

--tagasi votmine
rollback;
select * from T;


begin transaction;
delete from T where id > 1;
select * from T;


rollback;
select * from T;

--transaktsioon salvestab uuendamine ja votab tagasi
begin transaction;
update T set si = 3;
select * from T;
rollback;
select * from T;

Ülesanne

SQL

Создаём в базе данных transactionTARgv24 две таблицы Employee(Работники), EmployeeAddress(Адреса работников) и вставляем необходимые данные в таблицы.

Создаём процедуру, которая будет подставлять значения LONDON в таблицу Employee в поле City, где номер работника – 7 и адрес улицы Mäealuse, и в таблицу EmployeeAddress в поле City, где номер ид адреса равен 1 и номер работника равен 7. Если транзакция прошла успешно, то данные меняются в обеих таблицах.

Транзакция прошла успешно и данные поменяны.

Теперь меняем процедуру, подставляя другие значения в те же самые поля, как и в предыдущей процедуре, но умышленно ставим большее значение в поле City для таблицы EmployeeAddress.
Для поля City в обеих таблицах стоит ограничение: City nvarchar(10), что означает не больше 10 символов.
Цель транзакции – это откат к прошлой версии таблицы.
Что это означает:
Когда вызывается spUpdateAddress:

  1. Сначала меняем город в таблице Employee на ‘Tallinn’ – это работает
  2. Потом пытаемся изменить город в EmployeeAddress на ‘Tallinn Tallinn Tallinn’ – но здесь ошибка
  3. Как только возникает ошибка:
    • Все изменения отменяются (как будто ничего не было)
    • Город в обеих таблицах остаётся прежним

Итог: если хоть что-то пойдёт не так – ничего не изменится. Либо обновятся обе таблицы, либо ни одна.

Как мы видими на скриншоте ниже, транзакция прошла успешно и произошёл откат к прошлой версии таблиц.

Снова меняем процедуру, подставим значения в поля City – Tallinn.

Транзакция прошла успешно.

XAMPP

Та же работа в xampp.
Создаём две таблицы, вносим в них данные.

Проверяем, что данные введены корректно.

Создаём процедуру для изменения данных в полях City для обеих таблиц, как было сделано в SQL.

  1. DELIMITER //
    Временно изменяет разделитель команд с ; на //, чтобы MySQL правильно интерпретировал тело процедуры.
  2. CREATE PROCEDURE spUpdateAddress()
    Объявляет создание новой хранимой процедуры с именем spUpdateAddress.
  3. BEGIN ... END
    Ограничивает тело процедуры.
  4. DECLARE EXIT HANDLER – объявляет обработчик, который завершает процедуру при ошибке
  5. FOR SQLEXCEPTION – реагирует на любые SQL-ошибки
  6. ROLLBACK – откатывает текущую транзакцию
  1. Последовательность выполнения:
    • Старт процедуры
    • Регистрация обработчика ошибок
    • Начало транзакции
    • Первое обновление (Employee)
    • Второе обновление (EmployeeAddress)
    • Фиксация изменений (если успешно)
    • Или откат (при ошибке)

Вызываем функцию ->

Проверяем таблицы.

Транзакция прошла успешно, данные поменяны.

Чтобы сделать Alter procedure, как было сделано в SQL, в XAMPP в таком случае мы сначала сбрасываем функцию и только потом создаём на её место новую.

Процедура пытается изменить данные в двух таблицах:

  • Сначала в таблице Employee
  • Потом в таблице EmployeeAddress
  • Если вторая операция (с EmployeeAddress) даст ошибку:
  • Автоматически отменятся ОБЕ операции (и первая тоже) и данные будут возвращены в их исходный вид, то есть будет совершён ROLLBACK – откат всей транзакции.
  • Если ошибки не будет – сохранятся изменения в обеих таблицах

Как показано на скриншоте ниже, транзакция сделала ROLLBACK, откатила данные.

Снова сбрасываем действующую процедуру.

И создаём новую, подставляя новые данные в поля City в обеих таблицах.

Транзакция прошла успешно.