Systemowe aspekty języka SQL

Skoncentrujemy teraz uwagę na zagadnieniu, jak dopasować SQL do środowiska programistycznego. Żaden z tematów związanych z tym zagad­nieniem, które poruszamy poniżej, nie narusza standardu SQL2. W podroz­dziale 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ą przekazywa­nie 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 pod­stawowej 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 zdefinio­wane 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 programowa­nia, który zawiera elementy SQL. Programista przygotowuje program w stan­dardowej 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ę in­strukcję. 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 repre­zentacji, 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 stu­dent 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: al­bo 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ą po­mocą, jeśli działania na bazie trzeba wykonywać w sposób efektywny, a przy tym nie trzeba rezygnować z programowania wysokopoziomowego. Progra­mista 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ć jedno­cześ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ęzy­ka SQL, a programami podstawowymi odbywa się za pośrednictwem zmien­nych 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 dwukrop­kiem, 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 zna­kó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 podstawo­wym 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 deklara­cji. 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 przy­kł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 odgry­wają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 stu­dio, 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 zdefi­niowana 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 prze­znaczonych dla konkretnych wartości. Przypomnijmy tu, że nazwy tych zmiennych w instrukcjach SQL są poprzedzane dwukropkiem. Poniżej przed­stawiamy 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ła­dzie 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 poprze­dzona 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 po­kazano 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 przypisa­nie 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 instruk­cje 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 usuwa­nia 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 progra­mowania. 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 sa­mo 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 po­wstaje 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 zosta­nie wybrane więcej niż jedna krotka, to zmienne dzielone nie otrzymają no­wych wartości, a informacja o trybie wykonania instrukcji zostanie przekaza­na 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ż zna­my. 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 wier­szu 7) pojawia się klauzula mTO, dzięki której wiadomo, co należy zrobić z wynikiem przetwarzania. W tym przypadku oczekujemy, że w wyniku po­wstanie 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 od­naleziono ż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ś krot­ka, to wymienionym na liście kolejnym zmiennym dzielonym zo­stają 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 war­toś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 kro­tek 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ęp­ne 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 zde­finiowane 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 zmien­nej 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 zmien­nych 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 wy­szukano ż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ż żad­nej 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 pod­stawowego, to w wierszu 14) liczba cyfr wartości ceny sieci zostaje zainicjo­wana 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 zda­rzył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 wyni­ku zapytania) można nie tylko odczytywać i przetwarzać wartości zapamięta­ne w tej relacji, ale można także zmieniać lub usuwać knotki. Składnia in­strukcji UPDATE oraz DELETE jest prawie taka, jaką poznaliśmy w podroz­dziale 5.6, inaczej zapisuje się tylko klauzulę WHERE. Jest ona w tym przy­padku 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 zde­cydować, 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 rela­cji, 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 pod­stawie kodu zmiennej sQLSTATE „02000", rozstrzyga, czy należy analizo­wać 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 usu­nięta przez przetworzenie instrukcji DELETE zapisanej w wierszu 13). Nato­miast 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 zo­staka 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ą war­tości składowych wymienionych później. Składowe można specyfikować albo przez nazwę atrybutu, albo przez numer. W tym drugim przypadku numer po­krywa 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 pro­dukcji, 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ę kur­sora 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ępnia­ne 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 prze­są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 ko­rzysta z kursora gwiazdaKursor, zdefiniowanego w przykładzie 7.6, a jed­nocześnie inne funkcje (albo nawet ta sama funkcja) modyfikują którąś z rela­cji: Film lub Gwiazdyw. W podrozdziale 7.2 przedstawimy bardziej wyczer­pująco problematykę równoległego dostępu do zasobów. Teraz jednak skupi­my 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średnic­twem 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żąda­nych 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, po­nieważ 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ą modyfika­cji 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śred­nictwem 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 zo­stanie wybrana następna krotka (NExT jest domyślne).

b) FIRST lub LAST określa wybór odpowiednio pierwszej lub ostat­niej 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 pomi­nąć, aby pobrać nową krotkę. Na przykład RELATIVE 1 jest rów­noważne NEXT, a RELATIVE -1 jest równoważne PRIOR.

d) ABSOLUTE, a potem liczba całkowita, która wskazuje pozycję krot­ki, 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 przewi­jalny, 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 od­wrotnej jest jeszcze kosztowniejsze, ponieważ zazwyczaj są one wygenero­wane wszystkie jeszcze zanim zostaną po raz pierwszy udostępnione kurso­rowi dyrKursor.

O

7.1.12. Dynamiczny SQL

Prezentowany model łączenia SQL z innymi programami polega na osa­dzaniu 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 pod­stawowym. 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 za­pytanie w języku SQL, czyta je, a następnie przetwarza. Można tu przypo­mnieć 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 podsta­wowego o typie tekstowym. Tekst jest traktowany jako instrukcja SQL i stanowi on wartość zmiennej V. Najczęściej podlega on rozbio­rowi 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ę tyl­ko 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 przygotowa­nie 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 przetwarza­nie 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 komenta­rzem.

