SET autocommit=0;
--loo tabel
CREATE TABLE developers(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(40),
specialty varchar(20),
experience int,
salary int
);
insert into developers (name,specialty,experience,salary)
values('Eugene Suleimanov','Java',2,2500);
insert into developers (name,specialty,experience,salary)
values('Peter Romanenko','Java',3,3500);
insert into developers (name,specialty,experience,salary)
values('Andrei Komarov','C++',3,2500);
insert into developers (name,specialty,experience,salary)
values('Konstantin Geiko','C#',2,2000);
insert into developers (name,specialty,experience,salary)
values('Asya Suleimanova','UI/UX',2,1800);
insert into developers (name,specialty,experience,salary)
values('Ivan Ivanov','C#',1,900);
insert into developers (name,specialty,experience,salary)
values('Ludmila Geiko','UI/UX',2,1800);
select * from developers;
--loome transaktiooni, mis võtab tagasi kustutatud
DELETE FROM developers
WHERE SPECIALTY = 'C++';
select * from developers;
COMMIT;
ROLLBACK;
select * from developers;
C++ töötajad ei taganud sest me teeme COMMIT;
Proovime SAVEPOINT
SAVEPOINT SP1;
Kustutame mitu töötajad
DELETE FROM developers WHERE ID = 7;
DELETE FROM developers WHERE ID = 6;
DELETE FROM developers WHERE ID = 5;
Tagame kõik andmed
ROLLBACK TO SP1;
Kustutame SAVEPOINT
RELEASE SAVEPOINT SP1;
Kui me taheme et transaaktion peada töötada ainult lugemiseks seda kasutame
SET TRANSACTION READ ONLY;
Kui me taheme et transaaktion peada täidma andmeid kirjutamine seda kasutame
SET TRANSACTION READ WRITE;
Ülesanne 2
Loon tabel ja lisan andmeid
insert into tblProduct(name,unitPrice,qtyAvaible)
values('Laptops',2340,100);
insert into tblProduct(name,unitPrice,qtyAvaible)
values('Desktops',3467,20);
select * from tblProduct;
Uuendan andmeid ja loon transaktioon
begin transaction;
update tblProduct set qtyAvaible = 50 where productid = 1
select * from tblProduct;
READ UNCOMMITTED – Näitab, et väljavõtted saavad lugeda ridu, mida on muude tehingutega muudetud, kuid mida pole veel kinnitatud.
set transaction isolation level read uncommitted
Tagan andmeid
rollback transaction;
select * from tblProduct;
Uuesti uuendan andmeid ja proovime kasutada COMMIT
begin transaction;
update tblProduct set qtyAvaible = 300 where productid = 1
select * from tblProduct;
commit transaction;
nüüd me saame ei kasuta READ UNCOMMITTED
Loon uued tabelid ja lisan andmeid
create table tblPhysicalAddress(
addressid int primary key identity(1,1),
employeeNumber int,
houseNumber varchar(10),
streetAddress varchar(30),
city varchar(30),
postalCode varchar(10)
);
create table tblMailingAddress(
addressid int primary key identity(1,1),
employeeNumber int,
houseNumber varchar(10),
streetAddress varchar(30),
city varchar(30),
postalCode varchar(10)
);
insert into tblMailingAddress(employeeNumber,houseNumber,streetAddress,city,postalCode)
values(101,'#10','King Street','LONDOON','CR27DW');
insert into tblPhysicalAddress(employeeNumber,houseNumber,streetAddress,city,postalCode)
values(101,'#10','King Street','LONDOON','CR27DW');
Loo protsedur kasutamiga transaktioon – kui kõik on hästi me näeme ‘Transaction comitted’ muidu ‘Transaction rolled back”
create Procedure spUpdateAddress
as
Begin
Begin try
Begin Transaction
update tblMailingAddress set city = 'LONDOD'
where addressid = 1 and employeeNumber = 101
update tblPhysicalAddress set city = 'LONDOD'
where addressid = 1 and employeeNumber = 101
commit transaction
print 'Transaction comitted'
end try
begin catch
rollback transaction
print 'Transaction rolled back'
end catch
end
Kontroll protsedur
spUpdateAddress
Uuendan protseduur kuid teen vale UPDATE
alter Procedure spUpdateAddress
as
Begin
Begin try
Begin Transaction
update tblMailingAddress set city = 'LONDOD'
where addressid = 1 and employeeNumber = 101
update tblPhysicalAddress set city = 12345678911234566789123456789123
where addressid = 1 and employeeNumber = 101
commit transaction
print 'Transaction comitted'
end try
begin catch
rollback transaction
print 'Transaction rolled back'
end catch
end
Ja kontroll et kõik on töö
spUpdateAddress
