SQL transaktsioonid

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

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

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

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

Атомарность

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

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

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

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

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

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

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

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

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

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

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

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');
INSERT INTO T(id, s) VALUES(2, 'Second');
INSERT INTO T(id, s) VALUES(3, 'Third');

SELECT * FROM T;

--tagasi võtmine
ROLLBACK;
--alustame transaktsioon
BEGIN TRANSACTION;
INSERT INTO T(id, s) VALUES(4, 'Fourth');
SELECT * FROM T;
--tagasi võtmine
ROLLBACK;
SELECT * FROM T;

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

ROLLBACK;
SELECT * FROM T

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

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

CREATE TABLE tblProduct
(
 ProcutID int NOT NULL primary key,
 Name nvarchar(40),
 UnitPrice int,
 QtyAvailable int,
)

INSERT INTO tblProduct VALUES (1, 'Laptop', '1000', 10);
INSERT INTO tblProduct VALUES (2, 'Mouse', '100', 20)

SELECT * FROM tblProduct

BEGIN TRANSACTION
UPDATE tblProduct SET QtyAvailable 5 WHERE ProcutID = 1
SELECT * FROM tblProduct

Допустил опечатку переименовываю столбец 

EXEC sp_rename 'tblProduct.ProcutID', 'ProductID', 'COLUMN';

BEGIN TRANSACTION  -- Начинаем транзакцию

-- Обновляем количество ноутбуков до 5
UPDATE tblProduct 
SET QtyAvailable = 5 
WHERE ProductID = 1;

-- Проверяем изменения
SELECT * FROM tblProduct;

-- Завершаем транзакцию, фиксируем изменения
COMMIT;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

команда устанавливает уровень изоляции транзакции на READ UNCOMMITTED, что означает:

  • Разрешено читать данные, которые ещё не зафиксированы другими транзакциями.
  • Такие данные называются “грязными” (dirty reads).
  • Это самый низкий уровень изоляции — может привести к некорректным или временным данным.

Используется, когда важна скорость, а точность данных не критична (например, в аналитике).

Обновление изменений / откат

BEGIN TRANSACTION

UPDATE tblProduct 
SET QtyAvailable = QtyAvailable - 1 
WHERE ProductID = 1;
SELECT * FROM tblProduct;
-- Откат всех изменений
ROLLBACK TRANSACTION;



ROLLBACK TRANSACTION;
SELECT * FROM tblProduct;

2 часть видео

Create Table tblMailingAddress
(
   AddressId int NOT NULL primary key,
   EmployeeNumber int,
   HouseNumber nvarchar(50),
   StreetAddress nvarchar(50),
   City nvarchar(10),
   PostalCode nvarchar(50)
)

Insert into tblMailingAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')

Create Table tblPhysicalAddress
(
 AddressId int NOT NULL primary key,
 EmployeeNumber int,
 HouseNumber nvarchar(50),
 StreetAddress nvarchar(50),
 City nvarchar(10),
 PostalCode nvarchar(50)
)

Insert into tblPhysicalAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')


CREATE PROCEDURE spUpdateAddress
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION

        -- Обновляем поле City в почтовом адресе
        UPDATE tblMailingAddress 
        SET City = 'LONDON' 
        WHERE AddressId = 1 AND EmployeeNumber = 101

        -- Обновляем поле City в физическом адресе
        UPDATE tblPhysicalAddress 
        SET City = 'LONDON' 
        WHERE AddressId = 1 AND EmployeeNumber = 101

        COMMIT TRANSACTION
        PRINT 'Transaction committed' -- Подтверждение успешного завершения
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        PRINT 'Transaction rolled back'  -- Сообщение об откате при ошибке
    END CATCH
END



--уберем опечатку LONDOON
UPDATE tblMailingAddress SET City = 'London' WHERE AddressId = 1;
UPDATE tblPhysicalAddress SET City = 'London' WHERE AddressId = 1;

SELECT * FROM tblMailingAddress
SELECT * FROM tblPhysicalAddress

Запускаем процедуру, должна выйти инфа ‘Transaction commited’

Alter Procedure spUpdateAddress
as
Begin
 Begin Try
  Begin Transaction
   Update tblMailingAddress set City = 'LONDON-LONDON' 
   where AddressId = 1 and EmployeeNumber = 101
   
   Update tblPhysicalAddress set City = 'LONDON2' 
   where AddressId = 1 and EmployeeNumber = 101
  Commit Transaction
  Print 'Transaction committed'
 End Try
 Begin Catch
  Rollback Transaction
  print 'Transaction Rolled back'
 End Catch
End

SELECT * FROM tblMailingAddress
SELECT * FROM tblPhysicalAddress

EXEC spUpdateAddress;

поскольку LONDON-LONDON более 10ти символов это вызывает ошибку (у нас указано varchar10)
тем самым происходит откат без изменений. О чем свидетельствует сообщение Transaction Rolled Back

если заменить LONDON23 / LONDON25 то будет информация – запись закомичена

Alter Procedure spUpdateAddress
as
Begin
 Begin Try
  Begin Transaction
   Update tblMailingAddress set City = 'LONDON25' 
   where AddressId = 1 and EmployeeNumber = 101
   
   Update tblPhysicalAddress set City = 'LONDON23' 
   where AddressId = 1 and EmployeeNumber = 101
  Commit Transaction
  Print 'Transaction committed'
 End Try
 Begin Catch
  Rollback Transaction
  print 'Transaction Rolled back'
 End Catch
End

SELECT * FROM tblMailingAddress
SELECT * FROM tblPhysicalAddress

EXEC spUpdateAddress;

XAMPP

Первая часть с товарами

-- Таблица товаров
CREATE TABLE tblProduct (
    ProductID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(40),
    UnitPrice INT,
    QtyAvailable INT
);

-- Добавим данные
INSERT INTO tblProduct VALUES (1, 'Laptop', 1000, 10);
INSERT INTO tblProduct VALUES (2, 'Mouse', 100, 20);

-- Проверим
SELECT * FROM tblProduct;

-- Начинаем транзакцию
START TRANSACTION;

-- Обновляем данные
UPDATE tblProduct 
SET QtyAvailable = 5 
WHERE ProductID = 1;

-- Проверка
SELECT * FROM tblProduct;

-- Фиксируем
COMMIT;

-- Демонстрация отката
START TRANSACTION;

UPDATE tblProduct 
SET QtyAvailable = QtyAvailable - 1 
WHERE ProductID = 1;

SELECT * FROM tblProduct;

-- Откат
ROLLBACK;

SELECT * FROM tblProduct;

Вторая часть с адресами

-- Таблицы адресов
CREATE TABLE tblMailingAddress (
    AddressId INT PRIMARY KEY,
    EmployeeNumber INT,
    HouseNumber VARCHAR(50),
    StreetAddress VARCHAR(50),
    City VARCHAR(10),
    PostalCode VARCHAR(50)
);

INSERT INTO tblMailingAddress 
VALUES (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW');

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

INSERT INTO tblPhysicalAddress 
VALUES (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW');

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

DELIMITER //

CREATE PROCEDURE spUpdateAddress()
BEGIN
    START TRANSACTION;

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

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

    COMMIT;
END;
//

DELIMITER ;

Вызываем затем проверяем, значения должны поменятся на LONDON23/LONDON25

SELECT * FROM tblMailingAddress;
SELECT * FROM tblPhysicalAddress;