Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00538 010304 11040374 na godz. na dobę w sumie
Praktyczny kurs SQL - książka
Praktyczny kurs SQL - książka
Autor: , Liczba stron: 304
Wydawca: Helion Język publikacji: polski
ISBN: 83-246-1604-7 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> sql - programowanie
Porównaj ceny (książka, ebook, audiobook).
Wykorzystaj pełnię możliwości baz danych

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.

Uzupełnieniem do książki jest udostępniona tutaj baza danych.

Poznaj w praktyce język będący podstawą wszystkich nowoczesnych systemów zarządzania bazami danych.

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Praktyczny kurs SQL Autor: Danuta Mendrala, Marcin Szeliga ISBN: 978-83-246-1604-6 Format: 158x235, stron: 304 Wykorzystaj pe‡niŒ mo¿liwo(cid:156)ci baz danych (cid:149) Na czym polega model relacyjny? (cid:149) W jaki spos(cid:243)b pobiera(cid:230) dane z bazy? (cid:149) Jak projektowa(cid:230) bazy danych i tabele? Systemy zarz„dzania bazami danych to aplikacje, kt(cid:243)re spotka(cid:230) mo¿na praktycznie w ka¿dej firmie. Na rynku dostŒpnych jest wiele takich narzŒdzi, r(cid:243)¿ni„cych siŒ od siebie wydajno(cid:156)ci„, wymaganiami sprzŒtowymi, potencjalnymi zastosowaniami i (cid:150) przede wszystkim (cid:150) cen„. U¿ytkownicy mog„ wybiera(cid:230) zar(cid:243)wno w(cid:156)r(cid:243)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(cid:243)rym opiera siŒ praca z nimi (cid:150) SQL. To ustandaryzowany zbi(cid:243)r poleceæ pozwalaj„cych na niemal dowolne manipulacje danymi zgromadzonymi w bazach, tworzenie nowych baz oraz administrowanie serwerami baz danych. Bez jego znajomo(cid:156)ci wykorzystanie pe‡ni mo¿liwo(cid:156)ci bazy danych jest praktycznie niemo¿liwe. (cid:132)Praktyczny kurs SQL(cid:148) to ksi„¿ka, dziŒki kt(cid:243)rej poznasz ten jŒzyk. Czytaj„c j„, dowiesz siŒ, czym jest relacyjno(cid:156)(cid:230) w bazach danych, jak skonstruowane s„ takie bazy i czym s„ postaci normalne. Nauczysz siŒ pobiera(cid:230) dane w oparciu o r(cid:243)¿ne kryteria, przetwarza(cid:230) uzyskane wyniki i wy(cid:156)wietla(cid:230) je na ekranie w odpowiedni spos(cid:243)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. (cid:149) Modele baz danych (cid:149) Postaci normalne w modelu relacyjnym (cid:149) Historia jŒzyka SQL (cid:149) Pobieranie danych za pomoc„ instrukcji SELECT (cid:149) Dob(cid:243)r kryteri(cid:243)w wybierania (cid:149) Przetwarzanie wynik(cid:243)w zapytaæ (cid:149) Zapytania z‡o¿one i podzapytania (cid:149) Transakcje (cid:149) Modyfikowanie i dodawanie danych (cid:149) Projektowanie baz danych (cid:149) Uprawnienia Poznaj w praktyce jŒzyk bŒd„cy podstaw„ wszystkich nowoczesnych system(cid:243)w zarz„dzania bazami danych Wydawnictwo Helion ul. Ko(cid:156)ciuszki 1c 44-100 Gliwice tel. 032 230 98 63 e-mail: helion@helion.pl 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 Ĩró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óäbieĔnoĈè  Czym są transakcje?  Co oznacza skrót ACID?  Jakie są zalety transakcyjnego przetwarzania danych?  Na czym polega róĪnica pomiĊdzy transakcjami zagnieĪdĪonymi a zagnieĪdĪaniem transakcji?  Co oznacza termin „wspóábieĪnoĞü”?  Po co serwery bazodanowe zakáadają blokady?  Kiedy dochodzi do zakleszczeĔ?  Czy warto zmieniaü domyĞlny poziom izolowania transakcji?  W jakich sytuacjach optymistyczny model wspóábieĪnoĞci jest lepszy niĪ pesymistyczny? WäaĈciwoĈci transakcji Transakcje gwarantują spójnoĞü modyfikowanych informacji. Typowym przykáa- dem transakcyjnego przetwarzania danych jest przeniesienie pieniĊdzy 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 ii Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE Gdyby po wykonaniu pierwszej operacji wystąpiá báąd uniemoĪliwiający wykonanie drugiej, z systemu zniknĊáaby pewna suma pieniĊdzy. Równie nieprzyjemnym zasko- czeniem dla wáaĞciciela byáoby sprawdzenie przez niego stanu obu jego kont juĪ po odjĊciu 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 wchodzące w skáad transakcji instrukcje albo nie jest zatwierdzana Īadna z nich. Innymi sáowy, wszystkie wchodzące w skáad transakcji instrukcje muszą byü wykonane poprawnie — jeĪeli choü jedna z nich zgáosi báąd, 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ójnoĞci danych. PoniewaĪ wszystkie zmiany danych wykonywane są w ramach transakcji, przechowywane w bazach informacje zawsze bĊdą spójne1. 3. Izolowane (ang. Isolation). Izolowanie transakcji wymaga albo zablokowania modyfikowanych w ramach jednej z nich danych, albo utworzenia ich dodatkowej wersji. W zaleĪnoĞci od obowiązującego w ramach serwera lub sesji klienckiej poziomu izolowania transakcji, moĪe dojĞü do nastĊpujących sytuacji: a) Utrata aktualizacji (ang. lost update) ma miejsce, gdy dwa procesy modyfikują jednoczeĞnie te same dane. Przykáadowo jeden uĪytkownik zmienia cenĊ towaru na 100 zá, a drugi — na 200. W takim przypadku jedna ze zmian zostanie utracona (zastąpiona drugą modyfikacją). DomyĞlnie skonfigurowane serwery bazodanowe nie dopuszczają do utraty aktualizacji. b) Brudne odczyty (ang. dirty read) — do takiej sytuacji dochodzi, gdy moĪliwe jest odczytanie zmian niezatwierdzonych jeszcze przez inny proces. JeĪeli proces odczytujący nie zaĪąda zaáoĪenia blokady na odczytywanych danych, uzyska do nich dostĊp nawet wtedy, kiedy wáaĞnie bĊdą modyfikowane. Gdyby proces modyfikujący wycofaá wprowadzone zmiany, odczytane dane okazaáyby siĊ niespójne. DomyĞlnie 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 zakoĔczeniu odczytu (a nie po zakoĔczeniu caáej transakcji) proces odczytujący zdejmuje blokady zaáoĪone na odczytywane dane. Niezablokowane dane mogą byü zmienione przez inny proces, a wiĊc ich powtórne odczytanie da inny (niespójny) wynik. DomyĞlnie 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. ii Transakcje i wspóäbieĔnoĈè 199 d) Odczyty widma (ang. phantom reads) — sytuacja taka ma miejsce, jeĪeli pomiĊdzy dwoma wykonanymi w ramach transakcji odczytami zmieni siĊ liczba odczytywanych wierszy. JeĪeli np. podczas pierwszego odczytu w tabeli Produkty znajdowaáo siĊ 100 produktów o cenach niĪszych niĪ 10 zá, instrukcja SELECT * FROM Produkty WHERE Cena 10 zwróciáaby 100 wierszy. W trakcie trwania transakcji moĪliwa jest jednak zmiana pozostaáych wierszy tabeli, w tym obniĪenie ceny jakiegoĞ produktu poniĪej 10 zá. MoĪliwe jest równieĪ wstawienie do tej tabeli nowego produktu o cenie np. 7 zá. Z tego powodu drugie wykonanie tego samego zapytania zwróciáoby juĪ 102 wiersze. DomyĞlnie skonfigurowane serwery bazodanowe dopuszczają odczyty widma. 4. Trwaáe (ang. Durability). TrwaáoĞü transakcji gwarantuje, Īe efekty zatwierdzonych transakcji bĊdą zapisane w bazie, nawet w przypadku awarii serwera SQL 2005. Do przywrócenia spójnoĞci danych serwery bazodanowe z reguáy uĪywają jakiejĞ formy dziennika transakcyjnego. Pierwsze litery cech transakcji (A — Atomicity, C — Consistency, I — Isolation, D — Durability) tworzñ skrót ACID, powszechnie uĔywany do opisywania reguä przetwarzania danych, których muszñ przestrzegaè serwery bazodanowe, Ĕeby mogäy zostaè nazwane transakcyjnymi lub relacyjnymi. Transakcyjne przetwarzanie danych Serwery bazodanowe mogą dziaáaü w trybie niejawnego zatwierdzania transakcji (w ser- werze SQL 2008 taki tryb jest trybem domyĞlnym). Oznacza to, Īe uĪytkownicy nie muszą samodzielnie rozpoczynaü transakcji, bo serwer robi to za nich. W trybie niejawnego zatwierdzania transakcji wykonanie kaĪdej instrukcji jĊzyka SQL skáada siĊ z trzech etapów: 1. Serwer bazodanowy automatycznie rozpoczyna nową transakcjĊ. 2. Wykonywana jest pojedyncza instrukcja SQL. 3. JeĪeli instrukcja zostaáa wykonana z powodzeniem, transakcja jest zatwierdzana, w przeciwnym przypadku jest wycofywana. Taki sposób dziaäania oznacza, Ĕe uĔytkownicy nie mogñ samodzielnie zatwierdzaè lub wycofywaè automatycznie rozpoczötych transakcji. Dlatego nazywa siö on trybem niejawnego zatwierdzania transakcji. PoniĪszy przykáad ilustruje dziaáanie trybu niejawnego zatwierdzania transakcji za po- mocą funkcji systemowej @@TRANCOUNT zwracającej liczbĊ otwartych, aktywnych w danym momencie transakcji: 200 CzöĈè III ii 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 rozpoczĊciem i po zakoĔczeniu wykonywania instrukcji UPDATE nie byáo Īadnych otwartych transakcji. Tryb jawnego zatwierdzania transakcji W niektórych serwerach bazodanowych (np. w serwerze Oracle) domyĞlnym trybem transakcyjnego przetwarzania danych jest tryb ich jawnego zatwierdzania. W tym trybie wykonanie kaĪdej instrukcji jĊzyka SQL przebiega nastĊpująco: 1. Serwer bazodanowy automatycznie rozpoczyna nową transakcjĊ. 2. Wykonywana jest pojedyncza instrukcja SQL. 3. UĪytkownik samodzielnie musi zatwierdziü lub wycofaü otwartą przez serwer transakcjĊ. Dziaáanie tego trybu moĪna zasymulowaü w serwerze SQL 2008, ustawiając 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 rozpoczĊciem instrukcji UPDATE równieĪ nie byáo otwartych trans- akcji, ale niejawnie rozpoczĊta transakcja nie zostaáa po jej wykonaniu automatycznie zamkniĊta. Musi to zrobiü sam uĪytkownik — albo zatwierdzając wprowadzone zmiany, albo je wycofując. Przed przejĞciem do dalszych üwiczeĔ zakoĔcz transakcjĊ i wyáącz omawiany tryb: COMMIT TRAN; SET IMPLICIT_TRANSACTIONS OFF; Tryb jawnego zatwierdzania transakcji pozwala wycofywaè przypadkowe lub bäödne modyfikacje, ale zatwierdzanie transakcji, której samemu siö nie rozpoczöäo, jest maäo intuicyjne. Rozdziaä 9. ii Transakcje i wspóäbieĔnoĈè 201 Rozpoczynanie transakcji Mechanizm transakcyjnego przetwarzania danych pokaĪemy, jawnie rozpoczynając i koĔcząc transakcje. Pozwoli nam to wykonaü w ramach poszczególnych transakcji dowolną liczbĊ instrukcji oraz samodzielnie sterowaü czasem rozpoczĊcia i zakoĔczenia poszczególnych transakcji. ĩeby rozpocząü transakcjĊ, naleĪy wykonaü instrukcjĊ BEGIN TRAN2: BEGIN TRAN; SELECT @@TRANCOUNT; ------------------------------------------------------------ 1 JeĪeli 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 rozpoczĊta 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 przeprowadziliĞmy dowolne zmiany, jest otwarta, moĪemy 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 skoĔczy siĊ zupeánie inaczej niĪ ta sama próba wykonana przez innego uĪytkownika. ĩ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. Zostaáo ono natychmiast wykonane, a cena kaĪdego produktu z podkategorii 1 wynosi 1. 3. ĩeby wykonaü to samo zapytanie jako inny uĪytkownik, otwórz nowe okno edytora SQL3 i skopiuj do niego powyĪszą instrukcjĊ SELECT (rysunek 9.1). 2 W niektórych serwerach bazodanowych transakcje rozpoczyna siĊ instrukcjami BEGIN TRANSACTION lub BEGIN WORK. 3 MoĪna to zrobiü, naciskając kombinacjĊ klawiszy Ctrl+N lub klikając przycisk New Query. 202 CzöĈè III ii Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE Rysunek 9.1. Zapytanie wykonuje siĊ juĪ prawie dwie minuty, ale wciąĪ nie zwróciáo Īadnych danych Transakcyjne przetwarzanie danych polega na takim realizowaniu Ĕñdaþ klientów przez serwery bazodanowe, Ĕeby kaĔdy z nich miaä wraĔenie, Ĕe jest jedynym uĔyt- kownikiem serwera. Wymaga to opisanego w dalszej czöĈci rozdziaäu blokowania obiektów, do których w danym momencie odwoäujñ siö inni uĔytkownicy serwera. Wycofywanie transakcji Wycofanie transakcji oznacza przywrócenie danych do stanu sprzed jej rozpo- czĊcia i zdjĊcie wszystkich zaáoĪonych na potrzeby transakcji blokad. JeĪeli wrócimy do pierwszego okna edytora SQL (tego, w którym zapytanie zwróciáo wyniki) i wyko- namy w nim instrukcjĊ ROLLBACK TRAN4, a nastĊpnie przeáączymy siĊ do drugiego okna edytora SQL, przekonamy siĊ, Īe zapytanie wreszcie zostaáo wykonane i w dodatku ceny produktów z pierwszej podkategorii wcale nie wynoszą 1. Spowodowane jest to wycofaniem transakcji, w ramach której ceny byáy zmienione, i zdjĊciem zaáoĪonych na jej potrzeby blokad: 4 W niektórych serwerach bazodanowych transakcje wycofuje siĊ instrukcjami ROLLBACK TRANSACTION lub ROLLBACK WORK. Rozdziaä 9. ii Transakcje i wspóäbieĔnoĈè 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 zdjĊcie wszystkich zaáoĪonych na potrzeby transakcji blokad. Wspomniany na początku rozdziaáu przykáad przelania pieniĊdzy z jednego konta na drugie mógáby byü zaimplementowany w poniĪszy 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 rozpoczĊciu transakcji nastĊpuje wywoáanie dwóch (nieistniejących w bazie AdventureWorks) procedur. JeĪeli Īadna z nich nie zgáosi báĊdu, caáa transakcja bĊdzie zatwierdzona (zatwierdziü transakcjĊ moĪemy, wykonując instrukcjĊ COMMIT TRAN5), w przeciwnym przypadku zostanie ona wycofana. ZagnieĔdĔanie transakcji WiĊkszoĞü serwerów bazodanowych pozwala zagnieĪdĪaü transakcje, czyli wykonaü instrukcjĊ BEGIN TRAN w ramach wczeĞniej rozpoczĊtej transakcji. Wynikiem takiej ope- racji jest zwiĊkszenie licznika otwartych transakcji, a nie rozpoczĊcie nowej (atomowej, niepodzielnej, trwaáej i spójnej) transakcji. Dziaáanie mechanizmu zagnieĪdĪania transakcji ilustruje poniĪszy przykáad: wykona- nie instrukcji BEGIN TRAN powoduje zwiĊkszenie 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 ii 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 WiĊkszoĞü serwerów bazodanowych pozwala wycofaü nie tylko caáą transakcjĊ, ale teĪ jej czĊĞü. W tym celu naleĪy w trakcie transakcji wykonaü instrukcjĊ SAVE TRAN6, a nastĊpnie 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 okreĞlonego punktu nie powoduje jej za- koĔczenia (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 zostaáa wykonana po zdefiniowaniu punktu przy- wracania PP1, instrukcja ROLLBACK TRAN PP1 przywróciáa stan danych do momentu sprzed jej wykonania, i w rezultacie tyko pierwszy wiersz zostaá na trwaáe wstawiony do tabeli. 6 W niektórych serwerach bazodanowych punkty przywracania tworzy siĊ instrukcjami SAVE TRANSACTION lub SAVE WORK. Rozdziaä 9. ii Transakcje i wspóäbieĔnoĈè 205 WspóäbieĔnoĈè WspóábieĪnoĞü to zdolnoĞü systemu do jednoczesnego realizowania wielu operacji, z reguáy uzyskiwana poprzez uruchomienie osobnych procesów (robotników) na po- trzeby obsáugi poszczególnych ĪądaĔ. WspóäbieĔnoĈè ma ogromny wpäyw na skalowalnoĈè serwerów bazodanowych, czyli ich zdolnoĈè do coraz szybszego wykonywania transakcji dziöki rozbudowywaniu komputerów, na przykäad zwiökszaniu ich mocy obliczeniowej czy przepustowoĈci dysków twardych. ĩeby kaĪdy z kilkuset czy nawet kilku tysiĊcy jednoczesnych uĪytkowników serwera bazodanowego mógá pracowaü tak, jakby byá jego jedynym uĪytkownikiem, konieczne jest odizolowanie od siebie poszczególnych transakcji. UmoĪliwiają to automatycznie zakáadane blokady. Blokady Pomijając analizy wewnĊtrznych mechanizmów dziaáania róĪnych serwerów bazoda- nowych, blokady moĪna podzieliü ze wzglĊdu na ich tryb (sposób blokowania) i zakres (typ blokowanych zasobów). Tryby blokad Tryb blokady decyduje o tym, czy moĪliwe bĊdzie jej zaáoĪenie na zasobie wczeĞniej zablokowanym przez inny proces: 1. Blokady wspóádzielone S (ang. Shared) są domyĞlnie zakáadane na odczytywanych obiektach, takich jak tabele czy wiersze. Na obiekt zablokowany w trybie S inne procesy teĪ mogą zaáoĪyü blokadĊ S, czyli odczytujący nie blokują innych odczytujących. Blokady S domyĞlnie zakáadane są tylko na czas wykonywania zapytania, a nie caáej transakcji. 2. Blokady wyáączne X (ang. eXclusive) są zakáadane na modyfikowanych obiektach. Blokady X są niekompatybilne z innymi blokadami, czyli modyfikujący blokują innych uĪytkowników. W przeciwieĔstwie do blokad wspóádzielonych, blokady wyáączne domyĞlnie utrzymywane są do zakoĔczenia caáej transakcji, a nie pojedynczej operacji. Zakresy blokad Blokady mogą byü zakáadane na poziomie poszczególnych wierszy, kluczy indeksów, stron, zakresów lub caáych tabel. Te obiekty tworzą naturalną hierarchiĊ: tabela skáada siĊ z wielu stron, na kaĪdej stronie zapisanych jest wiele wierszy itd. Z tego powodu serwery bazodanowe muszą analizowaü wszystkie istniejące blokady, zanim zaáoĪą nową — jeĪeli choü jeden wiersz tabeli jest zablokowany w trybie X, nie moĪna na caáej tabeli zaáoĪyü innej blokady. 206 CzöĈè III ii Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE Im wiöksze obiekty sñ blokowane, tym mniejsza wspóäbieĔnoĈè (bo uĔytkownicy muszñ däuĔej czekaè na dostöp do zablokowanych zasobów), ale równieĔ tym mniej- sza liczba blokad, którymi musi zarzñdzaè serwer bazodanowy (zaäoĔy jednñ blokadö na caäej 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 zaáoĪyü wymaganych do ukoĔczenia juĪ rozpoczĊtych operacji blokad. NajczĊĞciej wystĊpują dwa typy zakleszczeĔ: 1. Zakleszczenia cykliczne, wynikające z tego, Īe dwa procesy w róĪnych kolejnoĞciach próbują uzyskaü dostĊp do tych samych zasobów. 2. Zakleszczenia konwersji blokad, związane ze zmianą wczeĞniej zaáoĪonej blokady wspóádzielonej (wiele procesów moĪe jednoczeĞnie zablokowaü ten sam zasób w trybie S) na blokadĊ wyáączną (tylko jeden proces moĪe zaáoĪyü na tym samym obiekcie blokadĊ X). Serwery bazodanowe automatycznie wykrywają zakleszczenia i przerywają dziaáanie jednego procesu. Na ofiarĊ zakleszczenia wybierany jest proces o niĪszym prioryte- cie, a jeĪeli oba procesy mają ten sam priorytet, ofiarą zakleszczenia zostaje ten, którego wycofanie jest mniej kosztowne. Mechanizm wykrywania i usuwania zakleszczeĔ pokazuje poniĪszy przykáad: Pierwszy uĪytkownik w ramach jawnie rozpoczĊtej transakcji modyfikuje kilka danych w tabeli HumanResources.Department: BEGIN TRAN; UPDATE HumanResources.Department SET Name = UPPER(Name) WHERE DepartmentID 5; ------------------------------------------------------------ (18 row(s) affected) NastĊpnie inny uĪytkownik w ramach jawnie rozpoczĊtej przez siebie transakcji mo- dyfikuje znacznie wiĊcej danych w tabeli Production.Product7: BEGIN TRAN; UPDATE Production.Product SET Name = UPPER(Name) WHERE ProductID 300; ------------------------------------------------------------ (500 row(s) affected) 7 Zasymulowaü jednoczesną pracĊ dwóch uĪytkowników moĪemy, otwierając nowe okno edytora SQL — kaĪde z okien nawiązuje wáasną sesjĊ z bazą danych. Rozdziaä 9. ii Transakcje i wspóäbieĔnoĈè 207 NastĊpnie pierwszy uĪytkownik próbuje odczytaü zawartoĞü tabeli zablokowanej juĪ przez 2. sesjĊ (okno wyników moĪe pokazaü pierwszych kilkadziesiąt wierszy, ale i tak uĪytkownik bĊdzie musiaá czekaü na moĪliwoĞü zablokowania w trybie S pozostaáych wierszy tabeli Production.Product): SELECT * FROM Production.Product; W tym momencie nie wystąpiáo jeszcze zakleszczenie — wystarczyáoby, Īeby drugi uĪytkownik zakoĔczyá swoją transakcjĊ. Ale jeĪeli w ramach 2. sesji uĪytkownik spró- buje odczytaü zawartoĞü tabeli zmodyfikowanej przez pierwszego uĪytkownika, 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 zostaáo jednak wykonane, co wiĊcej, nazwy departamentów nie zostaáy przekonwertowane na duĪe litery. ĩeby przekonaü siĊ, dlaczego tak siĊ staáo, wystarczy przeáączyü siĊ do okienka 1. sesji. Znajdziemy w nim poniĪszy ko- munikat báĊdu: 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. JeĪeli sprawdzimy liczbĊ otwartych w ramach 1. sesji transakcji, okaĪe siĊ, Īe jawnie rozpoczĊta przez pierwszego uĪytkownika transakcja zostaáa — zgodnie z komunikatem báĊdu — wycofana: SELECT @@TRANCOUNT; ------------------------------------------------------------ 0 PoniewaĪ wycofanie transakcji wiąĪe siĊ ze zdjĊciem zaáoĪonych na jej potrzeby blokad, druga sesja mogáa z powodzeniem zakoĔczyü operacje i odczytaü tabelĊ HumanResources. ´Department. Liczba transakcji otwartych w ramach 2. sesji nadal wynosi 1 — Īeby zakoĔczyü üwiczenie i wycofaü zmiany, naleĪy wykonaü w tym oknie edytora SQL instrukcjĊ ROLLBACK TRAN. Poziomy izolowania transakcji MoĪemy wpáywaü na sposób zakáadania blokad przez serwery bazodanowe, zmieniając poziom izolowania transakcji. WiĊkszoĞü 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 ii Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE maksymalna wspóábieĪnoĞü okupiona jest wystĊpowaniem najwiĊkszej liczby typów nie- spójnoĞci danych, do najbardziej restrykcyjnego, który kosztem ograniczenia wspóábieĪ- noĞci gwarantuje najwyĪszy poziom spójnoĞci danych. Read Uncommitted W trybie niezatwierdzonego odczytu (ang. Read Uncommitted) odczyt danych nie po- woduje zaáoĪenia blokady wspóádzielonej. Na tym poziomie wystĊpują brudne od- czyty, niepowtarzalne odczyty i odczyty widma (jedynym niekorzystnym zjawi- skiem niewystĊpującym na tym poziomie jest utrata aktualizacji). ĩeby siĊ o tym przekonaü: 1. W jednej sesji (oknie edytora SQL) rozpoczniemy transakcjĊ i zaktualizujemy nazwĊ dziaáu: 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 uĪytkownika dane: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT Name FROM HumanResources.Department WHERE DepartmentID = 5; ------------------------------------------------------------ ZmianaWToku Udaáo nam siĊ odczytaü dane, pomimo Īe osoba, która je zmieniaáa, nie zatwierdziáa jeszcze transakcji, a wiĊc w kaĪdej chwili moĪe ją wycofaü. W tym trybie (czĊsto wymuszanym na poziomie poszczególnych instrukcji za pomocą specyficznych dla danego serwera bazodanowego dyrektyw optymalizatora) moĪna odczytywaü dane, o których wiemy, Īe nie bĊdą w tym samym czasie modyfikowane. KoĔcząc üwiczenie, zamknij bez zatwierdzania otwartej transakcji i na nowo otwórz oba okna edytora SQL — w ten sposób kolejne üwiczenie rozpoczniemy, pracując w domyĞlnym trybie izolowania transakcji. Read Committed Tryb odczytu zatwierdzonego (ang. Read Committed) jest domyĞlnym poziomem izolowania transakcji. Na tym poziomie odczyt danych wymaga zaáoĪenia na nich blokady wspóádzielonej. PoniewaĪ zakáadana 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 wystĊpują niepowtarzalne odczyty i odczyty widma. Rozdziaä 9. ii Transakcje i wspóäbieĔnoĈè 209 Zjawisko niepowtarzalnego odczytu pokazuje poniĪszy przykáad: 1. W pierwszym oknie edytora SQL ustawiamy tryb odczytów zatwierdzonych8, 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. JeĪeli pierwszy uĪytkownik 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 zakoĔczenia caáej transakcji. DziĊki temu inny proces nie moĪe zmodyfikowaü odczytywanych w jej ramach danych, co eliminuje niepowtarzalne odczyty. Na tym poziomie wystĊpują tylko odczyty widma. Zjawisko odczytu widma pokazuje poniĪszy przykáad: 1. W ramach pierwszej sesji zmienimy poziom izolowania transakcji na Repeatable Read i w ramach jawnie rozpoczĊtej transakcji odczytamy nazwy towarów o cenach pomiĊdzy 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 domyĞlnym, instrukcja SET dodana jest tylko w celach demonstracyjnych. 210 CzöĈè III ii 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. JeĪeli pierwszy uĪytkownik raz jeszcze wykona, w ramach tej samej transakcji, to samo zapytanie, tym razem jego wynik bĊdzie 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. JeĪeli jednak w ramach drugiej sesji spróbujemy zmieniü dane odczytywane w ramach nadal otwartej pierwszej transakcji (czyli doprowadziü do niepowtarzalnego odczytu), instrukcja bĊdzie oczekiwaü, aĪ pierwsza transakcja zostanie zakoĔczona, a zaáoĪone dla niej blokady zdjĊte: UPDATE Production.Product SET ListPrice = 8 WHERE Name = Taillights - Battery-Powered ; 5. ĩeby powyĪsza aktualizacja zostaáa wykonana, w pierwszym oknie edytora SQL wykonaj instrukcjĊ COMMIT TRAN. W trybie Repeatable Read naleĪy odczytywaü te dane, które w ramach transakcji odczytywane są kilkukrotnie i mogą byü zmieniane w tym samym czasie przez innych uĪytkowników. Sytuacja taka ma miejsce np. w róĪnego rodzaju zestawieniach i raportach zbiorczych, w których odczytując te same dane, za kaĪdym razem musimy otrzymaü te same wyniki, inaczej zestawienie lub raport bĊdą niespójne. Serializable W trybie szeregowania transakcje odwoáujące siĊ do tych samych tabel wykonywane są jedna po drugiej. Blokowanie caáych obiektów, a nie tylko odczytywanych danych, na czas trwania transakcji pozwala wyeliminowaü odczyty widma, ale powoduje, Īe odczytując nawet jeden wiersz tabeli, moĪemy uniemoĪliwiü pozostaáym uĪytkownikom zmodyfikowanie przechowywanych w niej danych. ĩeby siĊ o tym przekonaü: 1. W pierwszym oknie edytora SQL przeáączymy 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. ii Transakcje i wspóäbieĔnoĈè 211 WHERE ListPrice BETWEEN 10 AND 15; ------------------------------------------------------------ 2 Bearing Ball 2. JeĪeli teraz w drugim oknie edytora SQL spróbujemy zmieniü cenĊ innego towaru, okaĪe siĊ, Īe aktualizacja zostaáa zablokowana i bĊdzie wykonana dopiero po zakoĔczeniu pierwszej transakcji: UPDATE Production.Product SET ListPrice = 120 WHERE ProductID =3; 3. KoĔcząc üwiczenie, zamknij oba okna edytora SQL bez zatwierdzania rozpoczĊtej w jednym z nich transakcji. W trybie Serializable mamy gwarancjĊ, Īe odczytywane w ramach transakcji dane zawsze bĊdą takie same — serwer bazodanowy nie dopuĞci nie tylko do ich zmiany, ale równieĪ do pojawienia siĊ nowych danych. Jednak przez ten czas pozostali uĪyt- kownicy nie bĊdą mogli modyfikowaü zablokowanych tabel. W wiĊkszoĞci przypad- ków powoduje to tak znaczne wydáuĪenie czasu reakcji serwera, Īe lepiej jest skopio- waü odczytywane dane9, a jeĪeli zmian nie jest zbyt duĪo, przeáączyü siĊ do modelu optymistycznego. Model optymistyczny W modelu optymistycznym tylko modyfikujący blokują innych modyfikujących, czyli róĪni uĪytkownicy mogą jednoczeĞnie modyfikowaü i odczytywaü te same dane. Serwery bazodanowe zapewniają spójnoĞü modyfikowanych w tym modelu danych poprzez ich wersjonowanie. Zakáadając (optymistycznie), Īe w czasie gdy jeden uĪyt- kownik odczytuje dane, inni raczej nie bĊdą ich modyfikowaü, są one w stanie na bieĪąco zarządzaü dodatkowymi wersjami danych. JeĪeli to zaáoĪenie jest prawdziwe, czyli jeĪeli jednoczesne modyfikacje i odczyty tych samych danych nie zachodzą zbyt czĊsto, moĪemy znacznie skróciü czas reak- cji serwera10, przeáączając bazĊ do optymistycznego modelu wspóábieĪnoĞci. ĩeby siĊ o tym przekonaü: 1. W pierwszym oknie edytora SQL wykonamy poniĪsze instrukcje, przeáączając 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, domyĞlnie dziaáają w optymistycznym modelu wspóábieĪnoĞci. 212 CzöĈè III ii Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE oraz MERGE 2. W tym samym oknie edytora SQL poáączymy siĊ z bazą AdventureWorks i w ramach jawnie rozpoczĊtej 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 zostaáo wykonane natychmiast, ale z zachowaniem wymogów domyĞlnego trybu izolowania transakcji, czyli trybu Read Committed — pozostali uĪytkownicy serwera odczytają ostatnią zatwierdzoną wersjĊ danych. Gdyby rozpoczĊta w ramach pierwszej sesji transakcja zostaáa zatwierdzona, to ponowne wykonanie tego samego zapytania zwróciáoby najnowszą, zatwierdzoną wersjĊ, ze zmienionymi tytuáami dwóch pierwszych pracowników. Model pesymistyczny W modelu pesymistycznym odczytujący są blokowani przez modyfikujących (serwer bĊdzie czekaá z zaáoĪeniem blokady S, aĪ zdjĊta zostanie blokada X), a mody- fikujący przez odczytujących (zaáoĪenie blokady X wymaga zdjĊcia blokady S). PoniewaĪ koszt zarządzania wieloma wersjami tych samych danych roĞnie wraz ze wzro- stem wersjonowanych danych, w tym modelu zakáada siĊ (pesymistycznie), Īe odczy- tywane dane bĊdą w tym samym czasie regularnie modyfikowane. ĩeby przywróciü pesymistyczny (domyĞlny) model wspóábieĪnoĞci bazy AdventureWorks, naleĪy wykonaü poniĪsze 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. ii Transakcje i wspóäbieĔnoĈè 213 Podsumowanie  Serwery bazodanowe przeprowadzają wszystkie zmiany danych w ramach jawnie lub niejawnie rozpoczĊtych transakcji.  Transakcje powinny byü otwierane jak najpóĨniej i zamykane jak najwczeĞniej.  Transakcje powinny zawieraü tylko powiązane ze sobą instrukcje.  Przerwane, czy to z powodu awarii klienta, czy teĪ serwera, transakcje bĊdą 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 zakáadanych i zwalnianych blokad.  MoĪna sterowaü sposobem zakáadania i czasem trwania blokad, zmieniając poziom izolowania transakcji.  W modelu optymistycznym serwery bazodanowe wersjonują dane, co poprawia wspóábieĪnoĞü kosztem wiĊkszego obciąĪenia serwera. Zadania 1. Twoim zadaniem jest przygotowanie raportu podsumowującego roczną sprzedaĪ. Wyliczając sumy i Ğrednie wartoĞci sprzedaĪy produktów, kilkukrotnie musisz odczytaü tabelĊ Production.Product. Jak zagwarantujesz poprawnoĞü wyników raportu? 2. Po przerwie na lunch uĪytkownicy zgáaszają, Īe próby dalszej pracy z bazą danych koĔczą siĊ chwilowym „zawieszeniem” programu i wreszcie komunikatem báĊdu mówiącym, Īe serwer bazodanowy jest niedostĊpny. Po sprawdzeniu okazuje siĊ, Īe serwer i sieü dziaáają normalnie, a baza nie zostaáa uszkodzona. Co jest najbardziej prawdopodobną przyczyną problemu? 3. W ramach tworzonej procedury modyfikujesz duĪe iloĞci danych zapisanych w kilkunastu tabelach oraz wstawiasz jeden wiersz, informujący o wykonaniu wszystkich operacji, do tabeli znajdującej siĊ w bazie danych na zdalnym serwerze. Poáączenie pomiĊdzy serwerami jest mocno obciąĪone i zdarza siĊ, Īe czas nawiązywania sesji i przesyáania danych miĊdzy serwerami wielokrotnie siĊ wydáuĪa. Co zrobiü, Īeby w przypadku zgáoszenia przez procedurĊ báĊdu braku poáączenia ze zdalnym serwerem nie trzeba byáo ponownie wykonywaü kosztownych modyfikacji danych?
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Praktyczny kurs SQL
Autor:
,

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: