Andmebaas: Transaktsioonid

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

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

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

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

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

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

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

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

SQL SERVER MANAGEMENT:

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;

ROLLBACK

BEGIN TRANSACTION
INSERT INTO T(id,s) VALUES(5, 'fourth');
SELECT * FROM T;

ROLLBACK;
SELECT * FROM T;

BEGIN TRANSACTION;
DELETE FROM T WHERE id > 1;

BEGIN TRANSACTION;
UPDATE T SET si=3;
SELECT * FROM T;
ROLLBACK;
SELECT * FROM T;

XAMPP:

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;

ROLLBACK

START TRANSACTION
INSERT INTO T(id,s) VALUES(5, 'fourth');
SELECT * FROM T;

ROLLBACK;
SELECT * FROM T;

START TRANSACTION;
DELETE FROM T WHERE id > 1;

START TRANSACTION;
UPDATE T SET si=3;
SELECT * FROM T;
ROLLBACK;
SELECT * FROM T;

Ülesanne:

Таймкоды видео:

• 00:00 – Вступление
• 00:16 – Что такое транзакции
• 01:29 – Зачем нужны транзакции
• 03:59 – Авто коммит
• 10:57 – Execute транзакции
• 13:53 – Rollback транзакции

Человек использует MySQL Workbench, буду использовать SQL Server Management Studio (другие функции). Для начала создадим базу данных “TARgv24Transactions” и используем её.

CREATE DATABASE TARgv24Transactions;
USE TARgv24Transactions;

Разбор видео по фактам (без звука):

SET autocommit = 0 / 1

В данном случае 0 отключит автоматическую фиксацию транзакций, это значит, что каждый SQL-запрос (INSERT, UPDATE, DELETE) не коммитится автоматически сразу после выполнения. Вы сами решите когда сделать COMMIT или ROLLBACK, это может запросто заменить START/BEGIN TRANSACTION. По умолчанию значение стоит на 1.

START TRANSACTION;
SET @orderNumber = (select max(orderNumber)+1 from orders);

START TRANSACTION – Запуск транзакции, изменения не будут зафиксированы, пока не будет вписан COMMIT или отменены, пока не будет вписан ROLLBACK (в дальнейшем не будет упоминаться с целью экономии времени, пространства).

SET @orderNumber = (select max(orderNumber)+1 from orders) – Создаётся или переопределяется пользовательская переменная @orderNumber, которой присваивается максимальное значение orderNumber из таблицы (orders) + 1. Это используется для генерации следующего номера заказа.

*Тут в видео делается исполнение двух разных команд, но COMMIT один, после этого обе команды будут выполнены и останутся*

SET autocommit = 0;
START TRANSACTION;
DELETE FROM orderdetails;
SELECT * FROM orderdetails;
ROLLBACK;

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

Начинем транзакцию, удаляем данные из таблицы orderdetails, после чего смотрим удалилось ли (удалилось), откатываем изменения ROLLBACK’ом.

Практическая часть (чето сделать надо):

Имеем уже готовую базу данных, что создали ранее. Создадим простую таблицу testProducts:

CREATE TABLE testProducts (
productID INT PRIMARY KEY NOT NULL,
productName VARCHAR(50),
productPrice INT NOT NULL)

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

BEGIN TRANSACTION
INSERT INTO testProducts(productID,productName,productPrice) VALUES(1,'Test1',1500);
INSERT INTO testProducts(productID,productName,productPrice) VALUES(2,'Test2',1700);
INSERT INTO testProducts(productID,productName,productPrice) VALUES(3,'Test3',2000);
INSERT INTO testProducts(productID,productName,productPrice) VALUES(4,'Test4',1200);
INSERT INTO testProducts(productID,productName,productPrice) VALUES(5,'Test5',1000);
COMMIT;

Добавим значение и откатим его, при этом нам покажет сразу 2 таблицы (обновлённую и откаченную):

BEGIN TRANSACTION;
INSERT INTO testProducts(productID,productName,productPrice) VALUES(6,'Test6',1500);
SELECT * FROM testProducts;
ROLLBACK;
SELECT * FROM testProducts;

Важное уточнение:

В Microsoft SQL Server Management Studio отсутствует autocommit, вместо этого можно воспользоваться:

1. BEGIN TRANSACTION – Создать транзакцию и коммитить, откатывать по своему желанию
2. Переключить IMPLICIT_TRANSACTIONS, после чего каждое изменение будет требовать COMMIT или ROLLBACK