Wydawnictwo Helion
ul. Koœciuszki 1c
44-100 Gliwice
tel. 032 230 98 63
Praktyczny kurs SQL
Autor: Danuta Mendrala, Marcin Szeliga
ISBN: 978-83-246-1604-6
Format: 158x235, stron: 304
Wykorzystaj pe³niê mo¿liwoœci baz danych
•
Na czym polega model relacyjny?
•
W jaki sposób pobieraæ dane z bazy?
•
Jak projektowaæ bazy danych i tabele?
Systemy zarz¹dzania bazami danych to aplikacje, które spotkaæ mo¿na praktycznie
w ka¿dej firmie.
Na rynku dostêpnych jest wiele takich narzêdzi, ró¿ni¹cych siê od siebie wydajnoœci¹,
wymaganiami sprzêtowymi, potencjalnymi zastosowaniami i – przede wszystkim
– cen¹. U¿ytkownicy mog¹ wybieraæ zarówno wœród rozwi¹zañ komercyjnych,
jak i nieustêpuj¹cych im rozwi¹zañ bezp³atnych. Cech¹ ³¹cz¹c¹ wszystkie systemy
zarz¹dzania bazami danych jest jêzyk, na którym opiera siê praca z nimi – SQL.
To ustandaryzowany zbiór poleceñ pozwalaj¹cych na niemal dowolne manipulacje
danymi zgromadzonymi w bazach, tworzenie nowych baz oraz administrowanie
serwerami baz danych. Bez jego znajomoœci wykorzystanie pe³ni mo¿liwoœci bazy
danych jest praktycznie niemo¿liwe.
„Praktyczny kurs SQL” to ksi¹¿ka, dziêki której poznasz ten jêzyk. Czytaj¹c j¹,
dowiesz siê, czym jest relacyjnoœæ w bazach danych, jak skonstruowane s¹ takie bazy
i czym s¹ postaci normalne. Nauczysz siê pobieraæ dane w oparciu o ró¿ne kryteria,
przetwarzaæ uzyskane wyniki i wyœwietlaæ je na ekranie w odpowiedni sposób. Poznasz
funkcje pozwalaj¹ce na modyfikacjê istniej¹cych i dodawanie nowych danych,
zastosujesz zapytania z³o¿one i podzapytania oraz wykorzystasz mechanizmy
transakcji. Przeczytasz tak¿e o projektowaniu baz danych oraz definiowaniu
i nadawaniu uprawnieñ do korzystania z nich.
•
Modele baz danych
•
Postaci normalne w modelu relacyjnym
•
Historia jêzyka SQL
•
Pobieranie danych za pomoc¹ instrukcji SELECT
•
Dobór kryteriów wybierania
•
Przetwarzanie wyników zapytañ
•
Zapytania z³o¿one i podzapytania
•
Transakcje
•
Modyfikowanie i dodawanie danych
•
Projektowanie baz danych
•
Uprawnienia
Poznaj w praktyce jêzyk bêd¹cy podstaw¹
wszystkich nowoczesnych systemów zarz¹dzania bazami danych
Spis tre!ci
Wst p .............................................................................................. 9
Cz "# I
Troch teorii, czyli modele i standardy .......................... 17
Rozdzia$ 1. Relacyjny model baz danych ........................................................... 19
Tabele jako zbiory danych .............................................................................................. 19
Kolumny maj" niepowtarzalne nazwy i zawieraj" okre#lone typy danych ............... 20
Wiersze powinny by$ unikalne ................................................................................ 21
Kolejno#$ kolumn jest bez znaczenia ....................................................................... 21
Kolejno#$ wierszy jest bez znaczenia ....................................................................... 22
Bazy danych ................................................................................................................... 22
Trzy modele baz danych: relacyjny, obiektowy i jednorodny ........................................ 23
Model jednorodny .................................................................................................... 23
Model relacyjny ....................................................................................................... 24
Model obiektowy ..................................................................................................... 25
Za'o(enia relacyjnego modelu baz danych ..................................................................... 27
Postulaty Codda dotycz"ce struktury danych ........................................................... 27
Postulaty Codda dotycz"ce przetwarzania danych ................................................... 28
Postulaty Codda dotycz"ce integralno#ci danych ..................................................... 28
Normalizacja ............................................................................................................ 29
Podsumowanie ................................................................................................................ 30
Zadania ........................................................................................................................... 31
Rozdzia$ 2. Standardy j zyka SQL ..................................................................... 33
Strukturalny j)zyk zapyta* ............................................................................................. 33
Przetwarzanie zbiorów a przetwarzanie pojedynczych danych ................................ 34
J)zyk deklaratywny a j)zyk proceduralny ................................................................ 35
J)zyk interpretowany a j)zyk kompilowany ............................................................. 36
Sk'adnia j)zyka SQL ................................................................................................ 37
Dialekty j)zyka SQL ................................................................................................ 39
Standardy ANSI .............................................................................................................. 40
Historia ..................................................................................................................... 40
SQL3 ........................................................................................................................ 41
Podsumowanie ................................................................................................................ 44
Zadania ........................................................................................................................... 44
4
Praktyczny kurs SQL
Cz "# II
Pobieranie danych, czyli instrukcja SELECT ................... 47
Rozdzia$ 3. Odczytywanie danych z wybranej tabeli ........................................... 49
Klauzula FROM ............................................................................................................. 49
W pe'ni kwalifikowane nazwy obiektów ................................................................. 50
Wybieranie kolumn ........................................................................................................ 51
Eliminowanie duplikatów ............................................................................................... 52
Wyra(enia ....................................................................................................................... 53
Operatory arytmetyczne ........................................................................................... 54
/"czenie danych tekstowych .................................................................................... 55
Funkcje systemowe .................................................................................................. 55
Formatowanie wyników ................................................................................................. 58
Aliasy ....................................................................................................................... 59
Sta'e (litera'y) ........................................................................................................... 60
Sortowanie wyników ...................................................................................................... 60
Sortowanie danych tekstowych ................................................................................ 63
Podsumowanie ................................................................................................................ 64
Zadania ........................................................................................................................... 65
Rozdzia$ 4. Wybieranie wierszy ......................................................................... 67
Logika trójwarto#ciowa .................................................................................................. 67
Warto#$ NULL ......................................................................................................... 68
Operatory logiczne ................................................................................................... 68
Klauzula WHERE .......................................................................................................... 70
Standardowe operatory porównania ......................................................................... 71
Operatory SQL ......................................................................................................... 72
Z'o(one warunki logiczne ........................................................................................ 75
Klauzula TOP ................................................................................................................. 78
Wydajne wyszukiwanie danych ...................................................................................... 80
W jaki sposób serwery bazodanowe odczytuj" dane? .............................................. 80
W jakiej kolejno#ci serwery bazodanowe wykonuj" poszczególne
klauzule zapyta*? ................................................................................................... 83
Argumenty SARG .................................................................................................... 84
Podsumowanie ................................................................................................................ 86
Zadania ........................................................................................................................... 87
Rozdzia$ 5. %&czenie tabel i wyników zapyta' ................................................... 89
Z'"czenia naturalne i nienaturalne .................................................................................. 89
Klucze obce .............................................................................................................. 90
Aliasy ....................................................................................................................... 93
Z'"czenia równo#ciowe i nierówno#ciowe ..................................................................... 94
Z'"czenia zewn)trzne ..................................................................................................... 95
Z'"czenie lewostronne .............................................................................................. 96
Z'"czenie prawostronne ............................................................................................ 97
Z'"czenie obustronne ................................................................................................ 97
Z'"czenie krzy(owe (iloczyn kartezja*ski) ..................................................................... 98
Z'"czenia wielokrotne ..................................................................................................... 99
Okre#lanie kolejno#ci z'"cze* ................................................................................ 102
Z'"czenie tabeli z ni" sam" ........................................................................................... 103
Eliminacja duplikatów ............................................................................................ 105
Klucze obce w obr)bie jednej tabeli ....................................................................... 106
/"czenie wyników zapyta* ........................................................................................... 107
Suma ....................................................................................................................... 107
Cz)#$ wspólna ........................................................................................................ 110
Ró(nica ................................................................................................................... 110
Spis tre"ci
5
/"czenie wierszy i wyników funkcji tabelarycznych ................................................... 111
Operator APPLY .................................................................................................... 112
Podsumowanie .............................................................................................................. 114
Zadania ......................................................................................................................... 114
Rozdzia$ 6. Grupowanie wierszy ...................................................................... 117
Funkcje grupuj"ce ........................................................................................................ 117
Funkcja COUNT() .................................................................................................. 118
Funkcje SUM() i AVG() ........................................................................................ 119
Funkcje MIN() i MAX() ......................................................................................... 120
Inne funkcje grupuj"ce ........................................................................................... 121
Wyra(enia .............................................................................................................. 121
Klauzula GROUP BY ................................................................................................... 122
Kolejno#$ wykonywania klauzuli GROUP BY ...................................................... 125
Operatory CUBE i ROLLUP .................................................................................. 126
Operator GROUPING SETS .................................................................................. 129
Wydajne grupowanie danych ....................................................................................... 131
Niestandardowa klauzula OVER .................................................................................. 132
Partycje ................................................................................................................... 134
Funkcje rankingu .................................................................................................... 135
Niestandardowe operatory PIVOT i UNPIVOT ........................................................... 137
PIVOT .................................................................................................................... 137
UNPIVOT .............................................................................................................. 139
Klauzula HAVING ....................................................................................................... 140
Podsumowanie .............................................................................................................. 142
Zadania ......................................................................................................................... 143
Rozdzia$ 7. Podzapytania ............................................................................... 145
Czym s" podzapytania? ................................................................................................ 145
Podzapytania jako zmienne .......................................................................................... 146
Podzapytania niepowi"zane .................................................................................... 146
Podzapytania powi"zane ........................................................................................ 151
Podzapytania jako Fród'a danych ................................................................................. 157
Tabele pochodne .................................................................................................... 157
CTE ........................................................................................................................ 160
Wyznaczanie trendów ............................................................................................ 165
Operatory ...................................................................................................................... 169
Operator EXISTS ................................................................................................... 170
Operator ANY lub SOME ...................................................................................... 173
Operator ALL ......................................................................................................... 176
Podsumowanie .............................................................................................................. 178
Zadania ......................................................................................................................... 179
Cz "# III Modyfikowanie danych, czyli instrukcje INSERT,
UPDATE, DELETE oraz MERGE .................................... 181
Rozdzia$ 8. Modyfikowanie danych ................................................................. 183
Wstawianie danych ....................................................................................................... 183
Klucze podstawowe ................................................................................................ 184
Warto#ci domy#lne ................................................................................................. 185
Warto#$ NULL ....................................................................................................... 185
Konstruktor wierszy ............................................................................................... 186
Wstawianie wyników zapyta* ................................................................................ 186
6
Praktyczny kurs SQL
Usuwanie danych .......................................................................................................... 188
Instrukcja DELETE ................................................................................................ 189
Instrukcja TRUNCATE TABLE ............................................................................ 191
Aktualizowanie danych ................................................................................................ 191
Jednoczesne aktualizowanie wielu kolumn ............................................................ 192
Wyra(enia .............................................................................................................. 192
Aktualizowanie danych wybranych na podstawie danych z innych tabel .............. 193
Aktualizowanie danych za pomoc" wyra(e* odwo'uj"cych si) do innych tabel .... 193
Instrukcja MERGE ....................................................................................................... 194
Podsumowanie .............................................................................................................. 196
Zadania ......................................................................................................................... 196
Rozdzia$ 9. Transakcje i wspó$bie(no"# .......................................................... 197
W'a#ciwo#ci transakcji ................................................................................................. 197
Transakcyjne przetwarzanie danych ............................................................................. 199
Tryb jawnego zatwierdzania transakcji .................................................................. 200
Rozpoczynanie transakcji ....................................................................................... 201
Wycofywanie transakcji ......................................................................................... 202
Zatwierdzanie transakcji ......................................................................................... 203
Zagnie(d(anie transakcji ........................................................................................ 203
Punkty przywracania .............................................................................................. 204
Wspó'bie(no#$ .............................................................................................................. 205
Blokady .................................................................................................................. 205
Zakleszczenia ......................................................................................................... 206
Poziomy izolowania transakcji ............................................................................... 207
Model optymistyczny ............................................................................................. 211
Model pesymistyczny ............................................................................................. 212
Podsumowanie .............................................................................................................. 213
Zadania ......................................................................................................................... 213
Cz "# IV Tworzenie baz danych, czyli instrukcje CREATE,
ALTER i DROP ............................................................ 215
Rozdzia$ 10. Bazy danych i tabele .................................................................... 217
Tworzenie i usuwanie baz danych ................................................................................ 217
Tworzenie i usuwanie tabel .......................................................................................... 220
Schematy ................................................................................................................ 221
Zmiana struktury tabeli ................................................................................................. 221
Ograniczenia ................................................................................................................. 222
NOT NULL ............................................................................................................ 222
Klucz podstawowy ................................................................................................. 223
Niepowtarzalno#$ ................................................................................................... 224
Warto#$ domy#lna .................................................................................................. 225
Warunek logiczny .................................................................................................. 225
Klucz obcy ............................................................................................................. 226
Ograniczenia a wydajno#$ instrukcji modyfikuj"cych i odczytuj"cych dane ......... 229
Podsumowanie .............................................................................................................. 231
Zadania ......................................................................................................................... 231
Rozdzia$ 11. Widoki i indeksy ........................................................................... 233
Widoki .......................................................................................................................... 233
Tworzenie i usuwanie widoków ............................................................................. 234
Modyfikowanie widoków ....................................................................................... 236
Korzystanie z widoków .......................................................................................... 236
Zalety widoków ...................................................................................................... 241
Spis tre"ci
7
Indeksy ......................................................................................................................... 241
Tworzenie, modyfikowanie i usuwanie indeksów .................................................. 243
Porz"dkowanie indeksów ....................................................................................... 245
Podsumowanie .............................................................................................................. 246
Zadania ......................................................................................................................... 247
Cz "# V
Uprawnienia u(ytkowników, czyli instrukcje
GRANT i REVOKE ....................................................... 249
Rozdzia$ 12. Nadawanie i odbieranie uprawnie' ................................................ 251
Konta u(ytkowników .................................................................................................... 251
Zak'adanie i usuwanie kont u(ytkowników ............................................................ 252
Role .............................................................................................................................. 253
Tworzenie i usuwanie ról ....................................................................................... 253
Przypisywanie ról do u(ytkowników ..................................................................... 254
Specjalna rola Public .............................................................................................. 254
Uprawnienia ................................................................................................................. 254
Nadawanie i odbieranie uprawnie* ........................................................................ 255
Dziedziczenie uprawnie* ....................................................................................... 256
Przekazywanie uprawnie* ...................................................................................... 258
Zasada minimalnych uprawnie* ............................................................................. 259
Podsumowanie .............................................................................................................. 259
Zadania ......................................................................................................................... 260
Dodatki ..................................................................................... 261
Dodatek A Rozwi&zania zada' ....................................................................... 263
Skorowidz .................................................................................... 295
Rozdzia 9.
Transakcje
i wspóbieno
Czym s transakcje?
Co oznacza skrót ACID?
Jakie s zalety transakcyjnego przetwarzania danych?
Na czym polega rónica pomidzy transakcjami zagniedonymi
a zagniedaniem transakcji?
Co oznacza termin „wspóbieno”?
Po co serwery bazodanowe zakadaj blokady?
Kiedy dochodzi do zakleszcze?
Czy warto zmienia domylny poziom izolowania transakcji?
W jakich sytuacjach optymistyczny model wspóbienoci jest lepszy
ni pesymistyczny?
Waciwoci transakcji
Transakcje gwarantuj spójno modyfikowanych informacji. Typowym przyka-
dem transakcyjnego przetwarzania danych jest przeniesienie pienidzy z jednego konta
na drugie. Taka operacja przebiega w dwóch etapach:
1.
zmniejszenie o pewn sum stanu konta X,
2.
dodanie tej sumy do stanu konta Y.
198
Cz III
i
i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE
Gdyby po wykonaniu pierwszej operacji wystpi bd uniemoliwiajcy wykonanie
drugiej, z systemu zniknaby pewna suma pienidzy. Równie nieprzyjemnym zasko-
czeniem dla waciciela byoby sprawdzenie przez niego stanu obu jego kont ju po
odjciu danej sumy z pierwszego konta, ale przed jej dodaniem do drugiego konta.
eby temu zapobiec, transakcje musz by:
1.
Niepodzielne (ang.
Atomicity). Niepodzielno oznacza, e zatwierdzane s
wszystkie wchodzce w skad transakcji instrukcje albo nie jest zatwierdzana
adna z nich. Innymi sowy, wszystkie wchodzce w skad transakcji instrukcje
musz by wykonane poprawnie — jeeli cho jedna z nich zgosi bd,
wszystkie przeprowadzone w ramach transakcji zmiany zostan wycofane.
2.
Spójne (ang.
Consistency). Ta cecha transakcji gwarantuje, e ich wykonanie
nie doprowadzi, nawet w przypadku awarii serwera, do utraty spójnoci danych.
Poniewa wszystkie zmiany danych wykonywane s w ramach transakcji,
przechowywane w bazach informacje zawsze bd spójne
1
.
3.
Izolowane (ang.
Isolation). Izolowanie transakcji wymaga albo zablokowania
modyfikowanych w ramach jednej z nich danych, albo utworzenia ich dodatkowej
wersji. W zalenoci od obowizujcego w ramach serwera lub sesji klienckiej
poziomu izolowania transakcji, moe doj do nastpujcych sytuacji:
a)
Utrata aktualizacji (ang.
lost update) ma miejsce, gdy dwa procesy
modyfikuj jednoczenie te same dane. Przykadowo jeden uytkownik
zmienia cen towaru na 100 z, a drugi — na 200. W takim przypadku
jedna ze zmian zostanie utracona (zastpiona drug modyfikacj).
Domylnie skonfigurowane serwery bazodanowe nie dopuszczaj
do utraty aktualizacji.
b)
Brudne odczyty (ang.
dirty read) — do takiej sytuacji dochodzi, gdy moliwe
jest odczytanie zmian niezatwierdzonych jeszcze przez inny proces.
Jeeli proces odczytujcy nie zada zaoenia blokady na odczytywanych
danych, uzyska do nich dostp nawet wtedy, kiedy wanie bd
modyfikowane. Gdyby proces modyfikujcy wycofa wprowadzone zmiany,
odczytane dane okazayby si niespójne. Domylnie skonfigurowane
serwery bazodanowe nie dopuszczaj brudnych odczytów.
c)
Niepowtarzalne odczyty (ang.
non-repeatable reads) maj miejsce, gdy
powtórzenie w ramach transakcji tego samego odczytu daje inny wynik.
Rónice w wynikach s spowodowane tym, e natychmiast po zakoczeniu
odczytu (a nie po zakoczeniu caej transakcji) proces odczytujcy zdejmuje
blokady zaoone na odczytywane dane. Niezablokowane dane mog by
zmienione przez inny proces, a wic ich powtórne odczytanie da inny
(niespójny) wynik. Domylnie skonfigurowane serwery bazodanowe
dopuszczaj niepowtarzalne odczyty.
1
Przynajmniej w teorii. W praktyce bazy danych ulegaj uszkodzeniu, cho bardzo rzadko z winy
serwerów bazodanowych.
Rozdzia 9.
i
i Transakcje i wspóbieno
199
d)
Odczyty widma (ang.
phantom reads) — sytuacja taka ma miejsce, jeeli
pomidzy dwoma wykonanymi w ramach transakcji odczytami zmieni si
liczba odczytywanych wierszy. Jeeli np. podczas pierwszego odczytu
w tabeli
Produkty
znajdowao si 100 produktów o cenach niszych ni 10 z,
instrukcja
SELECT * FROM Produkty WHERE Cena <10
zwróciaby 100 wierszy.
W trakcie trwania transakcji moliwa jest jednak zmiana pozostaych
wierszy tabeli, w tym obnienie ceny jakiego produktu poniej 10 z.
Moliwe jest równie wstawienie do tej tabeli nowego produktu o cenie
np. 7 z. Z tego powodu drugie wykonanie tego samego zapytania zwrócioby
ju 102 wiersze. Domylnie skonfigurowane serwery bazodanowe
dopuszczaj odczyty widma.
4.
Trwae (ang.
Durability). Trwao transakcji gwarantuje, e efekty
zatwierdzonych transakcji bd zapisane w bazie, nawet w przypadku awarii
serwera SQL 2005. Do przywrócenia spójnoci danych serwery bazodanowe
z reguy uywaj jakiej formy dziennika transakcyjnego.
Pierwsze litery cech transakcji (A —
Atomicity, C — Consistency, I — Isolation,
D —
Durability) tworz skrót ACID, powszechnie uywany do opisywania regu
przetwarzania danych, których musz przestrzega serwery bazodanowe, eby mogy
zosta nazwane transakcyjnymi lub relacyjnymi.
Transakcyjne przetwarzanie danych
Serwery bazodanowe mog dziaa w trybie niejawnego zatwierdzania transakcji (w ser-
werze SQL 2008 taki tryb jest trybem domylnym). Oznacza to, e uytkownicy nie
musz samodzielnie rozpoczyna transakcji, bo serwer robi to za nich.
W trybie niejawnego zatwierdzania transakcji wykonanie kadej instrukcji jzyka SQL
skada si z trzech etapów:
1.
Serwer bazodanowy automatycznie rozpoczyna now transakcj.
2.
Wykonywana jest pojedyncza instrukcja SQL.
3.
Jeeli instrukcja zostaa wykonana z powodzeniem, transakcja jest zatwierdzana,
w przeciwnym przypadku jest wycofywana.
Taki sposób dziaania oznacza, e uytkownicy nie mog samodzielnie zatwierdza
lub wycofywa automatycznie rozpocztych transakcji. Dlatego nazywa si on trybem
niejawnego zatwierdzania transakcji.
Poniszy przykad ilustruje dziaanie trybu niejawnego zatwierdzania transakcji za po-
moc funkcji systemowej
@@TRANCOUNT
zwracajcej liczb otwartych, aktywnych w danym
momencie transakcji:
200
Cz III
i
i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE
SELECT @@TRANCOUNT;
UPDATE Production.Product
SET Color='Red'
WHERE ProductID=1;
SELECT @@TRANCOUNT;
------------------------------------------------------------
0
0
Przed rozpoczciem i po zakoczeniu wykonywania instrukcji
UPDATE
nie byo adnych
otwartych transakcji.
Tryb jawnego zatwierdzania transakcji
W niektórych serwerach bazodanowych (np. w serwerze Oracle) domylnym trybem
transakcyjnego przetwarzania danych jest tryb ich jawnego zatwierdzania. W tym trybie
wykonanie kadej instrukcji jzyka SQL przebiega nastpujco:
1.
Serwer bazodanowy automatycznie rozpoczyna now transakcj.
2.
Wykonywana jest pojedyncza instrukcja SQL.
3.
Uytkownik samodzielnie musi zatwierdzi lub wycofa otwart przez serwer
transakcj.
Dziaanie tego trybu mona zasymulowa w serwerze SQL 2008, ustawiajc opcj sesji
IMPLICIT_TRANSACTIONS
:
SET IMPLICIT_TRANSACTIONS ON;
SELECT @@TRANCOUNT;
UPDATE Production.Product
SET Color='Red'
WHERE ProductID=1;
SELECT @@TRANCOUNT;
------------------------------------------------------------
0
1
Tym razem przed rozpoczciem instrukcji
UPDATE
równie nie byo otwartych trans-
akcji, ale niejawnie rozpoczta transakcja nie zostaa po jej wykonaniu automatycznie
zamknita. Musi to zrobi sam uytkownik — albo zatwierdzajc wprowadzone zmiany,
albo je wycofujc.
Przed przejciem do dalszych wicze zakocz transakcj i wycz omawiany tryb:
COMMIT TRAN;
SET IMPLICIT_TRANSACTIONS OFF;
Tryb jawnego zatwierdzania transakcji pozwala wycofywa przypadkowe lub bdne
modyfikacje, ale zatwierdzanie transakcji, której samemu si nie rozpoczo, jest
mao intuicyjne.
Rozdzia 9.
i
i Transakcje i wspóbieno
201
Rozpoczynanie transakcji
Mechanizm transakcyjnego przetwarzania danych pokaemy, jawnie rozpoczynajc
i koczc transakcje. Pozwoli nam to wykona w ramach poszczególnych transakcji
dowoln liczb instrukcji oraz samodzielnie sterowa czasem rozpoczcia i zakoczenia
poszczególnych transakcji.
eby rozpocz transakcj, naley wykona instrukcj
BEGIN TRAN
2
:
BEGIN TRAN;
SELECT @@TRANCOUNT;
------------------------------------------------------------
1
Jeeli teraz w ramach tej samej sesji (czyli w tym samym oknie edytora SQL) zaktu-
alizujemy ceny wybranych towarów i sprawdzimy liczb aktywnych transakcji, dowie-
my si, e rozpoczta przez nas transakcja nadal jest otwarta:
UPDATE Production.Product
SET ListPrice=1
WHERE ProductSubcategoryID=1;
SELECT @@TRANCOUNT;
------------------------------------------------------------
1
Dopóki transakcja, w ramach której przeprowadzilimy dowolne zmiany, jest otwarta,
moemy je albo wycofa, albo zatwierdzi. Poniewa serwer bazodanowy nie jest w sta-
nie przewidzie naszej decyzji, a jedn z cech transakcji jest jej odizolowanie, próba
odczytania danych z tabeli
Production.Product
w ramach tej samej sesji skoczy si
zupenie inaczej ni ta sama próba wykonana przez innego uytkownika.
eby si o tym przekona:
1.
W tym samym oknie kodu SQL wykonaj zapytanie:
SELECT Name,ListPrice,ProductSubcategoryID
FROM Production.Product
WHERE ProductSubcategoryID<3
ORDER BY ProductSubcategoryID;
------------------------------------------------------------
Mountain-100 Silver, 38 1,0000 1
Mountain-100 Silver, 42 1,0000 1
Mountain-100 Silver, 44 1,0000 1
Mountain-100 Silver, 48 1,0000 1
…
2.
Zostao ono natychmiast wykonane, a cena kadego produktu z podkategorii
1 wynosi 1.
3.
eby wykona to samo zapytanie jako inny uytkownik, otwórz nowe okno
edytora SQL
3
i skopiuj do niego powysz instrukcj
SELECT
(rysunek 9.1).
2
W niektórych serwerach bazodanowych transakcje rozpoczyna si instrukcjami
BEGIN TRANSACTION
lub
BEGIN
WORK
.
3
Mona to zrobi, naciskajc kombinacj klawiszy
Ctrl+N
lub klikajc przycisk
New Query
.
202
Cz III
i
i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE
Rysunek 9.1. Zapytanie wykonuje si ju prawie dwie minuty, ale wci nie zwrócio adnych danych
Transakcyjne przetwarzanie danych polega na takim realizowaniu da klientów
przez serwery bazodanowe, eby kady z nich mia wraenie, e jest jedynym uyt-
kownikiem serwera. Wymaga to opisanego w dalszej czci rozdziau blokowania
obiektów, do których w danym momencie odwouj si inni uytkownicy serwera.
Wycofywanie transakcji
Wycofanie transakcji oznacza przywrócenie danych do stanu sprzed jej rozpo-
czcia i zdjcie wszystkich zaoonych na potrzeby transakcji blokad. Jeeli wrócimy
do pierwszego okna edytora SQL (tego, w którym zapytanie zwrócio wyniki) i wyko-
namy w nim instrukcj
ROLLBACK TRAN
4
, a nastpnie przeczymy si do drugiego okna
edytora SQL, przekonamy si, e zapytanie wreszcie zostao wykonane i w dodatku
ceny produktów z pierwszej podkategorii wcale nie wynosz 1. Spowodowane jest to
wycofaniem transakcji, w ramach której ceny byy zmienione, i zdjciem zaoonych
na jej potrzeby blokad:
4
W niektórych serwerach bazodanowych transakcje wycofuje si instrukcjami
ROLLBACK TRANSACTION
lub
ROLLBACK WORK
.
Rozdzia 9.
i
i Transakcje i wspóbieno
203
SELECT Name,ListPrice,ProductSubcategoryID
FROM Production.Product
WHERE ProductSubcategoryID<3
ORDER BY ProductSubcategoryID;
------------------------------------------------------------
Mountain-100 Silver, 38 3399,9900 1
Mountain-100 Silver, 42 3399,9900 1
Mountain-100 Silver, 44 3399,9900 1
Mountain-100 Silver, 48 3399,9900 1
Mountain-100 Black, 38 3374,9900 1
Zatwierdzanie transakcji
Zatwierdzenie transakcji oznacza utrwalenie wprowadzonych w jej trakcie zmian
i zdjcie wszystkich zaoonych na potrzeby transakcji blokad. Wspomniany na
pocztku rozdziau przykad przelania pienidzy z jednego konta na drugie mógby
by zaimplementowany w poniszy sposób:
BEGIN TRAN;
EXEC uspDodajDoKonta '123-456-78-90', 500;
EXEC uspOdejmijOdKonta '231-645-87-09', 500;
IF @@ERROR=0
COMMIT TRAN;
ELSE
ROLLBACK TRAN;
Po jawnym rozpoczciu transakcji nastpuje wywoanie dwóch (nieistniejcych w bazie
AdventureWorks
) procedur. Jeeli adna z nich nie zgosi bdu, caa transakcja bdzie
zatwierdzona (zatwierdzi transakcj moemy, wykonujc instrukcj
COMMIT TRAN
5
),
w przeciwnym przypadku zostanie ona wycofana.
Zagniedanie transakcji
Wikszo serwerów bazodanowych pozwala zagnieda transakcje, czyli wykona
instrukcj
BEGIN TRAN
w ramach wczeniej rozpocztej transakcji. Wynikiem takiej ope-
racji jest zwikszenie licznika otwartych transakcji, a nie rozpoczcie nowej (atomowej,
niepodzielnej, trwaej i spójnej) transakcji.
Dziaanie mechanizmu zagniedania transakcji ilustruje poniszy przykad: wykona-
nie instrukcji
BEGIN
TRAN
powoduje zwikszenie o jeden licznika otwartych transakcji,
wykonie instrukcji
COMMIT TRAN
zmniejsza warto tego licznika o jeden, ale wykonanie
instrukcji
ROLLBACK
zamyka transakcje i zeruje licznik otwartych transakcji:
5
W niektórych serwerach bazodanowych transakcje zatwierdza si instrukcjami
COMMIT TRANSACTION
lub
COMMIT WORK
.
204
Cz III
i
i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE
BEGIN TRAN;
SELECT @@TRANCOUNT;
BEGIN TRAN;
SELECT @@TRANCOUNT;
BEGIN TRAN;
SELECT @@TRANCOUNT;
COMMIT TRAN;
SELECT @@TRANCOUNT;
ROLLBACK TRAN;
SELECT @@TRANCOUNT;
------------------------------------------------------------
1
2
3
2
0
Punkty przywracania
Wikszo serwerów bazodanowych pozwala wycofa nie tylko ca transakcj, ale
te jej cz. W tym celu naley w trakcie transakcji wykona instrukcj
SAVE TRAN
6
,
a nastpnie przywróci j do danego punktu:
BEGIN TRAN;
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TEST1', 'G1');
SAVE TRAN PP1;
INSERT INTO HumanResources.Department (Name, GroupName)
VALUES ('TEST2', 'G1');
SELECT @@TRANCOUNT;
ROLLBACK TRAN PP1;
SELECT @@TRANCOUNT;
------------------------------------------------------------
1
1
Poniewa przywrócenie stanu transakcji do okrelonego punktu nie powoduje jej za-
koczenia (liczba otwartych transakcji nadal wynosi 1), musimy j zatwierdzi lub
wycofa:
COMMIT TRAN;
SELECT *
FROM HumanResources.Department
WHERE Name LIKE 'TEST_';
------------------------------------------------------------
26 TEST1 G1 2008-01-27 10:02:34.690
Poniewa druga instrukcja
INSERT
zostaa wykonana po zdefiniowaniu punktu przy-
wracania
PP1
, instrukcja
ROLLBACK TRAN PP1
przywrócia stan danych do momentu sprzed
jej wykonania, i w rezultacie tyko pierwszy wiersz zosta na trwae wstawiony do tabeli.
6
W niektórych serwerach bazodanowych punkty przywracania tworzy si instrukcjami
SAVE TRANSACTION
lub
SAVE WORK
.
Rozdzia 9.
i
i Transakcje i wspóbieno
205
Wspóbieno
Wspóbieno to zdolno systemu do jednoczesnego realizowania wielu operacji,
z reguy uzyskiwana poprzez uruchomienie osobnych procesów (robotników) na po-
trzeby obsugi poszczególnych da.
Wspóbieno ma ogromny wpyw na skalowalno serwerów bazodanowych, czyli
ich zdolno do coraz szybszego wykonywania transakcji dziki rozbudowywaniu
komputerów, na przykad zwikszaniu ich mocy obliczeniowej czy przepustowoci
dysków twardych.
eby kady z kilkuset czy nawet kilku tysicy jednoczesnych uytkowników serwera
bazodanowego móg pracowa tak, jakby by jego jedynym uytkownikiem, konieczne
jest odizolowanie od siebie poszczególnych transakcji. Umoliwiaj to automatycznie
zakadane blokady.
Blokady
Pomijajc analizy wewntrznych mechanizmów dziaania rónych serwerów bazoda-
nowych, blokady mona podzieli ze wzgldu na ich tryb (sposób blokowania) i zakres
(typ blokowanych zasobów).
Tryby blokad
Tryb blokady decyduje o tym, czy moliwe bdzie jej zaoenie na zasobie wczeniej
zablokowanym przez inny proces:
1.
Blokady wspódzielone S (ang.
Shared) s domylnie zakadane na odczytywanych
obiektach, takich jak tabele czy wiersze. Na obiekt zablokowany w trybie S
inne procesy te mog zaoy blokad S, czyli odczytujcy nie blokuj
innych odczytujcych. Blokady S domylnie zakadane s tylko na czas
wykonywania zapytania, a nie caej transakcji.
2.
Blokady wyczne X (ang.
eXclusive) s zakadane na modyfikowanych
obiektach. Blokady X s niekompatybilne z innymi blokadami, czyli modyfikujcy
blokuj innych uytkowników. W przeciwiestwie do blokad wspódzielonych,
blokady wyczne domylnie utrzymywane s do zakoczenia caej transakcji,
a nie pojedynczej operacji.
Zakresy blokad
Blokady mog by zakadane na poziomie poszczególnych wierszy, kluczy indeksów,
stron, zakresów lub caych tabel. Te obiekty tworz naturaln hierarchi: tabela skada
si z wielu stron, na kadej stronie zapisanych jest wiele wierszy itd. Z tego powodu
serwery bazodanowe musz analizowa wszystkie istniejce blokady, zanim zao now
— jeeli cho jeden wiersz tabeli jest zablokowany w trybie X, nie mona na caej tabeli
zaoy innej blokady.
206
Cz III
i
i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE
Im wiksze obiekty s blokowane, tym mniejsza wspóbieno (bo uytkownicy
musz duej czeka na dostp do zablokowanych zasobów), ale równie tym mniej-
sza liczba blokad, którymi musi zarzdza serwer bazodanowy (zaoy jedn blokad
na caej tabeli zamiast miliona blokad na poszczególnych wierszach).
Zakleszczenia
Zakleszczenie (ang.
DeadLock) ma miejsce, gdy róne procesy blokuj si nawzajem
w taki sposób, e aden z nich nie jest w stanie zaoy wymaganych do ukoczenia
ju rozpocztych operacji blokad.
Najczciej wystpuj dwa typy zakleszcze:
1.
Zakleszczenia cykliczne, wynikajce z tego, e dwa procesy w rónych
kolejnociach próbuj uzyska dostp do tych samych zasobów.
2.
Zakleszczenia konwersji blokad, zwizane ze zmian wczeniej zaoonej
blokady wspódzielonej (wiele procesów moe jednoczenie zablokowa ten
sam zasób w trybie S) na blokad wyczn (tylko jeden proces moe zaoy
na tym samym obiekcie blokad X).
Serwery bazodanowe automatycznie wykrywaj zakleszczenia i przerywaj dziaanie
jednego procesu. Na ofiar zakleszczenia wybierany jest proces o niszym prioryte-
cie, a jeeli oba procesy maj ten sam priorytet, ofiar zakleszczenia zostaje ten, którego
wycofanie jest mniej kosztowne.
Mechanizm wykrywania i usuwania zakleszcze pokazuje poniszy przykad:
Pierwszy uytkownik w ramach jawnie rozpocztej transakcji modyfikuje kilka danych
w tabeli
HumanResources.Department
:
BEGIN TRAN;
UPDATE HumanResources.Department
SET Name = UPPER(Name)
WHERE DepartmentID>5;
------------------------------------------------------------
(18 row(s) affected)
Nastpnie inny uytkownik w ramach jawnie rozpocztej przez siebie transakcji mo-
dyfikuje znacznie wicej danych w tabeli
Production.Product
7
:
BEGIN TRAN;
UPDATE Production.Product
SET Name = UPPER(Name)
WHERE ProductID >300;
------------------------------------------------------------
(500 row(s) affected)
7
Zasymulowa jednoczesn prac dwóch uytkowników moemy, otwierajc nowe okno edytora SQL
— kade z okien nawizuje wasn sesj z baz danych.
Rozdzia 9.
i
i Transakcje i wspóbieno
207
Nastpnie pierwszy uytkownik próbuje odczyta zawarto tabeli zablokowanej ju
przez 2. sesj (okno wyników moe pokaza pierwszych kilkadziesit wierszy, ale i tak
uytkownik bdzie musia czeka na moliwo zablokowania w trybie S pozostaych
wierszy tabeli
Production.Product
):
SELECT *
FROM Production.Product;
W tym momencie nie wystpio jeszcze zakleszczenie — wystarczyoby, eby drugi
uytkownik zakoczy swoj transakcj. Ale jeeli w ramach 2. sesji uytkownik spró-
buje odczyta zawarto tabeli zmodyfikowanej przez pierwszego uytkownika, oba
procesy si zakleszcz:
SELECT *
FROM HumanResources.Department;
------------------------------------------------------------
1 Engineering Research and Development 1998-06-01 00:00:00.000
2 Tool Design Research and Development 1998-06-01 00:00:00.000
3 Sales Sales and Marketing 1998-06-01 00:00:00.000
…
Po chwili drugie zapytanie zostao jednak wykonane, co wicej, nazwy departamentów
nie zostay przekonwertowane na due litery. eby przekona si, dlaczego tak si
stao, wystarczy przeczy si do okienka 1. sesji. Znajdziemy w nim poniszy ko-
munikat bdu:
Msg 1205, Level 13, State 51, Line 2
Transaction (Process ID 57) was deadlocked on lock resources with another process
´and has been chosen as the deadlock victim. Rerun the transaction.
Jeeli sprawdzimy liczb otwartych w ramach 1. sesji transakcji, okae si, e jawnie
rozpoczta przez pierwszego uytkownika transakcja zostaa — zgodnie z komunikatem
bdu — wycofana:
SELECT @@TRANCOUNT;
------------------------------------------------------------
0
Poniewa wycofanie transakcji wie si ze zdjciem zaoonych na jej potrzeby blokad,
druga sesja moga z powodzeniem zakoczy operacje i odczyta tabel
HumanResources.
´
Department
. Liczba transakcji otwartych w ramach 2. sesji nadal wynosi 1 — eby
zakoczy wiczenie i wycofa zmiany, naley wykona w tym oknie edytora SQL
instrukcj
ROLLBACK TRAN
.
Poziomy izolowania transakcji
Moemy wpywa na sposób zakadania blokad przez serwery bazodanowe, zmieniajc
poziom izolowania transakcji. Wikszo serwerów pozwala ustawi (na poziomie
serwera, bazy danych lub poszczególnych sesji) jeden z czterech poziomów izolo-
wania transakcji, przedstawionych przez nas od najmniej restrykcyjnego, w którym
208
Cz III
i
i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE
maksymalna wspóbieno okupiona jest wystpowaniem najwikszej liczby typów nie-
spójnoci danych, do najbardziej restrykcyjnego, który kosztem ograniczenia wspóbie-
noci gwarantuje najwyszy poziom spójnoci danych.
Read Uncommitted
W trybie niezatwierdzonego odczytu (ang.
Read Uncommitted) odczyt danych nie po-
woduje zaoenia blokady wspódzielonej. Na tym poziomie wystpuj brudne od-
czyty, niepowtarzalne odczyty i odczyty widma (jedynym niekorzystnym zjawi-
skiem niewystpujcym na tym poziomie jest utrata aktualizacji).
eby si o tym przekona:
1.
W jednej sesji (oknie edytora SQL) rozpoczniemy transakcj i zaktualizujemy
nazw dziau:
BEGIN TRAN;
UPDATE HumanResources.Department
SET Name = 'ZmianaWToku'
WHERE DepartmentID=5;
------------------------------------------------------------
(1 row(s) affected)
2.
W drugiej sesji zmienimy poziom izolowania transakcji na Read Uncommitted
i spróbujemy odczyta modyfikowane przez innego uytkownika dane:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Name
FROM HumanResources.Department
WHERE DepartmentID = 5;
------------------------------------------------------------
ZmianaWToku
Udao nam si odczyta dane, pomimo e osoba, która je zmieniaa, nie zatwierdzia
jeszcze transakcji, a wic w kadej chwili moe j wycofa. W tym trybie (czsto
wymuszanym na poziomie poszczególnych instrukcji za pomoc specyficznych dla
danego serwera bazodanowego dyrektyw optymalizatora) mona odczytywa dane,
o których wiemy, e nie bd w tym samym czasie modyfikowane.
Koczc wiczenie, zamknij bez zatwierdzania otwartej transakcji i na nowo otwórz
oba okna edytora SQL — w ten sposób kolejne wiczenie rozpoczniemy, pracujc
w domylnym trybie izolowania transakcji.
Read Committed
Tryb odczytu zatwierdzonego (ang.
Read Committed) jest domylnym poziomem
izolowania transakcji. Na tym poziomie odczyt danych wymaga zaoenia na nich
blokady wspódzielonej. Poniewa zakadana na czas zmiany blokada X jest niekom-
patybilna z innymi blokadami, w tym z blokad S, eliminuje to brudne odczyty. Jednak
na tym poziomie nadal wystpuj niepowtarzalne odczyty i odczyty widma.
Rozdzia 9.
i
i Transakcje i wspóbieno
209
Zjawisko niepowtarzalnego odczytu pokazuje poniszy przykad:
1.
W pierwszym oknie edytora SQL ustawiamy tryb odczytów zatwierdzonych
8
,
jawnie rozpoczynamy transakcj i odczytujemy nazw wybranego departamentu:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
SELECT Name
FROM HumanResources.Department
WHERE DepartmentID = 5;
------------------------------------------------------------
Purchasing
2.
W tym momencie transakcja jest nadal otwarta, a my w drugim oknie
edytora SQL zmienimy nazw tego departamentu:
UPDATE HumanResources.Department
SET Name = 'OdczytWToku'
WHERE DepartmentID=5;
------------------------------------------------------------
(1 row(s) affected)
3.
Jeeli pierwszy uytkownik w ramach tej samej transakcji ponownie odczyta
nazw departamentu, uzyska inny wynik:
SELECT Name
FROM HumanResources.Department
WHERE DepartmentID = 5;
COMMIT TRAN;
------------------------------------------------------------
OdczytWToku
Repeatable Read
W trybie powtarzalnego odczytu (ang.
Repeatable Read) blokady wspódzielone S
utrzymywane s do czasu zakoczenia caej transakcji. Dziki temu inny proces nie
moe zmodyfikowa odczytywanych w jej ramach danych, co eliminuje niepowtarzalne
odczyty. Na tym poziomie wystpuj tylko odczyty widma.
Zjawisko odczytu widma pokazuje poniszy przykad:
1.
W ramach pierwszej sesji zmienimy poziom izolowania transakcji na
Repeatable
Read i w ramach jawnie rozpocztej transakcji odczytamy nazwy towarów
o cenach pomidzy 10 a 15 dolarów:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
SELECT Name
FROM Production.Product
WHERE ListPrice BETWEEN 10 AND 15;
------------------------------------------------------------
Taillights - Battery-Powered
8
Poniewa ten tryb jest trybem domylnym, instrukcja
SET
dodana jest tylko w celach demonstracyjnych.
210
Cz III
i
i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE
2.
Podczas gdy pierwsza transakcja jest wci otwarta, w drugim oknie
edytora SQL zmienimy cen jednego towaru na 12 dolarów:
UPDATE Production.Product
SET ListPrice = 12
WHERE ProductID =2;
------------------------------------------------------------
(1 row(s) affected)
3.
Jeeli pierwszy uytkownik raz jeszcze wykona, w ramach tej samej transakcji,
to samo zapytanie, tym razem jego wynik bdzie liczy dwa wiersze
— pojawi si w nim wiersz widmo:
SELECT Name
FROM Production.Product
WHERE ListPrice BETWEEN 10 AND 15;
------------------------------------------------------------
Bearing Ball
Taillights - Battery-Powered
4.
Jeeli jednak w ramach drugiej sesji spróbujemy zmieni dane odczytywane
w ramach nadal otwartej pierwszej transakcji (czyli doprowadzi
do niepowtarzalnego odczytu), instrukcja bdzie oczekiwa, a pierwsza
transakcja zostanie zakoczona, a zaoone dla niej blokady zdjte:
UPDATE Production.Product
SET ListPrice = 8
WHERE Name = 'Taillights - Battery-Powered';
5.
eby powysza aktualizacja zostaa wykonana, w pierwszym oknie edytora SQL
wykonaj instrukcj
COMMIT TRAN
.
W trybie Repeatable Read naley odczytywa te dane, które w ramach transakcji
odczytywane s kilkukrotnie i mog by zmieniane w tym samym czasie przez
innych uytkowników. Sytuacja taka ma miejsce np. w rónego rodzaju zestawieniach
i raportach zbiorczych, w których odczytujc te same dane, za kadym razem musimy
otrzyma te same wyniki, inaczej zestawienie lub raport bd niespójne.
Serializable
W trybie szeregowania transakcje odwoujce si do tych samych tabel wykonywane
s jedna po drugiej. Blokowanie caych obiektów, a nie tylko odczytywanych danych,
na czas trwania transakcji pozwala wyeliminowa odczyty widma, ale powoduje, e
odczytujc nawet jeden wiersz tabeli, moemy uniemoliwi pozostaym uytkownikom
zmodyfikowanie przechowywanych w niej danych.
eby si o tym przekona:
1.
W pierwszym oknie edytora SQL przeczymy si do trybu szeregowania,
jawnie rozpoczniemy transakcj i odczytamy informacje o wybranym towarze:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
SELECT ProductID, Name
FROM Production.Product
Rozdzia 9.
i
i Transakcje i wspóbieno
211
WHERE ListPrice BETWEEN 10 AND 15;
------------------------------------------------------------
2 Bearing Ball
2.
Jeeli teraz w drugim oknie edytora SQL spróbujemy zmieni cen innego
towaru, okae si, e aktualizacja zostaa zablokowana i bdzie wykonana
dopiero po zakoczeniu pierwszej transakcji:
UPDATE Production.Product
SET ListPrice = 120
WHERE ProductID =3;
3.
Koczc wiczenie, zamknij oba okna edytora SQL bez zatwierdzania
rozpocztej w jednym z nich transakcji.
W trybie
Serializable mamy gwarancj, e odczytywane w ramach transakcji dane
zawsze bd takie same — serwer bazodanowy nie dopuci nie tylko do ich zmiany,
ale równie do pojawienia si nowych danych. Jednak przez ten czas pozostali uyt-
kownicy nie bd mogli modyfikowa zablokowanych tabel. W wikszoci przypad-
ków powoduje to tak znaczne wyduenie czasu reakcji serwera, e lepiej jest skopio-
wa odczytywane dane
9
, a jeeli zmian nie jest zbyt duo, przeczy si do modelu
optymistycznego.
Model optymistyczny
W modelu optymistycznym tylko modyfikujcy blokuj innych modyfikujcych,
czyli róni uytkownicy mog jednoczenie modyfikowa i odczytywa te same dane.
Serwery bazodanowe zapewniaj spójno modyfikowanych w tym modelu danych
poprzez ich wersjonowanie. Zakadajc (optymistycznie), e w czasie gdy jeden uyt-
kownik odczytuje dane, inni raczej nie bd ich modyfikowa, s one w stanie na bieco
zarzdza dodatkowymi wersjami danych.
Jeeli to zaoenie jest prawdziwe, czyli jeeli jednoczesne modyfikacje i odczyty
tych samych danych nie zachodz zbyt czsto, moemy znacznie skróci czas reak-
cji serwera
10
, przeczajc baz do optymistycznego modelu wspóbienoci. eby
si o tym przekona:
1.
W pierwszym oknie edytora SQL wykonamy ponisze instrukcje, przeczajc
baz
AdventureWorks
do modelu optymistycznego:
USE master;
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
------------------------------------------------------------
Command(s) completed successfully.
9
Niektóre serwery bazodanowe pozwalaj utworzy migawk (ang.
Snapshot) danych.
10
Niektóre serwery bazodanowe, np. serwer Oracle, domylnie dziaaj w optymistycznym modelu
wspóbienoci.
212
Cz III
i
i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE
2.
W tym samym oknie edytora SQL poczymy si z baz
AdventureWorks
i w ramach jawnie rozpocztej transakcji zmienimy dane dwóch pracowników:
USE AdventureWorks;
BEGIN TRAN;
UPDATE HumanResources.Employee
SET Title = 'X'
WHERE EmployeeID <3;
------------------------------------------------------------
(2 row(s) affected)
3.
W nowym oknie edytora SQL odczytamy dane o kilku pracownikach:
SELECT EmployeeID, Title
FROM HumanResources.Employee
WHERE EmployeeID <5;
------------------------------------------------------------
1 Production Technician - WC60
2 Marketing Assistant
3 Engineering Manager
4 Senior Tool Designer
4.
Okazuje si, e tym razem zapytanie zostao wykonane natychmiast,
ale z zachowaniem wymogów domylnego trybu izolowania transakcji,
czyli trybu Read Committed — pozostali uytkownicy serwera odczytaj
ostatni zatwierdzon wersj danych. Gdyby rozpoczta w ramach pierwszej
sesji transakcja zostaa zatwierdzona, to ponowne wykonanie tego samego
zapytania zwrócioby najnowsz, zatwierdzon wersj, ze zmienionymi
tytuami dwóch pierwszych pracowników.
Model pesymistyczny
W modelu pesymistycznym odczytujcy s blokowani przez modyfikujcych
(serwer bdzie czeka z zaoeniem blokady S, a zdjta zostanie blokada X), a mody-
fikujcy przez odczytujcych (zaoenie blokady X wymaga zdjcia blokady S).
Poniewa koszt zarzdzania wieloma wersjami tych samych danych ronie wraz ze wzro-
stem wersjonowanych danych, w tym modelu zakada si (pesymistycznie), e odczy-
tywane dane bd w tym samym czasie regularnie modyfikowane.
eby przywróci pesymistyczny (domylny) model wspóbienoci bazy
AdventureWorks
,
naley wykona ponisze instrukcje:
USE master;
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT OFF
WITH ROLLBACK IMMEDIATE;
------------------------------------------------------------
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Rozdzia 9.
i
i Transakcje i wspóbieno
213
Podsumowanie
Serwery bazodanowe przeprowadzaj wszystkie zmiany danych w ramach
jawnie lub niejawnie rozpocztych transakcji.
Transakcje powinny by otwierane jak najpó niej i zamykane jak najwczeniej.
Transakcje powinny zawiera tylko powizane ze sob instrukcje.
Przerwane, czy to z powodu awarii klienta, czy te serwera, transakcje bd
wycofane.
Na czas trwania transakcji pewne obiekty bazy danych s automatycznie
blokowane.
Serwery bazodanowe automatycznie wykrywaj zakleszczenia i usuwaj je
poprzez wycofanie jednej z zakleszczonych transakcji.
Odizolowanie, jedn z czterech cech ACID transakcji, uzyskuje si za pomoc
automatycznie zakadanych i zwalnianych blokad.
Mona sterowa sposobem zakadania i czasem trwania blokad, zmieniajc
poziom izolowania transakcji.
W modelu optymistycznym serwery bazodanowe wersjonuj dane, co poprawia
wspóbieno kosztem wikszego obcienia serwera.
Zadania
1.
Twoim zadaniem jest przygotowanie raportu podsumowujcego roczn sprzeda.
Wyliczajc sumy i rednie wartoci sprzeday produktów, kilkukrotnie musisz
odczyta tabel
Production.Product
. Jak zagwarantujesz poprawno
wyników raportu?
2.
Po przerwie na lunch uytkownicy zgaszaj, e próby dalszej pracy z baz
danych kocz si chwilowym „zawieszeniem” programu i wreszcie
komunikatem bdu mówicym, e serwer bazodanowy jest niedostpny.
Po sprawdzeniu okazuje si, e serwer i sie dziaaj normalnie, a baza nie
zostaa uszkodzona. Co jest najbardziej prawdopodobn przyczyn problemu?
3.
W ramach tworzonej procedury modyfikujesz due iloci danych zapisanych
w kilkunastu tabelach oraz wstawiasz jeden wiersz, informujcy o wykonaniu
wszystkich operacji, do tabeli znajdujcej si w bazie danych na zdalnym
serwerze. Poczenie pomidzy serwerami jest mocno obcione i zdarza si,
e czas nawizywania sesji i przesyania danych midzy serwerami wielokrotnie
si wydua. Co zrobi, eby w przypadku zgoszenia przez procedur bdu
braku poczenia ze zdalnym serwerem nie trzeba byo ponownie wykonywa
kosztownych modyfikacji danych?
Wyszukiwarka
Podobne podstrony:
Praktyczny kurs SQL Wydanie II
informatyka praktyczny kurs sql danuta mendrala ebook
Praktyczny kurs SQL Wydanie II
Praktyczny kurs SQL Wydanie II 3
informatyka praktyczny kurs sql wydanie ii danuta mendrala ebook
Praktyczny kurs SQL Wydanie II pksql2
Praktyczny kurs elektroniki cz07
więcej podobnych podstron