SET TRANSACTION ISOLATION LEVEL

SET TRANSACTION ISOLATION LEVEL określa poziom izolacji transakcji dla bieżącej sesji.

SET TRANSACTION ISOLATION LEVEL — czym jest poziom izolacji

SET TRANSACTION ISOLATION LEVEL to polecenie języka SQL służące do określenia, w jaki sposób bieżąca sesja ma postrzegać dane modyfikowane przez inne transakcje. Poziom izolacji wpływa na relację między spójnością odczytów a możliwością równoległego wykonywania operacji. Im silniejsza izolacja, tym mniejsze ryzyko odczytania danych w stanie pośrednim, ale tym większa szansa na blokowanie, oczekiwanie na zwolnienie zasobu albo ograniczenie współbieżności.

W praktyce polecenie nie zmienia samych rekordów i nie wykonuje operacji biznesowej. Ustawia reguły obowiązujące dla transakcji uruchamianych w danej sesji, czyli połączeniu z bazą danych. Składnia ma zwykle postać SET TRANSACTION ISOLATION LEVEL nazwa_poziomu, na przykład SET TRANSACTION ISOLATION LEVEL READ COMMITTED. Dostępne poziomy zależą od używanego silnika bazy danych, dlatego przed zastosowaniem konkretnej wartości należy sprawdzić dokumentację oraz ustawienia domyślne danego środowiska.

Najczęściej spotyka się poziomy READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ oraz SERIALIZABLE. Niektóre systemy obsługują również SNAPSHOT albo rozwiązania oparte na wersjonowaniu wierszy. Nazwy mogą wyglądać podobnie w różnych bazach, lecz szczegółowe zachowanie nie zawsze jest identyczne. Znaczenie ma między innymi sposób zakładania blokad, czas ich utrzymywania, używanie wersji danych oraz to, czy ustawienie działa do końca sesji, do końca transakcji czy tylko dla pojedynczej instrukcji.

SET TRANSACTION ISOLATION LEVEL a spójność i współbieżność

Najniższy poziom, READ UNCOMMITTED, dopuszcza odczyt danych, które zostały zmienione, ale nie zatwierdzone przez inną transakcję. Taki odczyt określa się jako brudny odczyt. Jeżeli druga transakcja wycofa zmiany, wcześniej pobrana wartość przestaje odpowiadać stanowi, który kiedykolwiek został trwale zapisany. Rozwiązanie bywa szybkie, ponieważ ogranicza oczekiwanie na blokady, jednak nadaje się wyłącznie do zastosowań, w których przybliżony obraz danych jest akceptowalny.

READ COMMITTED zabrania odczytu zmian niezatwierdzonych. Nadal jednak możliwe jest, że ta sama instrukcja albo kolejne instrukcje w ramach jednej transakcji zobaczą inną wartość, jeśli w międzyczasie inna transakcja zatwierdzi aktualizację. Taki przypadek nazywa się odczytem niepowtarzalnym. Dodatkowo ponowne wykonanie zapytania może zwrócić nowo dodane lub usunięte wiersze spełniające warunek, co określa się jako odczyt fantomowy. Jest to częsty kompromis między bezpieczeństwem danych a wydajnością.

REPEATABLE READ zapewnia, że ponowny odczyt tych samych wierszy w ramach jednej transakcji pozostanie zgodny z wcześniejszym wynikiem. Zależnie od silnika może to oznaczać dłuższe blokowanie rekordów albo korzystanie z wersji danych. Ochrona nie zawsze obejmuje każdy możliwy fantom, dlatego nie należy automatycznie zakładać, że ten poziom zapewnia pełną serializowalność.

SERIALIZABLE jest najsilniejszym standardowym poziomem izolacji. Jego założeniem jest uzyskanie efektu takiego, jak gdyby transakcje wykonywano jedna po drugiej, mimo że fizycznie mogą działać równolegle. Ogranicza to ryzyko niespójnych wyników, lecz może prowadzić do większej liczby blokad, zakleszczeń, opóźnień i błędów związanych z przekroczeniem czasu oczekiwania. Przy dużym obciążeniu wymaga starannego projektowania zapytań i krótkich transakcji.

SET TRANSACTION ISOLATION LEVEL — porównanie scenariuszy

Wyobrażenie dwóch równoległych transakcji ułatwia zrozumienie działania ustawienia. Pierwsza modyfikuje stan rachunku, ale jeszcze nie zatwierdza operacji. Druga próbuje odczytać ten sam rekord. Przy READ UNCOMMITTED może zobaczyć wartość tymczasową. Przy READ COMMITTED odczyt najczęściej zaczeka albo pobierze ostatnią zatwierdzoną wersję, zgodnie z mechanizmem zastosowanym przez silnik. Przy REPEATABLE READ późniejsze odczyty w tej samej transakcji powinny zachować stabilność już odczytanych danych. Przy SERIALIZABLE dodatkowo ograniczane są zmiany, które mogłyby wpłynąć na zbiór rekordów objętych warunkiem.

Ważne jest rozróżnienie między blokowaniem a wersjonowaniem. W modelu blokad odczyt może czekać na zakończenie zapisu, a zapis może czekać na zakończenie odczytu. W modelu wersjonowania odczyt może korzystać z wcześniejszej, zatwierdzonej wersji rekordu, dzięki czemu część operacji przebiega bez bezpośredniego wzajemnego blokowania. Nie oznacza to jednak braku kosztów. System musi przechowywać starsze wersje, sprzątać je oraz rozwiązywać sytuacje, w których dwie transakcje próbują zatwierdzić sprzeczne zmiany.

Nie powinno się wybierać najwyższego poziomu wyłącznie dlatego, że brzmi najbezpieczniej. Raport podsumowujący sprzedaż może tolerować niewielką różnicę między odczytem a chwilowym stanem tabeli, podczas gdy rezerwacja ograniczonego zasobu wymaga kontroli, aby dwie operacje nie przyznały tego samego elementu. Z kolei proces aktualizujący wiele powiązanych rekordów powinien mieć jasno zdefiniowane granice transakcji, kolejność operacji i sposób obsługi konfliktu.

Warto też sprawdzić zakres ustawienia. W jednej bazie polecenie może obowiązywać do zamknięcia połączenia, a w innej konfiguracji może być zastępowane przez ustawienie na poziomie pojedynczej transakcji. Pula połączeń dodatkowo sprawia, że sesja może zostać użyta ponownie przez inną operację aplikacji. Bez jawnego przywracania oczekiwanej konfiguracji powstają trudne do odtworzenia różnice między kolejnymi żądaniami.

SET TRANSACTION ISOLATION LEVEL — typowe problemy diagnostyczne

Jednym z częstych problemów jest założenie, że ustawienie izolacji rozwiąże każdy błąd związany ze współbieżnością. Jeżeli transakcja obejmuje zbyt wiele instrukcji, utrzymuje zasoby przez długi czas albo pobiera niepotrzebnie duży zakres danych, nawet rozsądny poziom może powodować opóźnienia. Pomaga ograniczenie zakresu transakcji, poprawa warunków wyszukiwania, właściwe indeksowanie oraz pobieranie tylko niezbędnych kolumn i wierszy.

Innym problemem są zakleszczenia. Powstają wtedy, gdy jedna transakcja trzyma zasób potrzebny drugiej, a druga trzyma zasób potrzebny pierwszej. Zmiana poziomu izolacji czasami zmniejsza prawdopodobieństwo takiej sytuacji, lecz nie zastępuje poprawnej kolejności dostępu do tabel i rekordów. Aplikacja powinna mieć procedurę ponowienia operacji po kontrolowanym błędzie zakleszczenia, z ograniczeniem liczby prób i odpowiednim logowaniem.

