Transakcje w skryptach T-SQL

Transakcje w skryptach T-SQL pozwalają zapewnić spójność wielu poleceń wykonywanych sekwencyjnie.

Transakcje T-SQL jako mechanizm spójnego wykonywania operacji

Transakcje w skryptach T-SQL służą do grupowania kilku powiązanych poleceń w jedną logiczną operację. Dzięki temu baza danych może potraktować cały zestaw zmian jako całość, a nie jako zbiór niezależnych instrukcji. Ma to znaczenie wszędzie tam, gdzie wynik jednego polecenia wpływa na sens następnych. Przykładem może być przeniesienie środków pomiędzy dwoma kontami, zapis zamówienia wraz z jego pozycjami albo aktualizacja danych klienta oraz powiązanych informacji kontaktowych.

Najważniejsza zasada polega na tym, że transakcja powinna zakończyć się albo pełnym zatwierdzeniem zmian, albo ich wycofaniem. W pierwszym wariancie używa się instrukcji COMMIT TRANSACTION. W drugim stosuje się ROLLBACK TRANSACTION. Jeżeli pierwsza część operacji powiedzie się, lecz później wystąpi błąd, wycofanie pozwala przywrócić stan sprzed rozpoczęcia transakcji. Chroni to przed sytuacją, w której tylko część danych zostanie zmodyfikowana, a pozostałe rekordy pozostaną bez oczekiwanej aktualizacji.

W typowym skrypcie transakcja rozpoczyna się od BEGIN TRANSACTION, po którym wykonywane są instrukcje modyfikujące dane. Po zakończeniu operacji można wykonać zatwierdzenie. W praktycznych zastosowaniach nie wystarcza jednak samo wpisanie tych trzech poleceń. Konieczne jest także przewidzenie błędów, sprawdzenie warunków biznesowych oraz określenie, co ma się stać, gdy jedna z instrukcji nie powiedzie się.

Transakcje w skryptach T-SQL a atomowość, blokady i kolejność działań

Transakcje opierają się na kilku właściwościach kojarzonych z modelem ACID. Atomowość oznacza traktowanie operacji jako całości. Spójność wymaga, aby po zakończeniu transakcji dane nadal spełniały reguły zdefiniowane w bazie i w aplikacji. Izolacja opisuje sposób, w jaki równoległe operacje widzą wzajemnie swoje zmiany. Trwałość oznacza zachowanie zatwierdzonych danych mimo zakończenia sesji lub wystąpienia typowej awarii systemu.

Warto odróżnić transakcję od pojedynczego polecenia SQL. Pojedynczy UPDATE albo DELETE również jest wykonywany w określonym kontekście transakcyjnym, zależnie od ustawień połączenia. Transakcja jawna pozwala jednak objąć wspólnym zakresem kilka instrukcji, dzięki czemu można kontrolować ich wspólny rezultat. W trybie autocommit każde polecenie jest zwykle zatwierdzane osobno, co upraszcza proste operacje, lecz nie odpowiada zadaniom wymagającym wielu zależnych zmian.

Podczas działania transakcji silnik może zakładać blokady na zmienianych wierszach, zakresach albo tabelach. Blokady są potrzebne, aby równoległe sesje nie doprowadziły do sprzecznych wyników, ale zbyt długa transakcja może ograniczać dostęp innych użytkowników. Szczególne znaczenie ma kolejność odczytów i modyfikacji. Dwie transakcje, które blokują zasoby w odmiennej kolejności, mogą doprowadzić do zakleszczenia. W takim przypadku silnik wybiera jedną transakcję do przerwania, a skrypt powinien być przygotowany na ponowienie operacji.

Poziom izolacji wpływa na kompromis pomiędzy spójnością odczytu a współbieżnością. Niższa izolacja może ograniczyć oczekiwanie na blokady, lecz zwiększa ryzyko odczytu danych tymczasowych lub zmieniających się w trakcie analizy. Wyższa izolacja lepiej chroni wynik odczytu, ale może powodować większą liczbę blokad i dłuższe oczekiwanie. Dobór ustawienia powinien wynikać z charakteru operacji, a nie z przyzwyczajenia.

Obsługa błędów w transakcjach T-SQL i kontrola wyniku

Bezpieczny skrypt transakcyjny powinien rozróżniać sukces od częściowego wykonania. W T-SQL często wykorzystuje się konstrukcję TRY...CATCH. W bloku TRY umieszcza się rozpoczęcie transakcji i właściwe polecenia, natomiast w bloku CATCH sprawdza się, czy istnieje aktywna transakcja, a następnie wykonuje wycofanie. Po obsłudze błędu można ponownie zgłosić wyjątek za pomocą THROW, aby aplikacja lub osoba uruchamiająca skrypt otrzymała prawdziwą informację o problemie.

Przykładowy schemat logiczny może wyglądać następująco:

• rozpoczęcie transakcji;
• sprawdzenie warunków wstępnych, takich jak istnienie rekordu lub dostępność wymaganej wartości;
• wykonanie wszystkich powiązanych operacji;
• zatwierdzenie zmian po pozytywnym zakończeniu kontroli;
• wycofanie zmian w razie błędu;
• przekazanie informacji o przyczynie niepowodzenia.

