Veronika Portfoolio

Menu

SQL transaktsioonid

Транзакция — это логическая единица работы, состоящая из одной или нескольких команд DML (INSERT, UPDATE, DELETE), выполняемых как единое целое.

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

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

⚠️ Эти команды применяются только к DML-операциям.
DDL-операции (CREATE, DROP, ALTER) не поддерживают управление транзакциями.

Поведение транзакций:

  • Успешное выполнение транзакции должно завершаться COMMIT.
  • Неудачное выполнение должно завершаться ROLLBACK.
  • ROLLBACK автоматически откатывает изменения при:
    • разрыве соединения,
    • сбое программы,
    • отказе системы.

Преимущества ROLLBACK:

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

AUTOCOMMIT (автофиксация):

  • По умолчанию включён в некоторых СУБД:
    • MySQL (InnoDB),
    • PostgreSQL,
    • SQL Server.
  • Каждая команда автоматически фиксируется после выполнения.
  • Невозможно откатить изменения без явного управления транзакцией.
  • В случае ошибки требуется ручное восстановление логики, что сложно при параллельных запросах.

Назначение транзакций:

  • Обеспечивают целостность данных.
  • Используются как механизм восстановления после сбоев.
  • Гарантируют атомарность — “всё или ничего”.

Ülesanne

Выполнение примера со страницы  “Руководство по SQL. Транзакции”

Создаётся таблица accounts

    CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    balance INT
);

    Добавляются данные:

    INSERT INTO accounts VALUES (1, 'Alice', 1000), (2, 'Bob', 1000);

    Выполняется транзакция перевода денег от Alice к Bob

    BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

COMMIT TRANSACTION;

    Проверь результат:

    Для проверки ROLLBACK

    BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE name = 'Alice'; -- ошибка, если баланс отрицательный не допускается

ROLLBACK;

SELECT * FROM accounts;

    Конспект: Работа с транзакциями в MySQL и SQL Server

    Введение

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

    В данной работе мы изучим как работать с транзакциями в MySQL (XAMPP) и SQL Server, используя примеры с перевода денег между счетами.

    Видео по которому сделано задание

    https://csharp-video-tutorials.blogspot.com/2012/10/transactions-in-sql-server-part-57.html

    Часть 1: SQL Server

    Создание базы данных 
    CREATE DATABASE EmployeeDB;
USE EmployeeDB;

    Создание таблиц

    CREATE TABLE tblMailingAddress (
    AddressId INT PRIMARY KEY,
    EmployeeNumber INT,
    HouseNumber NVARCHAR(50),
    StreetAddress NVARCHAR(50),
    City NVARCHAR(50),
    PostalCode NVARCHAR(50)
);

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

    Данные в таблицы

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

    2. Создание хранимой процедуры для работы с транзакциями

    1. Напишем хранимую процедуру для обновления данных в обеих таблицах в рамках одной транзакции:
    CREATE PROCEDURE spUpdateAddress
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Обновление данных в таблице MailingAddress
        UPDATE tblMailingAddress
        SET City = 'LONDON'
        WHERE AddressId = 1 AND EmployeeNumber = 101;

        -- Обновление данных в таблице PhysicalAddress
        UPDATE tblPhysicalAddress
        SET City = 'LONDON'
        WHERE AddressId = 1 AND EmployeeNumber = 101;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- В случае ошибки откатить все изменения
        ROLLBACK TRANSACTION;
    END CATCH
END;

    Выполнение хранимой процедуры:

    EXEC spUpdateAddress;

    Проверка изменений в обеих таблицах:

    Тестирование ошибки: Модифицирую процедуру для имитации ошибки и отката транзакции:

    ALTER PROCEDURE spUpdateAddress
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

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

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

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
    END CATCH
END;

    Выполняю процедуру снова и чтобы убедиться, что данные не изменились:

    EXEC spUpdateAddress;
GO
SELECT * FROM tblMailingAddress;
SELECT * FROM tblPhysicalAddress;
GO

    Часть 2: XAMPP (MySQL)

    2. Создание базы данных и таблиц

    1. Создайте базу данных:
    CREATE DATABASE EmployeeDB;
USE EmployeeDB;

    Создание таблицы:

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

    Вставляем данные в таблицы:

    INSERT INTO tblMailingAddress VALUES (2, 101, '#10', 'King Street', 'London', 'CR27DW');
INSERT INTO tblPhysicalAddress VALUES (2, 101, '#10', 'King Street', 'London', 'CR27DW');
GO

    . Выполнение транзакции

    1. Вставляем код для транзакции с обновлением данных в обеих таблицах:
    START TRANSACTION;

-- Обновление данных в таблице MailingAddress
UPDATE tblMailingAddress SET City = 'LONDON' WHERE AddressId = 1 AND EmployeeNumber = 101;

-- Обновление данных в таблице PhysicalAddress
UPDATE tblPhysicalAddress SET City = 'LONDON' WHERE AddressId = 1 AND EmployeeNumber = 101;

COMMIT;

    Для тестирования отката ошибок использовала следующий код:

    START TRANSACTION;

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

-- Вторая строка вызывает ошибку из-за превышения длины
UPDATE tblPhysicalAddress SET City = 'LONDON LONDON' WHERE AddressId = 1 AND EmployeeNumber = 101;

ROLLBACK;

    Проверяю изменения в обеих таблицах:

    SELECT * FROM tblMailingAddress;
SELECT * FROM tblPhysicalAddress;

    Описание проделанной работы

    1. Создание базы данных и таблиц

    • В обеих системах создаются таблицы для хранения данных о сотрудниках и их адресах.
    2. Использование транзакций
    • SQL Server использует хранимую процедуру для обновления данных в рамках одной транзакции. В случае ошибки происходит откат.
    • В XAMPP (MySQL) транзакции выполняются с помощью команд START TRANSACTION, COMMIT и ROLLBACK.
    3. Тестирование ошибок
    • В случае ошибок в обновлениях транзакция откатывается, и данные не изменяются.