Ülesanne SQL transaktsioonid

1
2
3
4
5
6
create table developers(
id int not null primary key,
NAME varchar(100),
SPECIALITY varchar(50),
EXPERIANCE smallint,
SALARY int);
1
SET autocommit=0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
start TRANSACTION;
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (1, 'Eugene Suleimanov', 'JAVA', 2, 2500);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (2, 'Peter Romanenko', 'JAVA', 3, 3500);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (3, 'Andrei Komarov', 'C++', 3, 2500);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (4, 'Konstantin Geiko', 'C#', 2, 2000);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (5, 'Asya Suleimanova', 'UI/UX', 2, 1800);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (7, 'Ivan Ivanov', 'C#', 1, 900);
insert into Developers(id, NAME, SPECIALITY, EXPERIANCE, SALARY)
values (8, 'Ludmila Geiko ', 'UI/UX', 2, 1800);
commit;
loome tabelit ja sisestame andmed tabelise developers
1
2
3
4
5
6
start TRANSACTION;
select * from developers;
DELETE FROM developers WHERE SPECIALITY = 'C++';
select * from developers;
rollback;
select * from developers;
näitame tabeli sisu
kustutanud andmed
tagastame rollback

Ülesanne SQL transaktsioonid osa 3

1
2
3
4
5
6
7
8
9
Create Table tblMailingAddress
(
   AddressId int NOT NULL primary key,
   EmployeeNumber int,
   HouseNumber nvarchar(50),
   StreetAddress nvarchar(50),
   City nvarchar(10),
   PostalCode nvarchar(50)
)
loome tabeli tblMailingAddress
1
Insert into tblMailingAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')
sisestame andmed tabelise tblMailingAddress
1
2
3
4
5
6
7
8
9
Create Table tblPhysicalAddress
(
 AddressId int NOT NULL primary key,
 EmployeeNumber int,
 HouseNumber nvarchar(50),
 StreetAddress nvarchar(50),
 City nvarchar(10),
 PostalCode nvarchar(50)
)
loome tabeli tblPhysicalAddress
1
Insert into tblPhysicalAddress values (1, 101, '#10', 'King Street', 'Londoon', 'CR27DW')
lisame andmed tabelise tblPhysicalAddress
1
2
3
4
5
create table tblProducts(
ProductId int primary key,
Name varchar(50),
UnitPrice int,
QtyAvailable int);
loome tabeli tblProducts
1
2
3
4
insert into tblProducts(ProductId, Name, UnitPrice, QtyAvailable)
values(1, 'Laptops', 2340, 200);
insert into tblProducts(ProductId, Name, UnitPrice, QtyAvailable)
values(2, 'Desktops', 3467, 20);
lisame andmed tabelise tblProducts
1
update tblProducts set QtyAvailable = 250 where ProductId = 1
1
2
begin transaction
update tblProducts set QtyAvailable = 300 where ProductId = 1
1. update mitte transaktsioonita ei saa tagastada selle numbri mis oli ilma transaktsioonita
2. teeme transaktsiooni ja muudame QtyAvailable = 300 ja siis tagastame rollback -> 250
3. QtyAvailable ei saa olla 200 kuna ta pole tehtud transaktsioonis ja meil pole sellest infot ehk savepoint või transaktsiooni
1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
1
rollback transaction
1
2
3
begin transaction
update tblProducts set QtyAvailable = 300 where ProductId = 1
commit transaction
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
        Print 'Transaction Commited'
    end try
    begin catch
        rollback transaction
    end catch
end
1
2
select * from tblMailingAddress;
select * from tblPhysicalAddress;
alguses meil oli tabelid tblMailingAddress ja tblMailingAddress kus city oli Londoon ja protseduuriga me oleme teinud transaktsiooni kus me muudame nime Londoon -> LONDON -ks ja kui see õnnestus teha (protseduuri) siis ta näitab, et Transaction Commited ehk transaktsioon on tehtud ja salvestatud
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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
        Print 'Transaction Commited'
    end try
    begin catch
        rollback transaction
        print 'transaction rolled back'
    end catch
end
oleme muutnud protseduuris tblMailingAddress LONDON -> LONDON1 ja tblPhysicalAddress LONDON -> LONDON LONDON
ja prindime rollback juhul transaction rolled back
protseduur ei õnnestus kuna City pikkus on 10, aga teises tabelis on >10 ja saame tulemusena 1 tbl õnnestus muutma ja teisel mitte ehk 0. Kuna transaktsioon ei õnnestus midagi ei muutunud meie tabeli sisuga.