Zarzdzanie transakcjami Wprowadzenie do baz danych Transakcja n

Zarządzanie transakcjami Wprowadzenie do baz danych

Transakcja n Transakcjami nazywamy procedury, które wprowadzają zmiany n n n do bazy danych lub które wyszukują dane Transakcją może być jedna instrukcja lub zbiór instrukcji tworzących pewną logiczną jednostkę pracy O transakcjach mówimy w kontekście współużytkowania danych przez wielu użytkowników i powstawania ewentualnych wzajemnych konfliktów Przyczyną powstawania konfliktów są modyfikacje danych w środowisku wielu użytkowników Konflikty mogą prowadzić do nieprawidłowych odczytów danych a nieprawidłowe odczyty do nieprawidłowych zapisów System zarządzania bazą danych powinien rozwiązać potencjalne konflikty powstające przy przetwarzaniu transakcji

Wymagania stawiane transakcjom n Niepodzielność – albo wszystkie modyfikacje wchodzące w n n skład transakcji są wykonane albo żadna Spójność – wszystkie transakcje muszą zachować spójność i integralność bazy danych Izolacja – jeśli transakcja modyfikuje dane, to te dane mogą być czasowo niespójne, dlatego muszą być niedostępne dla innych transakcji dopóty, dopóki transakcja nie skończy ich używać Trwałość – gdy transakcja się kończy, to wszystkie zmiany przez nią dokonane muszą zostać w pełni utrwalone – nawet w przypadku awarii sprzętu (ang. ACID – Atomicity, Consistency, Isolation, Durability)

Właściwości transakcji n Transakcja to jedna modyfikacja lub seria modyfikacji bazy danych traktowana tak jakby była to jedna modyfikacja n Jeśli modyfikacje zachodzą, to wszystkie zachodzą w jednej chwili n Użytkownicy mogą zobaczyć stan bazy danych przed transakcją lub po jej zakończeniu, ale nigdy w trakcie

Zapętlone więzy i pierwszy kontrakt Żeby podpisać kontrakt trzeba mieć legitymację, Żeby dostać legitymację trzeba mieć podpisany kontrakt

Odraczanie sprawdzania więzów n Odroczenie sprawdzania więzów możliwe jest na czas trwania transakcji i stosowane w przypadkach więzów zapętlonych. n Więzy można zadeklarować jako n n DEFERRABLE – możliwe do odroczenia lub jako, NOT DEFERRABLE (wartość domyślna) – sprawdzane natychmiast po wykonaniu instrukcji. n Więzy odraczalne DEFERRABLE można zadeklarować jako n INITIALLY DEFERRED – odraczane do końca transakcji, n INITIALLY IMMEDIATE – sprawdzane po wykonaniu każdej instrukcji. n Określenie trybu sprawdzania ograniczeń SET CONSTRAINS {nazwa_ograniczenia | ALL} {DEFERRED | IMMEDIATE}

Przykłady transakcji i zagrożeń n Rezerwacja miejsca w samolocie – możliwe dwukrotne sprzedanie tego samego miejsca n Zwiększanie pensji 300 pracowników – możliwa awaria sprzętu w trakcie zapisu na dysk n Transfer pieniędzy z konta na konto – wszystkie operacje muszą być wykonana albo żadna n W trakcie transferu pieniędzy z konta na konto ktoś sprawdza sumę wszystkich kont, i nie może widzieć stanu pośredniego (z jednego konta pieniądze zniknęły, a na drugim się nie pojawiły)

Zatwierdzanie transakcji n n W języku SQL do zatwierdzenia transakcji służy instrukcja COMMIT Do odwołania transakcji służy polecenie ROLLBACK W standardzie języka SQL nie ma instrukcji zaczynającej transakcję SQL Server umożliwia przeprowadzanie transakcji w 3 trybach n n n Auto. Commit – każde polecenie SQL stanowi odrębną transakcję Explicit – transakcję trzeba rozpocząć poleceniem BEGIN TRAN[SACTION] i zakończyć poleceniem COMMIT lub ROLLBACK Implicit – transakcja rozpoczyna się automatycznie po wykonaniu 1 polecenia SQL i trzeba ją zakończyć jawnie poleceniem COMMIT lub ROLLBACK n Postgre. SQL domyślnie pracuje w trybie automatycznego zatwierdzania transakcji n Aby jawnie rozpocząć transakcję, należy wydać instrukcję BEGIN

Problem utraconej modyfikacji Transakcja 1 Transakcja 2 BEGIN Wartość pola Miejsca = 10 Odczyt (Miejsca = 10) BEGIN Miejsca = 10 Miejsca = Miejsca + 1 Odczyt (Miejsca = 10) Miejsca = 10 UPDATE Miejsca = 11 Miejsca = Miejsca + 5 Miejsca = 11 COMMIT UPDATE Miejsca = 15 COMMIT Miejsca = 15

Problem niezatwierdzonej zależności Transakcja 1 Transakcja 2 Wartość pola Miejsca BEGIN Miejsca = 10 Odczyt (Miejsca = 10) Miejsca = 10 Miejsca = Miejsca + 1 Miejsca = 10 UPDATE Miejsca = 11 ROLLBACK BEGIN Miejsca = 11 Odczyt (Miejsca = 11) Miejsca = 11 Miejsca = Miejsca + 5 = 16 Miejsca = 10 UPDATE Miejsca = 16 COMMIT Miejsca = 16

Częściowe rozwiązanie problemu n Zapisując nową wartość, w miejsce starej, należy sprawdzić czy stara wartość nie została zmieniona od czasu ostatniego odczytu (wartość ostatniego odczytu należy zapamiętać) n UPDATE SET Miejsca = nowa_wartość WHERE Miejsca = poprzednio_odczytana_wartość AND … n Jeśli wartość Miejsca uległa zmianie od ostatniego odczytu, to modyfikacja nie zostanie wykonana i otrzymamy komunikat o aktualizacji 0 liczby wierszy n Jeśli wartość Miejsca nie uległa zmianie od ostatniego odczytu, to modyfikacja zostanie wykonana i otrzymamy komunikat o aktualizacji 1 wiersza

Izolacja transakcji – język SQL n W języku SQL określono 4 poziomy izolacji transakcji n W celu zdefiniowania poziomów izolacji transakcji określono 3 n n rodzaje nieprawidłowych odczytów Poziom izolacji transakcji określa dozwolone dla danego poziomu nieprawidłowe odczyty Im wyższy poziom izolacji, tym mniej dopuszczalnych nieprawidłowych odczytów Im wyższy poziom izolacji tym wolniejsza praca systemu zarządzania bazą danych Dopuszczenie nieprawidłowych odczytów przyspiesza pracę systemu zarządzania bazą danych w porównaniu z najwyższym poziomem izolacji - szeregowym

Nieprawidłowe odczyty n Brudny odczyt (Dirty read): pierwsza transakcja modyfikuje wiersz, a druga go czyta, zanim zmiana została zatwierdzona przez instrukcję COMMIT. Jeśli pierwsza transakcja została anulowana, zmiana nie miała miejsca i druga transakcja przeczytała wiersz, który naprawdę nigdy nie istniał n Odczyt bez powtórzeń (Non-repeatable read): pierwsza transakcja czyta wiersz. Druga go usuwa lub modyfikuje i wykonuje COMMIT przed pierwszą. Teraz pierwsza transakcja mogłaby przeczytać ten sam wiersz jeszcze raz i otrzymać inne wyniki

Nieprawidłowe odczyty n Odczyt widmo (Phantom): pierwsza transakcja odczytuje wiersze spełniające predykat. Druga wstawia wartości (instrukcja INSERT) lub je modyfikuje (instrukcja UPDATE) tak, że one również spełniają predykat. Następne wykonanie tego samego zapytania przez pierwszą transakcję da inne wyniki

Poziomy izolacji i dozwolone błędne odczyty Poziom izolacji Brudny odczyt Odczyt bez powtórzeń Odczyt widmo READ UNCOMMITED TAK TAK READ COMMITED NIE TAK REPEATABLE READ NIE TAK SERIALIZABLE NIE NIE

Określanie poziomu izolacji n SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITED | READ COMMITED | REPEATABLE READ | SERIALIZABLE} READ WRITE | READ ONLY n Postgre. SQL obsługuje tylko poziomy: READ COMMITED (domyślny) i SERIALIZABLE wybranie innego poziomu izolacji powoduje ustawienie odpowiedniego wyższego poziomu

Szeregowy harmonogram transakcji n Transakcje są wykonywane kolejno bez przeplatania się operacji między transakcjami n Każda transakcja musi się zakończyć zanim zacznie się nowa transakcja n Szeregowy harmonogram transakcji gwarantuje, że baza danych nigdy nie znajdzie się w stanie niespójnym

Nieszeregowy harmonogram transakcji n W środowisku wielu użytkowników wiele transakcji może nie komunikować się ze sobą i być wykonywane równolegle n Szeregowalność to określenie takich harmonogramów nieszeregowych, które zachowują się jak szeregowe n Harmonogram nieszeregowy jest poprawny, jeśli tworzy taki sam wynik jak pewne wykonanie szeregowe n W celu zapewnienia szeregowalności wiele systemów stosuje blokady

Przykład n Linie lotnicze sprzedają bilety na 100 lotów dziennie, samolotami o średniej liczbie miejsc w samolocie 120 n Daje to średnio 12000 biletów dziennie, 500 biletów na godzinę, 8 biletów na minutę n Szeregowanie wszystkich transakcji jest trudne z uwagi na czas trwania jednej transakcji, szczególnie, gdy klient długo się zastanawia n Szeregowanie wszystkich transakcji jest także zbyteczne, gdyż transakcje sprzedaży biletów na poszczególne loty są wzajemnie niezależnie n Szeregowanie transakcji sprzedaży biletów na pojedynczy lot jest w pełni wykonalne – szczególnie, gdy uwzględnimy, że bilety na jeden lot są sprzedawane przez kilka dni

Blokady n Wiele baz danych implementuje izolację transakcji za pomocą blokad, które ograniczają dostęp do danych, używanych przez transakcję, innym transakcjom n Istnieją dwa typy blokad n Blokada współdzielona (shared lock), która pozwala innym użytkownikom odczytywać dane, ale nie pozwala na ich aktualizację n Blokada wyłączna, która nie zezwala innym transakcjom nawet na czytanie danych

Przykład blokady, MS SQL Server Transakcja 1. i 3. set transaction isolation level serializable begin transaction select * from lata commit Transakcja 2. set transaction isolation level serializable begin transaction insert into lata values ('21000') commit

Zakleszczenie BEGIN UPDATE wiersz 64 (blokada wiersza 64) UPDATE wiersz 68 (blokada wiersza 68) UPDATE wiersz 68 (czekanie na odblokowanie wiersza 68) UPDATE wiersz 64 (czekanie na odblokowanie wiersza 64) Auto-ROLLBACK COMMIT

Przebieg wykonywania transakcji n Rozpoczęcie – wprowadzenie transakcji do menadżera n Rejestracja wstępnych informacji w dzienniku transakcji n Sprowadzenie rekordów bazy danych n Rejestracja obrazu danych przed transakcją n Obliczenie nowych wartości n Rejestracja obrazu danych po transakcji n Rejestracja zatwierdzenia n Zapis nowych rekordów do bazy danych Po przerwaniu działania w dowolnym momencie można odtworzyć stan bazy danych przed lub po transakcji

Odraczanie więzów, SQL Server Żeby podpisać kontrakt trzeba mieć legitymację, Żeby dostać legitymację trzeba mieć podpisany kontrakt

Odraczanie więzów, SQL Server begin transaction ALTER TABLE kontrakty NOCHECK CONSTRAINT FK_kontrakty_legitymacje insert into kontrakty(id_legitymacji, tresc) values(0, 'P 22') DECLARE @ID int SET @ID = @@IDENTITY insert into legitymacje(id_kontraktu, nazwisko) values(@ID, 'P 22') DECLARE @ID_legitymacji int SET @ID_legitymacji = @@IDENTITY update kontrakty set id_legitymacji = @ID_legitymacji where id_kontraktu = @ID ALTER TABLE kontrakty WITH CHECK CONSTRAINT ALL commit

Transakcje rozproszone n W rozproszonych bazach danych dane są rozmieszczone na różnych serwerach – tzw. serwerach sprzężonych n Komercyjne serwery baz danych obsługują transakcje rozproszone, czyli transakcje obejmujące modyfikacje danych ulokowanych na różnych serwerach n MS SQL Server wyposażony jest w usługę MS DTS (Distributed Transaction Coordinator) do kontroli transakcji rozproszonych i gwarantuje spójność wszystkich transakcji na SQL Serverze i serwerach z nim sprzężonych n Warunkiem poprawnego wykonania transakcji rozproszonej jest jej zatwierdzenie na wszystkich serwerach