Trudności może sprawiać również mieszanie ustawień. Część kodu może ustanawiać poziom sesji, część poziom transakcji, a biblioteka dostępu do danych może samodzielnie otwierać i zamykać transakcje. W takiej sytuacji należy prześledzić rzeczywistą kolejność poleceń, a nie tylko deklaracje znajdujące się w konfiguracji. Przydatne jest rejestrowanie identyfikatora połączenia, czasu rozpoczęcia i zakończenia transakcji, poziomu izolacji oraz informacji o oczekiwaniu na blokadę.

Nieprawidłowe wyniki mogą też wynikać z błędnego testu. Odczyt uruchamiany po zatwierdzeniu zmian nie sprawdza zachowania podczas konfliktu. Do wiarygodnej próby potrzebne są co najmniej dwa równoległe połączenia, kontrolowane opóźnienie między instrukcjami oraz scenariusze obejmujące zatwierdzenie i wycofanie transakcji. Należy obserwować nie tylko zwrócone wartości, lecz także czas wykonania, liczbę oczekujących operacji i ewentualne konflikty.

SET TRANSACTION ISOLATION LEVEL — praktyczne wskazówki użycia

Przed ustawieniem poziomu izolacji warto opisać, jaki błąd ma zostać wyeliminowany. Jeżeli problemem jest odczyt niezatwierdzonych danych, należy wykluczyć READ UNCOMMITTED. Jeżeli wymagane jest powtarzalne odczytanie tych samych rekordów, trzeba ocenić REPEATABLE READ albo mechanizm wersjonowania. Jeżeli cała operacja musi zachowywać się tak, jak pojedyncza kolejka zmian, można rozważyć SERIALIZABLE, ale dopiero po sprawdzeniu wpływu na obciążenie.

Transakcje powinny być możliwie krótkie i obejmować wyłącznie operacje, które muszą zostać wykonane atomowo. Nie należy wykonywać w ich środku działań niezwiązanych z bazą, długiego przetwarzania plików ani oczekiwania na dane od użytkownika. Każda dodatkowa sekunda może wydłużać czas utrzymywania blokad lub starszych wersji rekordów.

Przydatne jest testowanie wariantów na danych zbliżonych do rzeczywistego obciążenia. Mały zbiór testowy może ukryć problemy, które pojawią się dopiero przy wielu równoległych operacjach. Po zmianie ustawienia należy porównać poprawność wyników, czas odpowiedzi, liczbę blokad, zużycie zasobów oraz częstość ponowień. Sam fakt, że zapytanie zwraca oczekiwane dane, nie wystarcza do oceny jakości rozwiązania.

Jeżeli pojawiają się nagłe zawieszenia aplikacji, niespójne wyniki odczytu, błędy zatwierdzania albo duże różnice między środowiskiem testowym i użytkowym, należy zebrać logi oraz sprawdzić konfigurację połączeń. W przypadku problemów ze sprzętem, który może wpływać na stabilność pracy aplikacji bazodanowej, pomoc techniczna Warszawskiego Pogotowia Komputerowego 24/7 obejmuje naprawy z reguły realizowane do 3 dni roboczych.

SET TRANSACTION ISOLATION LEVEL — kiedy potrzebna jest konsultacja

Konsultacja jest zasadna, gdy nie da się ustalić, czy źródłem problemu jest poziom izolacji, blokady, indeksy, błędna logika transakcji czy niestabilne działanie komputera. Przed zgłoszeniem warto przygotować nazwę silnika bazy danych, fragment używanego kodu, opis kolejności operacji, czas wystąpienia problemu oraz komunikaty z dzienników. Nie powinno się przekazywać haseł ani pełnych kopii danych zawierających informacje poufne.

Weryfikacja może obejmować sprawdzenie ustawień sesji, sposobu otwierania transakcji, czasu ich trwania, obciążenia pamięci i procesora oraz stabilności nośnika danych. Dopiero po połączeniu tych informacji można ocenić, czy zmiana SET TRANSACTION ISOLATION LEVEL będzie właściwym rozwiązaniem, czy też potrzebna jest korekta zapytań, konfiguracji albo środowiska pracy. Dzięki temu ogranicza się ryzyko przypadkowego obniżenia spójności danych lub zwiększenia blokowania bez usunięcia przyczyny.