SQL transaktsioonid

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

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

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

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

Атомарность

Гарантирует, что любая транзакция будет зафиксирована только целиком (полностью). Если одна из операций в последовательности не будет выполнена, то вся транзакция будет отменена. Тут вводится понятие “отката” (rollback). Т.е. внутри последовательности будут происходить определённые изменения, но по итогу все они будут отменены (“откачены”) и по итогу пользователь не увидит никаких изменений.

Согласованность

Согласованность означает, что любая завершённая транзакция (транзакция, которая достигла завершения транзакции – end of transaction) фиксирует только допустимые результаты. Например, при переводе денег с одного счёта на другой, в случае, если деньги ушли с одного счёта, они должны прийти на другой (это и есть согласованность системы). Списание и зачисление  – это две разные транзакции, поэтому первая транзакция пройдёт без ошибок, а второй просто не будет. Именно поэтому крайне важно учитывать это свойство и поддерживать баланс системы.

Изолированность

Каждая транзакция должна быть изолирована от других, т.е. её результат не должен зависеть от выполнения других параллельных транзакций. На практике, изолированность крайне труднодостижимая вещь, поэтому здесь вводится понятие “уровни изолированности” (транзакция изолируется не полностью).

Долговечность

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

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

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

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

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

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

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

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

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

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

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

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

SET AUTOCOMMIT = 1;

Значения 1 и ON эквивалентны, так же как и 0 и OFF.

После отправки запроса в режиме AUTOCOMMIT=0 вы оказываетесь в транзакции, пока не выполните команду COMMIT или ROLLBACK. После этого MySQL немедленно начинает новую транзакцию. 

————————————————————————–

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

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

SET IMPLICIT_TRANSACTIONS ON;

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

SET IMPLICIT_TRANSACTIONS OFF;

SQL Server

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,'first'),
(2,'second'),
(3,'third');
 
SELECT * FROM T;
 
-- Tagasi võtmine
ROLLBACK;

ROLLBACK не работает, нужно сделать через транзакцию

Транзакция добавления

-- Alustame transaktsiooni
BEGIN TRANSACTION;
INSERT INTO T(id,s) 
VALUES (4,'fourth');
SELECT * FROM T;

Используем ROLLBACK после транзакции добавления

-- Tagasi võtmine
ROLLBACK;
SELECT * FROM T;

Транзакция удаления

-- Kustutamine transaktsioon
BEGIN TRANSACTION;
DELETE FROM T 
WHERE id > 1;
SELECT * FROM T;

-- Tagasi võtmine
ROLLBACK;
SELECT * FROM T;

Транзакция, фиксирующая обновление и поддерживающая откат (ROLLBACK)

-- Transaktsion mis salvestab uuendamist ja võtab tagasi
BEGIN TRANSACTION;
UPDATE T 
SET si=3;
SELECT * FROM T;
ROLLBACK;
SELECT * FROM T;

XAMPP

Создаем такую же базу данных и таблицу в XAMPP

CREATE TABLE T(
id INT NOT NULL PRIMARY KEY,
s VARCHAR(40),
si SMALLINT);
 
INSERT INTO T(id,s) 
VALUES (1,'first'),
(2,'second'),
(3,'third');

SELECT * FROM T;

Создаем транзакцию на добавление данных

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

Делаем откат транзакции

ROLLBACK;
SELECT * FROM T;

Транзакция удаления

-- Kustutamine transaktsioon
START TRANSACTION;
DELETE FROM T 
WHERE id > 1;
SELECT * FROM T;

-- Tagasi võtmine
ROLLBACK;
SELECT * FROM T;

Результат:

Транзакция, фиксирующая обновление и поддерживающая откат (ROLLBACK)

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

Результат:

Ülesanne transaktsioonid

Использованное для задания видео:

В базе данных transactionTARgv24 создаю таблицу