*a) Należy użytkownika prosić o cenę i wyszukać te wszystkie PC, których ce­na 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 konfigu­racji 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 wy­drukować numer modelu, typ oraz wszystkie atrybuty charakteryzujące da­ny 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ść, roz­miary 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 na­leż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 dys­ponuje 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 wo­dowanego 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 uczest­niczenia w bitwie. Jeśli pojawi się tego typu błąd, to należy wysłać do użyt­kownika 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 pew­ną 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 zmie­niony 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 przetwarzane­go działania.

Życie jest jednak nieco bardziej skomplikowane. Najpierw zastanowimy się, jakie mogą być przyczyny zmiany stanu bazy danych inne niż przetwa­rzanie 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 banko­maty lub komputery w biurach podróży, lub też u pracowników linii lotni­czych, 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. Po­niż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 zmie­nia jego stan na niedostępny. Relację nazwiemy Rej sy, a atrybuty numer­Rej 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 wybra­nia 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 prak­tyce 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ą wykona­nie instrukcji z wiersza 9) w tej samej chwili, a obie kopie zmiennej rez otrzymują wartość 0, ponieważ w bieżącej chwili miejsce nie jest zarezer­wowane. W wierszu 12) w obu przypadkach zostaje podjęta decyzja o zare­zerwowaniu tego miejsca i ustawieniu wartości atrybutu Rezerwacja na 1. Następują dwie modyfikacje bazy danych, jedna po drugiej, i obaj klien­ci 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 unik­nąć, stosując zawarty w systemie SQL mechanizm, który służy do szerego­wania 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. Naj­pierw bowiem wykona się jedno wywołanie funkcji. Jej użytkownik odnaj­dzie 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 miej­sce 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 nieoczeki­wany 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 funk­cji 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 wy­starczają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 in­strukcji z wiersza 18).

Zastanówmy się teraz, co się stanie, jeśli po wykonaniu instrukcji z wier­sza 14) nastąpi awaria, tzn. albo zepsuje się komputer, albo zostanie uszko­dzona sieć między bazą danych a komputerem przetwarzającym funkcję. W bazie danych zostanie wówczas zapisane zwiększenie salda na koncie dru­gim, 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 se­kwencyjności są dołączane do systemów baz danych i nawet jeśli przetwa­rzanie 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 ba­zy danych po to, by uniknąć jednoczesnego dostępu do nich. Na przy­kł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ów­nolegle, ale nie można by uruchomić równolegle drugiego wykonania funkcji Rezerwacj a ( ) . A w praktyce, jak wiadomo już z p. 1.2.4, ko­rzystanie z blokad mniejszych niż cała relacja, takich jak bloki lub poje­dyncze krotki, umożliwia jeszcze więcej równoległości, można na przy­kład nawet wykonywać równolegle wywołania tej samej funkcji Rezer­wacja().

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 ba­zie 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 transak­cji. 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 sto­sowane 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 blo­kowany, 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 im­plementacje SQL zawierają mechanizmy, takie jak na przykład blokady, które umożliwiają ochronę dostępu do danych uczestniczących w trans­akcji.

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 za­pytanie 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 pozy­tywne 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 za­tem jest to negatywny wynik transakcji. Oznacza to, że zmiany wpro­wadzane 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 da­nych, 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 cof­nąć 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 odczyta­nie, a następnie w pewnych okolicznościach zapisanie danych do bazy da­nych. 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 przetwarza­nia funkcji. Jednakże w przypadku transakcji, które powodują tylko odczyt danych, istnieje więcej swobody w równoległym przetwarzaniu wielu trans­akcj 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 opi­szemy 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 doty­czył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 do­mniemanie, a zatem przeważnie nie trzeba jej stosować.

7.2.5. Czytanie brudnopisu

Brudnopis jest popularnym terminem dla danych zapisanych przez trans­akcję, 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 ry­zyko jest tak niewielkie, że warto skorzystać z tej możliwości przy czaso­chł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 so­bie, ż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 wynie­sie 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 wystar­czają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 brud­nopisu, 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 samo­locie, którą omawialiśmy już w podrozdziale 7.11. Oto nowy algorytm:

1. Znaleźć wolne miejsce i zarezerwować je, nadając zmiennej rezerwa­cja 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 zare­zerwować 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ła­dzie 7.16 problem nie wydaje się bardzo poważny. Klient może uzyskać in­formację 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 czy­tanie brudnopisu. Taką deklarację umożliwia stosowna opcja instrukcji SET TRANSACTION, którą omawialiśmy w p. 7.2.4. Dla transakcji z przykła­du 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 po­ziomie 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 zo­stanie 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 dopuszcze­nia 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 izo­lacji - 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 (do­puszczenie 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 dekla­rować.

Jak to wynika z samej nazwy, poziom izolacji - odczyt zatwierdzony - za­brania odczytu danych z brudnopisu (niezatwierdzonych). Jednakże można wówczas powtarzać wielokrotnie to samo zapytanie, otrzymywać rożne odpo­wiedzi 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 od­czyt 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 ra­zem 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.

---