Sekcja: Transakcje w języku SQL
80. Co to są transakcje bazodanowe?
Transakcja to zbiór operacji w bazie danych, które stanowią w istocie pewną całość i jako
takie powinny być wykonane wszystkie lub żadna z nich. Transakcja jest logiczną jednostką
działań, której nie można podzielić. Logiczna jednostka działań to zbiór zmian w bazie
danych, które należy wykonać wszystkie albo nie wykonywać żadnej.
Warunki jakie powinny spełniać transakcje bardziej szczegółowo opisują zasady ACID
(Atomicity, Consistency, Isolation, Durability - Atomowość, Spójność, Izolacja, Trwałość).
Przykładem transakcji może być transakcja bankowa jaką jest przelew. Muszą tu zostać
dokonane 2 operacje - zabranie pieniędzy z jednego konta oraz dopisanie ich do drugiego. W
przypadku niepowodzenia żadna z tych operacji nie powinna być zatwierdzona, gdyż zajście
tylko jednej powodowałoby nieprawidłowości w bazie danych (pojawienie się lub zniknięcie
pieniędzy). Transakcja bazodanowa składa się zawsze z 3 etapów:
•
rozpoczęcia transakcji
•
wykonania transakcji
•
zamknięcia
W systemach bazodanowych istotne jest, aby transakcja trwała jak najkrócej, ponieważ
równolegle może być dokonywanych wiele transakcji i część operacji musi zostać wykonana
w pewnej kolejności. Każdy etap transakcji jest logowany, dzięki czemu w razie awarii
systemu (dzięki zawartości logów), można odtworzyć stan bazy danych sprzed transakcji,
która nie została zamknięta.
82. Etapy transakcji.
Rozpoczęcie transakcji może być niejawne bądz jawne. Z niejawnym rozpoczęciem transakcji
mamy do czynienia gdy użytkownik przyłącza się do bazy danych, rozpoczyna nową
transakcję. Wszystkie operacje, które wykonuje od momentu rozpoczęcia pracy, są
operacjami w ramach transakcji. Użytkownik może jawnie rozpocząć nową transakcję,
żą
dając zakończenia transakcji bieżącej i otwierając nową transakcję za pomocą instrukcji
BEGIN.
Transakcja może zostać zamknięta (zakończona) na dwa sposoby. Zatwierdzenie transakcji
oznacza, że uruchomiona transakcja zakończyła się powodzeniem i wszystkie operacje, jakie
zostały w ramach transakcji zrealizowane, zostają trwale zapisane w bazie danych. Transakcję
zakończoną powodzeniem określamy jako zatwierdzoną.
Anulowanie transakcji oznacza niepowodzenie jej działania, wszystkie zmiany, wprowadzone
przez operacje w ramach transakcji, muszą zostać anulowane. Transakcję zakończoną
niepowodzeniem określamy mianem wycofanej.
Transakcja może zostać zakończona jawnie lub niejawnie. Zakończenie jawne następuje na
wyraźne żądanie użytkownika, który wykonuje polecenie COMMIT, zatwierdzające
transakcję, lub polecenie ROLLBACK, wycofujące transakcję. Z niejawnym zakończeniem
transakcji mamy do czynienia gdy użytkownik żąda zakończenia sesji. Wtedy bieżąca
transakcja użytkownika zostaje niejawnie zatwierdzona. Innym przypadkiem jest
uruchomienie przez użytkownika w ramach sesji polecenia z grup DDL lub DCL. W takiej
sytuacji przed wykonaniem polecenia bieżąca transakcja sesji użytkownika zostaje
zatwierdzona, a po zakończeniu wykonywania polecenia rozpoczynana jest nowa transakcja.
W przypadku awarii, bieżąca transakcja zostaje wycofana (ROLLBACK). Przykładem awarii
może być utrata połączenia między aplikacją klienta a serwerem bazy danych.
83. Diagram stanów transakcji
W celu dokładniejszego opisu zachowania transakcji w czasie wprowadza się pojęcia
diagramu stanów transakcji. Każda realizowana transakcja posiada zbiór ściśle określonych
stanów i zbiór ściśle określonych przejść z jednego stanu do drugiego. Poniższy rysunek
przedstawia diagram stanów transakcji zapisany w języku UML.
Transakcja posiada następujące stany:
•
Active: transakcja jest aktywna, jest w czasie realizowania swoich operacji;
•
Partially committed: transakcja jest częściowo zatwierdzona;
•
Committed: transakcja została zatwierdzona;
•
Failed: transakcja została wycofana;
•
Terminated: transakcja zakończyła się zatwierdzeniem lub wycofaniem.
Przejścia z jednego stanu do drugiego są opisane tzw. diagramem stanów transakcji
przedstawionym na powyższym rysunku Rozpoczęcie transakcji (Begin Transaction)
uruchamia transakcję, która jest wtedy w stanie aktywna. Operacje zapisu lub odczytu
wykonywane są w stanie Active.Przerwanie transakcji czyli kończenie transakcji wraz z jej
wycofaniem (Abort) przeprowadza transakcję ze stanu Active do stanu Failed, a następnie do
stanu Terminate. Kończenie transakcji z jej zatwierdzeniem przeprowadza ją ze stanu Active
do Partially committed - czyli stanu w którym transakcja jest gotowa do zatwierdzenia. Z tego
stanu można jeszcze transakcję wycofać, np. w sytuacji awarii systemu. Ostateczne
zatwierdzenie transakcji przeprowadza ją do stanu Committed, a następnie do Terminated, co
kończy działanie transakcji. End Transaction oznacza, ze wszystkie operacje odczytu i/lub
zapisu transakcji zostały wykonane. W tym momencie, zachodzi konieczność podjęcia
decyzji, czy zmiany wprowadzone przez transakcję mają być wprowadzone do bazy danych
(zatwierdzenie transakcji - COMMIT) czy też mają być wycofane z bazy danych
(ROLLBACK).
84. Transakcje w PostgreSQL.
W języku PostgreSQL zmiany te są kontrolowane przez trzy kluczowe frazy:
•
BEGIN WORK rozpoczyna transakcję;
•
COMMIT WORK informuje, że wszystkie elementy transakcji są kompletne i
powinny zostać zatwierdzone na stałe oraz stać się dostępne dla wszystkich transakcji;
•
ROLLBACK WORK mówi, że należy porzucić transakcję, a wszystkie zmiany
danych dokonane przez transakcję SQL mają być anulowane. Baza danych, z punktu
widzenia użytkowników, powinna się znajdować w takim stanie, jakby nie były
wykonywane żadne zmiany.
Standard ANSI/SQL definiuje frazy SQL BEGIN WORK, transakcje w tym standardzie
powinny wykonywać się automatycznie. Fraza ta jednak jest wymagana prawie we
wszystkich implementacjach baz danych. Słowo WORK we frazie COMMIT WORK,
ROLLBACK WORK można pominąć. Większość komercyjnych implementacji realizuje tan
model transakcji. Wywodzi się on z bazy DB2 firmy IBM.
Dowolna transakcja w bazie danych powinna być odizolowana od wszystkich pozostałych
transakcji, które są wykonywane w tym samym czasie. W idealnej sytuacji każda transakcja
zachowuje się tak jakby posiadała wyłączny dostęp do bazy danych Niestety realia związane z
osiągnięciem dobrej wydajności wymagają kompromisów o których powiemy później.
85. Konkurowanie o zasoby
Jeżeli dwóch klientów aplikacji konkuruje o zasoby to trzeba uważać aby nie wpaść w pewną
pułapkę. Na powyższym przykładzie mamy zdarzenie konkurowania dwóch klientów o 1
zasób. Jeśli nie zastosujemy ochrony tego zasobu np. przez zastosowanie transakcji to może
zdarzyć się, że jeden zasób zostanie dwa razy skonsumowany.
86. Przelew pieniędzy pomiędzy kontami za pomocą transakcji - COMMIT.
Przelewamy pieniądze w kwocie 100 zł z konta 82021 na konto 96814. Jeśli nie zastosujemy
transakcji to przerwanie przelewu w połowie może się skończyć nieprzyjemnie. Na żadnym z
kąt nie będzie pieniędzy!
Jeśli zastosujemy transakcję to przelew wykona się do końca albo wcale. Poniższy przykład
składa się z dwóch transakcji. Pierwsza tworzy bazę danych i wprowadza do niej dane (konto
i kwota na koncie). Druga transakcja wykonuje bezpieczny przelew z jednego konta na
drugie. Po zakończeniu transakcji za pomocą COMMIT mamy pewność że przelew odbył się
poprawnie.
BEGIN; -- pierwsza transakcja
DROP TABLE bankacct;
CREATE TABLE bankacct
(
acctno integer primary key,
saldo real
);
INSERT INTO bankacct VALUES (82021,1000.00);
INSERT INTO bankacct VALUES (96814,1000.00);
COMMIT; -- zako
ń
czenie pierwszej transakcji
BEGIN; -- druga transakcja
UPDATE bankacct SET saldo = saldo - 100 WHERE acctno = 82021;
UPDATE bankacct SET saldo = saldo + 100 WHERE acctno = 96814;
COMMIT; -- zako
ń
czenie drugiej transakcji (zatwierdzenie danych)
SELECT * FROM bankacct;
------------------
acctno saldo
------------------
82021
900
96814
1100
Przykład 86: Przelew pieniędzy pomiędzy kontami za pomocą transakcji - COMMIT
87. Przelew pieniędzy pomiędzy kontami za pomocą transakcji - ROLLBACK.
Jeśli w tym samym przykładzie zamiast instrukcji COMMIT wykonamy instrukcję
ROLLBACK to wartości na kontach nie ulegną zmianie ponieważ cała druga transakcja
zostanie cofnięta. ROLLBACK automatycznie wykonuje się w przypadku awarii (przerwania
sesji, etc.) wykonywania transakcji. Może też być wykonywany przez nas jeśli
stwierdziliśmy, że wykonanie transakcji nie przebiegło poprawnie.
BEGIN; -- pierwsza transakcja
DROP TABLE bankacct;
CREATE TABLE bankacct
(
acctno integer primary key,
saldo real
);
INSERT INTO bankacct VALUES (82021,1000.00);
INSERT INTO bankacct VALUES (96814,1000.00);
COMMIT; -- zako
ń
czenie pierwszej transakcji
BEGIN; -- druga transakcja
UPDATE bankacct SET saldo = saldo - 100 WHERE acctno = 82021;
UPDATE bankacct SET saldo = saldo + 100 WHERE acctno = 96814;
ROLLBACK; -- zako
ń
czenie drugiej transakcji (odrzucenie zmian)
SELECT * FROM bankacct;
------------------
acctno saldo
------------------
82021
1000
96814
1000
Przykład 87: Przelew pieniędzy pomiędzy kontami za pomocą transakcji - ROLLBACK
88. Punkty bezpieczeństwa transakcji
Dążymy do tego aby transakcje były jak najmniejsze aby ich ROLLBACK był możliwie jak
najmniej kosztowny. Czasami zdarza się, że transakcja musi być duża czyli posiada wiele
instrukcji czytających i modyfikujących dane. Wtedy dzielimy transakcję na etapy stosujący
tzw. punkty bezpieczeństwa transakcji.
Dzięki punktom bezpieczeństwa istnieje możliwość cofnięcia się do określonego momentu w
historii aktywnej transakcji. Cofnięcie powoduje anulowanie zmian, które zostały
wprowadzone przez operacje transakcji wykonane od momentu utworzenia punktu
bezpieczeństwa, do którego nastąpiło cofnięcie. Cofając się do punktu bezpieczeństwa
sprawiamy, że transakcja nadal pozostaje aktywna czyli nie zostaje w całości wycofana i
może być kontynuowana.
Utworzenie punktu bezpieczeństwa realizuje się poleceniem SAVEPOINT <etykieta>, gdzie
<etykieta> jest unikalną w ramach bieżącej transakcji nazwą punktu bezpieczeństwa. Punkt
bezpieczeństwa można usunąć z historii transakcji poleceniem RELEASE SAVEPOINT
<etykieta>. Polecenie to nie wpływa na przebieg transakcji i operacje w ramach transakcji.
Transakcja może posiadać wiele punktów bezpieczeństwa.
Wycofanie transakcji do wskazanego punktu bezpieczeństwa realizuje polecenie
ROLLBACK TO SAVEPOINT <etykieta>. Efektem polecenia jest wycofanie zmian,
wprowadzonych przez operacje aktywnej transakcji od momentu utworzenia punktu
bezpieczeństwa o podanej etykiecie do momentu wykonania polecenia. Jeśli wycofujemy się
do punktu bezpieczeństwa wcześniejszego niż ostatnio utworzony, wszystkie punkty
bezpieczeństwa utworzone później zostają usunięte.
89. ROLLBACK całej transakcji.
W naszym przykładzie mamy dwie transakcje. Pierwsza buduje tabelę bankacct i wstawia do
niej dwa rekordy. Druga transakcja wstawia dwa rekordy do relacji bankacct. Po wykonaniu
tej operacji tworzy punkt bezpieczeństwa S1. Następnie usuwa rekord z relacji i tworzy punkt
bezpieczeństwa s2. Ostatnie polecenie uaktualnia tabelę. Wykonujemy polecenie
ROLLBACK dla drugiej transakcji. Oznacza to wycofanie wszystkich zmian,
wprowadzonych przez transakcję, a więc efektów poleceń: INSERT, DELETE i UPDATE,
oraz usunięcie punktów bezpieczeństwa S1 i S2. Transakcja zostaje wycofana, przestaje być
aktywna. W tabeli zostają tylko dane wprowadzone przez pierwszą transakcję.
BEGIN; -- pierwsza transakcja
DROP TABLE bankacct;
CREATE TABLE bankacct
(
acctno integer primary key,
saldo real
);
INSERT INTO bankacct VALUES (70001,1000.00);
INSERT INTO bankacct VALUES (70002,1000.00);
COMMIT; -- zako
ń
czenie pierwszej transakcji (utrwalenie danych)
BEGIN; -- druga transakcja
INSERT INTO bankacct VALUES (70003,1000.00);
INSERT INTO bankacct VALUES (70004,1000.00);
SAVEPOINT S1; -- definicja savepoint s1
DELETE FROM bankacct WHERE acctno=70003;
SAVEPOINT S2; -- definicja savepoint s2
UPDATE bankacct SET saldo = saldo + 100 WHERE acctno = 70004;
ROLLBACK; -- rollback cało
ś
ci drugiej transakcji
-- transakcja jest zako
ń
czona
SELECT * FROM bankacct; -- wybór danych z tabeli
---------------------------
acctno saldo
---------------------------
70001
1000
70002
1000
Przykład 89: ROLLBACK całej transakcji
90. ROLLBACK do punktu bezpieczeństwa S1.
W naszym przykładzie mamy dwie transakcje. Pierwsza buduje tabelę bankacct i wstawia do
niej dwa rekordy. Druga transakcja wstawia dwa rekordy do relacji bankacct. Po wykonaniu
tej operacji tworzy punkt bezpieczeństwa S1. Następnie usuwa rekord z relacji i tworzy punkt
bezpieczeństwa s2. Ostatnie polecenie uaktualnia tabelę. Wykonujemy polecenie
ROLLBACK TO S1 transakcji. Oznacza to wycofanie wszystkich zmian, wprowadzonych
przez transakcję od punktu bezpieczeństwa S1, a więc efektów poleceń: INSERT, DELETE i
UPDATE, oraz usunięcie punktu bezpieczeństwa S2. Transakcja nie zostaje wycofana, jest
dalej aktywna. W tabeli zostają dane wprowadzone przez pierwszą transakcję i modyfikacje
zrealizowane do punku bezpieczeństwa S1.
BEGIN; -- pierwsza transakcja
DROP TABLE bankacct;
CREATE TABLE bankacct
(
acctno integer primary key,
saldo real
);
INSERT INTO bankacct VALUES (70001,1000.00);
INSERT INTO bankacct VALUES (70002,1000.00);
COMMIT; -- zako
ń
czenie pierwszej transakcji (utrwalenie danych)
BEGIN; -- druga transakcja
INSERT INTO bankacct VALUES (70003,1000.00);
INSERT INTO bankacct VALUES (70004,1000.00);
SAVEPOINT S1; -- definicja savepoint s1
DELETE FROM bankacct WHERE acctno=70003;
SAVEPOINT S2; -- definicja savepoint s2
UPDATE bankacct SET saldo = saldo + 100 WHERE acctno = 70004;
ROLLBACK TO S1; -- rollback do punktu bezpiecze
ń
stwa S1
-- transakcja nadal trwa!!!
SELECT * FROM bankacct; -- wybór danych z tabeli
-------------------------------
acctno saldo
-------------------------------
70001
1000
70002
1000
70003
1000
70004
1000
Przykład 90: ROLLBACK do punktu bezpieczeństwa S1.
91. ROLLBACK do punktu bezpieczeństwa S2.
W naszym przykładzie mamy dwie transakcje. Pierwsza buduje tabelę bankacct i wstawia do
niej dwa rekordy. Druga transakcja wstawia dwa rekordy do relacji bankacct. Po wykonaniu
tej operacji tworzy punkt bezpieczeństwa S1. Następnie usuwa rekord z relacji i tworzy punkt
bezpieczeństwa s2. Ostatnie polecenie uaktualnia tabelę. Wykonujemy polecenie
ROLLBACK TO S2 transakcji. Oznacza to wycofanie wszystkich zmian, wprowadzonych
przez transakcję od punktu bezpieczeństwa S2, a więc efektów poleceń: INSERT, DELETE i
UPDATE. Transakcja nie zostaje wycofana, jest dalej aktywna. W tabeli zostają dane
wprowadzone przez pierwszą transakcję i modyfikacje zrealizowane do punku
bezpieczeństwa S1.
BEGIN; -- pierwsza transakcja
DROP TABLE bankacct;
CREATE TABLE bankacct
(
acctno integer primary key,
saldo real
);
INSERT INTO bankacct VALUES (70001,1000.00);
INSERT INTO bankacct VALUES (70002,1000.00);
COMMIT; -- zako
ń
czenie pierwszej transakcji (utrwalenie danych)
BEGIN; -- druga transakcja
INSERT INTO bankacct VALUES (70003,1000.00);
INSERT INTO bankacct VALUES (70004,1000.00);
SAVEPOINT S1; -- definicja savepoint s1
DELETE FROM bankacct WHERE acctno=70003;
SAVEPOINT S2; -- definicja savepoint s2
UPDATE bankacct SET saldo = saldo + 100 WHERE acctno = 70004;
ROLLBACK TO S2; -- rollback do punktu bezpiecze
ń
stwa S2
-- transakcja nadal trwa!!!
SELECT * FROM bankacct; -- wybór danych z tabeli
--------------------------
acctno saldo
--------------------------
70001
1000
70002
1000
70004
1000
Przykład 91: ROLLBACK do punktu bezpieczeństwa S2.
91. COMMIT zatwierdzenie drugiej transakcji.
W naszym przykładzie mamy dwie transakcje. Pierwsza buduje tabelę bankacct i wstawia do
niej dwa rekordy. Druga transakcja wstawia dwa rekordy do relacji bankacct. Po wykonaniu
tej operacji tworzy punkt bezpieczeństwa S1. Następnie usuwa rekord z relacji i tworzy punkt
bezpieczeństwa s2. Ostatnie polecenie uaktualnia tabelę. Wykonujemy polecenie COMMIT
transakcji. Oznacza to utrwalenie wszystkich zmian, wprowadzonych przez transakcję.
Transakcja jest zamknięta a wszystkie wprowadzone przez nią zmiany utrwalone.
BEGIN; -- pierwsza transakcja
DROP TABLE bankacct;
CREATE TABLE bankacct
(
acctno integer primary key,
saldo real
);
INSERT INTO bankacct VALUES (70001,1000.00);
INSERT INTO bankacct VALUES (70002,1000.00);
COMMIT; -- zako
ń
czenie pierwszej transakcji (utrwalenie danych)
BEGIN; -- druga transakcja
INSERT INTO bankacct VALUES (70003,1000.00);
INSERT INTO bankacct VALUES (70004,1000.00);
SAVEPOINT S1; -- definicja savepoint s1
DELETE FROM bankacct WHERE acctno=70003;
SAVEPOINT S2; -- definicja savepoint s2
UPDATE bankacct SET saldo = saldo + 100 WHERE acctno = 70004;
COMMIT; -- zatwierdzenie transakcji
-- transakcja jest zako
ń
czona
SELECT * FROM bankacct; -- wybór danych z tabeli
--------------------------
acctno saldo
--------------------------
70001
1000
70002
1000
70004
1100
Przykład 92: COMMIT zatwierdzenie drugiej transakcji.