CREATE TABLE tooted (
    toodeID INT NOT NULL PRIMARY KEY,
    nimetus VARCHAR(100) NOT NULL,
    kirjeldus TEXT,
    hind DECIMAL(10, 2) NOT NULL,
    laos INT NOT NULL
);

INSERT INTO tooted (toodeID, nimetus, kirjeldus, hind, laos)
VALUES (1, 'Sülearvuti', '15-tolline sülearvuti', 799.99, 10),
(2, 'Nutitelefon', 'Android nutitelefon', 499.50, 25),
(3, 'Kõrvaklapid', 'Juhtmevabad kõrvaklapid', 99.99, 50);

SELECT * FROM tooted;

Обработка транзакции включает следующие шаги:

1. Начать транзакцию.
2. Выполнить команды базы данных.
3. Проверить наличие ошибок.
   Если произошли ошибки — откатить транзакцию (ROLLBACK),
   иначе — зафиксировать транзакцию (COMMIT).

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

-- Uuendamine transaktioon
BEGIN TRANSACTION;
UPDATE tooted set laos=500 WHERE toodeID=3;

Теперь, когда запрашиваем просмотр таблицы ‘tooted’, запрос SELECT выполняется долго, потому что он ждет завершения незавершённой транзакции, которая заблокировала строку.

Используем SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, чтобы обойти блокировки и избежать “зависания” SELECT-запроса

Делаем откат (ROLLBACK) обновления

ROLLBACK TRANSACTION;

SELECT * FROM tooted;

Ещё раз используем транзакцию на обновление и добавляем COMMIT TRANSACTION
COMMIT TRANSACTION фиксирует изменение и сохраняет в базе данных навсегда

BEGIN TRANSACTION;
UPDATE tooted set laos=500 WHERE toodeID=3;

COMMIT TRANSACTION;

Пример 1

Рассмотрим пример из видео, чтобы обновить данные в двух таблицах сразу и не допустить расхождений.
Если одна из операций не удалась, все изменения откатываются, чтобы сохранить целостность данных.
Если обе операции удались, транзакция подтверждается (COMMIT), и все изменения сохраняются в базе данных. Это гарантирует, что данные в обеих таблицах обновятся одновременно и правильно.

SQL Server

Создадим две таблицы – товары на складе и товары в магазине, добавим данные

CREATE TABLE laokaubad (
    toodeID INT NOT NULL PRIMARY KEY,
    nimetus VARCHAR(20),
    hind DECIMAL(10, 2),
    kogus INT
);

CREATE TABLE poekaubad (
    toodeID INT NOT NULL PRIMARY KEY,
    nimetus VARCHAR(20),
    hind DECIMAL(10, 2),
    kogus INT
);

INSERT INTO laokaubad VALUES (1, 'Mikser', 79.99, 100);
INSERT INTO poekaubad VALUES (1, 'Mikser', 79.99, 20);

Cоздаём процедуру, которая одновременно обновляет цену товара с названием “Mikser” в таблицах склада и магазина.

CREATE PROCEDURE spUuendaToodeHind
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE laokaubad SET hind=119.99
        WHERE toodeID=1 AND nimetus='Mikser';

        UPDATE poekaubad SET hind=119.99
        WHERE toodeID=1 AND nimetus='Mikser';

        COMMIT TRANSACTION;
		PRINT 'Transaction Commited'
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
		PRINT 'Transaction Rolled Back'
    END CATCH
END;

Запускаем процедуру, выводится сообщение ‘Transaction Commited’.
Оба обновления прошли успешно, изменения сохранились.

Проверка:

XAMPP

Создадим две таблицы – товары на складе и товары в магазине, добавим данные

CREATE TABLE laokaubad (
    toodeID INT NOT NULL PRIMARY KEY,
    nimetus VARCHAR(20),
    hind DECIMAL(10, 2),
    kogus INT
);
 