Przed zatwierdzeniem przydatna może być funkcja XACT_STATE(), która pomaga ustalić, czy transakcja jest aktywna i czy można ją zatwierdzić, czy też pozostaje w stanie wymagającym wycofania. Znaczenie ma także ustawienie SET XACT_ABORT ON. W wielu scenariuszach powoduje ono automatyczne przerwanie i wycofanie transakcji po wystąpieniu błędu wykonania. Nie zastępuje ono jednak świadomej obsługi wyjątków, ponieważ nadal trzeba zadbać o rejestrowanie problemu, przekazanie komunikatu i poprawne zakończenie procedury.

Nie każdy komunikat ostrzegawczy ma taki sam skutek jak błąd uniemożliwiający wykonanie instrukcji. Dlatego testowanie powinno obejmować błędy składni, naruszenia ograniczeń, brak oczekiwanego rekordu, konflikt równoległych zmian oraz przerwanie połączenia. Trzeba też sprawdzić, czy procedura nie zatwierdza zmian po niepełnym wykonaniu wcześniejszego kroku.

Projektowanie transakcji T-SQL w procedurach i skryptach

Transakcja powinna obejmować możliwie mały, ale kompletny fragment pracy. Nie należy rozpoczynać jej zbyt wcześnie, na przykład przed długimi obliczeniami, pobieraniem danych niezwiązanych z modyfikacją albo oczekiwaniem na dane z zewnętrznego źródła. Długi czas trwania zwiększa ryzyko blokad, zakleszczeń i spadku wydajności. Najlepiej przygotować wartości potrzebne do operacji wcześniej, a następnie możliwie szybko wykonać zmianę i zamknąć transakcję.

Przed rozpoczęciem modyfikacji warto zweryfikować warunki, które decydują o poprawności operacji. Jeżeli skrypt ma zmienić konkretny rekord, należy ustalić, co ma się stać, gdy rekord nie istnieje albo gdy liczba zmienionych wierszy różni się od oczekiwanej. Weryfikacja liczby wierszy po UPDATE lub DELETE może wykryć zmianę danych przez inną sesję. Przy operacjach finansowych, magazynowych lub ewidencyjnych szczególnie istotne jest blokowanie i sprawdzanie danych w sposób zgodny z przyjętym poziomem izolacji.

Procedury korzystające z transakcji powinny jasno określać, kto zarządza zakresem transakcji. Problem może pojawić się wtedy, gdy procedura zawsze wykonuje COMMIT, mimo że została wywołana wewnątrz transakcji rozpoczętej przez kod nadrzędny. W bardziej złożonych rozwiązaniach wykorzystuje się licznik zagnieżdżenia transakcji, punkty zapisu oraz ustalone zasady odpowiedzialności za zatwierdzanie. Samo zagnieżdżenie nie oznacza jednak niezależnych transakcji, dlatego trzeba rozumieć, jak silnik interpretuje kolejne wywołania.

Do diagnozowania problemów przydają się dzienniki aplikacyjne, informacje o czasie trwania zapytań, obserwacja oczekiwań oraz analiza planów wykonania. Warto mierzyć nie tylko czas całej procedury, ale również poszczególne etapy. Dzięki temu można rozpoznać, czy przyczyną spowolnienia jest duża liczba zmienianych rekordów, brak właściwego indeksu, oczekiwanie na blokadę, zakleszczenie czy zbyt szeroki zakres transakcji.

Kiedy sprawdzić transakcje w skryptach T-SQL w praktyce

O przegląd transakcji warto zadbać, gdy w danych pojawiają się częściowo wykonane operacje, rozbieżności pomiędzy tabelami, nieoczekiwane blokady albo komunikaty o zakleszczeniach. Sygnałem ostrzegawczym są także procedury, które pozostawiają aktywną transakcję po zakończeniu działania, wykonują wiele niepowiązanych zadań w jednym zakresie lub wycofują zmiany bez zapisania przyczyny błędu. Analiza powinna objąć kod T-SQL, sposób wywoływania procedur, ustawienia połączeń oraz zachowanie aplikacji przy utracie połączenia.

Przydatne jest przygotowanie scenariuszy testowych obejmujących jednoczesne uruchomienie kilku sesji. Należy sprawdzić, czy dane pozostają spójne, czy druga sesja prawidłowo czeka albo otrzymuje kontrolowany błąd oraz czy po niepowodzeniu nie pozostają blokady. Test powinien uwzględniać również większy zakres danych, ponieważ skrypt działający poprawnie na kilku rekordach może zachowywać się inaczej przy masowej aktualizacji.

Jeżeli problem dotyczy komputera używanego do uruchamiania narzędzi bazodanowych, konfiguracji środowiska, połączenia lub działania skryptów, można skonsultować stan sprzętu i oprogramowania z firmą Warszawskie Pogotowie Komputerowe 24/7. Wcześniej warto zanotować treść komunikatu, moment wystąpienia problemu, nazwę używanego narzędzia oraz informację, czy błąd pojawia się przy każdej transakcji, czy tylko przy określonych danych.

Najważniejsze pozostaje rozdzielenie dwóch kwestii: poprawności logiki transakcji oraz sprawności środowiska, w którym skrypt jest uruchamiany. Dobrze zaprojektowana transakcja ma jasno określony początek i koniec, kontroluje błędy, nie przetrzymuje blokad dłużej niż potrzeba i pozostawia system w przewidywalnym stanie zarówno po sukcesie, jak i po wycofaniu zmian.