Z góry przepraszam za wszelkie literówki, jeśli takowe się przytrafią po korekcie. Jest to owoc przepracowania.
1. Czym w zasadzie są transakcje?
Dla młodego programisty aplikacji internetowych dotąd zapytania SQL kojarzyły się jako odrębne zadania do wykonania.
Na przykład funkcja zliczająca unikalne wejścia na daną podstronę zarejestrowanych użytkowników wykonywała osobno (czysta teoria):
- zapytanie sprawdzające czy wejście jest rzeczywiście unikalne (np. pobieranie IP z listy pamiętanych adresów w tabeli bazy danych)
- zapytanie pobierające aktualną liczbę unikalnych wejść
- zapytanie zmieniające stan wejścia na podstroną danego użytkownika w rekordzie tabeli użytkowników
Jednak gdy w przypadku np. awarii systemu, w trakcie wykonywania operacji modyfikacje odbyłyby się tylko w części, co doprowadziłoby do dalszych komplikacji.
Wyobraźmy sobie to na przykładzie: jest system zarządzający inwentarzem w hurtowni.
Gdy klient kupuje 75 sztuk długopisów, do bazy danych idą zapytania aktualizujące stan kasy, w której kupował oraz zmieniające liczbę długopisów w inwentarzu.
W przypadku gdy długopisów jest już bardzo mało, system automatycznie wysyła zamówienie do producenta długopisów na kolejne 2000 długopisów.
Gdyby w połowie wykonywania tych operacji system straciłby zasilanie to np. stan kasy zostałby poprawiony, ale stan długopisów nie zgadzałby się już z rzeczywistością. Mogłoby dojść potem do sytuacji, że kolejny klient chce zakupić 200 długopisów, a w inwentażu brakuje 50, ponieważ nie było dostawy długopisów. Dlaczego? Ponieważ przez awarię system nie wykrył, że długopisów jest już mało i zamówienie nie zostało wysłane do producenta.
Jak uniknąć sytuacji tego typu i podobnych?
Rozwiązanie jest teoretycznie proste. Trzeba zastosować transakcje.
Transakcje grupują wyrażenia SQL, traktując ta grupę jako jedno.
Transakcja może składać się z jednego zapytania lub z wielu, to zależy od charakteru zadania do wykonania.
Charakteryzują je (transakcje) zasady ACID, Atomicity, Consistency, Isolation, Durability - Atomowość, Spójność, Izolacja, Trwałość.
Oznacza to, że każda transakcja wykona się w całości, lub wcale. Podczas jej wykonywania dwie równoległe transakcje nie widzą wprowadzanych przez siebie zmian (zależy od poziomu izolacji) do momentu zatwierdzenia transakcji. Zmiany nie są też widoczne dla pozostałych użytkowników tej bazy danych. Po jej wykonaniu nie zostaną naruszane zasady integralności bazy. A cały system udostępni nam nienaruszone dane po nagłej awarii systemu.
Transakcje można używać samodzielnie, ale ich zastosowanie najczęściej idzie w parze z zastosowaniem kursorów w procedurach składowych.
Procedury składowe są udoskonaleniem struktury języka SQL, są osobnymi programami przetwarzającymi dane na poziomie bazy danych.
Jednak by wgłębić się w tą tematykę polecam inną lekturę.
Zatem jakie mechanizmy pozwalają na właśnie takie działanie transakcji? Temat ten omówi kolejny rozdział.
2. Jak działają?
Zasada działania transakcji opiera się na trzech elementach, niejako fundamentach.
* Rozpoczęcie transakcji, inaczej inicjalizacja - po rozpoczęciu transakcji WSZYSTKIE kolejne zapytania SQL są traktowane jako część instrukcji transakcji, następuje ich zgrupowanie do momentu zamknięcia lub anulowania transakcji.
* Zatwierdzenie transakcji - zapisanie na trwałe wszystkich zmian dokonanych przez instrukcję transakcji. Jeśli nie będziesz kończył transakcji odpowiednio szybko, będzie dochodziło do blokowania całej aplikacji. Poprzez odpowiednio szybko rozumiem sytuację kiedy transakcja jest otwarta TYLKO do momentu, kiedy nie będzie pewne, że jej wynik będzie pozytywny. Nie należy zwlekać z zatwierdzeniem transakcji.
* Anulowanie transakcji, lub też wycofanie - kończy transakcję i porzuca wszystkie zmiany jakich miały dokonać instrukcje. Przydaje się w instrukcjach warunkowych aplikacji internetowej. Np. transakcja jest zatwierdzana jeśli przyjdzie potwierdzenie wpłaty pieniędzy na konto bankowe, w przeciwnym wypadku jest anulowana.
W teorii transakcji istnieje też pojęcie punkt pośredni lub punkt zapisu (save point), działające na tej samej zasadzie.
Chodzi i zapisanie stanu transakcji do danego punktu i możliwość cofania się do tego momentu, wewnątrz transakcji.
To jak zapisywanie stanu gry w czasie wykonywania konkretnej misji w grze komputerowej.
3. Transakcje w Transact-SQL.
Transact-SQL traktuje KAŻDE wyrażenie SQL jako osobną transakcję. Co odróżnia go znacznie od Oracle.
Transact-SQL WYMAGA deklarowania każdej transakcji. Ku temu służą nam słowa kluczowe: BEGIN, COMMIT, SAVE i ROLLBACK.
Możemy wycofać transakcję do określonego punktu dzięki opcji nadawania nazw każdej transakcji i tworzenia tz.punktów pośrednich.
Nie ma jednak możliwość nadawania nazw transakcjom i punktom pośrednim znajdującym się wewnątrz zapętlonej transakcji.
BEGIN - Rozpoczynanie transakcji (można pisać w skrócie TRAN). Można nadawać nazwy.
BEGIN {TRAN | TRANSACTION} [nazwa]
SAVE - Zapisuje stan bieżącej transakcji w określonym miejscu jako punkt pośredni. Dzięki temu można wycofywać podzbiory zapytań. Podanie nazwy jest konieczne.
SAVE {TRAN | TRANSACTION} nazwa_punktu
COMMIT - Kończy transakcję i zapisuje w bazie wszystkie zmiany wprowadzone przez zapytania zamykanej transakcji. Oznacza powrót do AUTOCOMMIT, trwający do momentu rozpoczęcia kolejnej transakcji. Wszystkie parametry w składni oprócz słowa kluczowego są parametrami opcjonalnymi. TRANS TRANSACTION i WORK sa synonimami. Nazwy transakcji nie trzeba podawać, lecz należy jeśli chcemy zakończyć całą zewnętrzną transakcję a nie najmłodszą w zapętleniu.
COMMIT {TRAN | TRANSACTION | WORK} [nazwa_transakcji]
ROLLBACK - Wycofuje wszystkie zmiany do ostatniego punktu pośredniego lub gdy go brak do początku transakcji, zależnie od tego czy podamy nazwę. Jeśli nie to nastąpi powrót do stanu przed rozpoczęciem transakcji. To samo tyczy się transakcji zagnieżdżonej.
ROLLBACK {TRAN | TRANSACTION | WORK} [nazwa_transakcji | nazwa punktu]
4. Transakcje w Oracle.
Transakcje w Oracle są o wiele łatwiejszą sprawą. Niczego innego nie moglibyśmy się spodziewać po tak wybornym produkcie 
Tutaj nie rozpoczynamy nigdy transakcji. Mamy tylko polecenia COMMIT i ROLLBACK.
W Oracle nowa transakcja zaczyna się w momencie zakończenie lub anulowania poprzedniej. COMMIT automatycznie zapisuje zmiany.
Gdy sesja zostanie zakończona losowym wydarzeniem (niekontrolowanie) np. gdy komputer straci połączenie z SQL*PLUS, transakcja automatycznie jest anulowana. Nie zaistnieje więc sytuacja niedokończenia pewnych instrukcji gdy np. w hipermarkecie odetną prąd w trakcie przetwarzania kodu kreskowego i odejmowania produktów z bazy inwentarza.
Ale przyjrzyjmy się bliżej w jaki sposób dokonujemy transakcji w Oracle.
Zacznijmy od pobrania kilku rekordów z tabeli Products.
SELECT product_id, product_price FROM Products
PRODUCT_ID PRODUCT_PRICE
-------------------------------
1 19.99
2 34.50
3 12.10
4 4.99
5 1.10
6 59.49
7 114.00
8 99.99
9 10.99
10 2.46
11 2.49
12 10.11
13 89.00
14 69.99
14 rows selected
Teraz skasujmy 4 z tych rekordów:
DELETE FROM Products WHERE product_price IN (1.10, 2.46, 2.49, 4.99)
4 rows deleted
PRODUCT_ID PRODUCT_PRICE
-------------------------------
1 19.99
2 34.50
3 12.10
6 59.49
7 114.00
8 99.99
9 10.99
12 10.11
13 89.00
14 69.99
10 rows selected
Jednak gdy w tym momencie inny użytkownik wywoła ten sam SELECT otrzyma wynik:
SELECT product_id, product_price FROM Products
PRODUCT_ID PRODUCT_PRICE
-------------------------------
1 19.99
2 34.50
3 12.10
4 4.99
5 1.10
6 59.49
7 114.00
8 99.99
9 10.99
10 2.46
11 2.49
12 10.11
13 89.00
14 69.99
14 rows selected
Dlaczego? Nie wydaliśmy polecenia COMMIT. W dalszym ciągu można anulować tą transakcję.
Dla innych użytkowników nie są widoczne zmiany wprowadzone przez nie zakończoną transakcję.
Można ustawić automatyczne zatwierdzanie transakcji co ustaloną liczbę zapytań. Służy do tego specjalny tryb: AUTOCOMMIT.
Aby go włączyć należy w SQL*PLUS wydać polecenie: SET AUTOCOMMIT ON
Analogicznie, aby wyłączyć wystarczy napisać: SET AUTOCOMMIT OFF
Aby ustawić co ile zapytań ma zatwierdzać transakcję wystarczy naskrobać: SET AUTOCOMMIT X
gdzie X oznacza liczbę poleceń.
Bieżące ustawienie AUTOCOMMIT sprawdzamy za pośrednictwem: SHOW AUTOCOMMIT
5. Transakcje w MySQL.
MySQL do wersji bodaj 5.0 nie obsługiwało ani procedur składowanych, ani transakcji, kursorów czy też wyzwalaczy. To było głownym argumentem przeciw używaniu MySQL. Przemawiało za konkurencyjnym PosgreSQL, który od dawna obsługiwał transakcje.
Wtedy można było sobie poradzić poprzez symulowanie transakcji. Takie elementy procedury transakcji jak blokowanie tabel czy wierszy (o czym będzie mowa w rozdziale 7) można było zrobić za pośrednictwem: LOCK TABLES i UNLOCK TABLES. Punkty kontrolne czy zwykłe pamiętanie stanu przed rozpoczeciem transakcji można było robić poprzez np. import i export tabel lub ręcznym zrobieniem dziennika logów.
Lecz takie rozwiązanie nie jest ani trochę optymalne i właściwie mija się z celem.
Poradził sobie w ten sposób dawno temu (za czasów MySQL 3.22) administrator webdeveloper.pl, lecz w tym momencie możemy tego uniknąć korzystają z dobroci programistów MySQL.
W MySQL 6.0 mamy do dyspozycji standardowe procedury czyli:
START TRANSACTION (lub BEGIN) - rozpoczynanie transakcji
COMMIT (lub END) - zatwierdzanie
ROLLBACK - anulowanie
I dla InnoDB oraz Falcon
SAVEPOINT - punkty pośrednie
ROLLBACK TO SAVEPOINT - cofnięcie do punktu pośredniego
RELEASE SAVEPOINT - usuwa punkt pośredni o podanej nazwie z obecnej transakcji
Wszystkim aspektom transakcji w MySQL 6.0 jest poświęcony cały rozdział 12.4 w podręczniku MySQL - MySQL Transactional and Locking Statements
6. Transakcje w PostgreSQL
Okazuje się że PostgreSQL jest jeszcze łatwiejsze do opanowania od MySQL. Silnik ten ma wbudowane MVCC (Multiversion Concurrency Control), eczyli mechanizm zarządzania transakcjami.
W PostgreSQL słowem kluczowym rozpoczynającym transakcję BEGIN, zaś anulującą jest ABORT (lub ROLLBACK), a zatwierdzającą END (lub COMMIT).
Wygląda to więc mniej więcej tak:
BEGIN
/* zapytania SQL */
ABORT | END
Użytkownik otrzymuje informacje o każdej wykonanej operacji i musi zdecydować którą z operacji zakończyć transakcję.
Wróćmy jednak na chwilę do Multi-Version Concurrency Control.
Dzięki temu mechanizmowi może istnieć wiele wersji danego rekordu, a każdy użytkownik widzi odpowiednią wersję do swojej transakcji.
Pozwala to na zmniejszenie blokowania tabel. Każdy z wierszy ma ukryte pola: xmin, xmax, cmin i cmax.
Xmin - numer transakcji, w której został stworzony
Xmax - numer transakcji, w której został usunięty
Cmin - numer zapytania SQL w bieżącej transakcji, w której został stworzony
Cmax - numer zapytania SQL w bieżącej transakcji, w której został usunięty
Jak działa MVCC?
W poleceniu INSERT ustawia Xmax na 0. W DELETE Xmax ustawia na numer bieżącej transakcji.
A UPDATE INSERT + DELETE.
Każdy z użytkowników korzystających z transakcji widzi tylko wiersze dla których xmin jest mniejsze lub równe numerowi bieżącej transakcji oraz xmax jest równe 0 lub xmax jest większe lub równe bieżącemu numerowi transakcji.
To tak jakbyśmy przez INSERT utworzyli:
--------------------------
xmin | xmax | title
--------------------------
--> 14 | 0 | bagietka
--------------------------
Wtedy UPDATE utworzy nową wersję wiersza:
--------------------------
xmin | xmax | title
--------------------------
15 | 15 | bagietka
--------------------------
--> 15 | 0 | bagietka
--------------------------
A DELETE ustawia Xmax kasowanego rekordu:
--------------------------
xmin | xmax | title
--------------------------
15 | 15 | bagietka
--------------------------
15 | 16 | bagietka
--------------------------
PostgreSQL pozostawia stare wersje wierszy w tabeli, co powoduje jej niepotrzebne rozrastanie. Zachodzi więc konieczność kasowania co jakiś czas wszystkich rekordów których Xmax jest mniejszy od numeru najstarszej transakcji. Do tego służy polecenie VACUUM.
Jeśli VACUUM nie wykonamy, możemy odzyskać skasowane rekordy za pomocą programu pgfsck.
7. Lost update problem
Problemem, o który się otarliśmy podczas tego kursu już kilka razy jest Lost Update Problem.
Spotykany w wielu dziedzinach życia informatyka. W każdym współdzielonym projekcie / obiekcie prędzej czy później wystąpi problem utraconych modyfikacji. Na czym to polega? Wyobraźmy sobie projekt tworzony przez dwóch programistów. Mają rozdzielone zadania, lecz słaby kontakt ze sobą.
Załóżmy, że obydwaj mają wolne popołudnie w piątek po pracy. Klawiatura, monitor i Żywiec przygotowane.
Programista A bierze się za dopisywanie kolejnych klas w silniku strony.
Programista B zaczyna pisać rejestrację użytkowników.
Kiedy Programista A edytuje plik klasy.php, Programista B tworzy rejestracja.php.
Jednak Programista B stwierdza, że w klasie użytkowników brakuje istotnego elementu.
Kiedy Programista A skończył swoją pracę i zaktualizował klasy.php na serwerze, Programista B aktualizował poprzednią wersję pliku klasy.php. Kiedy skończył zaktualizował go na serwerze i w ten sposób zignorował modyfikacje Programisty A.
Taka sytuacja oczywiście jest czysto teoretyczna, bo mało prawdopodobne by średnio rozgarnięci programiści popełnili podobny błąd.
W systemach operacyjnych zazwyczaj jest tak, ze gdy jeden program korzysta z pewnych klastrów pamięci, żaden inny program nie ma prawa zapisu tam, choć dostęp ma - może odczytywać stan pamięci przed modyfikacją do czasu zatwierdzenia zmiany. Systemy ignorujące tą zasadę mają skłonność do częstego zawieszania się.
Eliminacją problemu utraconych danych jest blokowanie dostępu do danych zasobów na czas edycji przez jednego użytkownika.
Inaczej mówiąc zapewniamy czasową synchronizację transakcjom wielu użytkowników. Choć transakcje działają jednocześnie to efekt jest taki jakby były wykonywane iteracyjne, kolejno następując po sobie. Wyróżniamy synchronizacje pesymistyczne (blokowanie dostępu) oraz optymistyczne, czyli wykonywanie transakcji i sprawdzanie ewentualnych niezgodności, następujące w kolejności w jakiej wymieniłem.
8. Typy blokad
Blokady dzielą się na poziomy. Są blokady na poziomie tabel i na poziomie wierszy.
I tutaj wynika pierwsze zagrożenie lub raczej problem w transakcjach.
Bo choć blokady na poziomie tabel można spotkać w większości (we wszystkich?) silników bazodanowych, to blokady wierszy tylko w tych bardziej zaawansowanych technologicznie.
Blokowanie CAŁEJ TABELI na czas wykonywania transakcji jest okropnie niewydajne. A co jeśli z tabeli korzysta kilku, albo o zgrozo, kilkunastu użytkowników, to nie będą mogli oni dokonać nic w całej tabeli do czasu, aż nie zakończy się tamta pierwsza transakcja. Teraz już widzisz jak ważne jest zamykanie transakcji kiedy już tylko można tego dokonać.
Blokowanie wierszy jest o wiele lepsze. Ponieważ blokuje tylko jeden rekord i jest mniejsze prawdopodobieństwa zastoju, lub w przypadku źle napisanych aplikacji internetowych, blokady całej strony.
9. Cofanie transakcji.
Jak już dobrze wiesz transakcje umożliwiają cofanie się do punktu pośredniego lub do stanu przed rozpoczęciem grupy zapytań.
Jak ta część mechanizmu współgra? Otóż na potrzeby tego projektu wynaleziono takie pojęcie jak dziennik transakcji. Nie jest on niczym innym jak dziennikiem logów aka zmian.
Dzięki temu dziennikowi możliwe jest cofanie transakcji JUŻ ZATWIERDZONYCH.
Dzienniki taki przechowują zapis wszystkich transakcji zazwyczaj od momentu ostatniego backupu bazy. Jak to działa?
Załóżmy że mamy backup bazy z przed 3 dni i nastąpiła awaria, dajmy na to dysku. W takim wypadku musimy przywrócić stan z przed 3 dni.
Przywrócenie stanu z przed awarii jest banalnie proste po przywróceniu kopii bazy. Musimy potem tylko przywrócić zapisane transakcji w dzienniku transakcji, który na nowo wykona zapisane zapytania SQL.
Jeśli awaria nastąpiła z naszej winy, to następnym krokiem powinno być czynność zapobiegająca powtórzeniu się sytuacji.
10. Podsumowanie i bibliografia.
Dowiedzieliśmy się już do czego służą transakcje, jak się ich używa, jak działają oraz poznaliśmy och zastosowanie w bazach różnego typu. Wiemy już też jak twórcy języka SQL ułatwili życie programistom dając dodatkowe wsparcia.
Wiemy już też jak ostrożnym trzeba być w korzystanie z pomocy tego typu. Wiemy zatem już wszystko co powinniśmy wiedzieć do wypróbowania tego w praktyce. Dlaczego więc zwlekasz?
Źródła mojej wiedzy w tych tematach:
Redakcja Reklama Kontakt Nasze portale i blogi
Wszelkie prawa zastrzeżone | Dział tematyczny Mindly.pl | Zasady korzystania & Polityka prywatności