CREATE TABLE poekaubad (
    toodeID INT NOT NULL PRIMARY KEY,
    nimetus VARCHAR(20),
    hind DECIMAL(10, 2),
    kogus INT
);
 
INSERT INTO laokaubad VALUES (1, 'Mikser', 79.99, 100);
INSERT INTO poekaubad VALUES (1, 'Mikser', 79.99, 20);

Cоздаём процедуру, которая одновременно обновляет цену товара с названием “Mikser” в таблицах склада и магазина.

DECLARE EXIT HANDLER FOR SQLEXCEPTION в MySQL — это аналог TRY/CATCH из SQL Server, который обрабатывает SQL-ошибки внутри процедур.

BEGIN
	-- обработка ошибок, аналог try/catch
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction Rolled Back' AS Message;
    END;

    START TRANSACTION;

    UPDATE laokaubad 
    SET hind = p_uusHind
    WHERE toodeID = p_toodeID AND nimetus = p_nimetus;

    UPDATE poekaubad 
    SET hind = p_uusHind
    WHERE toodeID = p_toodeID AND nimetus = p_nimetus;

    COMMIT;
    SELECT 'Transaction Committed' AS Message;
END

Запускаем процедуру, выводится сообщение ‘Transaction Commited’.
Оба обновления прошли успешно, изменения сохранились.

CALL spUUendaToodeHind(1, 'Mikser', 199.99);

Проверка:

Пример 2

В данной процедуре мы проверяем, произойдёт ли ошибка, если в двух связанных таблицах (laokaubad и poekaubad) пытаемся установить разные названия на один и тот же товар.
Если установить название товара, где кол-во символов больше 20, то должна появится ошибка.

SQL Server

Создаём процедуру, которая обновляет данные в поле nimetus, при этом во втором обновлении намеренно передаём значение длиной более 20 символов, чтобы вызвать ошибку и откат транзакции.

CREATE PROCEDURE spUuendaToodeNimetus
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE laokaubad SET nimetus='Mikser Pro'
        WHERE toodeID=1 AND hind=79.99;

        UPDATE poekaubad SET nimetus='Mikser Super Professional 2025'
        WHERE toodeID=1 AND hind=79.99;

        COMMIT TRANSACTION;
		PRINT 'Transaction Commited'
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
		PRINT 'Transaction Rolled Back'
    END CATCH
END;

Запускаем процедуру, одна из двух операций в транзакции не затронула данные и вышла ошибка ‘Transaction Rolled Back’
В результате был выполнен ROLLBACK — откат всей транзакции.

Проверка:

XAMPP

Если оставить процедуру без IF, то 'Transaction Rolled Back' не произойдёт, потому что в XAMPP по умолчанию sql_mode не включает STRICT_ALL_TABLES (режим строгой проверки данных), и MySQL принимает ошибки, вместо того чтобы выдать их.
Например, если передать текст длиной больше 20 символов, MySQL просто обрежет его до 20, вместо того чтобы выдать ошибку.

Процедура составлена по примеру –> “How to Handle Errors and Exceptions in Transactions”

BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transaction Rolled Back' AS Message;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE laokaubad SET nimetus = p_nimetus1
    WHERE toodeID = 1 AND hind = 79.99;

    UPDATE poekaubad SET nimetus = p_nimetus2
    WHERE toodeID = 1 AND hind = 79.99;

    IF CHAR_LENGTH(p_nimetus1) > 20 OR CHAR_LENGTH(p_nimetus2) > 20 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Nimetus liiga pikk!';
    END IF;

    COMMIT;
    SELECT 'Transaction Committed' AS Message;
END

Запускаем процедуру, одна из двух операций в транзакции не затронула данные и вышла ошибка ‘Transaction Rolled Back’
В результате был выполнен ROLLBACK — откат всей транзакции.

CALL spUuendaToodeNimetus('Mikserrr', 'Mikser Super Professional 2025');

Проверка: