SQL transaktsioonid - Anna Oleks ÕPIMAPP

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

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

Основные концепции транзакции описываются аббревиатурой 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-клиентов.

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;

--alustame transaktsiooni
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;

--transaktsion mis salvestab uuendamist ja võtab tagasi
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'),
(2,'second'),
(3,'third');

SELECT * FROM T;

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

ROLLBACK;
SELECT * FROM T;

SET AUTOCOMMIT=0;
START TRANSACTION;
DELETE FROM T WHERE id=3;
COMMIT;

ROLLBACK;
SELECT * FROM T;

START TRANSACTION;
SAVEPOINT sp1;
SELECT * FROM T;

UPDATE T 
SET si=6;
SELECT * FROM T;

ROLLBACK TO sp1;
SELECT * FROM T;

Задание:

SQL Server

-- Таблица с оценками за теоретическую часть
CREATE TABLE tblTheoryGrades (
    GradeId INT NOT NULL PRIMARY KEY,
    StudentId INT,
    SubjectName NVARCHAR(20),
    Grade CHAR(2)
);

-- Добавим тестовые данные
INSERT INTO tblTheoryGrades VALUES (1, 1001, 'Mathematics', 'C');


-- Таблица с оценками за практику
CREATE TABLE tblPracticalGrades (
    GradeId INT NOT NULL PRIMARY KEY,
    StudentId INT,
    SubjectName NVARCHAR(20),
    Grade CHAR(2)
);

-- Добавим те же тестовые данные
INSERT INTO tblPracticalGrades VALUES (1, 1001, 'Mathematics', 'C');


Create Procedure spUpdateGrades
as
Begin
 Begin Try
  Begin Transaction
   Update tblTheoryGrades set SubjectName = 'Math', Grade = 'B' 
   where GradeId = 1 and StudentId = 1001
   
   Update tblPracticalGrades set SubjectName = 'Math', Grade = 'B' 
   where GradeId = 1 and StudentId = 1001
  Commit Transaction
 End Try
 Begin Catch
  Rollback Transaction
 End Catch
End

ALTER PROCEDURE spUpdateGrades
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Успешное обновление
        UPDATE tblTheoryGrades
        SET SubjectName = 'Mathematics Advanced',
            Grade = 'A'
        WHERE GradeId = 1 AND StudentId = 1001;

        -- Ошибочное обновление (SubjectName > 20 символов)
        UPDATE tblPracticalGrades
        SET SubjectName = 'Mathematics and Physics',
            Grade = 'A'
        WHERE GradeId = 1 AND StudentId = 1001;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Ошибка: ' + ERROR_MESSAGE();
    END CATCH
END;

SELECT * FROM tblTheoryGrades;
SELECT * FROM tblPracticalGrades;

XAMPP

-- Таблица с оценками за теоретическую часть
CREATE TABLE tblTheoryGrades (
    GradeId INT NOT NULL PRIMARY KEY,
    StudentId INT,
    SubjectName VARCHAR(20),
    Grade CHAR(2)
);

-- Добавим тестовые данные
INSERT INTO tblTheoryGrades VALUES (1, 1001, 'Mathematics', 'C');


-- Таблица с оценками за практику
CREATE TABLE tblPracticalGrades (
    GradeId INT NOT NULL PRIMARY KEY,
    StudentId INT,
    SubjectName VARCHAR(20),
    Grade CHAR(2)
);

-- Добавим те же тестовые данные
INSERT INTO tblPracticalGrades VALUES (1, 1001, 'Mathematics', 'C');


START TRANSACTION;

UPDATE tblTheoryGrades
SET SubjectName = 'Math', Grade = 'B'
WHERE GradeId = 1 AND StudentId = 1001;

UPDATE tblPracticalGrades
SET SubjectName = 'Math', Grade = 'B'
WHERE GradeId = 1 AND StudentId = 1001;

COMMIT;

SELECT * FROM tblTheoryGrades;
SELECT * FROM tblPracticalGrades;

DELIMITER //

CREATE PROCEDURE spUpdateAddress()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    UPDATE tblTheoryGrades
    SET SubjectName = 'Math', Grade = 'B'
    WHERE GradeId = 1 AND StudentId = 1001;
    
    UPDATE tblPracticalGrades 
    SET SubjectName = 'Math-Magic-Magisters-XXL', Grade = 'A'
WHERE GradeId = 1 AND StudentId = 1001;
    
    COMMIT;
END //

DELIMITER ;