SQL Transaktsioonid

  1. ACID
  2. Näitus
    1. Päringud
    2. Andmete lisamine
    3. Lisamine ja rollback
    4. Kustutamine ja rollback
    5. Uuendamine ja rollback
  3. Ülesanne 1.
    1. Andmete lisamine
    2. DELETE ja ROLLBACK
    3. SAVEPOINT
  4. Ülesanne 2.
    1. SQL Server
      1. Andmete lisamine
      2. Protseduur: spUpdateAddress
      3. Protseduur näide veaga
    2. XAMPP
      1. Andmete lisamine
      2. Protseduur: spUpdateAddress
      3. Protseduur näide veaga

ACID

Транзакция — это последовательность операций с БД, которая выполняется как единое целое. Это любое действие с БД: добавление, изменение или удаление информации.

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

  • Атомарность (Atomicity) — всё или ничего: если хоть одна операция не выполнена, всё откатывается.
  • Согласованность (Consistency) — после транзакции данные должны оставаться в допустимом состоянии.
  • Изолированность (Isolation) — параллельные транзакции не мешают друг другу; возможны разные уровни изоляции.
  • Долговечность (Durability) — после подтверждения данные сохраняются даже при сбоях.

Näitus

Päringud

BEGIN / START TRANSACTION — начало транзакции.
COMMIT — зафиксировать изменения.
ROLLBACK — откатить все изменения с момента начала транзакции.
SAVEPOINT — создать точку сохранения внутри транзакции.
ROLLBACK TO — откатить изменения до точки сохранения.
RELEASE SAVEPOINT — удалить точку сохранения.
SET TRANSACTION READ ONLY — транзакция только для чтения.
SET TRANSACTION READ WRITE — транзакция с возможностью записи (по умолчанию).

Andmete lisamine

CREATE DATABASE TransaktsioonidDB;
USE TransaktsioonidDB;
 
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');
INSERT INTO T(id, s) 
    VALUES (4, 'fourth');

Lisamine ja rollback

Lisa väärtus ja tee ROLLBACK

BEGIN TRANSACTION;
    INSERT INTO T(id, s) VALUES (5, 'FIVE');
    SELECT * FROM T;
ROLLBACK;
SELECT * FROM T;

Kustutamine ja rollback

Kustuta väärtus ja tee ROLLBACK

BEGIN TRANSACTION;
    DELETE FROM T WHERE id=1;
    SELECT * FROM T;
    ROLLBACK;
SELECT * FROM T;

Uuendamine ja rollback

Uuenda väärtust ja tee ROLLBACK

BEGIN TRANSACTION;
   UPDATE T SET si = 5
   SELECT * FROM T;
   ROLLBACK;
SELECT * FROM T;

Ülesanne 1.

Andmete lisamine

CREATE TABLE developers (
ID INT NOT NULL PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
SPECIALTY VARCHAR(50) NOT NULL,
EXPERIENCE INT NOT NULL,
SALARY INT NOT NULL
)
 
INSERT INTO developers
VALUES (1, 'Eugene Suleimanov', 'Java', 2, 2500),
(2, 'Peter Romanenko', 'Java', 3, 3500),
(3, 'Andrei Komarov', 'C++', 3, 2500),
(4, 'Konstantin Geiko', 'C#', 2, 2000),
(5, 'Asya Suleimanova', 'UI/UX', 2, 1800),
(6, 'Ivan Ivanov', 'C#', 1, 900),
(7, 'Ludmila Geiko', 'UI/UX', 2, 1800)

DELETE ja ROLLBACK

Kustutada väärtus, kus C++ eriala.

START TRANSACTION;
DELETE FROM developers WHERE SPECIALTY = 'C++';
SELECT * FROM developers;

ROLLBACK, mis taastab väärtused eelmisesse olekusse.

ROLLBACK;
SELECT * FROM developers;

SAVEPOINT

Tehke SAVEPOINT, et salvestada väärtused soovitud kohas.

START TRANSACTION;

SAVEPOINT SP1;

DELETE FROM developers WHERE ID = 7;
DELETE FROM developers WHERE ID = 6;
DELETE FROM developers WHERE ID = 5;
SELECT * FROM developers;

ROLLBACK kohas, kuhu me oleme oma väärtused salvestanud.

ROLLBACK TO SP1;
SELECT * FROM developers;

Ülesanne 2.

hinne 5 – https://www.youtube.com/watch?v=shkt9Z5Gz-U

SQL Server

Andmete lisamine

CREATE TABLE tblMailingAddress (
	AddressId int PRIMARY KEY,
	EmployeeNumber int,
	HouseNumber varchar(50),
	StreetAddress varchar(50),
	City varchar(10),
	PostalCode varchar(50)
);
 
CREATE TABLE tblPhysicalAddress (
	AddressId int NOT NULL 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');
	
INSERT INTO tblPhysicalAddress 
VALUES 
(1, 101, '#10', 'King Street', 'Londoon', 'CR27DW');

Protseduur: spUpdateAddress

Uuendab linna nime kahes tabelis

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
 END TRY
 BEGIN CATCH
  ROLLBACK TRANSACTION
 END CATCH
END

Kui mõlemad käsud on edukalt sooritatud – sooritage COMMIT.
Kui vähemalt üks neist ebaõnnestus – taganemine ROLLBACKi abil.

Protseduur näide veaga

Muudame protseduuri nii, et teine käsk ebaõnnestub – määrame CITY väärtuse pikemaks kui 10 tähemärki.

ALTER PROCEDURE spUpdateAddress
AS
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION
		UPDATE tblMailingAddress SET City = 'LONDON1'
		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

Esimene käsk täidetakse, teine käsk ebaõnnestub.
Selle tulemusena käivitub ROLLBACK – mõlemad käsud, sealhulgas esimene käsk, võetakse tagasi.

XAMPP

Sama asi XAMPP-is.

Andmete lisamine

CREATE TABLE tblMailingAddress (
	AddressId int PRIMARY KEY,
	EmployeeNumber int,
	HouseNumber varchar(50),
	StreetAddress varchar(50),
	City varchar(10),
	PostalCode varchar(50)
);
 
CREATE TABLE tblPhysicalAddress (
	AddressId int NOT NULL 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');
	
INSERT INTO tblPhysicalAddress 
VALUES 
	(1, 101, '#10', 'King Street', 'Londoon', 'CR27DW');

Protseduur: spUpdateAddress

DELIMITER $$

CREATE PROCEDURE spUpdateAddress()
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
	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;
END $$

DELIMITER;

Protseduur näide veaga

DELIMITER $$

CREATE PROCEDURE spUpdateAddressError()
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
        SELECT 'ROLLBACK';
	END;

	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;
 
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Forced exception';
    
    COMMIT;
END $$

DELIMITER;