Ülesanne transaktsioonid

Просматриваю видео:

Повторяю работу автора, только в своей базе данных transactionTARgv24, где создаю таблицу broneeringud и заполняю данными:

CREATE TABLE broneeringud (
    broneeringID INT NOT NULL PRIMARY KEY,
    klientNimi VARCHAR(100) NOT NULL,
    tubaTüüp VARCHAR(50) NOT NULL,
    saabumiseKuupäev DATE NOT NULL,
    lahkumiseKuupäev DATE NOT NULL,
    hindÖöEest DECIMAL(6,2) NOT NULL
);

INSERT INTO broneeringud (broneeringID, klientNimi, tubaTüüp, saabumiseKuupäev, lahkumiseKuupäev, hindÖöEest)
VALUES 
(1, 'Anna Tamm', 'Üheinimesetuba', '2025-06-10', '2025-06-13', 75.00),
(2, 'Marek Saar', 'Kaheinimesetuba', '2025-07-01', '2025-07-07', 120.00),
(3, 'Kati Kuusk', 'Sviit', '2025-06-15', '2025-06-18', 220.50);

SELECT * FROM broneeringud;

Чтобы работать с транзакциями, придерживаемся следующего порядка:

  1. Открываем транзакцию.
  2. Выполняем нужные SQL-команды.
  3. Проверяем результат.
    • Если всё прошло хорошо — применяем изменения с помощью COMMIT.
    • Если возникла ошибка — возвращаем данные к исходному виду через ROLLBACK.

Транзакция на изменение:

Представим ситуацию: в бронировании номер 3 по ошибке указано слишком маленькое количество дней, и мы хотим изменить цену за ночь.

-- Muudame hinna
BEGIN TRANSACTION;
UPDATE broneeringud SET hindÖöEest = 500 WHERE broneeringID = 3;

Теперь, если кто-то попытается выполнить запрос SELECT * FROM broneeringud, он может «зависнуть», потому что строка с broneeringID = 3 временно заблокирована незавершённой транзакцией.

Чтобы обойти блокировку, можно использовать режим чтения без ожидания завершения транзакции:

Если передумали и хотим отменить редактирование:

Фиксируем изменения:

BEGIN TRANSACTION;
UPDATE broneeringud SET hindÖöEest = 500 WHERE broneeringID = 3;
COMMIT TRANSACTION;

Теперь новая цена будет сохранена в базе данных окончательно.

Пример1

SQL SERVER

CREATE TABLE aktiivsedBroneeringud (
    broneeringID INT NOT NULL PRIMARY KEY,
    klientNimi VARCHAR(100),
    tubaTüüp VARCHAR(50),
    hindÖöEest DECIMAL(6,2)
);

CREATE TABLE klientideAjalugu (
    broneeringID INT NOT NULL PRIMARY KEY,
    klientNimi VARCHAR(100),
    tubaTüüp VARCHAR(50),
    hindÖöEest DECIMAL(6,2)
);

INSERT INTO aktiivsedBroneeringud VALUES (1, 'Anna Tamm', 'Sviit', 220.50);
INSERT INTO klientideAjalugu VALUES (1, 'Anna Tamm', 'Sviit', 220.50);

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

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

Создание процедуры с транзакцией:

Теперь создадим хранимую процедуру, которая одновременно обновит цену номера в обеих таблицах:

CREATE PROCEDURE uuendaHind
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE aktiivsedBroneeringud
        SET hindÖöEest = 249.99
        WHERE broneeringID = 1 AND klientNimi = 'Anna Tamm';

        UPDATE klientideAjalugu
        SET hindÖöEest = 249.99
        WHERE broneeringID = 1 AND klientNimi = 'Anna Tamm';

        COMMIT TRANSACTION;
        PRINT 'Tehing edukalt lõpetatud';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Tehing katkestati vea tõttu';
    END CATCH
END;

XAMPP

Представим, что мы ведём учёт бронирований как в активной базе, так и в резервной копии. Цель — синхронно обновлять цену номера в обеих таблицах.

Создадим две таблицы с похожей структурой:

CREATE TABLE aktiivne_andmebaas (
    broneeringID INT NOT NULL PRIMARY KEY,
    klientNimi VARCHAR(100),
    tubaTüüp VARCHAR(50),
    hindÖöEest DECIMAL(6,2)
);

CREATE TABLE arhiiv_andmebaas (
    broneeringID INT NOT NULL PRIMARY KEY,
    klientNimi VARCHAR(100),
    tubaTüüp VARCHAR(50),
    hindÖöEest DECIMAL(6,2)
);


INSERT INTO aktiivne_andmebaas VALUES (1, 'Mihkel Maasik', 'Sviit', 199.99);
INSERT INTO arhiiv_andmebaas VALUES (1, 'Mihkel Maasik', 'Sviit', 199.99);

Для обеспечения безопасности данных мы обернём операцию в транзакцию с контролем ошибок.

BEGIN
    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

Пример 2

SQL SERVER

В этом примере мы проверяем, как работает транзакция в случае возникновения ошибки при обновлении записей в двух таблицах. Допустим, мы хотим изменить имя клиента в таблицах aktiivne_andmebaas и arhiiv_andmebaas.

Если во второй таблице поле klientNimi ограничено, скажем, 20 символами, а мы попытаемся записать туда более длинное имя — произойдёт ошибка. Благодаря транзакции, в этом случае изменения отменяются в обеих таблицах, и данные сохраняются без рассинхронизации.

CREATE PROCEDURE uuendaHind
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE aktiivsedBroneeringud
        SET hindÖöEest = 249.99
        WHERE broneeringID = 1 AND klientNimi = 'Anna Tamm';

        UPDATE klientideAjalugu
        SET hindÖöEest = 249.99
        WHERE broneeringID = 1 AND klientNimi = 'Anna Tamm';

        COMMIT TRANSACTION;
        PRINT 'Tehing edukalt lõpetatud';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT 'Tehing katkestati vea tõttu';
    END CATCH
END;

С помощью транзакций можно надёжно обновлять связанные данные в нескольких таблицах. Если где-то возникает ошибка, система автоматически отменяет все действия, чтобы сохранить целостность данных.

XAMPP

В бронировании номер 3 по ошибке указана неправильная цена за ночь.

Необходимо изменить цену с 220.50 на 500.00, используя транзакцию, чтобы обеспечить целостность данных 

BEGIN TRANSACTION;

UPDATE broneeringud 
SET hindÖöEest = 500.00 
WHERE broneeringID = 3;

COMMIT TRANSACTION;

BEGIN TRANSACTION; – начинается новая транзакция.

UPDATE – выполняется обновление данных.

COMMIT TRANSACTION; – изменения сохраняются в базе данных.