6.2 Transakcje w T-SQL

Nie można sobie wyobrazić produkcyjnej bazy danych bez wykorzystania transakcji
(ang. transaction). Transakcyjność jest właściwie wszędzie. Praktycznie
w każdej aplikacji istnieje potrzeba wykonywania wielu poleceń jako jednej
nierozerwalnej całości.
Transakcje w MS SQL Server mogą być dwojakiego rodzaju: jawne (ang. explicit) i niejawne (ang. implicit). W niniejszej lekcji zaprezentujemy Ci obydwa rodzaje transakcji.
 |
Na początek zapamiętaj jedną zasadę: w przypadku używania jawnych transakcji pojedyncze polecenie T-SQL (jak INSERT czy DELETE) jest samo w sobie transakcją (atomową jednostką), więc nie ma sensu umieszczać jednej instrukcji wewnątrz jawnej transakcji. Dzieje się tak dlatego, że domyślnie w SQL Server ustawiona jest opcja AutoCommit, która powoduje, że każde pojedyncze polecenie T-SQL jest samo dla siebie transakcją (chyba, że jawnie określimy początek transakcji).
|
Krok 1 - Użyj transakcji jawnych
1. Uruchom program Query Analyzer.
2. Zaloguj się używając uwierzytelnienia systemu Windows.
3. W menu głównym programu wybierz File - Open.
4. W oknie Open Query File wybierz plik demo_6_2_1.sql.
5. Uruchom skrypt (klawisz F5).
 |
USE Biblioteka
GO
BEGIN TRAN
INSERT INTO Autorzy(imie, nazwisko)
VALUES ('Jan','Kowalski')
IF @@ERROR = 0
BEGIN
PRINT 'Pierwsza czesc transakcji OK.'
INSERT INTO Autorzy(imie, nazwisko)
VALUES ('Jan','Nowak')
IF @@ERROR = 0
BEGIN
COMMIT TRAN
PRINT 'Druga czesc transakcji OK.'
END
ELSE
BEGIN
ROLLBACK TRAN
PRINT 'Wystapil blad w drugiej czesci transakcji.'
END
END
ELSE
BEGIN
ROLLBACK TRAN
PRINT 'Wystapil blad w pierwszej czesci transakcji.'
END
GO
|
Wynikiem wykonania skryptu jest wstawienie dwóch rekordów do tabeli Autorzy w bazie danych Biblioteka. W przypadku niepowodzenia którejkolwiek z operacji INSERT ani jeden rekord nie jest wstawiany. Dodatkowo wyświetlane są komunikaty mówiące o braku lub wystąpieniu błędów w kolejnych operacjach transakcji.
Zwróć uwagę na dwa szczegóły:
- transakcje są jawne (początek określa linia BEGIN TRAN, wykonanie transakcji - COMMIT TRAN, ewentualne wycofanie transakcji - ROLLBACK TRAN),
- do każdej operacji w transakcji dołączona jest obsługa błędów (wykorzystująca funkcję systemową @@ERROR, która zwraca numer błędu lub zero w przypadku braku błędu).
Krok 2 - Użyj transakcji niejawnych
1. W menu głównym programu Query Analyzer wybierz File - Open.
2. W oknie Open Query File wybierz plik demo_6_2_2.sql.
3. Tym razem już nie uruchamiaj skryptu, ale obejrzyj jego kod.
 |
USE Biblioteka
GO
SET IMPLICIT_TRANSACTIONS ON
GO
INSERT INTO Autorzy(imie, nazwisko)
VALUES ('Jan','Kowalski')
IF @@ERROR <> 0
ROLLBACK TRAN
INSERT INTO Autorzy(imie, nazwisko)
VALUES ('Jan','Nowak')
IF @@ERROR <> 0
ROLLBACK TRAN
COMMIT TRAN
GO
SET IMPLICIT_TRANSACTIONS OFF
GO
|
Powyższy skrypt jest praktycznie odpowiednikiem skryptu poprzedniego (z transakcjami jawnymi). Różnice są takie, że tym razem nie używamy instrukcji sterującej IF oraz, że nie wyświetlamy informacji o pomyślnym wykonaniu operacji oraz błędach. I oczywiście zasadnicza różnica - tym razem używamy transakcji niejawnych.
Decyzję o zastosowaniu transakcji niejawnych deklarujemy w linii:
SET IMPLICIT_TRANSACTIONS ON
Po takiej deklaracji każde polecenie T-SQL z następującej listy rozpoczyna nową transakcję (o ile wcześniej transakcja nie została rozpoczęta): ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRUNCATE TABLE, UPDATE.
Krok 3 - Zobacz praktyczne wykorzystanie transakcji
 |
Do przeprowadzenia tego kroku ćwiczenia niezbędne jest wcześniejsze przeprowadzenie ćwiczenia z lekcji 3.10 Import i eksport danych (musisz zaimportować dane do bazy danych Biblioteka).
|
1. W menu głównym programu Query Analyzer wybierz File - Open.
2. W oknie Open Query File wybierz plik demo_6_2_3.sql.
3. Uruchom skrypt (F5).
 |
