Systemowe aspekty języka SQL
Skoncentrujemy teraz uwagę na zagadnieniu, jak dopasować SQL do środowiska programistycznego. Żaden z tematów związanych z tym zagadnieniem, które poruszamy poniżej, nie narusza standardu SQL2. W podrozdziale 7.1 zobaczymy, że najczęściej używa się kodu SQL jako fragmentu innego programu napisanego w pewnym popularnym języku programowania, np. w C. SQL zawiera szereg mechanizmów, które umożliwiają przekazywanie wartości między relacjami a zmiennymi z języka programu zewnętrznego, czy mówiąc inaczej ,języka podstawowego".
W podrozdziale 72 zostanie wprowadzone pojęcie transakcji, czyli podstawowej jednostki wykonywanego zadania. Mimo że szereg działań można wykonywać równolegle, w wielu zastosowaniach baz danych, takich jak na przykład bankowość, wymaga się jednak, aby dzialania na danych tworzyły niepodzielne „atomy". SQL dostarcza struktury służące do specyfikowania transakcji, a systemy realizujące SQL zapewniają, że to co zostało zdefiniowane jako transakcja rzeczywiście wykona się jako działanie atomowe.
W podrozdziale 7.3 zostaną opisane pozostale elementy systemu SQL, takie jak na przykład mechanizmy wsparcia dla modelu obliczeniowego typu klient-serwer, a następnie w podrozdziale 7.4 sposoby autoryzowania dostępu do danych oraz metody jego nadzoru z poziomu systemu.
7.1. sQL w środowisku programistycznym
Dotychczas w przykladach używaliśmy języka SQL w sposób bezpośredni. Zakladaliśmy więc, że istnieje interpreter SQL, który umożliwia wprowadzenie oraz wykonanie poznawanych zapytań i poleceń. Jednakże rzadko się zdarza, aby praca przebiegala w takim trybie. W praktyce pewne instrukcje SQL stanowią fragmenty większego programu lub zbioru funkcji. Zatem bardziej realistyczne jest postrzeganie kodu SQL jako elementu więk
4I G 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
szego programu zapisanego w konwencjonalnym języku programowania, na przykład w C. W bieżącym rozdziale opiszemy, w jaki sposób można łączyć kod SQL ze zwykłymi programami.
Na rysunku 7.1 przedstawiono schemat typowego systemu programowania, który zawiera elementy SQL. Programista przygotowuje program w standardowej formie, ale z wstawionymi instrukcjami SQL, które nie należą do języka podstawowego. Gotowy program jest analizowany przez preprocesor, który powoduje zamianę kodu SQL na kod zapisany w języku podstawowym. Interpretacja SQL w najprostszym przypadku sprowadza się do wywołania funkcji, której argumentem jest tekst instrukcji SQL i która uruchamia tę instrukcję. Na schemacie pokazano, że zamiast osadzać kod SQL w programie programista może w swoim programie bezpośrednio wywołać funkcję, która uruchamia SQL.
Język podstawowy Osadzony SQL
Preprocesor Język podstawowy
Wywołania funkcji Kompilator
języka Biblioteka SQL podstawowego
Program języka podstawowego
RYSUNEK 7.1
Przetwarzanie programów z osadzonymi instrukcjami SQL
Po fazie preprocesowania program jest kompilowany w zwykly sposób. Dostawcy systemów baz danych na ogół dołączają do systemu biblioteki z definicjami niezbędnych funkcji. Potem funkcje, które implementują SQL, można wywoływać w programach zapisanych w innych językach; stanowią z nimi spójną cakość.
7.1. SQL W ŚRODOWISKU PROGRAMISTYCZNYM 4I 7
7.1.1. Problem niedopasowania falowego
Podstawowy problem przy łączeniu instrukcji SQL z konwencjonalnym językiem programowania można porównać z brakiem dopasowania falowego w fizyce, a wynika on z dużych różnic między modelami danych języka SQL i innych języków programowania. Jak wiemy SQL ma zaimplementowany relacyjny model danych. Natomiast C i inne języki konwencjonalne operują modelem typów strukturalnych z podstawowymi typami danych: całkowitym, rzeczywistym, znakowym itp. oraz strukturami typu macierz. Ani w języku C, ani w innych językach imperatywnych zbiory nie mają bezpośredniej reprezentacji, natomiast w języku SQL nie można w bezpośredni sposób korzystać ze wskaźników, macierzy ani innych typów strukturalnych, które powszechnie stosuje się w językach konwencjonalnych. W wyniku tego nie istnieje bezpośrednie przełożenie między strukturami SQL i innych języków, a więc trzeba tworzyć dodatkowe mechanizmy umożliwiające przepływ danych między fragmentami programu zapisanymi w języku SQL i w języku podstawowym.
Języki, z którymi standardowo łączy się SQL2
Standard SQL2 narzuca wymaganie, aby jego implementacje umożliwiały połączenie z następującymi językami programowania: ADA, C, Cobol, Fortran, M (poprzednio nazywany Mumps), Pascal oraz PL/I. Każdy student informatyki powinien znać te języki, być może z pominięciem M czy Mumpsa, którego stosowanie było ograniczone do środowisk medycznych. W naszych przykładach będziemy korzystać z języka C.
Na pozór mogłoby się wydawać, ze najlepiej używać jednego języka: albo wszystkie obliczenia zapisywać tylko w języku SQL, albo zaniechać jego stosowania i całość zapisać w innym języku programowania. Jednakże, gdy ma się do czynienia z działaniami na bazach danych, wówczas bardzo szybko odrzuca się pomysł zaniechania SQL. System SQL służy bowiem wielką pomocą, jeśli działania na bazie trzeba wykonywać w sposób efektywny, a przy tym nie trzeba rezygnować z programowania wysokopoziomowego. Programista zostaje odciążony od konieczności ręcznego organizowania danych w pamięci oraz od wymyślania optymalnych algorytmów przeszukiwania bazy danych.
Jednakże SQL nie zapewnia obsługi wielu innych ważnych zadań. Na przykład w języku SQL nie da się zażądać obliczenia silni liczby h (n! = n x x (n-1) x...x 2 x I), co jest jednym z najprostszych zadań programowania w języku C czy innym podobnym języku. Z poziomu SQL nie uda się także wyrazić zgrabnego formatu dla wyników, na przykład w postaci wykresu. A zatem w praktyce przy programowaniu baz danych trzeba korzystać jednocześnie zarówno z języka SQL, jak i konwencjonalnego języka, który będziemy nazywać językiem podstawowym.
41 S 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
7.1.2. Interfejs między językiem SQL i językiem podstawowym
Przesyłanie danych między bazą danych, dostępną tylko z poziomu języka SQL, a programami podstawowymi odbywa się za pośrednictwem zmiennych z języka podstawowego, których wartości są odczytywane lub określane przez instrukcje systemu SQL. Zmienne takie określa się jako dzielone. W instrukcjach SQL są one wyróżniane przez poprzedzenie nazwy dwukropkiem, w języku podstawowym ich nazwa nie jest natomiast odróżniana od innych nazw zmiennych.
Kod SQL, który ma być osadzony w programie zapisanym w języku podstawowym, poprzedza się słowem kluczowym EXEC SQL. Instrukcje, które następują po tym słowie, są zamieniane przez preprocesor na stosowne wywołania w języku podstawowym funkcji dostępnych w bibliotece SQL.
W standardzie SQL2 występuje specjalna zmienna SQLSTATE, która służy do łączenia programów zapisanych w języku podstawowym z systemem przetwarzania SQL". Zmienna SQLSTATE jest tablicą złożoną z pięciu znaków. Za każdym razem, gdy wywołuje się jakąś funkcję biblioteczną SQL, do zmiennej sQLSTATE zapisuje się kod oznaczający, czy i ewentualnie jakie problemy napotkano przy wykonywaniu instrukcji SQL. Na przykład kod `00000' (pięć zer) oznacza, że instrukcja SQL została przetworzona bez błędów, a z kolei kod `20000' oznacza, że w wyniku przetwarzania zapytania SQL nie odnaleziono wskazanej w nim krotki. W języku podstawowym można odczytywać wartość zmiennej SQLSTATE i dzięki temu można uzależnić od jej wartości dalsze przetwarzanie.
7.1.3. Sekcja DECLARE
Deklaracje zmiennych dzielonych wyróżnia się w programie podstawowym przez poprzedzenie jej i zakończenie następującymi instrukcjami SQL:
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
Fragment kodu między tymi dwiema instrukcjami nazywa się sekcja deklaracji. Postać deklaracji w tej sekcji jest zgodna z konwencją deklaracji w języku podstawowym. Ponadto nie ma sensu deklarować zmiennych innyćh typów niż typy wspólne dla języka podstawowego i SQL, czyli innych niż na przykład real, integer lub character.
' Może się zdarzyć, że systemy, które nie są zgodne ze standardem SQL2, nie zawierają zmiennej SQLATATE, ale należy oczekiwać. iż występuje w nich jakaś inna zmienna odgrywająca taką samą rolę.
7.1. SQL W ŚRODOWISKU PROGRAMISTYCZNYM 419
PRZYKŁAD 7.1
W funkcji zapisanej w języku C, która służy do modyfikowania relacji studio, może na przykład wystąpić następująca instrukcja:
EXEC SQL BEGIN DECLARE SECTION;
char nazwastudia[15], adresstudia[50]; char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
Instrukcje pierwsza oraz ostatnia są wymaganymi nawiasami sekcji deklaracji. Wewnątrz sekcji występuje deklaracja dwóch zmiennych: nazwastudia i adresstudia. Obie sązmiennymi znakowymi i, jak przekonamy się później, służą do przechowywania nazwy oraz adresu studia, które połączone razem tworzą krotkę wstawianą do relacji Studio. W trzeciej instrukcji została zdefiniowana zmienna sQLSTATE jako tablica o długości sześciu znaków*.
0
7.1.4. Stosowanie zmiennych dzielonych
W instrukcjach SQL zmienne dzielone występują w kontekstach przeznaczonych dla konkretnych wartości. Przypomnijmy tu, że nazwy tych zmiennych w instrukcjach SQL są poprzedzane dwukropkiem. Poniżej przedstawiamy przykład zastosowania zmiennych dzielonych zdefiniowanych w przykładzie 7.1 w instrukcji wstawiania nowej krotki do relacji studio.
PRZYKŁAD 7.2
Na rysunku 7.2 został przedstawiony szkielet funkcji podaj studio, która służy do pobrania od użytkownika wartości atrybutów określających nazwę i adres studia filmowego oraz wstawienia nowej krotki do relacji studio. W wierszach od 1) do 4) umieszczono deklaracje analizowane w przykładzie 7.1. Kod w języku C, który służy do interakcyjnego pobrania wartości do tab11C nazwaStudia 1 adresStudia, zostak pominięty.
W wierszach 5) i 6) zawarto osadzony w programie C kod instrukcji SQL służącej do wstawienia nowej krotki. Ta instrukcja INSERT została poprzedzona słowem kluczowym ExEC SQL, po to by było wiadomo, że jest ona osadzonym kodem języka SQL, a nie jest niepoprawnym kodem w języku C. Dzięki temu ten fragment zostanie dostrzeżony przez preprocesor, który pokazano na rys. 7.1, i przetworzony na właściwąsekwencję instrukcji.
Pięcioznakowa wartość zmiennej SQLSTATE musi być przechowywana jako napis o długości sześciu znaków, ponieważ w programie przykładowym korzystamy z funkcji strcmp przy porównywaniu napisów. Funkcja strcmp natomiast korzysta ze standardowej konwencji kończenia napisu znakiem `\0', a więc i wartość zmiennej SQLSTATE musi być w taki sposób zakończona. Wartość tego szóstego znaku trzeba początkowo ustawićjako `\0', ale to przypisanie nie wystąpi jawnie w naszym przykładzie.
420
7. SYSTEMOWE ASPEKTY JĘZYKA SQL
void podajStudio(
1) EXEC SQL BEGIN DECLARE SECTION;
2) char nazwaStudia[15], adresStudia[50]; 3) char SQLSTATE[6];
4) EXEC SQL END DECLARE SECTION;
/* interakcyjne określenie nazwy i adresu studia i określenie wartości zmiennych nazwaStudia oraz adresStudia */
5) EXEC SQL INSERT INTO Studio (nazwa, adres) 6) VALUES(:nazwaStudia, :adresStudia);
RYSUNEK 7.2
Zastosowanie zmiennych dzielonych do wstawienia nowego studia
Wartości wstawiane do relacji w wierszach S) i 6) nie są dane w sposób jawny jako stale, do czego przyzwyczailiśmy się w poprzednich przykładach, np. w przykładzie 5.27. Tym razem są to wartości zapamiętane w zmiennych dzielonych i staną się one składowymi wstawianej krotki.
0
W języku podstawowym można osadzać także inne niż INSEKT instrukcje SQL i korzystać w nich ze zmiennych dzielonych przy przekazywaniu wartości. W programie podstawowym każdy osadzony fragment w języku SQL musi być poprzedzony frazą EXEC sQL, a można w nim korzystać ze zmiennych dzielonych tak, jakby były to wartości stałe. Można osadzać wszystkie te instrukcje SQL, które nie tworzą żadnych wartości (tzn. nie są zapytaniami). Przykłady kodu osadzonego zawierają zatem instrukcje SQL usuwania i modyfikowania krotek oraz tworzenia, modyfikowania lub usuwania elementów schematu, takich jak tabele czy też perspektywy.
Jednakże zapytania select-from-where nie mogą być osadzane. Wynika to z niedopasowania falowego. Zapytania tworzą bowiem zbiory krotek, a taka struktura danych nie jest dopuszczalna w typowych językach programowania. W języku SQL muszą zatem istnieć jeszcze inne mechanizmy, które umożliwiają przekazanie wyniku zapytania do programu podstawowego. Przedstawiamy dwa z nich:
1. Jeżeli w wyniku zapytania powstaje dokładnie jedna krotka, to można ją przekazywać poprzez zmienne dzielone, których musi być tyle samo co składowych krotki. Korzysta się wówczas ze specjalnej postaci instrukcji SELECT o nazwie single-~ow select (czyli wybór jednego wiersza).
7.1. SQL W ŚRODOWISKU PROGRAMISTYCZNYM
421
2. Można przetwarzać również inne zapytania, w wyniku których powstaje wiele krotek. Trzeba wówczas zadeklarować kursor.
Omówimy bardziej szczegółowo te dwa mechanizmy.
7.1.5. Insirukcj e wyboru pojedynczych wierszy
Postać instrukcji powodującej wybór pojedynczego wiersza prawie się nie różni od zwykłej instrukcji typu select-from-where, z wyjątkiem tego, że pojawia się za klauzulą SELECT słowo kluczowe INTO oraz lista zmiennych dzielonych przeznaczonych do przekazania wartości składowych. Tak jak przy stosowaniu zmiennych dzielonych w instrukcjach SQL, i w tym kontekście ich nazwy należy poprzedzać dwukropkiem. Jeżeli w wyniku zapytania powstaje pojedyncza krotka, to zmiennym dzielonym przypisuje się wartości składowych. Jeśli natomiast nie zostanie wyszukana żadna krotka albo zostanie wybrane więcej niż jedna krotka, to zmienne dzielone nie otrzymają nowych wartości, a informacja o trybie wykonania instrukcji zostanie przekazana dzięki określeniu właściwego kodu zmiennej sQLSTATE.
void drukCenySieci() {
1) EXEC SQL BEGIN DECLARE SECTION; 2) char nazwaStudia[15];
3) int cenaSieciPrezesa; 4) char SQLSTATE[6];
5) EXEC SQL END DECLARE SECTION;
/* interakcyjne określenie nazwy studia i określenie wartości zmiennej nazwaStudia */
6) EXEC SQL SELECT cenaSieci 7) INTO : cenaSieciPrezesa 8) EROM Studio, Filmdyr
9) WHERE prezC# = cert# AND
Studio.nazwa = : nazwaStudia;
/* Jeśli w zmiennej SQLSTATE wszystkie elementy są zerami, to należy wydrukować wartość zmiennej cenaSieciPrezesa */
RYSUNEK 7.3
Wybór pojedynczego wiersza osadzony w funkcji C
422 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
PRZYKŁAD 7.3
Przedstawimy tu funkcję C, która służy do określenia wartości sieci prezesa na podstawie podanej nazwy studia. Szkic tej funkcji został przedstawiony na rys. 7.3. Zaczyna się ona od sekcji deklaracji, w której w wierszach od 1) do 5) zapisano definicję potrzebnych zmiennych. Potem następują pominięte na rysunku instrukcje C, które powodują wczytanie wartości nazwy studia z wejścia standardowego. W wierszach od 6) do 9) występuje instrukcja wyboru pojedynczego wiersza. Jest ona bardzo podobna do instrukcji, którą już znamy. Pierwsza różnica polega na tym, że zamiast stalej w warunku występującym w wierszu 9) użyto zmiennej nazwastudia, a druga na tym, że w wierszu 7) pojawia się klauzula mTO, dzięki której wiadomo, co należy zrobić z wynikiem przetwarzania. W tym przypadku oczekujemy, że w wyniku powstanie dokładnie jedna krotka, która ma tylko jedną składową, odpowiadającą atrybutowi cenasieci. Wartość tej jednej składowej jednej krotki zostaje zapamiętana w całkowitej zmiennej dzielonej cenasieciPrezesa.
0
7.1.6. Kursory
Elegantszy sposób przekazywania wartości między językiem SQL a językiem podstawowym polega na zastosowaniu kursora, który pobiera wartości z kolejnych krotek relacji. Relacja może być albo pamiętaną tabelą, albo wynikiem zapytania. Do utworzenia i wykonania kursora trzeba użyć następujących elementów:
1. Deklaracja kursora. Najprostsza postać deklaracji kursora składa się z następujących elementów:
a) Wstępny ExEC sQL, tak jak w każdym przypadku osadzania kodu SQL.
b) Slowo kluczowe DECLARE. c) Nazwa kursora.
d) SIOWO kIUCZOWe CURSOR FOR.
e) Wyrażenie, którego wynikiem jest relacja, a zatem np. nazwa tabeli lub wyrażenie typu select-from-where. Zakres kursora stanowią krotki tej relacji, co oznacza, że kursor wskazuje po kolei krotki relacji wówczas, gdy zostaje uruchomiony instrukcją FETCH.
Postać deklaracji kursora można przedstawić w następujący sposób:
EXEC SQL DECLARE <kursor> CURSOR FOR <zapytanie>
2. Instrukcja EXEC sQL oPEN, po której umieszcza się nazwę kursora. Powoduje ona przygotowanie kursora do odczytu pierwszej krotki z właściwego mu zakresu.
7.1. SQL W ŚRODOWISKU PROGRAMISTYCZNYM
423
3. Jedno (lub więcej) użycie instrukcji FETCH. Celem instrukcji FETCH jest przesunięcie kursora do kolejnej krotki jego zakresu. Po osiągnięciu końca zakresu żadna krotka nie jest zwracana, natomiast zmiennej sQLSTATE zostaje przypisana wartość ' 02000', co oznacza: „nie odnaleziono żadnej krotki". Do instrukcji FETCH wchodzą następujące elementy:
a) Słowo kluczowe EXEC SQL FETCH EROM. b) Nazwa kursora.
c) Słowo kluczowe INTO.
d) Lista zmiennych dzielonych, oddzielanych przecinkami. Jeśli w wyniku dzialania instrukcji FETCH zostanie wybrana jakaś krotka, to wymienionym na liście kolejnym zmiennym dzielonym zostają przypisane wartości kolejnych skladowych tej krotki.
Postać deklaracji instrukcji FETCH można przedstawić w następujący sposób:
EXEC SQL FETCH EROM <kursor> CURSOR INTO <lista
zmiennych dzielonych>
4. Instrukcja EXEC SQL CLOSE, po której umieszcza się nazwę kursora. Powoduje ona zamknięcie kursora, który przestaje być związany z dotychczasowyh zakresem. Oczywiście można go potem jeszcze raz zainicjować inną instrukcją oPEN i wówczas można ponownie mieć dostęp do kolejnych krotek relacji.
PRZYKŁAD 7.4
Przypuśćmy, że trzeba określić ilu dyrektorów produkcji posiada sieć o wartości mieszczącej się w kolejnych zakresach liczbowych, przy czym zakresy wynikają z liczby cyfr wartości sieci. W tym celu powstanie zapytanie, które do zmiennej cena zapisuje wartość atrybutu cenasieci, pochodzącą z krotek relacji FilmDyr. Wynikowa relacja o jednym atrybucie stanowi zakres dla kursora dyrKursor. Po pobraniu krotki oblicza się liczbę cyfr zmiennej całkowitej cena oraz zwiększa się odpowiedni element tabeli liczniki.
Funkcja zakresyCen została zapisana w języku C i przedstawiona na rys. 7.4. W wierszu 1) jest jej naglówek, w wierszu 2) zapisano deklaracje tych zmiennych, które są dostępne tylko z poziomu języka C, a nie są dostępne z poziomu SQL. W tablicy liczniki przechowuje się zliczane wartości wynikowe liczby dyrektorów, których sieci mają wartość mieszczącą się w danym zakresie. Zmienna cyfry struży do przechowania wyliczonej na bieżąco liczby cyfr wartości sieci, a zmienna i określa wartość indeksu bieżącego elementu tablicy liczniki.
W wierszach od 3) do 6) zawarto sekcję DECLARE, w której zostały zdefiniowane zmienne dzielone cena oraz zmienna standardowa sQLSTATE. Z kolei w wierszach 7) i 8) umieszczono deklarację kursora dyrKursor,
424 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
którego zakresem ma być wynik przetworzenia zapytania zdefiniowanego w wierszu 8), a które służy do wyboru wartości sieci wszystkich dyrektorów. Ten kursor zostaje uruchomiony w wierszu 9). W wierszu 10) kończy się faza inicjowania przez wyzerowanie elementów tablicy liczniki.
1) void zakresyWartości() {
2) int i, cyfry, liczniki[15];
3) EXEC SQL BEGIN DECLARE SECTION; 4) int cena;
5) char SQLSTATE[6];
6) EXEC SQL END DECLARE SECTION;
7) EXEC SQL DECLARE dyrKursor CURSOR FOR 8) SELECT cenaSieci EROM FilmDyr;
9) EXEC SQL OPEN dyrKursor;
10) for (i = 0; i < 15; i++) liczniki[i] = 0; 11) while(1) {
12) EXEC SQL FETCH EROM dyrKursor INTO :cena; 13) if (BRAK_NOWYCH_KROTEK) break;
14) cyfry = 1;
15) while ((cena /= 10) > 0) cyfry++; 16) if( cyfry <= 14) liczniki[cyfry]++; 17) EXEC SQL CLOSE dyrKursor;
18) for (i = 0; i < 15; i++)
19) printf(„cyfry = %d: liczba dyrektorów = %d\n", i, liczniki[i]);
RYSUNEK 7.4
Grupowanie wartości sieci dyrektorów według rzędów wielkości
Główne zadania są wykonywane za pomocą pętli zapisanej w wierszach od 11) do 16). W wierszu 12) krotka bieżąca jest przechwytywana do zmiennej dzielonej cena. Ponieważ krotki powstające w wyniku przetworzenia zapytania z wiersza 8) mają tylko po jednej składowej, więc jest potrzebna tylko jedna zmienna dzielona w myśl zasady, że trzeba stosować tyle zmiennych dzielonych, z ilu składowych jest złożona przechwycona krotka. W wierszu 13) zachodzi sprawdzenie, czy przechwycenie krotki powiodło się. Korzystamy w tym celu z makra o nazwie BRAK_NOWYCH_KROTEK, które zapewne jest zdefiniowane w następujący sposób:
?.l. SQL W ŚRODOWISKU PROGRAMISTYCZNYM 42S
#define BRAK_NOWYCH_KROTEK !(strcmp(SQLSTATE, „02000"))
Przypomnijmy tu, że kod „02000" zmiennej sQLSTATE oznacza, że nie wyszukano żadnej krotki. A zatem w wierszu 13) następuje sprawdzenie, czy już wszystkie krotki zostały zanalizowane i czy w związku z tym nie ma już żadnej następnej krotki do przetworzenia. Jeśli tak jest, to pętla zostaje przerwana i sterowanie zostaje przekazane do wiersza 17).
Jeśli natomiast pewna krotka zostanie sprowadzona do programu podstawowego, to w wierszu 14) liczba cyfr wartości ceny sieci zostaje zainicjowana na 1. W wierszu 1 S) zapisano instrukcje iteracyjnego dzielenia wartości sieci przez 10 i dodawania 1 do zmiennej cyfry. W chwili gdy cena sieci osiągnie wartość 0, po kolejnym podzieleniu przez 10, w zmiennej cyfry znajduje się liczba określająca liczbę cyfr wartości zmiennej cena. Zakłada się, że długość liczb całkowitych nie przekracza 14 cyfr. Gdyby jednak zdarzyło się, że cena sieci ma więcej niż 14 cyfr, to i wartość odpowiedniego elementu tablicy liczniki nie zostanie zwiększona, co zapewnia stosowny warunek, a więc nadspodziewanie wysokie wartości sieci nie są uwzględniane w statystyce.
W wierszu 17) zostało zapisane zakończenie fragmentu obliczeniowego funkcji. Kursor zostaje zamknięty, a instrukcje, zapisane w wierszach 18) i 19), służą do wydrukowania wartości z tablicy liczniki.
0
7.1.7. Dokonywanie modyfikacji za pomocą kursorów
Po przypisaniu kursora do pewnego zakresu krotek w tabeli podstawowej (tzn. do relacji zapamiętanej w bazie danych, a nie do perspektywy lub wyniku zapytania) można nie tylko odczytywać i przetwarzać wartości zapamiętane w tej relacji, ale można także zmieniać lub usuwać knotki. Składnia instrukcji UPDATE oraz DELETE jest prawie taka, jaką poznaliśmy w podrozdziale 5.6, inaczej zapisuje się tylko klauzulę WHERE. Jest ona w tym przypadku złożona ze słowa kluczowego WHERE cuRRENT oF, po którym podaje się nazwę kursora. Oczywiście można z poziomu języka podstawowego zdecydować, czy analizowana knotka ma być zmieniona lub usunięta, lub też nie.
PRZYKŁAD 7.5
Na rysunku 7.S umieszczono zapis definicji funkcji zbliżonej do tej, którą przedstawiono na rys. 7.4. W obu został zadeklarowany kursor dyrKursor, który służy do odczytu knotek z relacji FilmDyr. Na rysunku 7.S służy on jednak do rozstrzygnięcia, czy analizowana knotka ma zostać usunięta z relacji, czy też należy w niej podwoić cenę sieci.
426
1) void zmianaCeny
7. SYSTEMOWE ASPEKTY JĘZYKA SQL
2) EXEC SQL BEGIN DECLARE SECTION; 3) int cena;
4) char SQLSTATE[6];
5) EXEC SQL END DECLARE SECTION
6) EXEC SQL DECLARE dyrKursor CURSOR FOR 7) SELECT cenaSieci EROM FilmDyr;
8) EXEC SQL OPEN dyrKursor;
9) while(1) {
10) EXEC SQL FETCH EROM dyrKursor INTO: cena; 11) if (BRAK_NOWYCH_KROTEK)break;
12) if (cena < 1000)
13) EXEC SQL DELETE EROM FilmDyr
WHERE CURRENT OF dyrKursor;
14) else
15) EXEC SQL UPDATE FilmDyr
SET cenaSieci = 2* cenaSieci WHERE CURRENT OF dyrKursor;
16) EXEC SQL CLOSE dyrKursor;
RYSUNEK 7.~
Zmiana ceny sieci dyrektora
Także tutaj skorzystano z makra BRAK NOWYCH KROTEK, które, na podstawie kodu zmiennej sQLSTATE „02000", rozstrzyga, czy należy analizować jeszcze jakieś krotki. Z kolei w wierszu 12) jest sprawdzany warunek, czy wartość sieci wynosi poniżej 1000 $. Jeśli tak, to taka krotka zostaje usunięta przez przetworzenie instrukcji DELETE zapisanej w wierszu 13). Natomiast jeśli cena sieci przekracza 1000 $, to zwiększa się ona dwukrotnie w wyniku wykonania instrukcji z wiersza 15).
0
7.1. 8. Opcje kursora
Stosując kursory można korzystać z wielu opcji oferowanych w języku SQL2. Poniżej przedstawiamy ich krótką charakterystykę. Szczegółowo zostaka ona opisana w p. od 7.1.9 do 7. I .11.
7.1. SQL W ŚRODOWISKU PROGRAMISTYCZNYM
427
1. Można określić kolejność wczytywania krotek z relacji do programu. 2. Można ograniczyć zmiany w relacji w zakresie objętym kursorem.
3. Można sterować kolejnością krotek na liście tworzonej przez kursor.
7.1.9. Określanie kolejności wczytywania krotek
Na początku rozważmy kolejność krotek. Krotki można wprowadzać do przetwarzania ułożone zgodnie z porządkiem zadanym dla wartości określonej składowej. Określenie takiego porządku następuje w definicji relacji będącej zakresem kursora, do której dopisuje się słowo kluczowe ORDER BY, a następnie listę składowych porządkujących krotki, zgodnie z zasadami określonymi dla zapytań w p. 5.1.5. Sortowanie odbywa się hierarchicznie według priorytetu od lewej strony, tzn. porządkuje się krotki według wartości pierwszej składowej, a jeśli tam wartości powtarzają się, to o ich porządku decydują wartości składowych wymienionych później. Składowe można specyfikować albo przez nazwę atrybutu, albo przez numer. W tym drugim przypadku numer pokrywa się z numerem pozycji atrybutu na liście atrybutów w definicji relacji.
PRZYKŁAD 7.6
Załóżmy, że analizuje się krotki powstałe w wyniku złączenia relacji Film i Gwiazdyw rzutowanego na atrybuty określające tytuł filmu, rok jego produkcji, gwiazdę oraz studio. Krotki mają być uporządkowane według roku produkcji, a krotki z tego samego roku mają być pogrupowane według tytułów w porządku alfabetycznym. Na rysunku 7.6 umieszczono deklarację kursora gwiazdaKursor, którego zakresem jest relacja opisana powyżej.
1) EXEC SQL DECLARE GwiazdaFilmowaKursor CURSOR FOR 2) SELECT tytuł, rok, studio, nazwiskoGwiazdy 3) EROM Film, GwiazdyW
4) WHERE tytuł = tytułfilmu AND rok = rokFilmu 5) ORDER BY rok, tytuł;
RYSUNEK 7.6
Zastosowanie klauzuli ORDER BY do ustalania kolejności pobieranych krotek
W wierszach od 2) do 4) jest zapisana klauzula SELECT, a wiersz 1) służy do zadeklarowania kursora, dla którego zakresem jest wynik przetwarzania tej klauzuli. Z instrukcji zapisanej w wierszu 5) wynika, że krotki udostępniane przez kursor gwiazdaKursor są uporządkowane według lat produkcji w kolejności od wyprodukowanego najwcześniej. Knotki o tym samym roku są uporządkowane według atrybutu tytuł. Porządek w ramach roku jest zadany alfabetycznie, ponieważ właśnie tak porządkuje się teksty. Nie przesądza się porządku knotek dotyczących różnych gwiazd tego samego filmu.
0
428 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
7.1.10. Ochrona przed jednoczesnymi modyfikacjami
Teraz z kolei zajmijmy się takim przypadkiem, gdy pewna funkcja korzysta z kursora gwiazdaKursor, zdefiniowanego w przykładzie 7.6, a jednocześnie inne funkcje (albo nawet ta sama funkcja) modyfikują którąś z relacji: Film lub Gwiazdyw. W podrozdziale 7.2 przedstawimy bardziej wyczerpująco problematykę równoległego dostępu do zasobów. Teraz jednak skupimy uwagę na przypadku, w którym inne procesy usiłują modyfikować relację używaną przez nasz program.
Co wówczas zrobić? Być może nic. Może się zdarzyć bowiem, że chcemy tylko śledzić dane dotyczące filmów lub gwiazd filmowych i fakt, że jakaś krotka została właśnie wstawiona albo usunięta nie ma większego znaczenia. Jeśli tak, to po prostu przeglądamy dane dostępne za pośrednictwem kursora.
Jednakże może się zdarzyć i tak, że nie chcemy, aby przeglądane krotki ulegały modyfikacjom., Na przykład, jeśli wartości przeglądane przez kursor mająprzesądzić o wstawieniu (lub nie) nowej krotki do relacji Gwiazdyw, to przetwarzanie może się zapętlić, gdy nowa krotka wygeneruje nowe krotki wstawiane za pośrednictwem kursora, który z kolei wygeneruje kolejne krotki itd. Jeśli istnieje ryzyko tego typu lub ryzyko wystąpienia innych niepożądanych zachowań, to można określić kursor jako niewrażliwy na równoczesne modyfikacje.
PRZYKł,AD 7.7
Wiersz 1) z rys. 7.6 zostaje zmieniony w następujący sposób:
1) EXEC SQL DECLARE gwiazdaFilmowaKursor INSENSITIVE
CURSOR FOR
W takun przypadku system SQL zagwarantuje, że modyfikacje relacji Film oraz Gwiazdyw, przetwarzane między otwarciem a zamknięciem kursora gwiazdaFilmowaKursor nie będą wprowadzane do zakresu dostępnego przez ten kursor.
0
Korzystanie z kursora niewrażliwego może się okazać kosztowne, ponieważ system SQL może potrzebować wiele czasu na zorganizowanie wlaściwego dostępu do danych. Szczególowe omówienie tego zagadnienia nastąpi w podrozdziale 7.2. Teraz napomkniemy jedynie, że można zwyczajnie na czas czytania relacji Film oraz Gwiazdyw przez kursor niewrażliwy zabronić innym procesom dostępu do zawartych w nich danych.
O niektórych kursorach wiadomo na pewno, że nie powodują modyfikacji w relacji R stanowiącej ich zakres. Takie kursory mogą działać jednocześnie z kursorem niewrażliwym dla R, bez napotkania ryzyka niekorzystnych
7.1. SQL W ŚRODOWISKU PROGRAMISTYCZNYM X129
dla kursora niewrażliwego zmian R. Takie kursory można zdefiniować jako FOR READ oNLY i w ten sposób jawnie powiadomić system bazy danych, że dostęp za pośrednictwem tych kursorów nie spowoduje zmian w danych.
PRZYKŁAD 7.8
Po wierszu 5) na rys. 7.6 można dopisać następującą frazę:
6) FOR READ ONLY;
Wówczas każda próba wykonania instrukcji DELETE lub uPDATE za pośrednictwem kursora gwiazdaFilmowaKursor skończy się sygnalizacjąblędu.
a
7.1.11. Przewijanie kursora
Ostatnia opcja dotycząca kursora umożliwia określenie kolejności krotek dostępnych przez kursor. Standardowy porządek, który obowiązuje najczęściej, określa rozpoczęćle procesu przeg~ądaciia krotek od początku relacji, a ostatnią wczytywaną krotką jest ostatnia krotka relacji. Jednakże można zmienić ten naturalny porządek i wczytywać wielokrotnie te same krotki za pośrednictwem kursora otwartego tylko jeden raz. Korzystanie z tej opcji wymaga wykonania dwóch czynności.
1. W deklaracji kursora należy umieścić słowo kluczowe sCROLL bezpośrednio przed słowem kluczowym cURSOR. Powoduje to, że system
' SQL „wie", że krotki będą wczytywane w innej kolejności niż wynika to z porządku zapisania ich w relacji.
2. W instrukcji FETCH po słowie kluczowym FETCH należy umieścić określenie miejsca następnej krotki. Należy wybrać jedną z następującydr możliwości:
a) Aby pobrać krotkę następną po bieżącej lub poprzednią, należy wpisać odpowiednio NExT lub PRIOR. Są one liczone względem bieżącej pozycji kursora. Jeśli nie poda się nic, to standardowo zostanie wybrana następna krotka (NExT jest domyślne).
b) FIRST lub LAST określa wybór odpowiednio pierwszej lub ostatniej krotki z relacji.
c) RELATIVE, a potem liczba całkowita, dodatnia lub ujemna, która określa, ile krotek wprzód albo odpowiednio wstecz należy pominąć, aby pobrać nową krotkę. Na przykład RELATIVE 1 jest równoważne NEXT, a RELATIVE -1 jest równoważne PRIOR.
d) ABSOLUTE, a potem liczba całkowita, która wskazuje pozycję krotki, licząc od pierwszej (jeśli wartość jest dodatnia) lub od ostatniej wstecz (jeśli liczba jest ujemna). Na przykład ABSOLUTE 1 jest równoważne FIRST, a ABSOLUTE -1 jest równoważne LAST.
43 O 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
PRZYKŁAD 7.9
Funkcja z rys. 7.5 zostanie przepisana tak, aby krotki były przeglądane od ostatniej wprzód. Najpierw należy określić kursor kursorDyr jako przewijalny, co osiąga się przez dopisanie słowa kluczowego ScROLL w wierszu 6):
6) EXEC SQL DECLARE dyrKursor SCROLL CURSOR FOR 7) SELECT cenaSieci EROM FilmDyr;
Wczytywanie krotek trzeba zainicjować przez instrukcję FETCH LAST, a w pętli skorzystać z instrukcji FETCH PRIOR. A zatem instrukcje z wierszy od 9) do 15) z rys. 7.5 przyjmą następującą postać:
EXEC SQL FETCH LAST EROM dyrKursor INTO :cena; while(1) {
/* to samo co w wierszach 11) do 15) */
EXEC SQL FETCH PRIOR EROM dyrKursor INTO :cena;
Mylne jest przekonanie, że czytanie krotek w kolejności odwrotnej niż są one generowane przez zapytanie:
SELECT cenaSieci EROM FilmDyr
ma jakiekolwiek zalety. W rzeczywistości analizowanie ich w kolejności odwrotnej jest jeszcze kosztowniejsze, ponieważ zazwyczaj są one wygenerowane wszystkie jeszcze zanim zostaną po raz pierwszy udostępnione kursorowi dyrKursor.
O
7.1.12. Dynamiczny SQL
Prezentowany model łączenia SQL z innymi programami polega na osadzaniu zapytań i innych poleceń SQL w programach zapisanych w języku podstawowym. Istnieje jeszcze im~y tryb zanurzania SQL winnym języku. Instrukcje SQL mogą bowiem być przetwarzane bezpośrednio w języku podstawowym. Nie są one znane w czasie kompilacji ani nie są obsługiwane przez preprocesor, ani przez inny kompilator.
Takim przykladem może być program, który interakcyjnie pobiera zapytanie w języku SQL, czyta je, a następnie przetwarza. Można tu przypomnieć interpreter zapytań SQL, o którym była mowa w rozdziale 5. W każdym komercyjnym systemie SQL taki interpreter istnieje. Jeżeli zapytania są czytane i wykonywane w trakcie przetwarzania, to podczas kompilacji nic się nie dzieje. Należy po prostu dokonać rozbioru skladniowego zapytania i okre
7.1. SQL W ŚRODOWISKU PROGRAMISTYCZNYM 43 I
ślić wkaściwy sposób jego wykonania w języku SQL zaraz po przeczytaniu zapytania.
Program zapisany w języku podstawowym musi pobrać tekst, przekazać go do systemu SQL jako poprawną instrukcję, a następnie ją wykonać. Tym celom służą dwie instrukcje dynamicznego SQL.
1. EXEC SQL PREPARE, po którym umieszcza się zmienną, np. V, słowo kluczowe EROM, a następnie zmienną lub wyrażenie z języka podstawowego o typie tekstowym. Tekst jest traktowany jako instrukcja SQL i stanowi on wartość zmiennej V. Najczęściej podlega on rozbiorowi składniow~;mu, a SQL określa sposób wykonania, natomiast przetwarzanie nie zachodzi.
2. EXEC sQL EX CUTE, po którym wymienia się nazwę zmiennej z punktu 1, np. Y. Ta instrukcja powoduje przetworzenie instrukcji SQL zapisanej w zmiennej.
Można oba etapy wykonać jako jeden, jeśli skorzysta się z instrukcji
EXEC SQL EXECUTE IMMEDIATE
po której umieszcza się zmienną tekstową, wyrażenie tekstowe lub zmienną dzieloną. Wada tego rozwiązania ujawnia się, jeśli chcemy to samo zapytanie przetwarzać wielokrotnie w różnych kontekstach. Korzystając z EXECUTE IMMEDIATE, za każdym razem trzeba ponieść koszt przygotowania instrukcji, podczas gdy przy rozwiązaniu dwuetapowym przygotowanie odbywa się tylko jeden raz.
PRZYKŁAD 7.10
Na rysunku 7.7 umieszczono szkic programu w języku C, który pobiera ze standardowego wejścia tekst do zmiennej zapytanie, analizuje to zapytanie,
1) 2) 3) 4) 5)
void czytaj Zapytanie( ){
6) 7)
EXEC SQL BEGIN DECLARE SECTION; char *zapytanie;
EXEC SQL END DECLARE SECTION;
/* pobranie od użytkownika zapytania, alokacja pamięci (np. wywołanie malloc) i przygotowanie zmiennej dzielonej :zapytanie, które wskazuje na pierwszy znak zapytania */
EXEC SQL PREPARE SQLzapytanie EROM :zapytanie; EXEC SQL EXECUTE SQLzapytanie;
RYSUNEK 7.7
Przygotowanie oraz uruchomienie zapytania SQL
432
7. SYSTEMOWE ASPEKTY JĘZYKA SQL
a następnie wykonuje. Zapytanie jest przechowywane w zmiennej tekstowej sQLzapytanie. Ponieważ to zapytanie w całym programie jest przetwarzanie tylko jeden raz, więc wiersze 6) i 7) z rys. 7.7 można zastąpić pojedynczą następującą instrukcją:
EXEC SQL EXECUTE IMMEDIATE :zapytanie;
7.1.13. Ćwiczenia do podrozdzialu 7.1
Ćwiczenie 7.1.1. Korzystając ze schematu bazy danych z ćwiczenia 4.1.1
Produkt (producent, model, typ)
PC (model, szybkość, ram, hd, cd, cena)
Laptop (model, szybkość, ram, hd, ekran, cena) Drukarka (model, kolor, typ, cena)
O
należy utworzyć osadzone zapytania SQL. Można korzystać z dowolnego znanego języka podstawowego, a szczegółowe instrukcje można zastąpić czytelnym komentarzem.
*a) Należy użytkownika prosić o cenę i wyszukać te wszystkie PC, których cena jest najbliższa podanej wartości. Należy wydrukować producenta, numer modelu i szybkość wyszukanego PC.
b) Należy pobrać od użytkownika wymagania określające minimalnej konfiguracji PC, a więc szybkość, rozmiar RAM i twardego dysku oraz wymiar ekranu. Należy wyszukać wszystkie laptopy, które spełniają te wymagania. Należy wydrukować wszystkie atrybuty tych laptopów (czyli wartości wszystkich atrybutów z relacji laptop) oraz ich producentów.
!c) Należy zapytać użytkownika o producenta. Wydrukować specyfikacje wszystkich produktów dostarczanych przez niego. To znaczy, należy wydrukować numer modelu, typ oraz wszystkie atrybuty charakteryzujące dany produkt w odpowiedniej relacji.
~!!d) Należy dowiedzieć się od użytkownika o „budżecie" (koszt zestawu PC i drukarki) oraz minimalnej szybkości PC. Wyszukać najtańszy zestaw, który spełnia zadane warunki i dobrać, o ile to możliwe, drukarkę kolorową. Wydrukować numery modeli elementów zestawu.
e) Należy zapytać użytkownika o producenta, numer modelu, szybkość, rozmiary RAM i dysku twardego, szybkość CD-ROM oraz cenę nowego PC. Następnie trzeba sprawdzić, że w bazie nie ma PC o tym numerze modelu. Jeśli jest, to należy wydrukować komentarz z ostrzeżeniem; jeśli nie, to należy pobrane dane wstawić odpowiednio do tabel Produkt i Pc.
*!~ Ceny wszystkich „starych" PC należy obniżyć o 100 $. Trzeba przy tym uzyskać pewność, że cena żadnego z „nowych" PC, tzn. tych, które zostały wstawione do bazy danych w trakcie bieżącego przebiegu programu, nie zostały obniżone.
7.1. SQL W ŚRODOWISKU PROGRAMISTYCZNYM 433
Ćwiczenie 7.1.2. Używając schematu bazy „okręty" z ćwiczenia 4.1.3:
Klasy (klasa, typ, kraj, liczbaDział, działo, wyporność) Okręt (nazwa, klasa, wodowanie)
Bitwa (nazwa, data)
Rezultat (okręt, bitwa, wynik)
należy zapisać w formie osadzonego SQL przedstawione poniżej zadania:
a) Siła bojowa okrętu jest w przybliżeniu proporcjonalna do iloczynu liczby dział i sześcianu średnicy działa. Należy określić, która klasa okrętów dysponuje największą siłą bojową.
!b) Należy zapytać użytkownika o nazwę bitwy. Odnaleźć wszystkie kraje, w których posiadaniu znajdują się okręty uczestniczące w tej bitwie.
c) Należy dowiedzieć się od użytkownika nazwy klasy okrętu i innych danych niezbędnych do jednoznacznego wybrania krotki w tabeli Klasy. Potem trzeba pobrać listę nazw okrętów w tej klasie oraz daty wodowania tych okrętów. Użytkownik nie powinien podawać jednak nazwy pierwszego wodowanego statku, która pokrywa się z nazwą klasy.
!d) Należy sprawdzić czy w tabelach sitwa, Rezultat i okręt nie występuje przypadkiem okręt, którego data wodowania jest późniejsza od daty uczestniczenia w bitwie. Jeśli pojawi się tego typu błąd, to należy wysłać do użytkownika stosowny komunikat o błędzie oraz dać mu sposobność zmiany niewłaściwych danych. Następnie, jeśli użytkownik poda nowe wartości, trzeba je zapisać w bazie danych.
*!Ćwiczenie 7.1.3. Kolejne zadanie polega na wybraniu z relacji
PC (model, szybkość, ram, hd, cd, cena)
tych wszystkich PC, dla których można w tej relacji wyszukać co najmniej dwa inne PC o takiej samej szybkości, ale które są od niego droższe. Istnieje wiele różnych sposobów rozwiązania tego zadania, ale tu należy skorzystać z kursora przewijanego. Krotki PC należy wczytywać uporządkowane według szybkości, a następnie według ceny. Wskazówka: Przy wczytywaniu kolejnych krotek należy sprawdzać, czy następuje zmiana szybkości.
!!Ćwiczenie 7.1.4. W punkcie 7.1.1 wspominaliśmy o tym, że nie da się w języku SQL zapisać programu obliczającego silnię. To stwierdzenie dotyczy wersji SQL2. Jednakże w wersji SQL3, dzięki istnieniu rekurencji - pisano o tym w podrozdziale 5.3, można uzyskać coś podobnego do silni. Należy napisać w SQL3 rekurencyjne zapytania, które dotyczy relacji M, zawierającej jedyną krotkę (m), gdzie m jest pewną wartością całkowitą nieujemną. W wyniku ma powstać zbiór krotek (n, n!), gdzie 1 _<n<_m.
434 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
7.2. Transakcj e w j ęzyku SQL
Opisane dotychczas działania na bazie danych polegały na tym, że użytkownik przeszukiwał dane w bazie lub modyfikował je. Tego typu działania były wykonywane sekwencyjnie, tzn. że stan bazy danych zmieniony w wyniku jednego działania stanowił stan wejściowy dla kolejnego działania. Ponadto zakładaliśmy, że działania są niepodzielne, tzn. że w trakcie wykonywania działania nie mogła się zdarzyć awaria, a więc baza uzyskałaby stan, którego nie dałoby się wyjaśnić jako wynik przetwarzanego działania.
Życie jest jednak nieco bardziej skomplikowane. Najpierw zastanowimy się, jakie mogą być przyczyny zmiany stanu bazy danych inne niż przetwarzanie formalnych działań na niej, a potem opiszemy narzędzia SQL, dzięki którym można unikać niekontrolowanych zmian danych w bazie.
7.2.1. Szeregowanie
W zastosowaniach baz danych w bankowości lub rezerwacji rejsów w jednej sekundzie mogą być przetwarzane setki działań. Mogą być one inicjowane w dowolnym z setek lub tysięcy punktów, takich jak bankomaty lub komputery w biurach podróży, lub też u pracowników linii lotniczych, a nawet bezpośrednio u klientów. Jest bardzo prawdopodobne, że w tej sytuacji może się zdarzyć zainicjowanie kilku działań dotyczących tego samego konta lub tego samego rejsu i nałożenia się tych działań w czasie. Konsekwencje takiej sytuacji mogą okazać się niepożądane. Poniżej przedstawiamy przykład niekorzystnych zdarzeń, gdy w systemie zarządzania bazą danych nie działają żadne mechanizmy nadzorowania kolejności przetwarzania. Należy przy tym podkreślić, że takie sytuacje nie są typowe dla komercyjnych systemów baz danych i trzeba się bardzo starać, żeby uzyskać w praktyce wystąpienie takich błędów.
PRZYKŁAD 7.11
Załóżmy, że funkcja Rezerwacj a ( ) przegląda krotki relacji dotyczących rejsów i dostępnych miejsc, znajduje wolne miejsce na dogodny rejs i zmienia jego stan na niedostępny. Relację nazwiemy Rej sy, a atrybuty numerRej su, dataRej su, rumerMiej sca Oraż zaj ęte mają znaczenie Zgodne z nazwą. Szkic programu rezerwującego miejsce został przedstawiony na rys. 7.8.
Fragment zapisany w wierszach od 9) do 11) na rys. 7.8 służy do wybrania jednego wiersza i określenia wartości zmiennej rez jako prawda lub fałsz (0 lub 1) w zależności od tego, czy miejsce jest zarezerwowane, czy też nie. W wierszu 12) sprawdza się, czy miejsce jest zarezerwowane i jeśli nie, to następuje modyfikacja wartości w krotce, powodująca zapis rezerwacji tego
7.2. TRANSAKCJE W JĘZYKU SQL
1) EXEC SQL BEGIN DECLARE SECTION;
435
2) int rejs; /* numer rejsu */
3) char data[10]; /* data rejsu w formacie SQL *?
4) Char fotel[3]; /* miejsce jest oznaczone dwie-
ma cyframi oraz literą */
5) int rez; /* wartość logiczna, która sygnalizu-
je, czy miejsce jest zarezerwowane */
6) EXEC SQL END DECLARE SECTION;
7) void Rezerwuj () {
8) /* Kod w C, który zawiera interakcyjne pobra-
nie wartości określających rejs, datę i fo-
tel oraz zapamiętuje je w trzech zmiennych
o takich nazwach */
9) EXEC SQL SELECT zajęte INTO :rez
10) EROM Rejsy
11) WHERE numerRejsu = :rejs
AND dataRejsu = :data
AND miejsce = :fotel;
12) if(!rez){
13) EXEC SQL UPDATE Rejsy
14) SET zajęte = `B1'
15) WHERE numerRejsu = :rejs
AND dataRejsu = :data;
AND miejsce = :fotel;
16) /* kod w SQL i C, który służy do zapamięta-
nia przydziału miejsc w samolocie oraz
informuje o tym użytkownika */
17) else /* kod w C, który służy do poinformowa-
nia uzytkownika o tym, że miejsce
jest niedostępne oraz wprowadzenia
innego wyboru miejsca */
RYSUNEK 7.8
Wybór miejsca
miejsca. Modyfikacja następuje w wierszach od 13) do 15), a w wierszu 16) przypisuje się to miejsce klientowi, który oczekuje na tę rezerwację. W praktyce prawdopodobnie do zapisu rezerwacji służyłaby inna relacja. W końcu w wierszu 17) zapisano instrukcję powiadomienia klienta o tym, że nie można dokonać rezerwacji, jeśli właśnie tak się zdarzy.
Zauważmy, że funkcja Rezerwacj a () może być wywołana jednocześnie przez kilku użytkowników. Załóżmy, że przypadkiem dwóch agentów
436 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
chce zarezerwować to samo miejsce na ten sam rejs na dany dzień i o zbliżonej porze; tę sytuację przedstawiono na rys. 7.9. Obaj powodują wykonanie instrukcji z wiersza 9) w tej samej chwili, a obie kopie zmiennej rez otrzymują wartość 0, ponieważ w bieżącej chwili miejsce nie jest zarezerwowane. W wierszu 12) w obu przypadkach zostaje podjęta decyzja o zarezerwowaniu tego miejsca i ustawieniu wartości atrybutu Rezerwacja na 1. Następują dwie modyfikacje bazy danych, jedna po drugiej, i obaj klienci otrzymują potwierdzenie rezerwacji na to samo miejsce w tym samym rejsie.
Klient 1 znajduje wolne miejsce
czas Klient 2 znajduje wolne miejsce Klient 1 rezerwuje
miejsce zajęte
Klient 2 rezerwuje miejsce zajęte
RYSUNEK 7.9
Dwóch klientów próbuje jednocześnie zarezerwować to samo mięjsce
O
Jak widać z przykładu 7.1 1, może się zdarzyć, że dwa działania zostaną poprawnie wykonane, ale wynik ostateczny nie jest poprawny: obaj klienci wierzą, że dokonali rezerwacji. Takich kłopotliwych sytuacji można uniknąć, stosując zawarty w systemie SQL mechanizm, który służy do szeregowania wykonywania wywołań funkcji. Mówimy, że wykonanie funkcji tej samej bazy danych jest uszeregowane, jeśli wykonanie każdej uruchomionej funkcji musi być zakończone, zanim zostanie uruchomiona inna funkcja. Mówimy także, że przetwarzanie jest sekwencyjne, jeśli funkcje zachowuje się tak, jakby odbywały się sekwencyjnie, nawet jeśli faktycznie nakładają się na siebie w czasie.
Oczywiście, jeśli wywołania funkcji Rezerwacj a ( ) są uszeregowane (lub sekwencyjne), to nie dojdzie do błędu przedstawionego powyżej. Najpierw bowiem wykona się jedno wywołanie funkcji. Jej użytkownik odnajdzie wolne miejsce i zostanie ono dla niego zarezerwowane. Dopiero potem zostanie uruchomione drugie wywołanie tej funkcji, ale to miejsce będzie już zarezerwowane. Oczywiście któryś z użytkowników będzie zawiedziony, ale pod względem prawidłowości działania systemu jest ważne tylko to, że miejsce zostało przydzielone tylko jednej osobie.
7.2. TRANSAKCJE W JĘZYKU SQL
7.2.2. Niepodzielność
437
Obok sytuacji nieuszeregowanych wykonań funkcji, która występuje, gdy dwa działania na bazie danych wykonują się w tym samym czasie, może się zdarzyć również, że w wyniku pojedynczego działania wystąpi nieoczekiwany stan bazy danych wówczas, gdy podczas wykonywania nastąpi awaria sprzętu lub oprogramowania. Poniższy przykład stanowi dobrą ilustracje tego, co może się stać w takim przypadku. Podobnie jak poprzedni przykład, jest to sytuacja wyłącznie hipotetyczna i w praktycznie działających komercyjnych systemach baz danych takie błędy nie występują, chyba że program użytkowy jest niepoprawny.
1) EXEC SQL BEGIN DECLARE SECTION;
2) int kontol, konto2; /* dwa konta */
3) int saldol; /*kwota na pierwszym koncie*/ 4) int kwota; /* kwota przelewu */
5) EXEC SQL END DECLARE SECTION;
6) void przelew () {
7) /* kod w C, który służy do wprowadzenia konta 1 i konta 2 oraz kwoty przelewu do zmiennych kontol, konto2 oraz kwota */
8) EXEC SQL SELECT saldo INTO :saldol 9) EROM Konta
10) WHERE numerKonta = :kontol; 11) if (saldol >= kwota) {
12) EXEC SQL UPDATE Konta
13) SET saldo = saldo + :kwota 14) WHERE numerKonta = :konto2; 15) EXEC SQL UPDATE Konta
16) SET saldo = saldo - :kwota 17) WHERE numerKonta = :kontol; i
18) else /* Kod w C, który drukuje komunikat
o tym, że nie można wykonać przelewu ze względu na zbyt niskie saldo*/
RYSUNEK 7.10
Przelew pieniędzy z pewnego konta na inne
PRZYKŁAD 7.12
Rozważmy kolejny, popularny rodzaj baz danych: obsługę kont bankowych. Zasadnicza relacja nazywa się Konta i ma dwa atrybuty: NrKonta oraz sal
438 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
do. Wartości atrybutów określają więc numer konta oraz bieżące saldo na tym koncie.
Chcemy, aby przykładowa funkcja przelew ( ) dokonywała przelewu pewnej ustalonej kwoty z jednego konta na inne, o ile jest to możliwe, tzn. o ile saldo z pierwszego konta nie jest niższe od zadanej kwoty. Szkic funkcji przelew ( ) został przedstawiony na rys. 7.10. Instrukcje zapisane w wierszach od 8) do 10) powodują wczytanie wartości salda z pierwszego konta. W wierszu 10) ocenia się, czy saldo na pierwszym koncie jest wystarczające do dokonania przelewu z pierwszego konta na drugie. Jeśli tak, to wykonują się instrukcje z wierszy od 12) do 14), które powodują dodanie zadanej kwoty do salda na drugim koncie, oraz instrukcje z wierszy od 15) do 19), które powodują zmniejszenie salda na pierwszym koncie o tę kwotę. Jeśli saldo na pierwszym koncie jest zbyt niskie, to przelewu nie dokonuje się, a użytkownik zostaje o tym powiadomiony w wyniku wykonania instrukcji z wiersza 18).
Zastanówmy się teraz, co się stanie, jeśli po wykonaniu instrukcji z wiersza 14) nastąpi awaria, tzn. albo zepsuje się komputer, albo zostanie uszkodzona sieć między bazą danych a komputerem przetwarzającym funkcję. W bazie danych zostanie wówczas zapisane zwiększenie salda na koncie drugim, ale saldo na pierwszym koncie nie zostanie zmniejszone. W wyniku tego bank musi uzupełnić kwotę, która została dopisana do drugiego konta.
0
Zapewnienie sekwencyjności
W praktyce wymaganie, aby działania były przetwarzane sekwencyjnie, jest trudne do wyegzekwowania, po prostu jest ich zbyt dużo i część z nich musi być przetwarzana równolegle. Dlatego mechanizmy zapewnienia sekwencyjności są dołączane do systemów baz danych i nawet jeśli przetwarzanie odbywa się równolegle, to jego wyniki dla użytkownika wyglądają tak, jakby przetwarzanie było sekwencyjne.
W punkcie 1.2.4 wspominaliśmy, że jeden ze sposobów zapewnienia właściwego wyniku przetwarzania polega na blokowaniu elementów bazy danych po to, by uniknąć jednoczesnego dostępu do nich. Na przykład, gdyby funkcję Rezerwacja ( ) z przykładu 7.11 napisać tak, że na czas jej działania byłby zablokowany dostęp do relacji Rej sy, to inne funkcje, które nie korzystają z danych tej relacji, mogłyby działać równolegle, ale nie można by uruchomić równolegle drugiego wykonania funkcji Rezerwacj a ( ) . A w praktyce, jak wiadomo już z p. 1.2.4, korzystanie z blokad mniejszych niż cała relacja, takich jak bloki lub pojedyncze krotki, umożliwia jeszcze więcej równoległości, można na przykład nawet wykonywać równolegle wywołania tej samej funkcji Rezerwacja().
7.2. TRANSAKCJE W JĘZYKU SQL 439
Przykład 7.12 dobrze obrazuje ten typ sytuacji, kiedy cały ciąg działań na bazie danych powinien zostać wykonany niepodzielnie, tzn. albo zostaną przetworzone wszystkie działania cząstkowe, albo żadne z nich. Dość często rozwiązuje się ten problem, rezerwując lokalną przestrzeń roboczą, w której odbywa się całość przetwarzania; dopiero po wykonaniu wszystkich działań zatwierdza się zmiany zapisane w bazie danych i wówczas zmiany te stają się widoczne w bieżącym stanie bazy dla innych funkcji.
7.2.3 . Transakcj e
Rozwiązanie problemów związanych z szeregowaniem i atomizacją, o których była mowa w p. 7.2.1 i 7.2.2, polega na grupowaniu działań na bazie danych w transakcje. Transakcję tworzy jedno lub kilka działań na bazie danych, które trzeba wykonywać niepodzielnie: albo zostaną przetworzone wszystkie, albo żadne z nich. Poza tym we wczesnych wersjach SQL istniało wymaganie, aby transakcje były wykonywane sekwencyjnie. Jednakże już w SQL2 osłabiono to wymaganie. Ta sekwencyjność jest przyjmowana przez domniemanie`, ale użytkownik może ograniczyć ten wymóg i określić, które działania w transakcji mogą być wykonywane sekwencyjnie. W dalszych rozdziałach poświęcimy więcej uwagi temu zagadnieniu.
W jaki sposób zmienia się baza danych podczas transakcji
W różnych systemach transakcje są różnie implementowane. Zdarza się, że w trakcie wykonania transakcji stan bazy danych ulega zmianie. Wówczas, pomimo cofnięcia transakcji, te zmiany były widoczne dla innych transakcji. Aby uniknąć niekorzystnych konsekwencji, system najczęściej blokuje modyfikowane dane na użytek jednej transakcji i ten chwilowy stan bazy nie jest dostępny dla innych transakcji, aż do momentu jej zatwierdzenia albo cofnięcia. Blokady lub rozwiązania im równoważne są na pewno stosowane wówczas, gdy użytkownik wymaga sekwencyjności.
Jednakże, jak przekonamy się w p. 7.2.4, SQL2 umożliwia wybór sposobu traktowania zmian chwilowych. Można zatem wybrać taki tryb pracy, że dostęp do danych zmienianych w trakcie transakcji nie jest blokowany, a więc wartości zmieniane są widoczne dla innych transakcji nawet wówczas, gdy zmiany znikną wkrótce, w wyniku pojawienia się instrukcji ROLLBACK. A więc to projektant decyduje, które zmiany chwilowe są widoczne dla innych transakcji, a które nie. Wszystkie implementacje SQL zawierają mechanizmy, takie jak na przykład blokady, które umożliwiają ochronę dostępu do danych uczestniczących w transakcji.
Aczkolwiek w niektórych implementacjach stosuje się mniej restrykcyjne założenia.
410 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
Transakcja rozpoczyna się od wykonywania instrukcji stanowiącej zapytanie lub inne dzialanie na bazie danych albo jej schemacie. W języku SQL nie ma żadnej specjalnej instrukcji rozpoczęcia transakcji. Natomiast trzeba ją w jawny sposób zakończyć. Można to zrobić na dwa sposoby:
1. Instrukcja COMMIT zatwierdza wykonanie transakcji i stanowi ona pozytywne zakończenie transakcji. Oznacza to, że wszystkie zmiany w bazie danych, które zostaly wykonane przez instrukcje wchodzące w skład transakcji, są do bazy danych wprowadzone na stałe (tzn. do momentu zatwierdzenia innych modyfikacji). Zanim nastąpi zatwierdzenie zmiany sątylko chwilowe i nie sąwidoczne dla innych transakcji.
2. Instrukcja ROLLBACK stanowi cofnięcie wprowadzonych zmian, a zatem jest to negatywny wynik transakcji. Oznacza to, że zmiany wprowadzane przez instrukcje nie są obowiązujące i stan bazy jest taki jak przed transakcją.
PRZYKŁAD 7.13
Załóżmy, że funkcja Przelew ( ) z rys. 7.10 ma się wykonywać zawsze jako jednostkowa transakcja. Ta transakcja rozpoczyna się w wierszu 8) w chwili wczytywania salda z pierwszego konta. Jeśli test w wierszu 11) przebiegnie pozytywnie i przelew zostanie wykonany, to trzeba zatwierdzić zmiany danych, a więc trwale zapisać je w bazie. A więc przy końcu bloku if z wierszy od 12) do 17) należy umieścić dodatkową instrukcję w SQL:
EXEC SQL COMMIT;
Jeśli warunek sprawdzany w wierszu 11) okaże się fałszywy, a więc saldo na pierwszym koncie jest zbyt niskie, aby był dokonany przelew, to trzeba cofnąć transakcję. Można to zrobić, umieszczając na końcu bloku else z wiersza 18) następującą instrukcję:
EXEC SQL ROLLBACK;
Jednakże w tym przypadku, czyli przy wyborze tej ścieżki w programie, nie pojawiły się żadne instrukcje modyfikacji, tj. żadne zmiany chwilowe nie następują, a zatem jest zupełnie obojętne czy zastosujemy instrukcję COMMIT, Czy ROLLBACK.
a
7.2.4. Transakcje tylko do odczytu
Transakcje, opisane w przykładach 7.11 i 7.12, obejmowały odczytanie, a następnie w pewnych okolicznościach zapisanie danych do bazy danych. Omawiane transakcje przypominają problem sekwencyjności. Przed
7.2. TRANSAKCJE W JĘZYKU SQL 441
stawiliśmy w przykładzie 7.11 konsekwencje jednoczesnego wywołania funkcji rezerwującej to samo miejsce w samolocie, a z kolei w przykładzie 7.12 uwidoczniono skutki awarii, która zdarza się w trakcie przetwarzania funkcji. Jednakże w przypadku transakcji, które powodują tylko odczyt danych, istnieje więcej swobody w równoległym przetwarzaniu wielu transakcj i'.
PRZYKŁAD 7.14
Rozważmy funkcję, która wczytuje dane z bazy po to, by stwierdzić, czy pewne miejsce jest dostępne; jej tekst stanowi fragment z rys. 7.8 zapisany w wierszach od 1) do 11). Oczywiście bez szkody dla bazy danych możemy przetwarzać jednocześnie wiele wywołań tej funkcji. Najgorsze co może się stać, to że w trakcie odczytu dostępności miejsca inna funkcja właśnie to miejsce rezerwowała albo zwalniala. Można by zatem uzyskać informację o tym, że miejsce jest dostępne albo że jest niedostępne w pewnym niewielkim przesunięciu w czasie, ale odpowiedź ma sens.
0
Jeśli system SQL zostanie poinformowany o tym, że bieżąca transakcja jest tylko do odczytu, tzn. że jej działanie nie powoduje zmiany stanu bazy danych, to najczęściej ta informacja zostanie wykorzystana. Mimo że nie opiszemy tutaj szczegółów tego mechanizmu, to należy zapamiętać, iż umożliwia on jednoczesne przetwarzanie wielu transakcji tylko do odczytu, które działają na tych samych danych, ale z kolei blokuje dane w przypadku transakcji, które zapisująnowe wartości w bazie.
W języku SQL transakcje określa się jako tylko do odczytu w następujący sposób:
SET TRANSACTION READ ONLY;
Ta instrukcja musi wystąpić, zanim rozpocznie się transakcja. Gdyby to dotyczyło fragmentu funkcji zapisanej w wierszach od 1) do 11) na rys. 7.8, to należaloby umieścić następującą instrukcję
EXEC SQL SET TRANSACTION READ ONLY;
tuż przed wierszem 9), w którym transakcja się rozpoczyna. Umieszczenie tej deklaracji po wierszu 9) jest zbyt późne.
Można przedstawić pewne porównanie transakcji z kursorami. Na przykład w p. 7.1.10 zwróciliśmy uwagę na to, że można stosować więcej równoległości w przypadku kursorów przeznaczonych tylko do czytania danych niż w przypadku dowolnych kursów. Podobnie rzecz się ma z transakcjami, transakcje tylko do odczytu umożliwiają stosowanie równoległości w szerszym zakresie niż zwykłe transakcje.
442 7 SYSTEMOWE ASPEKTY JĘZYKA SQL
Można także poinformować system SQL o tym, że transakcja powoduje zapis nowych wartości w bazie i stosuje się wówczas następującą instrukcję:
SET TRANSACTION READ WRITE;
Jednakże jest to opcja, która zazwyczaj występuje w języku SQL przez domniemanie, a zatem przeważnie nie trzeba jej stosować.
7.2.5. Czytanie brudnopisu
Brudnopis jest popularnym terminem dla danych zapisanych przez transakcję, która jeszcze nie została zatwierdzona. Czytanie brudnopisu oznacza po prostu czytanie danych brudnopisu. Ryzyko przy czytaniu tych danych polega na tym, że w końcowym efekcie transakcja może nie zostać zatwierdzona. Jeśli tak się stanie, to dane brudnopisu zostaną usunięte z bazy danych i wszystkie funkcje powinny dawać takie wyniki, jakby tych danych nigdy nie było w bazie. Jeśli jednak jakieś inne transakcje odczytały dane z brudnopisu, który został w rezultacie usunięty, to mogłoby to spowodować niekorzystne konsekwencje.
Niekiedy odczytanie danych z brudnopisu ma znaczenie. A niekiedy ryzyko jest tak niewielkie, że warto skorzystać z tej możliwości przy czasochłonnym przetwarzaniu i uniknąć w ten sposób dodatkowych czynności związanych z ochroną przed czytaniem brudnopisu. Poniżej przedstawiamy przykłady, które ilustrują, co może się zdarzyć w przypadku dopuszczenia odczytu brudnopisu.
PRZYKŁAD 7.15
Rozważmy ponownie przelew między kontami bankowymi z przykładu 7.12. Teraz załóżmy, że przelew jest dokonywany za pomocą programu P, którego działanie można opisać w następujących punktach:
1. Dodaj pieniądze do konta 2.
2. Sprawdź, czy na koncie 1 jest wystarczająco dużo pieniędzy.
a) Jeśli nie, to usuń dodane pieniądze z konta (2) i cofnij transakcję. b) Jeśli pieniędzy wystarcza, to odejmij właściwą kwotę od salda z konta 1 i zatwierdź transakcję.
Jeśli program P wykonuje się sekwencyjnie, to wówczas nie ma znaczenia fakt, że na koncie 2 chwilowo może być za dużo pieniędzy. Nikt o tym nie będzie wiedział, a jeśli przelew nie dojdzie do skutku, to zostanąone usunięte.
Załóżmy z kolei, że dopuszcza się czytanie brudnopisu. Wyobraźmy sobie, że istnieją trzy konta A 1, A2 i A3, z saldami odpowiednio: 100 $, 200 $ i 300 $. Załóżmy, że transakcja T~ ma powodować, wykonując program P,
7.2. TRANSAKCJE W JĘZYKU SQL 443
przelew 150 $ z konta AI na A2. A w tym samym czasie transakcja T2 ma wykonać program P po to, by dokonać przelewu 250 $ z A2 na A3. Poniżej przedstawiamy potencjalnie możliwy ciąg zdarzeń:
1. T2 wykonuje pierwszy punkt i dodaje kwotę 250 $ do A3, gdzie saldo wynosi teraz 550 $.
2. Tl wykonuje pierwszy punkt i dodaje 150 $ do A2, gdzie saldo wyniesie 350 $.
3. T2 wykonuje sprawdzenie z punktu 2, na koncie A2 jest dostateczna suma (350 $), co umożliwia przelew 250 $ z A2 na A3.
4. T~ wykonuje sprawdzenie z punktu 2, na koncie AI nie ma wystarczająco dużo pieniędzy (100 $), aby dokonać przelewu z A l na A2.
5. T2 wykonuje punkt 2b. Odejmuje 250 $ od A2, gdzie teraz pozostaje 100 $, a transakcja T2 zostaje zatwierdzana.
6. Tl wykonuje punkt 2a. Od A2 zostaje odjęta kwota 150 $ i zostaje tam teraz -50 $. Transakcja Tl zostaje cofnięta.
Ogólna kwota na trzech kontach A1, A2 i A3 nie zmieniła się w wyniku tych działań, wynosiła i wynosi nadal 600 $. Jednakże, ponieważ w trzecim z sześciu wykonywanych działań transakcja T2 dokonała odczytu wartości z brudnopisu, zatem nie dało się uchronić salda przed zapisem wartości ujemnej, co było celem testu zapisanego w punkcie 2 programu P.
0
PRZYKŁAD 7.16
Kolejny przykład stanowi modyfikację funkcji rezerwującej miejsce w samolocie, którą omawialiśmy już w podrozdziale 7.11. Oto nowy algorytm:
1. Znaleźć wolne miejsce i zarezerwować je, nadając zmiennej rezerwacja wartość 1. Jeśli nie ma wolnego miejsca, to odwołać transakcję.
2. Sprawdzamy, czy klient akceptuje zarezerwowane miejsce. Jeśli tak, następuje zatwierdzenie. Jeśli nie, to zwalniamy miejsce, nadając zmiennej rezerwacja wartość 0 i powtarzamy punkt I po to, by zarezerwować inne miejsce.
Zakóżmy, że ten algorytm jest przetwarzany jednocześnie prZeZ dwie transakcje i jedna z nich zarezerwowała miejsce S, które potem nie zostało zaakceptowane przez klienta. Jeśli w trakcie przetwarzania punktu I druga transakcja uzyskała informację o tym, że S jest zarezerwowane, to jej klient nie będzie w efekcie mógł z tego miejsca skorzystać.
Tak jak w przykładzie 7.15 tego kłopotu można uniknąć, gdyby zabronić czytania z brudnopisu. Niekorzystny bowiem wynik został spowodowany tym, że druga transakcja uzyskała dostęp do danych modyfikowanych przez niezatwierdzoną transakcj ę.
0
444 7. SYSTEMOWE ASPEKTY JĘZYKA SQL
Jak bardzo istotny jest fakt, że odczytano wartości z brudnopisu? W przykładzie 7. I S było to bardzo ważne, ponieważ nastąpiło wpisanie na konto salda ujemnego, czego próbowano uniknąć. Jednakże już w przykładzie 7.16 problem nie wydaje się bardzo poważny. Klient może uzyskać informację o tym, że nie ma miejsca, które mu odpowiada, albo że wcale nie ma miejsca na dany lot. Jednakże kolejne uruchomienie transakcji spowoduje, że uzyska on dostęp do miejsca S. A więc w przypadku funkcji z tego przykładu udostępnienie możliwości czytania brudnopisu może mieć sens, transakcje bowiem będą wykonywały się wówczas dużo szybciej, co zmniejszy średni czas dokonywania rezerwacji.
W systemie SQL2 można określić, że pewna transakcja umożliwia czytanie brudnopisu. Taką deklarację umożliwia stosowna opcja instrukcji SET TRANSACTION, którą omawialiśmy w p. 7.2.4. Dla transakcji z przykładu 7.16 ma ona następującą postać:
1) SET TRANSACTION READ WRITE
2) ISOLATION LEVEL READ UNCOMMITTED;
Oznacza ona, że transakcja:
1. Zarówno czyta, jak i zapisuje dane (wiersz 1)).
2. W wierszu 2) określa się, że transakcja może się wykonywać na poziomie izolacji: odczyt niezatwierdzony. W punkcie 7.2.6 zostaną omówione cztery poziomy izolacji, dotychczas mieliśmy do czynienia z dwoma: sekwencyjnym oraz odczytem niezatwierdzonym.
Zauważmy, że jeśli transakcja nie jest tylko do odczytu (tzn. powoduje zapisanie do bazy co najmniej jednej nowej wartości), a poziom izolacji zostanie określony jako READ UNCOMMITTED, to transakcja musi być typu READ wRITE. Przypomnijmy sobie, z p. 7.2.4, że jest to opcja standardowa. Jednakże w systemie SQL2 następuje wyjątek od tej reguły w przypadku dopuszczenia odczytu brudnopisu. Wówczas zmienia się domniemany typ transakcji na tylko do odczytu, ponieważ, jak już się przekonaliśmy, istnieje duże ryzyko przy transakcjach typu read-write i dopuszczeniem odczytu brudnopisu. A zatem, jeśli chcemy, aby transakcja odczyt-zapis działała na poziomie izolacji - odczyt niezatwierdzony, to opcja READ WBITE musi zostać jawnie zadana, jak to występuje powyżej.
7.2.6. Inne poziomy izolacji
W systemie SQL2 są dostępne cztery poziomy izolacji. Z dwoma mieliśmy już do czynienia: z sekwencyjnym i odczytem niezatwierdzonym (dopuszczenie odczytu z brudnopisu). Dwa pozostałe nazywają się odczyt za
7.2. TRANSAKCJE W JĘZYKU SQC, 44S
twierdzony oraz odczyt powtarzalny. Dla danej transakcji sposób określenia tych poziomów jest następujący:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
i odpowiednio
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Zawsze przez domniemanie transakcje są typu odczyt-zapis, a więc jeśli chcemy to zmienić, to trzeba dolączyć w każdym przypadku opcje READ ONLY. Dopuszczalna jest także poniższa specyfikacja:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Jest ona jednak przyjęta przez domniemanie, więc nie trzeba jej jawnie deklarować.
Jak to wynika z samej nazwy, poziom izolacji - odczyt zatwierdzony - zabrania odczytu danych z brudnopisu (niezatwierdzonych). Jednakże można wówczas powtarzać wielokrotnie to samo zapytanie, otrzymywać rożne odpowiedzi tak dlugo, aż w odpowiedzi znajdą się dane z zatwierdzonych transakcji.
PRZYKŁAD 7.17
Ponownie rozważmy funkcję rezerwacji miejsc w samolocie z przykładu 7.16, ale teraz będzie ona wykonywana przy poziomie izolacji określonym jako odczyt zatwierdzony. W chwili wyszukiwania wolnego miejsca (punkt 1) nie będą widoczne wcale te miejsca, które są zarezerwowane przez inne transakcje, ale jeszcze nie są zatwierdzone". Jeśli jednak klient zrezygnuje z tego miejsca i funkcja będzie powtarzała zapytania o wolne miejsca cyklicznie, to za każdym razem może być w wyniku podany inny zbiór miejsc, ponieważ w tym samym czasie inne transakcje również dokonująrezerwacji i je wycofują.
0
Teraz z kolei rozważymy poziom izolacj i - odczyt powtarzalny. Tym razem nazwa jest nieco myląca, ponieważ powtórzenie zapytania nie gwarantuje wcale otrzymania tej samej odpowiedzi. Przy tym poziomie izolacji, jeśli jakaś krotka zostanie odczytana przy pierwszym wykonaniu zapytania, to w powtórnej odpowiedzi też ona wystąpi. Jednakże może się zdarzyć również, że do kolejnych odpowiedzi będą dołączane krotki - fantomy. Są to takie krotki w bazie, które powstają w wyniku wstawiania dokonywanego przez inne traysakeje.
` To, co teraz się stanie, może wyglądać nieco magicznie, ponieważ w algorytmie nie przewidywaliśmy wymuszenia poziomów izolacji. Najprawdopodobniej, jeśli dwie transakcje jednocześnie będą chciały rezerwować to samo miejsce, jedna z nich będzie zmuszona przez system do przerwania działania, nawet jeśli nie występuje tam instrukcja ROLLBACK.