Ćwiczenia > Transakcje w MS SQL Server 2000 > Transakcje w T-SQL

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



Ćwiczenia > Transakcje w MS SQL Server 2000 > Transakcje w T-SQL