USE Biblioteka
GO
-- deklaracje zmiennych
DECLARE @ID_wyd int, @ID_ks int
SET @ID_wyd = 3 -- wydawnictwo Helion
-- deklaracja kursora pobierajacego
-- ID ksiazek wydanych przez wydawnictwo
DECLARE kursor_ksiazki CURSOR FOR
SELECT ID_ksiazki
FROM Ksiazki
WHERE ID_wydawnictwa = @ID_wyd
-- otwarcie kursora
OPEN kursor_ksiazki
-- pobranie pierwszej wartosci z kursora
-- do zmiennej
FETCH NEXT FROM kursor_ksiazki
INTO @ID_ks
-- poczatek transakcji
BEGIN TRAN
-- najpierw w petli usuwamy informacje o autorstwie
-- dotyczace ksiazek, ktore za moment usuniemy
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM Autorstwo WHERE ID_ksiazki = @ID_ks
IF @@ERROR <> 0
ROLLBACK TRAN
FETCH NEXT FROM kursor_ksiazki INTO @ID_ks
END
-- nastepnie kasujemy wszystkie ksiazki
-- wydane przez wydawnictwo, ktore chcemy usunac
DELETE FROM Ksiazki
WHERE ID_wydawnictwa = @ID_wyd
IF @@ERROR <> 0
ROLLBACK TRAN
-- na koniec usuwamy samo wydawnictwo
DELETE FROM Wydawnictwa
WHERE ID_wydawnictwa = @ID_wyd
IF @@ERROR <> 0
ROLLBACK TRAN
-- wyslanie transakcji
COMMIT TRAN
-- zamykamy i usuwamy z pamieci kursor
CLOSE kursor_ksiazki
DEALLOCATE kursor_ksiazki
GO
|
Powyższy skrypt jest przykładem praktycznego wykorzystania transakcji do usunięcia z bazy danych Biblioteka wydawnictwa, wszystkich książek wydanych przez to wydawnictwo oraz informacji o autorstwie (nie o autorach).
Skrypt działa na następującej zasadzie:
1) ustawiamy bazę danych Biblioteka jako bazę roboczą,
2) deklarujemy dwie zmienne do przechowywania identyfikatorów wydawnictwa i książki (ustawiamy identyfikator wydawnictwa na 3 - u nas odpowiada to wydawnictwu Helion),
3) deklarujemy kursor kursor_ksiazki pobierający identyfikatory książek wydanych przez wybrane wydawnictwo,
4) otwieramy kursor i pobieramy pierwszą wartość z zestawu identyfikatorów książek do zmiennej ID_ks,
5) w pętli (dopóki pobieramy identyfikatory książek wydanych przez wydawnictwo) usuwamy informacje o autorstwie (dotyczące książek przeznaczonych do usunięcia) z tabeli Autorstwo (musimy najpierw usunąć dane z tej tabeli, by zachować spójność danych),
6) po usunięciu informacji o autorstwie, z tabeli Ksiazki usuwamy dane książek, które w kolumnie ID_wydawnictwa mają wartość odpowiadającą identyfikatorowi wydawnictwa, które chcemy usunąć,
7) wreszcie, możemy usunąć z tabeli Wydawnictwa wybrane wydawnictwo.
Wynikiem w naszym przypadku powinno być usunięcie wydawnictwa Helion oraz książki XML na poważnie.
 |
Powyższy skrypt nie jest optymalnym rozwiązaniem
konkretnego problemu - prawdopodobnie lepszym rozwiązaniem jest utworzenie
widoku i wykorzystanie go do usuwania niezbędnych danych. Nie jest
też przykładem optymalnie napisanej transakcji. Ale mimo to jest funkcjonalny
i ilustruje mechanizmy transakcji jawnych. |
 |
Planuj zawsze kolejność wykonywania operacji, by zachować spójność danych. Przez zachowanie spójności danych rozumiemy brak sytuacji, gdy istnieją wpisy w którejś z tabel odnoszące się do nieistniejących wpisów w innej tabeli. Przykład: w powyższym skrypcie nie możemy usunąć wydawnictwa od razu z tabeli Wydawnictwa, ponieważ w tabeli Ksiazki znajdują się wiersze odnoszące się do identyfikatora tego wydawnictwa (usunięcie spowoduje, że nie będzie wiadomo, kto wydał daną książkę).
|
 |
Uprzedzając trochę następne lekcje zamieszczamy poniżej kilka porad, jak tworzyć dobre i funkcjonalne transakcje:
- wykonuj operacje pobierania danych i ich przetwarzania poza transakcją,
- nigdy nie pobieraj w transakcji danych od użytkownika (nie znasz wówczas czasu odpowiedzi użytkownika i nie masz
wpływu na czas trwania transakcji,
- komunikaty informujące o cofnięciu transakcji umieszczaj po wykonaniu operacji ROLLBACK,
- początek i koniec transakcji trzymaj w jednym wsadzie (a jeszcze lepiej użyj procedury składowanej do wykonania całej operacji),
- staraj się tworzyć krótkie transakcje,
- ostrożnie wybieraj poziomy izolacji transakcji (o tym w następnej lekcji),
- staraj się, by transakcje miały dostęp do jak najmniejszej ilości danych.
|
 |
W projekcie Prace dyplomowe koniecznie użyj transakcji (najlepiej do usuwania danych). Zapisz przykład transakcji w skrypcie o nazwie transakcje.sql. Używaj transakcji jawnych.
|
Przejdź dalej  |