Transakcje Wykad 10 Prowadzcy dr Pawe Drozda Wprowadzenie

Transakcje Wykład 10 Prowadzący: dr Paweł Drozda

Wprowadzenie o Zmiany świata rzeczywistego muszą znaleźć odzwierciedlenie w bazie (przejście ze stanu spójnego do innego stanu spójnego) o Problemy: n Awaryjność n Współbieżny dostęp do danych n Rozproszenie baz dr Paweł Drozda

Przykład wprowadzający o Rezerwacja biletów lotniczych na lot X w firmie A przez pasażera Y za kwotę Z n Awaria po dokonaniu zapłaty (przed wystawieniem biletu) – częściowo wykonane operacje n Dwie osoby w tym samym czasie rezerwują ostatni bilet na dany lot o Rozwiązanie - transakcja dr Paweł Drozda

Transakcje o Sekwencja logicznie powiązanych operacji na bazie danych. Przeprowadza bazę z jednego stanu spójnego w inny stan spójny o Dozwolone operacje: n Odczyt, zapis danych n Zakończenie transakcji n Akceptację lub wycofanie transakcji o Rozwiązuje problemy awaryjności, wielodostępności i rozproszenia dr Paweł Drozda

Transakcja – rezerwacja lotu begin UPDATE Loty SET wolne. Miejsca=wolne. Miejsca-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) o Atomowość (Atomicity) Wykonanie całej transakcji albo niewykonanie żadnej operacji składowej (odzwierciedlenie świata rzeczywistego) o 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) o Izolacja (Isolation) Transakcje wykonywane jednocześnie wpływają na siebie o Trwałość (Durability) Po zakończeniu transakcji zaktualizowane dane nie mogą zostać w żaden sposób utracone dr Paweł Drozda

Zachowanie ACID - przykład o Atomowość – gdy lot zostanie zarezerwowany z zapłatą o 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 o Izolacja – różne rezerwacje nie wpływają na siebie o 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 o Operacje transakcji: n n Zapis – w(x) Odczyt – r(x) Zatwierdzenie – c wycofanie – a o Reprezentacja za pomocą grafu G(V, A): n V – węzły odpowiadające operacjom transakcji n A – krawędzie reprezentujące porządek na zbiorze operacji dr Paweł Drozda

Reprezentacja transakcji - przykład T 1 a) r(x) r(y) w(x) w(y) c T 2 b) r(x) r(y) w(x) r(z) r(y) w(y) dr Paweł Drozda c

Kontrola wielodostępu o Konieczność zapewnienia dostępu do bazy danych wielu użytkownikom o Zapewnienie możliwości wykonania współbieżnie transakcji dr Paweł Drozda

Szeregowalność transakcji (1) o Definicje: n Harmonogram (realizacja) – ciąg operacji z więcej niż jednej transakcji z zachowaniem porządku w każdej z transakcji o sekwencyjny – bez przeplatania operacji pomiędzy transakcjami o niesekwencyjny – operacje różnych transakcji przeplatają się n Szeregowalność – możliwość ustawienia transakcji współbieżnych, tak aby wynik był równoważny z wykonaniem sekwencyjnym dr Paweł Drozda

Szeregowalność transakcji (2) o Zapewnienie szeregowalności – odpowiednie uporządkowanie operacji zapisu i odczytu: n transakcje czytają ten sam element – kolejność odczytu nieistotna n Transakcje czytają lub zapisują różne elementy – kolejność wykonania nieistotna n Jedna z transakcji zapisuje element a druga odczytuje lub zapisuje – kolejność istotna dr Paweł Drozda

Szeregowalność - przykład T 1 r(x) w(x) T 2 r(y) r(x) w(y) w(x) commit r(y) w(y) commit Szeregowanie kolizji T 1 T 2 r(x) w(x) r(y) w(y) commit r(x) dr Paweł Drozda w(x) r(y) w(y) commit

Graf szeregowalności o Graf skierowany G(N, E) jest grafem szeregowalności, gdzie: n transakcje Ti obrazowane są przez wierzchołki n Ti Tj jest krawędzią grafu, gdy Tj zapisuje wartość elementu wcześniej czytanego przez Ti n Ti Tj jest krawędzią grafu, gdy Tj czyta wartość elementu zapisanego przez Ti n Ti Tj jest krawędzią grafu, gdy Tj zapisuje wartość elementu wcześniej zapisanego przez Ti o 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 o Harmonogram jest szeregowalny wtedy i tylko wtedy, gdy graf szeregowalności dla tego harmonogramu jest acykliczny T 1 r(x) x=x-30 T 2 w(x) r(y) r(x) w(y) w(x) commit r(y) T 1 x=x*1. 1 y=y+30 y=y*1. 1 w(y) T 2 dr Paweł Drozda Graf cykliczny commit

Odtwarzalność o Szeregowalność daje gwarancję poprawności wykonania harmonogramu transakcji, gdy wszystkie transakcje są zaakceptowane (commited) o Problem – jedna z transakcji wycofana (przez użytkownika, awaria systemu itd. ) H = r 1(x) w 1(x), r 2(x), w 2(x), c 2 r 1(x), w 1(x), <awaria>, c 1 dr Paweł Drozda

Odtwarzalność - definicje o Ti czyta daną x z transakcji Tj w harmonogramie H jeżeli: n wj(x)<ri(x) n aj< ri(x) n jeżeli istnieje operacja wk(x) taka, że wj(x)< wk(x) <ri(x) to aj< ri(x) o 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 o 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 o Harmonogram H unika kaskadowych wycofań (ACA) wówczas, jeżeli transakcja Ti czyta z Tj, to cj<ri(x) o 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 T 1= w 1(x) w 1(y)w 1(z)c 1 T 2= r 2(u)w 2(x) r 2(y)w 2(y)c 2 H 1= H 2= H 3= H 4= w 1(x)w 1(y)r 2(u)w 2(x)r 2(y)w 2(y)c 2 w 1(z)c 1 w 1(x)w 1(y)r 2(u)w 2(x)r 2(y)w 1(z)c 1 c 2 w 1(x)w 1(y)r 2(u)w 2(x)w 1(z)c 1 r 2(y)w 2(y)c 2 w 1(x)w 1(y)r 2(u)w 1(z)c 1 w 2(x)r 2(y)w 2(y)c 2 H 1 – nie odtwarzalna, H 2 – RC, H 3 – RC, ACA, H 4 – RC, ACA, ST dr Paweł Drozda

Odtwarzalność - zależności dr Paweł Drozda

Algorytmy zarządzania współbieżnym wykonaniem transakcji o blokowanie - uszeregowanie transakcji wynika z kolejności uzyskiwanych blokad o znaczników czasowych – uszeregowanie wynika z wartości znaczników czasowych związanych z transakcjami o optymistyczne – walidacja poprawności uszeregowania dr Paweł Drozda

Blokowanie (1) o Blokada – zmienna skojarzona z każdą daną w bazie; określa możliwość wykonania określonych operacji o Możliwe stany: n dana nie zablokowana n dana zablokowana do odczytu R n dana zablokowana do zapisu W dr Paweł Drozda

Blokowanie (2) o Operacje SZBD związane z blokowaniem: n Blokowanie danej x do odczytu (LR(x)) n Blokowanie danej x do zapisu (LW(x)) n Odblokowanie danej x (UNL(x)) o Operacje blokowania muszą poprzedzać operacje odczytu lub zapisu zmiennej dr Paweł Drozda

Blokowanie (3) o Dwie blokady są kompatybilne, gdy mogą być założone na jednej danej przez dwie różne transakcje (tylko dwie blokady odczytu są kompatybilne) o Konwersja blokad – zmiana typu blokady. Niedopuszczalna konwersja z blokady do zapisu na blokadę do odczytu dr Paweł Drozda

Algorytm blokowania (1) o Dla każdej danej dwie kolejki: n Transakcji, które uzyskały dostęp do danej n Transakcji oczekujących na dostęp do danej dana idt blokada x 1 T 1 W x 2 T 1 R x 2 T 2 R x 3 T 2 W dana idt blokada kolejka x 1 T 2 R 1 x 1 T 3 W 2 x 2 T 4 W 1 dr Paweł Drozda

Algorytm blokowania (2) o Potrzebne funkcje n Założenie blokady do odczytu – gdy dana niezablokowana lub zablokowana do odczytu– założenie blokady, w przeciwnym razie transakcja czeka w kolejce n Założenie blokady do zapisu – gdy dana jest niezablokowana – założenie blokady, w przeciwnym razie do kolejki n Zdjęcie blokady dr Paweł Drozda

