Transakcje
W tym artykule skupię się na omówieniu podstaw transkacji. Zostaną one opisane oraz pokażę przykłady operacji takich jak: commit oraz rollback. Pokażę również jak poprawnie wyłapywać błędy procedur składowanych używanych wewnątrz transakcji.
W internetowych poradnikach możecie przeczytać, że transakcja jest opisywana jako: "sekwencja operacji wykonywanych jako pojedyncza jednostka pracy". Każda transakcja ma cztery kluczowe wartości, które sa określone skrótem ACID. Jest to akronim od Atomic Consistent Isolated Durability.
ATOMIC oznacza, iż cała operacja jest trakowana jako pojedyncza jednostka. Wykonywane jest wszystko albo nic. Consistent oznacza, że zakończona transakcja pozostawi bazę danych w spójnym stanie wewnętrznym. ISOLATED oznacza, że transakcja widzi bazę danych we wspomnianym powyżej stanie spójnym. Jeżeli dwie transakcję próbują zaktualizować tą samą tabelę najpierw zostanie wykonana pierwsza z nich a potem druga. DURABILITY oznacza, że wyniki transakcji są stale przechowywane w systemie.
Najprostszą transkacją w SQL Server jest pojedyncza instrukcja modyfikacji danych. Wszystkie moje przykłady używają wielokrotnie wspominanej bazy danych Adventureworks. Ponizsza operacja:
UPDATE Person.Address
SET AddressLine1 = 'Prosta 51'
WHERE AddressID = 1
jest transakcją mimo, że nie robi za wiele. Transakcja ta z angielskiego nosi nazwę Autocommit. W pierwszej kolejności SQL Server zapisuje do loga co robi. Następnym krokiem jest rzeczywiste dokonanie aktualizacji danych oraz zapisanie do loga informacji o przeprowadzaniu operacji. Dane zapisywane w dzienniku zapisywane są bezpośrednio na dysku ale sama aktualizacja jest (prawdopodobnie) wykonywana na kopii danych znajdujących się w pamięci. Dopiero kolejnym krokiem jest zapisanie tej zaktualizowanej bazy danych na dysku. Jeżeli pojawi się błąd po wykonaniu transakcji i zapisaniu informacji w dzienniku zdarzeniu, SQL Server wykorzysta tą informację do przeprowadzenia operacji roll forward na przeprowadzonej transkacji podczas jej kolejnego uruchomienia.
Transakcje z wieloma operacjami
Aby transakcje były bardziej przydatne należy przeprowadzać w nich dwie lub więcej operacji. Z angielskiego nosza one nazwę Explicit Transactions. Proszę spojrzeć na poniższy przykład:
BEGIN TRANSACTION
UPDATE Person.Address
SET AddressLine1 = 'Prosta 51'
WHERE AddressID = 1
UPDATE Person.Address
SET AddressLine1= 'Przyokopowa 31'
WHERE AddressID = 2
COMMIT TRANSACTION
Zauważ początkową BEGIN TRANSACTION oraz końcową instrukcję END TRANSACTION. Polcenia te rozpoczynają oraz kończą transakcje. Wszystkie instrukcje zawarte wewnątrz tych poleceń traktowane są jako logiczna jednostka pracy. Jeżeli po pierwszej aktualizacji bazy danych pojawi się jakikolwiek błąd nie dojdzie do wykonania polecenia COMMIT TRANSACTION - oznacza to, że ani pierwsza ani druga instrukcja nie zostaną wykonane.
ROLL BACK
Możesz również sam dokonać cofnięcia transakcji jeżeli nie robi tego czego od niej oczekujesz. Spójrz na poniższy przykład:
BEGIN TRANSACTION
UPDATE Person.Person
SET Title = 'TS'
WHERE FirstName = 'Ken' AND LastName='Sánchez'
IF @@ROWCOUNT = 2
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
Przykład trudny do spotkania w rzeczywistości. Po analizie bazy danych stwierdziliśmy, że mamy dwa pasujące rekordy i nieważne co się stanie w przyszłości chcemy, żeby ta konkretna transakcja aktualizowała jedynie dwa wiersze. Możemy dlatego zauważyć warunek ustawiony na ROWCOUNT - mówi on ile wierszy zostało poddanych modyfikacji. Tylko i wyłacznie w przypadku gdy dojdzie do aktualizacji dwóch wierszy dokonamy "popełenienia" transkacji, tj. COMMIT TRANSACTION. W przyciwnym wypadku za pomocą polecenia ROLLBACK TRANSACTION wskazujemy, że modyfikacja innej liczby wierwszy niż 2 nie ma sensu i cofamy wszystkie zmiany, które zaszły, np. aktualizacja 1 czy 42 wierwszy. W naszym powyższym przypadku, za pomocą Query Analyzer, możemy zobaczyć, że doszło do aktualizacji jednego wiersza ale poprzez umieszczenie tych dwóch operacji w transakcji nie dostało do zrealizowania operacji w naszej bazie danych – zmiany zostały cofnięte.
Procedury składowane
Na szczęście, większość transakcji napisanych przez Ciebie będzie wewnątrz procedur składowanych. Spójrz na poniższy przykład:
CREATE PROCEDURE TransactionTest1
AS
BEGIN TRANSACTION
INSERT INTO Person.Address(AddressLine1, City, StateProvinceID, PostalCode)
VALUES ('1234 TestStreet', 'Seattle', 79, 99999)
UPDATE Person.Address
SET AddressLine1='1234 TestStreetUpdated'
WHERE AddressLine1 = '1234 TestStreet'
COMMIT TRANSACTION
GO
Problemem w powyższej procedurze jest to, że transakcja nie dba o to czy instrukcje zostały wykonane poprawnie. Sprawdza tylko czy składnia SQL jest poprawna. Jeżeli uruchomisz tę procedurę, spróbuje one wstawić duplikaty wpisu do zdefiniowanej tabeli. Zobacz błąd mówiący o naruszeniu więzów integralności:
EXEC TransactionTest1
-- (1 row(s) affected)
-- Msg 2601, Level 14, State 1, Procedure TransactionTest1, Line 8
-- Cannot insert duplicate key row in object 'Person.Address' with unique index 'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'. The duplicate key value is (1234 TestStreetUpdated, , Seattle, 79, 99999).
-- The statement has been terminated.
Możesz również zobaczyć informacje, że polecenie to zostało przerwane. Transakcja jednak ciągle trwa. Operacja UPDATE działa poprawnie dlatego SQL Server pozwala na wykonanie transakcji. Kod ten należy przerobić, żeby wyglądał w następujący sposób:
CREATE PROCEDURE TransactionTest2
AS
BEGIN TRANSACTION
INSERT INTO Person.Address(AddressLine1, City, StateProvinceID, PostalCode)
VALUES ('1234 TestStreet', 'Seattle', 79, 99999)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
return 10
END
UPDATE Person.Address
SET AddressLine1='1234 TestStreetUpdated'
WHERE AddressLine1 = '1234 TestStreet'
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
return 11
END
COMMIT TRANSACTION
GO
Możesz zobaczyć, że sprawdzamy poprawność wykonania każdej z dwóch instrukcji. Jeżeli instrukcja się nie powiedzie (@ERROR <> 0) cofamy poprzednie zmiany oraz używamy instrukcji RETURN do zaprzestania wykonywania procedury składowanej. Bardzo ważne jest, aby sprawdzać błędy po każdym wykonaniu bloku instrukcji ponieważ w przeciwnym razie możemy niewłaściwie wykonać transakcję.
Celem wyjaśnienia wszelkich wątpliwości warto skupić się jeszcze na statusach zwracanych błedów. Błąd o stopniu zagrożenia wynoszącym 10 lub mniej oznacza ostrzeżenie, nie jest rzucany wyjątek.
Błędy występujące w procedurach zgłaszane z poziomem 11 do 20 rzucają wyjątek języka SQL, który jest wyłapywany przez SqlErrorCollection. Należy również zauważyć, że niektóre błędy o poziomie 11 lub większym przerywają całą procedurę składowaną.
