SQL Транзакции

Transaktsioonid on loogilises järjekorras toimingute jada, mida kasutaja või andmebaasiga töötav programm teeb.

Transaktsioonid on muudatuste levitamine andmebaasis. Näiteks kui me loome, muudame või kustutame kirje, siis sooritame tehingu. Tehingute kontrollimine on kindlustunde tagamiseks kriitilise tähtsusega.

Tehingu põhimõisteid kirjeldab akronüüm ACID —

  • Atomicity — aatomilisus
  • Consistency — konsistentsus
  • Isolation — isolatsioon
  • Durability — kestvus

Aatomisus

garanteerib, et iga tehing pühendatakse ainult tervikuna (täielikult). Kui üks operatsioon järjestuses jääb sooritamata, tühistatakse kogu tehing. Sellega võetakse kasutusele mõiste «rollback» (tagasipööramine). See tähendab, et järjestuse sees toimuvad teatud muudatused, kuid lõpuks tühistatakse («rullitakse tagasi») kõik need ja lõpuks ei näe kasutaja ühtegi muudatust.

Järjepidevus

Konsistentsus tähendab, et iga lõpetatud tehing (tehing, mis on jõudnud tehingu lõpuni — end of transaction) salvestab ainult kehtivad tulemused. Näiteks raha ülekandmisel ühelt kontolt teisele, kui raha on ühelt kontolt lahkunud, peab see ka teisele kontole tulema (see on süsteemi järjepidevus). Debiteerimine ja krediteerimine on kaks erinevat tehingut, seega esimene tehing läheb läbi ilma vigadeta, kuid teine lihtsalt mitte. Seepärast on äärmiselt oluline seda omadust arvesse võtta ja süsteemi tasakaalu säilitada.

Isolatsioon

Iga tehing peab olema teistest tehingutest isoleeritud, st selle tulemus ei tohi sõltuda teiste paralleelsete tehingute täitmisest. Praktikas on isolatsiooni äärmiselt raske saavutada, mistõttu on siinkohal kasutusele võetud mõiste «isolatsioonitasemed» (tehing ei ole täielikult isoleeritud

Kestvus

See kontseptsioon tagab, et kui oleme saanud kinnituse, et tehing on sooritatud, siis ei tohiks selle tehingu põhjustatud muudatusi tühistada süsteemi rikke (nt voolukatkestuse) tõttu

Translated with www.DeepL.com/Translator (free version)

Tehingute haldamine

Tehingute haldamiseks kasutatakse järgmisi käske:

  • COMMIT
    Salvestab muudatused
  • ROLLBACK
    Tühistab (tühistab) muudatused
  • SAVEPOINT
    Loob punkti, kuhu tehingugrupp saab tagasi pöörduda.
  • SET TRANSACTION
    Määrab tehingu nime.

Transaction management käske kasutatakse ainult DML käskude puhul: INSERT, UPDATE, DELETE. Neid ei saa kasutada tabeli loomise, muutmise või kustutamise ajal.

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

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

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

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

Атомарность

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

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

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

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

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

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

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

Управление транзакциями

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

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

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

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')
INSERT INTO T(id, s) VALUES (5, 'fifth')
SELECT * FROM T;
--transaktsiooni loomine
BEGIN TRANSACTION;
INSERT INTO T(id, s) VALUES (6, 'sixth')
SELECT * FROM T;
--tegevuse tagasi võtmine
ROLLBACK;
SELECT * FROM T;
--loome tanskatsiooni, mis võtab tagasi kustutatud kirjed
BEGIN TRANSACTION;
DELETE FROM T WHERE id>1;
SELECT * FROM T;
--tegevuse tagasi võtmine
ROLLBACK;
SELECT * FROM T;

Ülesanne

Esimene

tabeli loomine ja andmete lisamine

CREATE TABLE developers (
    ID INT PRIMARY KEY,
    NAME VARCHAR(40),
    SPECIALTY VARCHAR(40),
    EXPERIENCE INT,
    SALARY INT
);
INSERT INTO developers (ID, NAME, SPECIALTY, EXPERIENCE, SALARY) 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),
(7, 'Ivan Ivanov', 'C#', 1, 900),
(8, 'Ludmila Geiko', 'UI/UX', 2, 1800);

SAVEPOINT

START TRANSACTION;
SAVEPOINT SP1;
DELETE FROM developers
WHERE SPECIALTY = 'C++';
SELECT * FROM developers;
ROLLBACK TO SP1;
SELECT * FROM developers

Vaatleme käsku SET TRANSACTION, mida kasutatakse andmebaasi tehingu algatamiseks. See käsk võimaldab meil määratleda tehingu omadused.
Näiteks kui me tahame määrata, et tehing on ainult lugemiseks, peaksime kasutama järgmist päringut:

SET TRANSACTION READ ONLY;

Kui aga soovime, et tehing võimaldaks andmete kirjutamist, on taotlus allpool esitatud kujul:

SET TRANSACTION READ WRITE;

Teine

Luua ja täita 2 tabelit

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')

Luua protseduur, mis töötab hästi ja muudab seega ainult tabeli andmeid

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

Ja see on sama protseduuri kasutamine, kuid see on valesti täidetud, mille tõttu tekib tagasipöördumine, st kui taotlus BEGIN-END TRY on vale, läheb kursus BEGIN-END CATCH, kus asub ROLLBACK, kuigi see on nii ka ilma selleta.

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