wyk10

Transakcje
Wykład 10
Prowadzący: dr Paweł Drozda
Wprowadzenie
 Zmiany świata rzeczywistego muszą
znaleźć odzwierciedlenie w bazie
(przejście ze stanu spójnego do
innego stanu spójnego)
 Problemy:
 Awaryjność
 Współbieżny dostęp do danych
 Rozproszenie baz
dr Paweł Drozda
Przykład wprowadzający
 Rezerwacja biletów lotniczych na lot X w
firmie A przez pasażera Y za kwotę Z
 Awaria
po dokonaniu zapłaty (przed wystawieniem
biletu) – częściowo wykonane operacje
 Dwie osoby w tym samym czasie rezerwują
ostatni bilet na dany lot
 Rozwiązanie - transakcja
dr Paweł Drozda
Transakcje
 Sekwencja logicznie powiązanych
operacji na bazie danych.
Przeprowadza bazę z jednego stanu
spójnego w inny stan spójny
 Dozwolone operacje:
 Odczyt, zapis danych
 Zakończenie transakcji
 Akceptację lub wycofanie transakcji
 Rozwiązuje problemy awaryjności,
wielodostępności i rozproszenia
dr Paweł Drozda
Transakcja – rezerwacja lotu
begin
UPDATE Loty
SET wolneMiejsca=wolneMiejsca-1
WHERE idlotu =X;
UPDATE Konta
SET saldo = saldo –Z WHERE klient=Y;
UPDATE Konta
SET saldo = saldo +Z WHERE klient=A;
INSERT INTO Pasażerowie(lot,klient)
VALUES (X,Y);
commit; // zatwierdzenie transakcji
dr Paweł Drozda
Własności transakcji ACID (1)
 Atomowość (Atomicity)
Wykonanie całej transakcji albo niewykonanie
żadnej operacji składowej
(odzwierciedlenie świata rzeczywistego)
 Spójność (Consistency)
Transakcja nie narusza spójności (w czasie
wykonywania transakcji baza może być
przejściowo niespójna)
dr Paweł Drozda
Własności transakcji ACID (2)
 Izolacja (Isolation)
Transakcje wykonywane jednocześnie nie
wpływają na siebie
 Trwałość (Durability)
Po zakończeniu transakcji
zaktualizowane dane nie mogą zostać w
żaden sposób utracone
dr Paweł Drozda
Zachowanie ACID - przykład
 Atomowość – gdy lot zostanie
zarezerwowany z zapłatą
 Spójność – taka sama kwota dodana co
odjęta; liczba miejsc w tabeli loty musi być
równa liczbie nowych rekordów w tabeli
Pasażerowie
 Izolacja – różne rezerwacje nie wpływają na
siebie
 Trwałość – odzwierciedlenie w bazie nowych
stanów
dr Paweł Drozda
Możliwe przebiegi transakcji
p. COMMITED
Start
COMMITED
READ/WRITE
FAILED
TERMINATED
Koniec transakcji – wszystkie operacje zostały wykonane.
Wybór:
-wprowadzić zmiany do bazy poprzez zatwierdzenie (commit)
-wycofać transakcję (rollback)
dr Paweł Drozda
Reprezentacja transakcji
 Operacje transakcji:




Zapis – w(x)
Odczyt – r(x)
Zatwierdzenie – c
wycofanie – a
 Reprezentacja za pomocą grafu G(V,A):
 V – węzły odpowiadające operacjom transakcji
 A – krawędzie reprezentujące porządek na zbiorze
operacji
dr Paweł Drozda
Reprezentacja transakcji - przykład
T1
a)
r(x)
r(y)
w(x)
w(y)
c
T2
b)
r(x)
r(y)
w(x)
r(z)
r(y)
w(y)
dr Paweł Drozda
c
Kontrola wielodostępu
 Konieczność zapewnienia dostępu do
bazy danych wielu użytkownikom
 Zapewnienie możliwości wykonania
współbieżnie transakcji
dr Paweł Drozda
Szeregowalność transakcji (1)
 Definicje:
 Harmonogram (realizacja) – ciąg operacji z
więcej niż jednej transakcji z zachowaniem
porządku w każdej z transakcji
 sekwencyjny – bez przeplatania operacji
pomiędzy transakcjami
 niesekwencyjny – operacje różnych transakcji
przeplatają się
 Szeregowalność – możliwość ustawienia
transakcji współbieżnych, tak aby wynik był
równoważny z wykonaniem sekwencyjnym
dr Paweł Drozda
Szeregowalność transakcji (2)
 Zapewnienie szeregowalności –
odpowiednie uporządkowanie operacji
zapisu i odczytu:
 transakcje czytają ten sam element –
kolejność odczytu nieistotna
 Transakcje czytają lub zapisują różne
elementy – kolejność wykonania
nieistotna
 Jedna z transakcji zapisuje element a
druga odczytuje lub zapisuje – kolejność
istotna
dr Paweł Drozda
Szeregowalność - przykład
T1
r(x)
w(x)
T2
r(y)
r(x)
w(y)
w(x)
commit
r(y)
w(y)
commit
Szeregowanie kolizji
T1
T2
r(x)
w(x)
r(y)
w(y)
commit
r(x)
dr Paweł Drozda
w(x)
r(y)
w(y)
commit
Graf szeregowalności
 Graf skierowany G(N,E) jest grafem
szeregowalności, gdzie:
 transakcje Ti obrazowane są przez wierzchołki
 Ti  Tj jest krawędzią grafu, gdy Tj zapisuje wartość
elementu wcześniej czytanego przez Ti
 Ti  Tj jest krawędzią grafu, gdy Tj czyta wartość
elementu zapisanego przez Ti
 Ti  Tj jest krawędzią grafu, gdy Tj zapisuje wartość
elementu wcześniej zapisanego przez Ti
 Jeśli w grafie G istnieje krawędź Ti  Tj to w
sekwencyjnym harmonogramie równoważnym
Ti występuje przed Tj
dr Paweł Drozda
Warunek szeregowalności grafu
 Harmonogram jest szeregowalny
wtedy i tylko wtedy, gdy graf
szeregowalności dla tego
harmonogramu jest acykliczny
T1
r(x)
x=x-30
T2
w(x)
r(y)
r(x)
r(y)
T1
x=x*1.1
w(x)
y=y*1.1
y=y+30
w(y)
commit
w(y)
T2
dr Paweł Drozda
Graf cykliczny
commit
Odtwarzalność
 Szeregowalność daje gwarancję
poprawności wykonania harmonogramu
transakcji, gdy wszystkie transakcje są
zaakceptowane (commited)
 Problem – jedna z transakcji wycofana
(przez użytkownika, awaria systemu itd.)
H = r1(x) w1(x), r2(x), w2(x), c2 r1(x), w1(x), <awaria>, c1
dr Paweł Drozda
Odtwarzalność - definicje
 Ti czyta daną x z transakcji Tj w
harmonogramie H jeżeli:
 wj(x)<ri(x)
 aj< ri(x)
 jeżeli istnieje operacja wk(x) taka, że wj(x)<
wk(x) <ri(x) to aj< ri(x)
 Ti czyta z transakcji Tj w harmonogramie H
jeżeli Ti czyta dowolną daną z transakcji Tj
w harmonogramie H
dr Paweł Drozda
Harmonogram odtwarzalny
 Harmonogram H jest odtwarzalny (RC)
wówczas, jeżeli Ti czyta z Tj w
harmonogramie H i ci należy do H to cj<ci
 Harmonogram H unika kaskadowych
wycofań (ACA) wówczas, jeżeli transakcja
Ti czyta z Tj, to cj<ri(x)
 Harmonogram H jest ścisły (ST) wówczas,
jeżeli wj(x)<oi(x), zachodzi aj<oi(x) lub
cj<oi(x), gdzie oi(x) jest jedną z operacji
ri(x) lub wi(x)
dr Paweł Drozda
Odtwarzalność - przykład
T1= w1(x) w1(y)w1(z)c1
T2= r2(u)w2(x) r2(y)w2(y)c2
H1 =
H2=
H3 =
H4 =
w1(x)w1(y)r2(u)w2(x)r2(y)w2(y)c2w1(z)c1
w1(x)w1(y)r2(u)w2(x)r2(y)w2(y)w1(z)c1c2
w1(x)w1(y)r2(u)w2(x)w1(z)c1r2(y)w2(y)c2
w1(x)w1(y)r2(u)w1(z)c1w2(x)r2(y)w2(y)c2
H1 – nie odtwarzalna, H2 – RC, H3 – RC, ACA,
H4 – RC,ACA,ST
dr Paweł Drozda
Odtwarzalność - zależności
ST  ACA  RC
dr Paweł Drozda
Algorytmy zarządzania współbieżnym
wykonaniem transakcji
 blokowanie - uszeregowanie transakcji
wynika z kolejności uzyskiwanych
blokad
 znaczników czasowych – uszeregowanie
wynika z wartości znaczników
czasowych związanych z transakcjami
 optymistyczne – walidacja poprawności
uszeregowania
dr Paweł Drozda
Blokowanie (1)
 Blokada – zmienna skojarzona z
każdą daną w bazie; określa
możliwość wykonania określonych
operacji
 Możliwe stany:
 dana nie zablokowana
 dana zablokowana do odczytu R
 dana zablokowana do zapisu W
dr Paweł Drozda
Blokowanie (2)
 Operacje SZBD związane z
blokowaniem:
 Blokowanie danej x do odczytu (LR(x))
 Blokowanie danej x do zapisu (LW(x))
 Odblokowanie danej x (UNL(x))
 Operacje blokowania muszą
poprzedzać operacje odczytu lub
zapisu zmiennej
dr Paweł Drozda
Blokowanie (3)
 Dwie blokady są kompatybilne, gdy
mogą być założone na jednej danej
przez dwie różne transakcje (tylko dwie
blokady odczytu są kompatybilne)
 Konwersja blokad – zmiana typu
blokady. Niedopuszczalna konwersja z
blokady do zapisu na blokadę do
odczytu
dr Paweł Drozda
Algorytm blokowania (1)
 Dla każdej danej dwie kolejki:
 Transakcji, które uzyskały dostęp do
danej
 Transakcji oczekujących na dostęp do
danej
dana idt blokada
x1
T1
W
x2
T1
R
x2
T2
R
x3
T2
W
dana idt blokada
kolejka
x1
T2
R
1
x1
T3
W
2
x2
T4
W
1
dr Paweł Drozda
Algorytm blokowania (2)
 Potrzebne funkcje
 Założenie blokady do odczytu – gdy dana
niezablokowana lub zablokowana do
odczytu– założenie blokady, w
przeciwnym razie transakcja czeka w
kolejce
 Założenie blokady do zapisu – gdy dana
jest niezablokowana – założenie blokady,
w przeciwnym razie do kolejki
 Zdjęcie blokady
dr Paweł Drozda
Algorytm blokowania - przykład
T1
T2
Rlock(T1,Y)
Rlock(T2,X)
r(Y)
r(X)
unlock(Y)
unlock(X)
Wlock(T1,X) Wlock(T2,Y)
r(X)
r(Y)
X=X+Y
Y=X+Y
w(X)
w(Y)
unlock(X)
unlock(Y)
X=20, Y=30
Współbieżnie:
X=50, Y=50
Sekwencyjnie T1T2
X=50, Y=80
Sekwencyjnie T2T1
X=70, Y=50
dr Paweł Drozda
Zakleszczenie transakcji
 Gdy dwie transakcje czekają
T1
T2
Rlock(T1,Y)
r(Y)
Rlock(T2,X)
r(X)
Wlock(T2,Y)
Wlock(T1,X)
wait
wait
wait
wait
wait
…
…
dr Paweł Drozda
Metody wykrywania i
rozwiązywania zakleszczeń
 Za pomocą grafu:
 transakcje jako węzły
 oczekiwanie transakcji Ti na daną zablokowaną
przez Tj reprezentowane przez krawędź
skierowaną
 cykl w grafie oznacza zakleszczenie
 eliminacja – wycofanie jednej z transakcji cyklu
 graf sprawdzany
 jeśli transakcja czeka zbyt długo – przekroczyła
ustalony limit czasu
 co określony czas
dr Paweł Drozda
Poziomy izolacji (1)
 Większość SZBD nie zapewnia
szeregowalności transakcji
 Poziom izolacji wprowadzany do znalezienia
kompromisu pomiędzy współbieżnością a
poprawnością wykonania transakcji
 Możliwe poziomy izolacji:




READ UNCOMMITTED
READ COMMITED
REPEATABLE READ
SERIALIZABLE
dr Paweł Drozda
Poziomy izolacji (2)
 SERIALIZABLE
 Odczytuje wyłącznie dane zatwierdzone
 Do momentu zakończenia transakcji żadna dana
nie zostanie zmieniona przez inną transakcje
 Blokuje dostęp do tabeli
 Zapewnia szeregowalność
 REPEATABLE READ
 Tak samo jak SERIALIZABLE – tylko, że blokuje
dostęp do danych a nie całej tabeli
 Pojawia się problem „fantomów” – gdy jedna
transakcja czyta dane, druga w tym samym czasie
dodaje lub uaktualnia
dr Paweł Drozda
Poziomy izolacji (3)
 READ COMMITTED
 Odczytuje tylko zatwierdzone dane
 Zapisane dane nie zostaną zmienione przez
inną transakcję do momentu zakończenia
transakcji
 Brak gwarancji, że dane odczytane nie
zostaną zmienione przez inną transakcję
 Problem niepowtarzalnego odczytu – gdy ta
sama dana jest czytana dwa razy a w
międzyczasie inna transakcja ją modyfikuje
dr Paweł Drozda
Poziomy izolacji (4)
 READ UNCOMMITTED
 Dopuszczenie odczytywania danych, które zostały zmienione
przez niezatwierdzone transakcje – może czytać dodane
wiersze które później będą wycofane (Czytanie na brudno)
 Najniższy poziom izolacji
 Poziom izolacji SQL – ustawiany przed
uruchomieniem transakcji
SET TRANSACTION ISOLATION LEVEL (SERIALIZABLE | REPEATABLE
READ | READ COMMITTED | READ UNCOMMITTED)
Sprawdzanie poziomu izolacji dla sesji:
select @@session.tx_isolation;
dr Paweł Drozda
Poziomy izolacji - zestawienie
Czytanie na
brudno
Niepowtarzalny
odczyt
Fantom
READ
UNCOMMITED
możliwe
możliwe
możliwe
READ
COMMITED
Nie występuje
możliwe
możliwe
REPEATABLE
READ
Nie występuje
Nie występuje
możliwe
SERIALIZABLE Nie występuje
Nie występuje
Nie
występuje
dr Paweł Drozda
Transakcje – SQL (1)
 Dostępne dla parametru engine
ustawionego na InnoDB (podobnie jak
przy wymuszaniu więzów integralności
referencyjnej)
CREATE TABLE Studenci(…) engine=InnoDB;
 Dwa tryby pracy z transakcjami
 autocommit – każda operacja od razu
zatwierdzana (chyba, że się zaznaczy transakcje)
 Bez autocommit – możliwość grupowania poleceń
w transakcję (konieczność potwierdzania commit
lub wycofania rollback)
dr Paweł Drozda
Transakcje – SQL (2)
 Ustawienie autocommit:
 SET AUTOCOMMIT =1; - domyślnie
 SET AUTOCOMMIT =0; - możliwość
grupowania instrukcji w transakcje
 Zmiana ustawienia na stałe:
 Zapis w pliku my.ini lub my.cnf w sekcji
[mysqld] init_connect=‘SET AUTOCOMMIT=0’
 Sprawdzenie ustawienia trybu
autocommit:
 SELECT @@AUTOCOMMIT;
dr Paweł Drozda
Transakcje SQL (3)
 Polecenia:
 START TRANSACTION – rozpoczyna
transakcje
 AUTOCOMMIT=0 – START TRANSACTION
niepotrzebne – uruchamiane automatycznie
 AUTOCOMMIT=1 – START TRANSACTION
uruchamia transakcje, COMMIT lub ROLLBACK
kończy blok transakcji
 COMMIT – zatwierdza wykonanie transakcji
 ROLLBACK – wycofuje transakcje
dr Paweł Drozda
Transakcja przykład
Student(id,nazwisko,rok)
SET AUTOCOMMIT=0;
UPDATE Student set rok=rok+1;
INSERT INTO Student VALUES(123,’Nowak’,3);
EXIT; - zmiany niezachowane
COMMIT; EXIT; - zmiany zachowane
SET AUTOCOMMIT=1;
UPDATE Student set rok=rok+1;
START TRANSACTION;
INSERT INTO Student VALUES(123,’Nowak’,3);
ROLLBACK;
insert niezachowany, update zachowany
dr Paweł Drozda
Transakcje SQL (4)
 Blokowanie wierszy dla InnoDB:
 Blokowanie do aktualizacji:
 SELECT … FROM … WHERE … FOR
UPDATE;
 Blokowanie do czytania:
 SELECT … FROM … WHERE … LOCK IN
SHARE MODE;
dr Paweł Drozda
Transakcje - przykład
AUTOCOMMIT=1;
BEGIN TRANSACTION;
SELECT rok FROM Student FOR UPDATE;
UPDATE Student SET rok=rok+1;
COMMIT;
AUTOCOMMIT=1;
BEGIN TRANSACTION;
SELECT * FROM Student WHERE nazwisko=‘kowal’ LOCK IN SHARE
MODE;
INSERT INTO Oceny VALUES(1,2,4);
COMMIT;
dr Paweł Drozda
Transakcje SQL (5)
 Blokowanie tabel – dla parametru
engine ustawionego na MyISAM –
domyślnie
 Polecenia:
 LOCK TABLES tabela1 typBlokady, …;
 typBlokady = READ|WRITE
 UNLOCK TABLES; - odblokowuje dostęp
do tabel
dr Paweł Drozda
Transakcje – przykład
Sesja1
sesja2
LOCK TABLES Student READ, Oceny WRITE;
SELECT * FROM Student;
- zostanie wykonana
SELECT * FROM Oceny;
- czeka na odblokowanie tabeli
Oceny
UNLOCK TABLES;
wykonanie zapytania
dr Paweł Drozda