COMMIT / ROLLBACK

COMMIT / ROLLBACK to polecenia Transact-SQL odpowiednio zatwierdzające lub wycofujące wszystkie operacje wykonane w ramach transakcji.

COMMIT i ROLLBACK — czym są w Transact-SQL

COMMIT i ROLLBACK należą do podstawowych poleceń kontroli transakcji w języku Transact-SQL. Transakcja jest logicznym zestawem operacji na danych, który powinien zostać potraktowany jako jedna całość. W jej trakcie można wykonać kilka instrukcji, na przykład dodać rekord, zmienić powiązane wartości i usunąć dane pomocnicze. Do momentu zatwierdzenia wynik takich działań może pozostawać odseparowany od innych użytkowników i procesów, zależnie od ustawień izolacji oraz rodzaju używanych obiektów.

COMMIT służy do potwierdzenia poprawnego zakończenia transakcji. Po jego wykonaniu zmiany zostają zatwierdzone i stają się trwałą częścią stanu bazy danych. ROLLBACK pełni funkcję odwrotną: pozwala anulować operacje wykonane od początku transakcji albo od wybranego punktu oznaczonego jako punkt zapisu. Nie chodzi więc wyłącznie o cofnięcie ostatniej instrukcji. Przy pełnym wycofaniu anulowany zostaje cały zestaw niezatwierdzonych zmian należących do danej transakcji.

Największe znaczenie tych poleceń ujawnia się wtedy, gdy kilka operacji musi zakończyć się razem. Przykładem może być przeniesienie środków między dwoma kontami: zmniejszenie salda jednego konta i zwiększenie salda drugiego powinno zostać wykonane jako spójna operacja. Jeżeli druga część działania nie powiedzie się, ROLLBACK umożliwia wycofanie pierwszej zmiany. Dzięki temu nie pozostaje stan, w którym jedna strona operacji została zapisana, a druga nie.

COMMIT / ROLLBACK a spójność i porównania transakcji

Mechanizm COMMIT i ROLLBACK wiąże się z właściwościami transakcji określanymi skrótem ACID. Atomowość oznacza, że zestaw działań jest traktowany jako całość: wszystko zostaje zapisane albo wszystko zostaje wycofane. Spójność wymaga, aby po zakończeniu transakcji dane nadal spełniały reguły określone przez schemat, ograniczenia i relacje. Izolacja opisuje wpływ niezatwierdzonych zmian na równolegle wykonywane transakcje, a trwałość oznacza zachowanie zatwierdzonych danych mimo późniejszego zakończenia połączenia.

Transakcję można porównać do roboczego dokumentu, który dopiero po akceptacji trafia do wersji obowiązującej. COMMIT przypomina zatwierdzenie całego dokumentu, natomiast ROLLBACK przywraca stan sprzed rozpoczęcia edycji. Taka analogia pomaga odróżnić transakcję od pojedynczego polecenia SQL. Jedna instrukcja może obejmować wiele zmian, a kilka instrukcji może być połączonych w jedną transakcję. O tym, jaki zakres zostanie zatwierdzony lub wycofany, decyduje granica transakcji oraz sposób zarządzania punktami zapisu.

W Transact-SQL spotyka się transakcje jawne, rozpoczęte na przykład przez BEGIN TRANSACTION, oraz transakcje sterowane ustawieniem autocommit. W trybie automatycznego zatwierdzania pojedyncze instrukcje są zazwyczaj kończone osobno. Przy bardziej złożonych operacjach potrzebna jest kontrola ręczna, ponieważ dopiero wtedy można połączyć kilka poleceń w jeden logiczny etap i zdecydować, czy należy użyć COMMIT, czy ROLLBACK. Należy też pamiętać, że zachowanie transakcji może zależeć od błędu, poziomu izolacji, blokad i ustawień sesji.

Odrębną rolę pełnią punkty zapisu. SAVE TRANSACTION pozwala oznaczyć miejsce, do którego można wrócić bez wycofywania całej transakcji. ROLLBACK TRANSACTION nazwa_punktu powoduje cofnięcie zmian wykonanych po wskazanym punkcie, ale nie kończy całej transakcji. Dopiero pełne ROLLBACK zamyka ją przez anulowanie wszystkich pozostających zmian, a COMMIT zatwierdza zakres, który nadal jest aktywny.

COMMIT i ROLLBACK przy typowych problemach

Problemy z transakcjami często wynikają z braku jednoznacznej obsługi błędów. Jeżeli jedna instrukcja zakończy się błędem, sama obecność komunikatu nie zawsze oznacza, że wszystkie wcześniejsze operacje zostały automatycznie anulowane. Sposób działania zależy między innymi od rodzaju błędu i ustawień sesji. Dlatego w kodzie wykonywanym na produkcyjnych danych stosuje się konstrukcje kontrolne, które sprawdzają stan transakcji i w razie potrzeby wywołują ROLLBACK.

Typowy wzorzec wykorzystuje TRY...CATCH. W bloku TRY umieszcza się operacje składające się na transakcję, a w bloku CATCH analizuje się błąd oraz stan transakcji. Jeżeli transakcja jest aktywna i może zostać wycofana, wykonywany jest ROLLBACK. Następnie błąd może zostać przekazany dalej za pomocą THROW, aby aplikacja nie uznała niepełnej operacji za zakończoną prawidłowo. Na końcu bloku TRY umieszcza się COMMIT tylko wtedy, gdy wszystkie wymagane działania zakończyły się poprawnie.

Jednym z częstszych skutków niewłaściwego zarządzania transakcją jest pozostawienie otwartej transakcji. Taka sytuacja może utrzymywać blokady, zwiększać rozmiar dziennika transakcji i opóźniać pracę innych operacji. Przyczyną bywa przerwane połączenie, błąd aplikacji, ręczne uruchomienie skryptu bez końcowego COMMIT albo obsługa wyjątku, w której pominięto ROLLBACK. W diagnostyce analizuje się aktywne sesje, czas trwania transakcji, blokady oraz polecenia oczekujące na zakończenie.

Nie powinno się także obejmować jedną transakcją niepotrzebnie dużej liczby działań. Długa transakcja może długo utrzymywać blokady i powodować wzrost dziennika. Z drugiej strony zbyt wczesny COMMIT może doprowadzić do zapisania tylko części operacji, jeśli kolejne działania nadal są konieczne do zachowania spójności. Zakres transakcji dobiera się więc do rzeczywistej jednostki pracy, a nie wyłącznie do wygody zapisu kodu.

COMMIT / ROLLBACK — praktyczne wskazówki dla skryptów SQL

Przed wykonaniem zmian na większej liczbie rekordów warto rozpocząć transakcję, sprawdzić działanie zapytania i dopiero potem zdecydować o zatwierdzeniu. W środowisku testowym można najpierw użyć SELECT do zweryfikowania liczby i zakresu rekordów, a następnie wykonać UPDATE lub DELETE w ramach transakcji. Jeżeli wynik okaże się niezgodny z założeniem, ROLLBACK pozwoli powrócić do wcześniejszego stanu, o ile zmiany nadal nie zostały zatwierdzone.

Pomocne bywa dodanie warunku kontrolnego, który sprawdza liczbę zmienionych wierszy. Gdy oczekiwano modyfikacji niewielkiego, określonego zbioru rekordów, a zapytanie obejmie znacznie szerszy zakres, można przerwać operację i wykonać ROLLBACK. Taki mechanizm ogranicza ryzyko skutków błędnego warunku WHERE. W skryptach administracyjnych przydatne jest również czytelne oznaczenie początku i końca transakcji oraz zapisanie informacji o przyczynie wycofania.

Warto unikać polegania na przypadkowym stanie narzędzia do obsługi bazy danych. Niektóre programy mają opcję automatycznego zatwierdzania, inne pozwalają ręcznie przełączać tryb transakcyjny. Przed uruchomieniem skryptu należy sprawdzić, czy COMMIT zostanie wykonany automatycznie, czy też wymagane będzie jawne potwierdzenie. Szczególną ostrożność zachowuje się przy poleceniach DDL, zmianach schematu oraz operacjach, których zachowanie transakcyjne może różnić się od zwykłych INSERT, UPDATE i DELETE.

Dobry skrypt powinien być możliwy do prześledzenia po kilku tygodniach. Czytelne nazwy punktów zapisu, obsługa wyjątków, kontrola liczby zmienionych rekordów i jednoznaczne miejsce wykonania COMMIT ułatwiają późniejszą analizę. ROLLBACK nie zastępuje kopii zapasowej. Cofnięcie dotyczy bieżącej, niezatwierdzonej transakcji, natomiast odzyskiwanie danych z wcześniejszego momentu wymaga odpowiedniej strategii kopii oraz dzienników.

COMMIT i ROLLBACK — kiedy zgłosić problem do serwisu

Do konsultacji warto zgłosić się wtedy, gdy aplikacja zgłasza błędy transakcji, operacje pozostają długo zablokowane, dane po awarii są niespójne albo nie wiadomo, czy ostatnie zmiany zostały zatwierdzone. Sygnałem ostrzegawczym jest także powtarzające się ręczne zamykanie procesów, po którym inne zapytania przestają odpowiadać. Przed dalszymi próbami modyfikacji dobrze zabezpieczyć dostępne kopie, opisać kolejność zdarzeń i zanotować komunikaty błędów.

W ramach wsparcia można przeanalizować konfigurację środowiska, sposób obsługi wyjątków, stan aktywnych transakcji i zależności między aplikacją a bazą danych. Przydatne jest rozdzielenie problemu z samym poleceniem SQL od problemu z połączeniem, blokadami, uprawnieniami lub urządzeniem, na którym działa oprogramowanie. W Warszawie takie sprawy można skonsultować z firmą Warszawskie Pogotowie Komputerowe 24/7, przekazując możliwie dokładny opis objawów oraz informację, jakie operacje poprzedziły wystąpienie błędu.

Do czasu wyjaśnienia sytuacji nie należy wielokrotnie powtarzać tego samego skryptu bez sprawdzenia stanu danych. Ponowne wykonanie częściowo zakończonej operacji może dopisać duplikaty albo zmienić rekordy, które wcześniej zostały już zatwierdzone. Bezpieczniejsze jest ustalenie granicy transakcji, zweryfikowanie wyniku zapytań i dopiero późniejsze użycie COMMIT lub ROLLBACK zgodnie z rozpoznanym stanem. Takie postępowanie ogranicza ryzyko dalszych zmian i ułatwia odtworzenie przebiegu zdarzeń.