Algorytm blokowania - przykład T 1 T 2 Rlock(T 1, Y) Rlock(T 2, X) r(Y) r(X) unlock(Y) unlock(X) Wlock(T 1, X) Wlock(T 2, 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 T 1 T 2 X=50, Y=80 Sekwencyjnie T 2 T 1 X=70, Y=50 dr Paweł Drozda

Zakleszczenie transakcji o Gdy dwie transakcje czekają T 1 T 2 Rlock(T 1, Y) r(Y) Rlock(T 2, X) r(X) Wlock(T 2, Y) Wlock(T 1, X) wait wait … … dr Paweł Drozda

Metody wykrywania i rozwiązywania zakleszczeń o Za pomocą grafu: n transakcje jako węzły n oczekiwanie transakcji Ti na daną zablokowaną przez Tj reprezentowane przez krawędź skierowaną n cykl w grafie oznacza zakleszczenie n eliminacja – wycofanie jednej z transakcji cyklu n graf sprawdzany o jeśli transakcja czeka zbyt długo – przekroczyła ustalony limit czasu o co określony czas dr Paweł Drozda

Poziomy izolacji (1) o Większość SZBD nie zapewnia szeregowalności transakcji o Poziom izolacji wprowadzany do znalezienia kompromisu pomiędzy współbieżnością a poprawnością wykonania transakcji o Możliwe poziomy izolacji: n n READ UNCOMMITTED READ COMMITED REPEATABLE READ SERIALIZABLE dr Paweł Drozda

Poziomy izolacji (2) o SERIALIZABLE o Odczytuje wyłącznie dane zatwierdzone o Do momentu zakończenia transakcji żadna dana nie zostanie zmieniona przez inną transakcje o Blokuje dostęp do tabeli o Zapewnia szeregowalność o REPEATABLE READ o Tak samo jak SERIALIZABLE – tylko, że blokuje dostęp do danych a nie całej tabeli o Pojawia się problem „fantomów” – gdy jedna transakcja czyta dane, druga w tym samym czasie dodaje lub uaktualnia dr Paweł Drozda

Poziomy izolacji (3) o READ COMMITTED o Odczytuje tylko zatwierdzone dane o Zapisane dane nie zostaną zmienione przez inną transakcję do momentu zakończenia transakcji o Brak gwarancji, że dane odczytane nie zostaną zmienione przez inną transakcję o 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) o READ UNCOMMITTED o 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) o Najniższy poziom izolacji o 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 READ COMMITED Nie występuje możliwe REPEATABLE READ Nie występuje możliwe SERIALIZABLE Nie występuje dr Paweł Drozda

Transakcje – SQL (1) o Dostępne dla parametru engine ustawionego na Inno. DB (podobnie jak przy wymuszaniu więzów integralności referencyjnej) CREATE TABLE Studenci(…) engine=Inno. DB; o Dwa tryby pracy z transakcjami n autocommit – każda operacja od razu zatwierdzana (chyba, że się zaznaczy transakcje) n Bez autocommit – możliwość grupowania poleceń w transakcję (konieczność potwierdzania commit lub wycofania rollback) dr Paweł Drozda

Transakcje – SQL (2) o Ustawienie autocommit: n SET AUTOCOMMIT =1; - domyślnie n SET AUTOCOMMIT =0; - możliwość grupowania instrukcji w transakcje n Zmiana ustawienia na stałe: o Zapis w pliku my. ini lub my. cnf w sekcji [mysqld] init_connect=‘SET AUTOCOMMIT=0’ o Sprawdzenie ustawienia trybu autocommit: n SELECT @@AUTOCOMMIT; dr Paweł Drozda

Transakcje SQL (3) o Polecenia: n START TRANSACTION – rozpoczyna transakcje o AUTOCOMMIT=0 – START TRANSACTION niepotrzebne – uruchamiane automatycznie o AUTOCOMMIT=1 – START TRANSACTION uruchamia transakcje, COMMIT lub ROLLBACK kończy blok transakcji n COMMIT – zatwierdza wykonanie transakcji n 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) o Blokowanie wierszy dla Inno. DB: n Blokowanie do aktualizacji: o SELECT … FROM … WHERE … FOR UPDATE; n Blokowanie do czytania: o 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) o Blokowanie tabel – dla parametru engine ustawionego na My. ISAM – domyślnie o Polecenia: n LOCK TABLES tabela 1 typ. Blokady, …; o typ. Blokady = READ|WRITE n UNLOCK TABLES; - odblokowuje dostęp do tabel dr Paweł Drozda

Transakcje – przykład Sesja 1 sesja 2 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