#313 Rozdział 14. Transakcje i kursory Być może czytajšc tę ksišżkę zauważyłe, że SQL[Chodzi tutaj o standard języka SQL (przyp. tlum.).] pozbawiony jest wielu cech stanowišcych podstawowe elementy większoci języków programowania. Nie ma w nim zmiennych, instrukcji pętli, wyrażeń warunkowych, czy też funkcji lub procedur definiowanych przez użytkownika. W założeniach SQL miał dawać użytkownikom możliwoć tworzenia wyrażeń wydobywajšcych dane z bazy danych oraz modyfikujšcych je, a do tych celów wymienione konstrukcje nie były potrzebne. Aby móc przetworzyć wyniki zapytania lub zastosować reguły biznesowe do danych zapisanych w bazie, programici musieli wydobyć dane przy użyciu odrębnej aplikacji, wykonać na nich niezbędne operacje i na końcu odpowiednio uaktualnić zawartoć bazy danych. W końcu nadszedł jednak czas, kiedy firmy dostarczajšce na rynek relacyjne bazy danych zlikwidowały to drobne niedocišgnięcie, rozszerzajšc język SQL o nowy mechanizm pozwalajšcy umieszczać w bazie nie tylko dane, ale również powišzane z nimi programy nazywane procedurami składowanymi (ang. stored procedures). Procedury składowane pozwalajš na przetwarzanie danych wyłšcznie na poziomie bazy danych. Rozszerzenia SQL umożliwiajšce tworzenie procedur składowanych wprowadzajš do tego języka również szereg nowych konstrukcji, znanych z innych języków programowania. Procedurom składowanym powięcone sš trzy kolejne rozdziały: 15. Procedury składowane", 16. Procedury składowane w języku Transact-SQL" i 17. Procedury składowane w języku PL/SQL (Oracle)." Tutaj wyjanię ich zwišzek z zagadnieniami omawianymi w tym rozdziale, tzn. transakcjami i kursorami. Transakcje i kursory mogš być używane samodzielnie, ale ich zastosowanie często idzie w parze z procedurami składowanymi, dlatego istotnš sprawš jest omówienie wzajemnej zależnoci obu tych koncepcji. #314 Transakcje Do tej pory wyrażenie SQL traktowane było jako indywidualna jednostka - pewien samodzielny twór. Mówišc o transakcjach należy spojrzeć na wyrażenia SQL w nieco inny sposób. Najprociej rzecz ujmujšc, transakcje sš grupami wyrażeń języka SQL, traktowanymi z punktu widzenia przetwarzania jako jedna całoć. Transakcja może składać się z pojedynczego wyrażenia, jeżeli wynika to ze specyfiki realizowanego zadania lub z wielu wyrażeń. Istotnš cechš transakcji jest to, że zmiany wprowadzane przez nie do bazy danych sš trwale zapisywane tylko wtedy, gdy wykonane zostanš wszystkie wyrażenia wchodzšce w skład transakcji. Działanie takie ma szczególne znaczenie, ponieważ pozwala zachować integralnoć bazy danych w sytuacji, gdy w trakcie wykonywania transakcji nastšpi awaria systemu. Wyobra sobie system zarzšdzajšcy inwentarzem w sklepie. Kiedy klient kupuje kilka towarów i płaci za nie w kasie, suma wydanych przez niego pieniędzy trafia do bazy danych księgowoci, natomiast wszystkie zakupione towary sš usuwane z bazy inwentarza. Sklep dokonuje zakupu nowej porcji towaru, gdy jego iloć spadnie poniżej okrelonego poziomu. Po dostarczeniu dostawy przez hurtownika, dane dotyczšce iloci posiadanego towaru sš uaktualniane w bazie danych. Kiedy przedsiębiorca przechowuje swoje dane w taki sposób, niezbędny jest zapis wszelkich zmian wprowadzanych do bazy danych. Bez transakcji, każda zmiana w bazie danych musiałaby być dokonywana niezależnie i co ważniejsze, nie byłoby możliwoci pogrupowania wyrażeń w celu okrelenia, które z nich sš częciš jednej większej całoci. Przykładowo, jeżeli klient kupiłby 80 różnych towarów, każdy z nich zostałby wprowadzony do bazy danych jako oddzielna transakcja. Gdyby zaszła potrzeba cofnięcia tego zakupu, odnalezienie wszystkich pojedynczych transakcji wišzałoby się z nie lada trudnociami. Gdyby w bazie danych wystšpił błšd krytyczny lub system straciłby zasilanie w połowie przetwarzania towarów nabytych przez klienta, zakup ten byłby zatwierdzony jedynie w połowie. W takiej sytuacji niezbędne byłoby cofnięcie transakcji, do tego jednak potrzebna byłaby lista towarów już przetworzonych przez system. W tym miejscu nieocenionš pomoc niosš transakcje SQL. Wszystkie towary kupujšcego można umiecić w pojedynczej transakcji razem z innymi wyrażeniami SQL przetwarzajšcymi ten zakup. Gdyby teraz z jakiego powodu zaszła potrzeba wycofania zapytań zwišzanych z zakupem, wystarczy przerwać transakcję, a wszelkie zmiany zostanš anulowane. Zasada działania Z transakcjami zwišzane sš trzy podstawowe operacje. Pierwsza z nich to inicjali-zacja, rozpoczęcie transakcji. Po zainicjalizowaniu transakcji, wszystkie kolejne wyrażenia SQL traktowane sš jako częć transakcji, aż do momentu jej zakończenia. Drugš operacjš jest zatwierdzenie transakcji. Jest ono równoznaczne z jej zakończeniem i sprawia, że wszelkie wprowadzone przez niš modyfikacje sš na stałe zapisywane w bazie danych. Trzecia operacja, wycofanie transakcji, ma na celu zakończenie transakcji i anulowanie wszelkich zmian wprowadzonych do tego miejsca. #315 Do momentu, kiedy transakcja nie zostanie zatwierdzona, wprowadzone przez niš zmiany nie sš widoczne dla innych użytkowników korzystajšcych z tej samej bazy danych. Transakcje w Oracle Bazy danych obsługujšce transakcje udostępniajš polecenia przeznaczone do rozpoczynania, zatwierdzenia oraz anulowania transakcji. W programie SQL*PLUS (interfejs bazy danych Oracle działajšcy w trybie wiersza poleceń) do zatwierdzania transakcji służy polecenie COMMIT, natomiast do ich anulowania polecenie ROLLBACK. Nie ma polecenia rozpoczynajšcego transakcję. Nowa transakcja jest rozpoczynana automatycznie, zawsze kiedy zakończona zostanie transakcja bieżšca (bšd to przez jej zatwierdzenie lub też anulowanie). Istnieje pewna liczba poleceń, które powodujš automatyczne zatwierdzenie transakcji. Zaliczajš się do nich między innymi: utworzenie lub usunięcie tabeli lub widoku, a także polecenia typu alter oraz connect i disconnect. Również opuszczenie programu SQL*PLUS automatycznie zatwierdza wszelkie zmiany w bazie danych. Z drugiej strony, jeli sesja zostanie zakończona w sposób niekontrolowany (np. gdy komputer straci sieciowe połšczenie z bazš danych), transakcja jest automatycznie anulowana. Przyjrzyjmy się, w jaki sposób transakcje wykonywane sš w programie SQL* PLUS. Zaczniemy od wybrania kilku wierszy z tabeli Movies (listing 14.1). -------------------------------- Listing 14.1. Wyrażenie SELECT SELECT movie_id, movie_title FROM Movies MOVIE ID MOVIE TITLE -------------------------------- 1 Vegetable House 2 Prince Kong 3 The Code Warrior 4 Bili Durham 5 Codependence Day 6 The Linux Files 7 SQL Strikes Back 8 The Programmer 9 Hard Code 10 The Rear Windows 10 rows selected -------------------------------- Jeżeli teraz bazie danych wydane zostanie polecenie DELETE, odpowiednie wiersze będš traktowane tak, jakby zostały usunięte - demonstruje to listing 14.2. -------------------------------- Listing 14.2. Usuwanie wierszy z tabeli Movies DELETE FROM Movies WHERE movie_id IN (1, 3, 5, 7) 4 rows deleted. #316 SELECT movie_id, movie_title FROM Movies MOVIE_ID MOVIE_TITLE -------------------------------- 2 Pririce Kong 4 Bili Durham 6 The Linux Files 8 The Programmer 9 Hard Code 10 The Rear Windows 6 rows selected -------------------------------- Usunięte wiersze przestajš być widoczne dla użytkownika, który w swojej sesji wydał polecenie ich usunięcia, pozostajš jednak widoczne dla innych użytkowników połšczonych z bazš danych. W tym miejscu można jeszcze anulować transakcję, wydajšc polecenie ROLLBACK - listing 14.3. -------------------------------- Listing 14.3. Anulowanie transakcji poleceniem ROLLBACK Rollback complete. SELECT movie__id, movie_title FROM Movies MOVIE_ID MOVIE_TITLE -------------------------------- 1 Vegetable House 2 Prince Kong 3 The Code Warrior 4 Bili Durham 5 Codependence Day 6 The Linux Files 7 SQL Strikes Back 8 The Programmer 9 Hard Code 10 The Rear Windows 10 rows selected -------------------------------- Po wydaniu polecenia ROLLBACK, tabela Movies jest przywracana do swojego oryginalnego stanu. Polecenie COMMIT kończy bieżšcš transakcję i zapisuje do bazy danych wszelkie zmiany dokonane w trakcie jej trwania. W Oracle'u zatwierdzenie bieżšcej transakcji (poleceniem COMMIT) automatycznie rozpoczyna nowš transakcję, natomiast ROLLBACK anuluje bieżšcš transakcję. AUTOCOMMIT Większoć baz posiada specjalny tryb pracy (ang. autocommit modę), w którym każde wykonanie zapytania powoduje automatyczne zatwierdzenie transakcji. Do włšczenia trybu autocommit w programie SQL*PLUS służy następujšce polecenie: SET AUTOCOMMIT ON #317 Jeżeli za poleceniem AUTOCOMMIT wyspecyfikowany zostanie parametr w postaci liczby, automatyczne zatwierdzanie zmian będzie się odbywać co ustalonš liczbę poleceń, np.: SET AUTOCOMMMIT 5 Wydanie powyższego polecenia sprawi, że zmiany będš zatwierdzane zawsze po uruchomieniu pięciu kolejnych wyrażeń SQL. Aby wyłšczyć tryb AUTOCOMMIT i powrócić do manualnego zatwierdzania zmian, należy zmienić ustawienie AUTOCOMMIT na OFF: SET AUTOCOMMIT OFF Bieżšce ustawienie trybu AUTOCOMMIT można sprawdzić wydajšc polecenie: SHOW AUTOCOMMIT Transakcje w języku Transact-SQL W przeciwieństwie do Oracle'a, bazy danych korzystajšce z Transact-SQL traktujš domylnie każde wyrażenie SQL jako odrębnš transakcję. Ze względu na koniecznoć jawnego deklarowania transakcji, w Transact-SQL istniejš aż cztery różne polecenia zwišzane z tym mechanizmem: BEGIN, SAVE, COMMIT i ROLLBACK. Oprócz tego Transact-SQL umożliwia nadawanie nazw transakcjom oraz tworzenie punktów porednich, co daje dużš swobodę w zakresie wycofywania transakcji do okrelonego punktu. Zacznijmy od polecenia BEGIN TRANSACTION: BEGIN {TRAN I TRANSACTION) [nazwa_transakcji] Wyra...
sliwak