БД – работа с транзакциями

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

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

Основные концепции транзакции описываются аббревиатурой 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. Транзакции.
https://proselyte.net/tutorials/sql/sql-transactions/

Выполняем пример из статьи:

--Создаём БД transactionsKirillMelnikov
CREATE DATABASE transactionsKirillMelnikov;

--Переходим в созданную БД
USE transactionsKirillMelnikov;

--Создаём необходимую таблицу developers
CREATE TABLE developers (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50),
    specialty VARCHAR(20),
    experience INT,
    salary INT
);

--Вставляем необходимые данные из примера
INSERT INTO developers (name, specialty, experience, salary) VALUES
('Eugene Suleimanov', 'Java', 2, 2500),
('Peter Romanenko', 'Java', 3, 3500),
('Andrei Komarov', 'C++', 3, 2500),
('Konstantin Geiko', 'C#', 2, 2000),
('Asya Suleimanova', 'UI/UX', 2, 1800),
('Ivan Ivanov', 'C#', 1, 900),
('Ludmila Geiko', 'UI/UX', 2, 1800);

--Проверяем запросом, как внеслись данные в таблицу и получаем результат
SELECT * FROM developers;

-- начало работы с транзакциями 
BEGIN TRANSACTION;

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

DELETE FROM developers
WHERE specialty = 'C++';

Проверим через SELECT результат и если у нас 3 строка(3, Andrei Komarov, C++,3, 2500) будет удалена, то выполняем COMMIT

SELECT * FROM developers;

-- если всё ОК, применяем изменения
COMMIT;

--откатываем, если передумали:
ROLLBACK;

Создаём точку сохранения SAVEPOINT и удаляем id(5,6 и 7)

-- начало работы с транзакциями 
BEGIN TRANSACTION;

SAVE TRANSACTION SavePoint1;

DELETE FROM developers WHERE id = 7;
DELETE FROM developers WHERE id = 6;
DELETE FROM developers WHERE id = 5;

SELECT * FROM developers;

-- Откат только к SavePoint1:
ROLLBACK TRANSACTION SavePoint1;

SELECT * FROM developers;

SAVEPOINT’ы и транзакции не записываются на диск напрямую.

Все изменения до COMMIT находятся во временных логах и буферах.

Если произошёл сбой до COMMIT — изменения будут потеряны или отменены.

-- Фиксация оставшихся изменений:
COMMIT;

🛑 Нет аналога RELEASE SAVEPOINT в SQL Server

SQL Server не требует команды RELEASE SAVEPOINT — точка автоматически теряет актуальность после COMMIT или полной отмены.

⚙️ SET TRANSACTION READ ONLY в SQL Server?

Такой команды нет напрямую, но аналог реализуется так:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;  -- или READ COMMITTED
BEGIN TRANSACTION;

-- Только чтение возможно, если нет прав на изменение
SELECT * FROM developers;

COMMIT;

Задание на 5:

Transactions in sql server Part 57

SQL Server

Создаём БД и/или в моём случае создаём таблицы из видео-примера:

CREATE DATABASE transactionsKirillMelnikov;

USE transactionsKirillMelnikov;

CREATE TABLE tblMailingAddress (
    AddressId INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeNumber INT,
    HouseNumber VARCHAR(10),
    StreetAddress VARCHAR(100),
    City VARCHAR(10),
    PostalCode VARCHAR(10)
);

CREATE TABLE tblPhysicalAddress (
    AddressId INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeNumber INT,
    HouseNumber VARCHAR(10),
    StreetAddress VARCHAR(100),
    City VARCHAR(10),
    PostalCode VARCHAR(10)
);

Заполняем таблицы tblMailingAddress и tblPhysicalAddress данными:

INSERT INTO tblMailingAddress (EmployeeNumber, HouseNumber, StreetAddress, City, PostalCode)
VALUES (101, '#10', 'King Street', 'LONDOON', 'CR27DW');

INSERT INTO tblPhysicalAddress (EmployeeNumber, HouseNumber, StreetAddress, City, PostalCode)
VALUES (101, '#10', 'King Street', 'LONDOON', 'CR27DW');

И при запуске SELECT проверяем, что у нас получилось:

Создаём процедуру:

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;

И запускаем её:

И через SELECT проверяем изменились ли результаты:

А теперь изменим нашу процедуру, но намеренно сделаем так, чтобы в таблице “tblPhysicalAddress” она заменяла “LONDON” на “LONDON LONDON“:

ALTER PROCEDURE spUpdateAddress
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE tblMailingAddress 
        SET City = 'LONDON'
        WHERE AddressId = 1 AND EmployeeNumber = 101;

        UPDATE tblPhysicalAddress 
        SET City = 'LONDON 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;

И запускаем её проверить:

А произошло это потому, что в столбике “City” разрешено всего 10 символов,

XAMPP

Создаём БД “transactionsKirillMelnikov“ и создаём таблицы “tblMailingAddress” и “tblPhysicalAddress” из видео-примера:

-- 1. Создание базы данных
CREATE DATABASE transactionsKirillMelnikov;
USE transactionsKirillMelnikov;

-- 2. Таблицы
CREATE TABLE tblMailingAddress (
    AddressId INT AUTO_INCREMENT PRIMARY KEY,
    EmployeeNumber INT,
    HouseNumber VARCHAR(10),
    StreetAddress VARCHAR(100),
    City VARCHAR(10),
    PostalCode VARCHAR(10)
);

CREATE TABLE tblPhysicalAddress (
    AddressId INT AUTO_INCREMENT PRIMARY KEY,
    EmployeeNumber INT,
    HouseNumber VARCHAR(10),
    StreetAddress VARCHAR(100),
    City VARCHAR(10),
    PostalCode VARCHAR(10)
);

-- 3. Вставка данных
INSERT INTO tblMailingAddress (EmployeeNumber, HouseNumber, StreetAddress, City, PostalCode)
VALUES (101, '#10', 'King Street', 'LONDOON', 'CR27DW');

INSERT INTO tblPhysicalAddress (EmployeeNumber, HouseNumber, StreetAddress, City, PostalCode)
VALUES (101, '#10', 'King Street', 'LONDOON', 'CR27DW');

Создаём процедуру “spUpdateAddress“:

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

    START TRANSACTION;

    UPDATE tblMailingAddress
    SET City = 'LONDON'
    WHERE AddressId = 1 AND EmployeeNumber = 101;

    UPDATE tblPhysicalAddress
    SET City = 'LONDON'
    WHERE AddressId = 1 AND EmployeeNumber = 101;

    COMMIT;
    SELECT 'Transaction Committed' AS Message;
END

И запускаем её:

CALL spUpdateAddress();

В результате получаем “LONDON” вместо “LONDOON” и система выдаёт сообщение “Transaction Commited“:

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

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

А теперь создадим чуть изменённую процедуру, но намеренно сделаем так, чтобы в таблице “tblPhysicalAddress“ она заменяла “LONDON” на “LONDON LONDON“:

Проверяем процедуру “spUpdateAddress2” :

В результате остаётся “LONDON” вместо “LONDON LONDON” и система выдаёт сообщение “Transaction Rolled Back“: