Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00270 006941 14488593 na godz. na dobę w sumie
Praktyczny kurs SQL. Wydanie II - książka
Praktyczny kurs SQL. Wydanie II - książka
Autor: , Liczba stron: 304
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-246-3373-9 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> sql - programowanie
Porównaj ceny (książka, ebook, audiobook).

Poznaj modele baz danych i standardy języka SQL.

Opanuj język SQL w praktyce!

Bazy danych są dosłownie wszędzie. Trudno sobie dziś bez nich wyobrazić funkcjonowanie nowoczesnej biblioteki, choćby najmniejszego sklepu internetowego, biura rachunkowego czy nawet niewielkiego serwisu WWW. Użytkownicy korzystający z baz danych często nie mają nawet pojęcia, w jaki sposób odbywa się dostęp do informacji i jaki mechanizm jest za to odpowiedzialny. Na ignorancję tę nie mogą sobie jednak pozwolić osoby odpowiedzialne za tworzenie, zarządzanie i konserwowanie baz danych. Powinny one znać przynajmniej jeden z popularnych serwerów bazodanowych i sprawnie posługiwać się językiem SQL stanowiącym standardowe narzędzie komunikacji z relacyjnymi bazami.

Jeśli pragniesz dołączyć do ekskluzywnego grona administratorów baz danych lub chcesz zostać programistą aplikacji bazodanowych, lecz przeszkadza Ci brak znajomości SQL-a, sięgnij po książkę 'Praktyczny kurs SQL. Wydanie II'. W prosty i przystępny sposób prezentuje ona podstawowe pojęcia i zasady rządzące relacyjnym modelem baz danych, a także najważniejsze cechy i konstrukcje języka SQL oraz metody ich wykorzystywania. Lektura książki umożliwi Ci poznanie instrukcji odpowiedzialnych za odczytywanie danych z bazy i ich zapisywanie oraz modyfikację, jak również tworzenie baz i zmianę ich struktury. Poznasz też sposoby tworzenia ról i kont użytkowników oraz zarządzania ich uprawnieniami. Twoją wiedzę ugruntują praktyczne zadania kończące każdy rozdział, a zamieszczone na końcu książki rozwiązania pomogą skorygować ewentualne błędy.

Dowiedz się, jak tworzyć relacyjną bazę danych i zarządzać nią za pomocą języka SQL.

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

Darmowy fragment publikacji:

Praktyczny kurs SQL. Wydanie II Autor: Danuta Mendrala, Marcin Szeliga ISBN: 978-83-246-3373-9 Format: 158×235, stron: 304 Poznaj modele baz danych i standardy języka SQL • Naucz się korzystać z instrukcji pobierania i modyfikacji danych • Dowiedz się, jak tworzyć i zmieniać strukturę bazy oraz zarządzać jej użytkownikami Opanuj język SQL w praktyce! Bazy danych są dosłownie wszędzie. Trudno sobie dziś bez nich wyobrazić funkcjonowanie nowoczesnej biblioteki, choćby najmniejszego sklepu internetowego, biura rachunkowego czy nawet niewielkiego serwisu WWW. Użytkownicy korzystający z baz danych często nie mają nawet pojęcia, w jaki sposób odbywa się dostęp do informacji i jaki mechanizm jest za to odpowiedzialny. Na ignorancję tę nie mogą sobie jednak pozwolić osoby odpowiedzialne za tworzenie, zarządzanie i konserwowanie baz danych. Powinny one znać przynajmniej jeden z popularnych serwerów bazodanowych i sprawnie posługiwać się językiem SQL stanowiącym standardowe narzędzie komunikacji z relacyjnymi bazami. Jeśli pragniesz dołączyć do ekskluzywnego grona administratorów baz danych lub chcesz zostać programistą aplikacji bazodanowych, lecz przeszkadza Ci brak znajomości SQL-a, sięgnij po książkę „Praktyczny kurs SQL. Wydanie II”. W prosty i przystępny sposób prezentuje ona podstawowe pojęcia i zasady rządzące relacyjnym modelem baz danych, a także najważniejsze cechy i konstrukcje języka SQL oraz metody ich wykorzystywania. Lektura książki umożliwi Ci poznanie instrukcji odpowiedzialnych za odczytywanie danych z bazy i ich zapisywanie oraz modyfikację, jak również tworzenie baz i zmianę ich struktury. Poznasz też sposoby tworzenia ról i kont użytkowników oraz zarządzania ich uprawnieniami. Twoją wiedzę ugruntują praktyczne zadania kończące każdy rozdział, a zamieszczone na końcu książki rozwiązania pomogą skorygować ewentualne błędy. • Teoretyczne podstawy funkcjonowania baz danych • Historia języka SQL i obowiązujące standardy zapytań • Odczytywanie, przeszukiwanie, łączenie i grupowanie danych • Korzystanie z podzapytań • Zapisywanie, modyfikacja i usuwanie danych • Transakcje i równoległy dostęp do danych • Tworzenie baz danych i modyfikacja ich struktury • Korzystanie z widoków i indeksów • Zarządzanie użytkownikami, rolami i prawami dostępu do baz danych Dowiedz się, jak tworzyć relacyjną bazę danych i zarządzać nią za pomocą języka SQL Idź do • Spis treści • Przykładowy rozdział • Skorowidz Katalog książek • Katalog online • Zamów drukowany katalog Twój koszyk • Dodaj do koszyka Cennik i informacje • Zamów informacje o nowościach • Zamów cennik Czytelnia • Fragmenty książek online Kontakt Helion SA ul. Kościuszki 1c 44-100 Gliwice tel. 32 230 98 63 e-mail: helion@helion.pl © Helion 1991–2011 Spis treĈci Wstöp .............................................................................................. 9 Trochö teorii, czyli modele i standardy .......................... 17 CzöĈè I 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 ..................................................................................................... 26 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 ..................................................... 29 Normalizacja ............................................................................................................ 29 Podsumowanie ................................................................................................................ 31 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 ............................................................. 35 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 ....................................................................................................................... 54 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 ................................................................................................................ 65 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 Stronicowanie wierszy .................................................................................................... 79 Wydajne wyszukiwanie danych ...................................................................................... 80 W jaki sposób serwery bazodanowe odczytują dane? .............................................. 81 W jakiej kolejnoĞci serwery bazodanowe wykonują poszczególne klauzule zapytaĔ? .................................................................................................. 84 Argumenty SARG .................................................................................................... 85 Podsumowanie ................................................................................................................ 87 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 ..................................................................................................... 96 Záączenie lewostronne .............................................................................................. 97 Záączenie prawostronne ............................................................................................ 97 Záączenie obustronne ................................................................................................ 98 Záączenie krzyĪowe (iloczyn kartezjaĔski) ..................................................................... 98 Záączenia wielokrotne ................................................................................................... 100 OkreĞlanie kolejnoĞci záączeĔ ................................................................................ 103 Záączenie tabeli z nią samą ........................................................................................... 104 Eliminacja duplikatów ............................................................................................ 105 Klucze obce w obrĊbie jednej tabeli ....................................................................... 106 Spis treĈci 5 àączenie wyników zapytaĔ ........................................................................................... 107 Suma ....................................................................................................................... 108 CzĊĞü wspólna ........................................................................................................ 111 RóĪnica ................................................................................................................... 111 àączenie wierszy i wyników funkcji tabelarycznych ................................................... 112 Operator APPLY .................................................................................................... 113 Podsumowanie .............................................................................................................. 115 Zadania ......................................................................................................................... 115 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 ........................................................................................... 120 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 ....................................................................................... 132 Niestandardowa klauzula OVER .................................................................................. 132 Partycje ................................................................................................................... 134 Funkcje rankingu .................................................................................................... 136 Niestandardowe operatory PIVOT i UNPIVOT ........................................................... 137 PIVOT .................................................................................................................... 137 UNPIVOT .............................................................................................................. 140 Klauzula HAVING ....................................................................................................... 141 Podsumowanie .............................................................................................................. 143 Zadania ......................................................................................................................... 144 Zadanie dodatkowe, do wykonania w bazie AdventureWorks ............................... 144 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 ................................................................................. 156 Tabele pochodne .................................................................................................... 157 CTE ........................................................................................................................ 159 Wyznaczanie trendów ............................................................................................ 165 Operatory ...................................................................................................................... 169 Operator EXISTS ................................................................................................... 170 Operator ANY lub SOME ...................................................................................... 173 Operator ALL ......................................................................................................... 177 Podsumowanie .............................................................................................................. 178 Zadania ......................................................................................................................... 179 Zadanie dodatkowe, do wykonania w bazie AdventureWorks ............................... 179 6 Praktyczny kurs SQL 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 ....................................................................................................... 186 Konstruktor wierszy ............................................................................................... 187 Wstawianie wyników zapytaĔ ................................................................................ 187 Usuwanie danych .......................................................................................................... 189 Instrukcja DELETE ................................................................................................ 189 Instrukcja TRUNCATE TABLE ............................................................................ 191 Aktualizowanie danych ................................................................................................ 191 Jednoczesne aktualizowanie wielu kolumn ............................................................ 192 WyraĪenia .............................................................................................................. 193 Aktualizowanie danych wybranych na podstawie danych z innych tabel .............. 193 Aktualizowanie danych za pomocą wyraĪeĔ odwoáujących siĊ do innych tabel .... 194 Instrukcja MERGE ....................................................................................................... 194 Podsumowanie .............................................................................................................. 196 Zadania ......................................................................................................................... 196 Zadanie dodatkowe, do wykonania w bazie AdventureWorks ............................... 197 Rozdziaä 9. Transakcje i wspóäbieĔnoĈè .......................................................... 199 WáaĞciwoĞci transakcji ................................................................................................. 199 Transakcyjne przetwarzanie danych ............................................................................. 201 Tryb jawnego zatwierdzania transakcji .................................................................. 202 Rozpoczynanie transakcji ....................................................................................... 203 Wycofywanie transakcji ......................................................................................... 204 Zatwierdzanie transakcji ......................................................................................... 205 ZagnieĪdĪanie transakcji ........................................................................................ 205 Punkty przywracania .............................................................................................. 206 WspóábieĪnoĞü .............................................................................................................. 207 Blokady .................................................................................................................. 207 Zakleszczenia ......................................................................................................... 208 Poziomy izolowania transakcji ............................................................................... 209 Model optymistyczny ............................................................................................. 213 Model pesymistyczny ............................................................................................. 214 Podsumowanie .............................................................................................................. 215 Zadania ......................................................................................................................... 215 CzöĈè IV Tworzenie baz danych, czyli instrukcje CREATE, ALTER i DROP ............................................................ 217 Rozdziaä 10. Bazy danych i tabele .................................................................... 219 Tworzenie i usuwanie baz danych ................................................................................ 219 Tworzenie i usuwanie tabel .......................................................................................... 222 Schematy ................................................................................................................ 223 Zmiana struktury tabeli ................................................................................................. 223 Ograniczenia ................................................................................................................. 224 NOT NULL ............................................................................................................ 224 Klucz podstawowy ................................................................................................. 225 NiepowtarzalnoĞü ................................................................................................... 227 Spis treĈci 7 WartoĞü domyĞlna .................................................................................................. 227 Warunek logiczny .................................................................................................. 228 Klucz obcy ............................................................................................................. 228 Ograniczenia a wydajnoĞü instrukcji modyfikujących i odczytujących dane ......... 231 Podsumowanie .............................................................................................................. 232 Zadania ......................................................................................................................... 233 Rozdziaä 11. Widoki i indeksy ........................................................................... 235 Widoki .......................................................................................................................... 235 Tworzenie i usuwanie widoków ............................................................................. 235 Modyfikowanie widoków ....................................................................................... 238 Korzystanie z widoków .......................................................................................... 238 Zalety widoków ...................................................................................................... 243 Indeksy ......................................................................................................................... 243 Tworzenie, modyfikowanie i usuwanie indeksów .................................................. 245 Porządkowanie indeksów ....................................................................................... 247 Podsumowanie .............................................................................................................. 248 Zadania ......................................................................................................................... 249 CzöĈè V Uprawnienia uĔytkowników, czyli instrukcje GRANT i REVOKE ................................ 251 Rozdziaä 12. Nadawanie i odbieranie uprawnieþ ................................................ 253 Konta uĪytkowników .................................................................................................... 253 Zakáadanie i usuwanie kont uĪytkowników ............................................................ 254 Role .............................................................................................................................. 255 Tworzenie i usuwanie ról ....................................................................................... 255 Przypisywanie ról do uĪytkowników ..................................................................... 255 Specjalna rola Public .............................................................................................. 256 Uprawnienia ................................................................................................................. 256 Nadawanie i odbieranie uprawnieĔ ........................................................................ 257 Dziedziczenie uprawnieĔ ....................................................................................... 258 Przekazywanie uprawnieĔ ...................................................................................... 260 Zasada minimalnych uprawnieĔ ............................................................................. 261 Podsumowanie .............................................................................................................. 261 Zadania ......................................................................................................................... 262 Zadanie dodatkowe, do wykonania w bazie AdventureWorks ............................... 262 Dodatek A Rozwiñzania zadaþ ....................................................................... 263 Zadania z rozdziaáu 1. ................................................................................................... 263 Zadanie 1. ............................................................................................................... 263 Zadanie 2. ............................................................................................................... 264 Zadanie 3. ............................................................................................................... 264 Zadania z rozdziaáu 2. ................................................................................................... 265 Zadanie 1. ............................................................................................................... 265 Zadanie 2. ............................................................................................................... 265 Zadanie 3. ............................................................................................................... 265 Zadania z rozdziaáu 3. ................................................................................................... 266 Zadanie 1. ............................................................................................................... 266 Zadanie 2. ............................................................................................................... 267 Zadanie 3. ............................................................................................................... 267 Zadanie 4. ............................................................................................................... 268 Zadanie 5. ............................................................................................................... 270 8 Praktyczny kurs SQL Zadania z rozdziaáu 4. ................................................................................................... 271 Zadanie 1. ............................................................................................................... 271 Zadanie 2. ............................................................................................................... 272 Zadanie 3. ............................................................................................................... 273 Zadanie 4. ............................................................................................................... 274 Zadania z rozdziaáu 5. ................................................................................................... 275 Zadanie 1. ............................................................................................................... 275 Zadanie 2. ............................................................................................................... 275 Zadanie 3. ............................................................................................................... 276 Zadanie 4. ............................................................................................................... 276 Zadania z rozdziaáu 6. ................................................................................................... 277 Zadanie 1. ............................................................................................................... 277 Zadanie 2. ............................................................................................................... 278 Zadanie 3. ............................................................................................................... 278 Zadanie 4. ............................................................................................................... 279 Zadania z rozdziaáu 7. ................................................................................................... 280 Zadanie 1. ............................................................................................................... 280 Zadanie 2. ............................................................................................................... 281 Zadanie 3. ............................................................................................................... 282 Zadanie 4. ............................................................................................................... 284 Zadania z rozdziaáu 8. ................................................................................................... 285 Zadanie 1. ............................................................................................................... 285 Zadanie 2. ............................................................................................................... 286 Zadanie 3. ............................................................................................................... 286 Zadanie 4. ............................................................................................................... 288 Zadania z rozdziaáu 9. ................................................................................................... 289 Zadanie 1. ............................................................................................................... 289 Zadanie 2. ............................................................................................................... 290 Zadanie 3. ............................................................................................................... 290 Zadania z rozdziaáu 10. ................................................................................................. 291 Zadanie 1. ............................................................................................................... 291 Zadanie 2. ............................................................................................................... 291 Zadanie 3. ............................................................................................................... 292 Zadania z rozdziaáu 11. ................................................................................................. 293 Zadanie 1. ............................................................................................................... 293 Zadanie 2. ............................................................................................................... 293 Zadanie 3. ............................................................................................................... 294 Zadania z rozdziaáu 12. ................................................................................................. 294 Zadanie 1. ............................................................................................................... 294 Zadanie 2. ............................................................................................................... 295 Zadanie 3. ............................................................................................................... 295 Skorowidz .................................................................................... 297 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. 200 CzöĈè III i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE, 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 zaskocze- niem dla wáaĞciciela byáoby sprawdzenie przez niego stanu obu jego kont juĪ po odjĊciu danej sumy z pierwszego, 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 są wykonywane 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 reads) — 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. 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Ğli np. podczas pierwszego odczytu w tabeli 1 Przynajmniej w teorii. W praktyce bazy danych ulegają uszkodzeniu, choü bardzo rzadko z winy serwerów bazodanowych. Rozdziaä 9. i Transakcje i wspóäbieĔnoĈè 201 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 baz danych. 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ä przetwarza- nia danych, których muszñ przestrzegaè serwery bazodanowe, Ĕeby mogäy byè nazwane transakcyjnymi lub relacyjnymi. Transakcyjne przetwarzanie danych Serwery bazodanowe mogą dziaáaü w trybie niejawnego zatwierdzania transakcji (w serwerze SQL 2011 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 razie 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 pomocą funkcji systemowej @@TRANCOUNT zwracającej liczbĊ otwartych, aktywnych w danym momencie transakcji: SELECT @@TRANCOUNT; UPDATE dbo.Produkty SET [Koszt standardowy]=3 WHERE [Kod produktu]= NWTC-82 ; 202 CzöĈè III i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE, MERGE SELECT @@TRANCOUNT; ------------------------------------------------------------ 0 0 Jak widaü, 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 2011, ustawiając opcjĊ sesji IMPLICIT_TRANSACTIONS: SET IMPLICIT_TRANSACTIONS ON; SELECT @@TRANCOUNT; UPDATE dbo.Produkty SET [Koszt standardowy]=3 WHERE [Kod produktu]= NWTC-82 ; 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óra nie zostaäa przez nas rozpoczöta, jest maäo intuicyjne. Rozdziaä 9. i Transakcje i wspóäbieĔnoĈè 203 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Ĕcze- nia 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, dowiemy siĊ, Īe rozpoczĊta przez nas transakcja nadal jest otwarta: UPDATE dbo.Produkty SET [Cena katalogowa]=1 WHERE Kategoria= Zupy 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 stanie przewidzieü naszej decyzji, a jedną z cech transakcji jest jej odizolowanie, próba odczytania danych z tabeli dbo.Produkty 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 [Nazwa produktu], [Cena katalogowa], Kategoria FROM dbo.Produkty WHERE Kategoria IN ( Zupy , Sosy ) ORDER BY Kategoria; ------------------------------------------------------------ Northwind Traders Hot Pepper Sauce 21,05 Sosy Northwind Traders Tomato Sauce 17,00 Sosy Northwind Traders Curry Sauce 30,00 Sosy Northwind Traders Clam Chowder 1,00 Zupy Northwind Traders Vegetable Soup 1,00 Zupy Northwind Traders Chicken Soup 1,00 Zupy 2. Zostaáo ono natychmiast wykonane, a cena kaĪdej zupy wynosi 1. 3. Aby 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. 204 CzöĈè III i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE, 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 klient miaä wraĔenie, iĔ 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: 3 MoĪna to zrobiü, naciskając kombinacjĊ klawiszy Ctrl+N lub klikając przycisk New Query. 4 W niektórych serwerach bazodanowych transakcje wycofuje siĊ instrukcjami ROLLBACK TRANSACTION lub ROLLBACK WORK. Rozdziaä 9. i Transakcje i wspóäbieĔnoĈè 205 SELECT [Nazwa produktu], [Cena katalogowa], Kategoria FROM dbo.Produkty WHERE Kategoria IN ( Zupy , Sosy ) ORDER BY Kategoria; ------------------------------------------------------------ Northwind Traders Hot Pepper Sauce 21,05 Sosy Northwind Traders Tomato Sauce 17,00 Sosy Northwind Traders Curry Sauce 30,00 Sosy Northwind Traders Clam Chowder 7,2375 Zupy Northwind Traders Vegetable Soup 1,4175 Zupy Northwind Traders Chicken Soup 1,4625 Zupy 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 taki 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 przy- káadowej bazie danych) 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 razie 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 (ato- mowej, niepodzielnej, trwaáej i spójnej) transakcji. Dziaáanie mechanizmu zagnieĪdĪania transakcji ilustruje poniĪszy przykáad: wykonanie instrukcji BEGIN TRAN powoduje zwiĊkszenie o jeden licznika otwartych transakcji, wykonanie instrukcji COMMIT TRAN zmniejsza wartoĞü tego licznika o jeden, ale wykona- nie instrukcji ROLLBACK TRAN zamyka transakcje i zeruje licznik otwartych transakcji: BEGIN TRAN; SELECT @@TRANCOUNT; 5 W niektórych serwerach bazodanowych transakcje zatwierdza siĊ instrukcjami COMMIT TRANSACTION lub COMMIT WORK. 206 CzöĈè III i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE, MERGE 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Ċ, lecz takĪe 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 dbo.Dostawcy(Firma) VALUES ( TEST1 ); SAVE TRAN PP1; INSERT INTO dbo.Dostawcy(Firma) VALUES ( TEST2 ); SELECT @@TRANCOUNT; ROLLBACK TRAN PP1; SELECT @@TRANCOUNT; ------------------------------------------------------------ 1 1 PoniewaĪ przywrócenie stanu transakcji do okreĞlonego punktu nie powoduje jej zakoĔ- czenia (liczba otwartych transakcji nadal wynosi 1), musimy ją zatwierdziü lub wycofaü: SELECT ID, Firma FROM dbo.Dostawcy WHERE Firma LIKE TEST_ ; ------------------------------------------------------------ 17 TEST1 Jako Īe 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 tylko 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. i Transakcje i wspóäbieĔnoĈè 207 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 potrzeby 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 są utrzymywane 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. 208 CzöĈè III i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE, 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 mniejsza liczba blokad, którymi musi zarzñdzaè serwer bazodanowy (zostanie zaäoĔona jedna blokada 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ü blokad wymaganych do ukoĔ- czenia juĪ rozpoczĊtych operacji. 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 priorytecie, 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 dbo.Dostawcy: BEGIN TRAN; UPDATE dbo.Dostawcy SET Firma = UPPER(Firma) WHERE ID 5;; ------------------------------------------------------------ (4 row(s) affected) NastĊpnie inny uĪytkownik w ramach jawnie rozpoczĊtej przez siebie transakcji mody- fikuje znacznie wiĊcej danych w tabeli Transakcje magazynowe7: BEGIN TRAN; UPDATE dbo.[Transakcje magazynowe] SET Iloħè += 1 WHERE [ID transakcji] 135; ------------------------------------------------------------ (100 row(s) affected) 7 MoĪemy zasymulowaü jednoczesną pracĊ dwóch uĪytkowników, otwierając nowe okno edytora SQL — kaĪde z okien nawiązuje wáasną sesjĊ z bazą danych. Rozdziaä 9. i Transakcje i wspóäbieĔnoĈè 209 W dalszej kolejnoĞci pierwszy uĪytkownik próbuje odczytaü zawartoĞü tabeli zablo- kowanej juĪ przez drugą sesjĊ (okno wyników moĪe pokazaü kilka wierszy, ale i tak uĪytkownik bĊdzie musiaá czekaü na moĪliwoĞü zablokowania w trybie S pozostaáych wierszy tabeli Transakcje magazynowe): SELECT * FROM dbo.[Transakcje magazynowe]; W tym momencie nie wystąpiáo jeszcze zakleszczenie — wystarczyáoby, Īeby drugi uĪytkownik zakoĔczyá swoją transakcjĊ. Ale jeĪeli w ramach drugiej sesji uĪytkownik spróbuje odczytaü zawartoĞü tabeli zmodyfikowanej przez pierwszego uĪytkownika, oba procesy siĊ zakleszczą: SELECT ID, Firma, [Tytuđ zawodowy] FROM dbo.Dostawcy; ------------------------------------------------------------ 1 Dostawca A Kierownik ds. sprzedaľy 2 Dostawca B Kierownik ds. sprzedaľy 3 Dostawca C Przedstawiciel handlowy 4 Dostawca D Kierownik ds. marketingu 5 Dostawca E Kierownik ds. sprzedaľy … Po chwili drugie zapytanie zostaáo jednak wykonane, co wiĊcej — nazwy firm nie zostaáy przekonwertowane na wielkie litery. ĩeby przekonaü siĊ, dlaczego tak siĊ staáo, wystarczy przeáączyü siĊ do okienka pierwszej sesji. Znajdziemy w nim poniĪszy komunikat báĊdu: Transaction (Process ID 52) 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 pierwszej sesji transakcji, okaĪe siĊ, Īe jawnie rozpoczĊta przez pierwszego uĪytkownika transakcja zostaáa — zgodnie z komu- nikatem 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Ċ dbo.Dostawcy. Liczba transakcji otwartych w ramach drugiej 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, zmienia- jąc poziom izolowania transakcji. WiĊkszoĞü serwerów pozwala ustawiü (na poziomie serwera, bazy danych lub poszczególnych sesji) jeden z czterech poziomów izolowa- nia transakcji, przedstawionych przez nas od najmniej restrykcyjnego, w którym mak- symalna wspóábieĪnoĞü jest okupiona wystĊpowaniem najwiĊkszej liczby typów niespój- noĞci danych, do najbardziej restrykcyjnego, który kosztem ograniczenia wspóábieĪnoĞci gwarantuje najwyĪszy poziom spójnoĞci danych. 210 CzöĈè III i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE, MERGE Read Uncommitted W trybie niezatwierdzonego odczytu (ang. Read Uncommitted) odczyt danych nie powoduje zaáoĪenia blokady wspóádzielonej. Na tym poziomie wystĊpują brudne odczyty, 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 adres klienta: BEGIN TRAN; UPDATE dbo.Klienci SET Adres = ZmianaWToku WHERE ID=1; ------------------------------------------------------------ (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; GO SELECT Adres FROM dbo.Klienci WHERE ID=1; ------------------------------------------------------------ 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Ğl- nym 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. Zjawisko niepowtarzalnego odczytu pokazuje poniĪszy przykáad: 1. W pierwszym oknie edytora SQL ustawiamy tryb odczytów zatwierdzonych8, jawnie rozpoczynamy transakcjĊ i odczytujemy adres wybranego klienta: 8 PoniewaĪ ten tryb jest trybem domyĞlnym, instrukcja SET jest dodana tylko w celach demonstracyjnych. Rozdziaä 9. i Transakcje i wspóäbieĔnoĈè 211 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN TRAN; SELECT Adres FROM dbo.Klienci WHERE ID=1; ------------------------------------------------------------ ul. Jasna 123 2. W tym momencie transakcja jest nadal otwarta, a my w drugim oknie edytora SQL zmienimy adres tego klienta: UPDATE dbo.Klienci SET Adres = OdczytWToku WHERE ID=1; ------------------------------------------------------------ (1 row(s) affected) 3. JeĪeli pierwszy uĪytkownik w ramach tej samej transakcji ponownie odczyta adres tego klienta, uzyska inny wynik: SELECT Adres FROM dbo.Klienci WHERE ID=1; COMMIT TRAN; ------------------------------------------------------------ OdczytWToku Repeatable Read W trybie powtarzalnego odczytu (ang. Repeatable Read) blokady wspóádzielone typu S są utrzymywane 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. Z niekorzystnych zjawisk związanych z izolowaniem transakcji na tym pozio- mie 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 kodach koĔczących siĊ cyfrą 6: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN TRAN; SELECT [Nazwa produktu] FROM dbo.Produkty WHERE [Kod produktu] LIKE 6 ; ------------------------------------------------------------ Northwind Traders Boysenberry Spread Northwind Traders Marmalade Northwind Traders Gnocchi Northwind Traders Tomato Sauce Northwind Traders Cake Mix Northwind Traders Smoked Salmon 2. Podczas gdy pierwsza transakcja jest wciąĪ otwarta, w drugim oknie edytora SQL zmienimy kod jednego z pozostaáych, niezwróconych przez pierwsze zapytanie produktu na NWTCO-6, a wiĊc na kod speániający warunki pierwszego zapytania: 212 CzöĈè III i Modyfikowanie danych, czyli instrukcje INSERT, UPDATE, DELETE, MERGE UPDATE dbo.Produkty SET [Kod produktu] = NWTCO-6 WHERE [Kod produktu] = NWTCO-3 ; ------------------------------------------------------------ (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á wiĊcej wierszy — pojawi siĊ w nim wiersz widmo: SELECT [Nazwa produktu] FROM dbo.Produkty WHERE [Kod produktu] LIKE 6 ; ------------------------------------------------------------ Northwind Traders Syrup Northwind Traders Boysenberry Spread Northwind Traders Marmalade Northwind Traders Gnocchi Northwind Traders Tomato Sauce Northwind Traders Cake Mix Northwind Traders Smoked Salmon 4. JeĞli 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 dbo.Produkty SET [Kod produktu] = NWTCO-1 WHERE [Kod produktu] = NWTJP-6 ; 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 są odczytywane 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 są wykonywane jedna po drugiej. Blokowanie caáych obiektów, a nie tylko odczytywanych danych, na czas trwania transakcji pozwala wyeliminowaü odczyty widma, ale powoduje, Īe odczy- tują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: SELECT [Nazwa produktu] FROM dbo.Produkty Rozdziaä 9. i Transakcje i wspóäbieĔnoĈè 213 WHERE [Kod produktu] LIKE 6 ; ------------------------------------------------------------ Northwind Traders Syrup Northwind Traders Gnocchi Northwind Traders Tomato Sauce Northwind Traders Cake Mix Northwind Traders Smoked Salmon 2. JeĪeli teraz w drugim oknie edytora SQL spróbujemy zmieniü dane dowolnego, równieĪ niezwróconego przez pierwsze zapytanie produktu, okaĪe siĊ, Īe aktualizacja zostaáa zablokowana i bĊdzie wykonana dopiero po zakoĔczeniu pierwszej transakcji: UPDATE dbo.Produk
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Praktyczny kurs SQL. Wydanie II
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ą: