martin rosman blog

SQL transactions

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

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

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

  • Atomicity – Атомарность гарантирует, что любая транзакция будет зафиксирована только целиком (полностью). Если одна из операций в последовательности не будет выполнена, то вся транзакция будет отменена. Тут вводится понятие “отката” (rollback). Т.е. внутри последовательности будут происходить определённые изменения, но по итогу все они будут отменены (“откачены”) и по итогу пользователь не увидит никаких изменений.
  • Consistency – Согласованность означает, что любая завершённая транзакция (транзакция, которая достигла завершения транзакции – end of transaction) фиксирует только допустимые результаты. Например, при переводе денег с одного счёта на другой, в случае, если деньги ушли с одного счёта, они должны прийти на другой (это и есть согласованность системы). Списание и зачисление  – это две разные транзакции, поэтому первая транзакция пройдёт без ошибок, а второй просто не будет. Именно поэтому крайне важно учитывать это свойство и поддерживать баланс системы.
  • Isolation – Изолированность каждая транзакция должна быть изолирована от других, т.е. её результат не должен зависеть от выполнения других параллельных транзакций. На практике, изолированность крайне труднодостижимая вещь, поэтому здесь вводится понятие “уровни изолированности” (транзакция изолируется не полностью).
  • Durability – Долговечность эта концепция гарантирует, что если мы получили подтверждение о выполнении транзакции, то изменения, вызванные этой транзакцией не должны быть отменены из-за сбоя системы (например, отключение электропитания).

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

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

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

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

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

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

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

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

Создадим транзакцию в SQL Server Management Studio

create database transaction_rosman;
use transaction_rosman;

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

insert into T(id, s) values (1, 'first');
insert into T(id, s) values (2, 'second');
insert into T(id, s) values (3, 'third');

select * from T;

--revert changes doesn't work if there is not "BEGIN TRANSACTION"
ROLLBACK;

BEGIN TRANSACTION;
insert into T(id, s) values (4, 'fourth');

ROLLBACK;

--deleting first row and taking it back

BEGIN TRANSACTION;
delete from T where id=1;
select * from T;
ROLLBACK;

--update and roll back
BEGIN TRANSACTION;
update T set si=3 where id=1;
select * from T;
ROLLBACK;

Задание в PHPMyAdmin

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

SET autocommit=0;

Предположим, что у нас есть таблица developers, которая содержит следующие записи:

INSERT INTO developers (id, name, specialty, EXPERIENCE, SALARY) VALUES (1, 'John Doe', 'Frontend', 5, 70000), (2, 'Jane Smith', 'Backend', 8, 90000), (3, 'Mike Brown', 'Full Stack', 10, 120000), (4, 'Linda Green', 'Data Scientist', 4, 85000), (5, 'Tom Clark', 'DevOps', 7, 95000);

Удалим всех Full Stack разработчиков с помощью следующей команды:

START TRANSACTION;
DELETE FROM developers 
       WHERE SPECIALTY = 'Full Stack';
COMMIT;
select * from developers;
ROLLBACK;

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

Теперь попробуем выполнить команду ROLLBACK:

ROLLBACK;

К сожалению, в PHPMyAdmin команды не работают

  •  SAVEPOINT foo
  •  ROLLBACK [WORK] TO [SAVEPOINT] foo where [WORK] is optional
  •  RELEASE SAVEPOINT foo

https://github.com/phpmyadmin/sql-parser/issues/322

Теперь постараемся разобраться с SAVEPOINT.
Для начала создадим точку сохранения, используя следующий запрос:

SAVEPOINT SP1;

Теперь выполним следующие запросы:

DELETE FROM developers WHERE ID = 3;
DELETE FROM developers WHERE ID = 4;
DELETE FROM developers WHERE ID = 5;

На данный момент наша таблица содержит следующие записи:

Теперь мы вернёмся к точке сохранения SP1 с помощью команды:

ROLLBACK TO SP1;

Данная комманда не работает в PHPmyadmin

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

SET TRANSACTION READ ONLY;

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

SET TRANSACTION READ WRITE;

Задание в SQL Server

Добавим новую таблицу товаров

create table tblProduct (
	ProductId INT,
	ProductName VARCHAR(50),
	UnitPrice INT,
	QtyAvailable INT
);

insert into tblProduct (ProductId, ProductName, UnitPrice, QtyAvailable) values (1, 'Alectura lathami', 37, 2);
insert into tblProduct (ProductId, ProductName, UnitPrice, QtyAvailable) values (2, 'Vanellus chilensis', 81, 70);
insert into tblProduct (ProductId, ProductName, UnitPrice, QtyAvailable) values (3, 'Canis mesomelas', 92, 69);
insert into tblProduct (ProductId, ProductName, UnitPrice, QtyAvailable) values (4, 'Isoodon obesulus', 51, 52);

Создадим транзакцию по обновлению количества товара

Begin transaction
Update tblProduct set QtyAvailable = 50 where ProductId = 1

Откатить изменения можно с помощью команды 

Rollback;

Создадим таблицу с адресами сотрудников

create table tblMailingAddress (
	AddressID INT,
	City VARCHAR(50),
	EmployeeNumber INT,
);

create table tblPhysicalAddress (
	AddressID INT,
	City VARCHAR(50),
	EmployeeNumber INT,
);

insert into tblMailingAddress (AddressID, City, EmployeeNumber) values (1,  'Tallinn', 101);
insert into tblPhysicalAddress (AddressID, City, EmployeeNumber) values (1, 'Tallinn', 101);

Создадим процедуру вместе с транзакцией на обновление адреса

Create Procedure spUpdateAddress
as
Begin
	Begin try
		Begin Transaction
		Update tblMailingAddress set City = 'LONDON'
		where AddressID = 1 and EmployeeNumber = 101

		Update tblPhysicalAddress set City = 'LONDON'
		where AddressID = 1 and EmployeeNumber = 101
		Commit Transaction
		Print 'Transaction Commited'
	End try
	Begin Catch
		Rollback Transaction
		Print 'Transaction Rolled Back'
	End Catch
End