Rozłączone sesje a długotrwałe blokady

Transakcyjność zgodna z założeniami wyrażonymi w popularnym akronimie ACID (Atomicity, Consistency, Isolation, Durability) w warunkach współbieżności i konkurencyjności o zasoby wymaga od silnika bazodanowego odpowiedniej obsługi blokad zasobów. Nie inaczej jest w przypadku produktu SQL Server, który implementuje dynamiczny mechanizm blokad o zmiennej ziarnistości (granularności).

MSSQL jest w stanie nakładać blokady różnego typu (m.in. współdzielona - S, wyłączna - X) na zasoby określonych rozmiarów - wiersz, klucz indeksu, stronę danych, także tabelę, czy nawet całą bazę danych. Istotne jest to, że im mniejsza ziarnistość blokad (lock granularity), tym mniejsze obiekty są blokowane i tym bardziej jest to kosztowne. Naturalnie nałożenie i obsługa kilkunastu tysięcy blokad na poziomie wierszy czy stron będzie dla serwera o wiele większym problemem, niż zablokowanie całej tabeli (czyli tzw. eskalacja blokady). Z drugiej strony, na zwiększeniu ziarnistości blokad (blokowanie dużych obiektów) znacznie ucierpi konkurencyjność - procesy będą oczekiwać na odpowiedni dostęp do zasobu. Ziarnistość blokad jest zawsze pewnym kompromisem między wydajnością a pragmatyką w środowisku współbieżnego przetwarzania. Warto mieć w świadomości fakt, że w momencie wzrostu obciążenia (braku zasobów sprzętowych) SQL Server zacznie blokować coraz większe obiekty, co automatycznie przełoży się na pogorszenie warunków konkurencyjnej pracy.

Blokady przyznawane są poszczególnym sesjom. Zwolnienie blokady następuje zasadniczo w momencie zatwierdzenia lub wycofania transakcji, w której uczestniczy blokowany zasób. SQL Server nie zna ograniczeń maksymalnego czasu trwania transakcji, także blokady mogą wisieć w najlepsze np. przez kilkanaście godzin. Jak widać, w całej opowieści o blokowaniu wiele zależy od momentu wypowiedzenia magicznych słów COMMIT TRAN (ROLLBACK TRAN).

Jeżeli chodzi o transakcje, to warto zaznaczyć, że bazy danych wypracowały dwa podstawowe modele zarządzania transakcjami - obsługa po stronie klienta (client side transaction processing) oraz obsługa po stronie serwera (server side transaction processing). Osobiście jestem wielkim przeciwnikiem pierwszego modelu. Skoro od transakcji zależeć może praktyczne działanie naszego systemu (dostępność tabel i innych obiektów), nie powinno się projektować rozwiązań, które zarządzanie tego typu procesami umiejscawiają poza logiką znajdującą się na serwerze. Bezpośredni argument na poparcie tego stwierdzenia przedstawię poniżej.

Dla całości obrazu konieczne jest omówienie jeszcze jednego problemu, z którym boryka się SQL Server. Chodzi o kwestię osieroconych (rozłączonych) sesji (orphaned sessions). Osieroconą sesją jest sesja, która pozostaje otwarta na serwerze, pomimo że klient nie jest już podłączony. Powodów takiej sytuacji może być kilka - od problemów sieciowym (awaria łącza), przez problemy z systemem operacyjnym klienta (pad systemu), po problemy samej aplikacji (zawieszenie się wątku). Co istotne, SQL Server sam nie sprawdza istnienia partnera na drugim końcu połączenia - w tym zakresie polega na systemie operacyjnym. Windows NT, bo o nim mowa, za pomocą odpowiedniego IPC (np. TCP) może okresowo potwierdzać, czy dane połączenie jest jeszcze aktywne - zależnie od wersji systemu odbywa się to albo stosunkowo rzadko, albo w ogóle sprawdzanie takie nie jest domyślnie włączone. Mechanizm weryfikacji polega na wysyłaniu pakietów KeepAlive, co w powszechnej opinii nie jest 100% skuteczne i ma sporą grupę przeciwników twierdzących, że metoda ta jest już definitywnie skompromitowana. Dodam tylko na pocieszenie, ze SQL Server 2005, w przeciwieństwie do poprzednich wersji, domyślnie włącza KeepAlive dla swoich połączeń - przy pewnej (czasem niemałej) dozie szczęścia rozłączone sesje zostaną wykryte po około 35 sekundach.

Składając wyżej opisane kwestie w jedno podsumowujące stwierdzenie można powiedzieć, że jeżeli nastąpi osierocenie sesji będącej w transakcji otwartej po stronie klienta, która przykładowo trzymała przyznaną w warunkach dużego obciążenia blokadę wyłączna na całą tabelę, to może pojawić się spory problem. Skoro nie można polegać na metodzie KeepAlive (a według mnie - nie można), to potencjalnie blokada w sposób automatyczny nie zostanie zdjęta nigdy. Jeżeli dotyczyła jakiegoś zasobu o podstawowym znaczeniu (np. blokada wyłączna na całą tabelę kolejki), to nasz system może zostać w prosty sposób sparaliżowany.

Według mnie jedynym rozsądnym wyjściem z opisanej sytuacji jest zrezygnowanie z modelu zarządzania transakcjami po stronie klienta. Jest to możliwe w większości przypadków - choć zaznaczam, że nie we wszystkich (wtedy zostaje KeepAlive i o ile to możliwe - przejście na wersję SQL Server 2005). Zamiast pozwalać klientowi otwierać transakcję, wykonywać np. dwie operacje UPDATE, następnie zatwierdzać transakcję - powinniśmy udostępnić odpowiedni interfejs do zrealizowania tej samej funkcjonalności. Chodzi o przygotowanie procedury składowanej, którą klient wywoła z odpowiednimi parametrami, a która transakcyjnie i bezpiecznie obsłuży opisaną modyfikację danych. W takim modelu nie istnieje niebezpieczeństwo wynikające z rozłączenia sesji, gdyż nawet jeśli się to stanie w chwilę po przesłaniu żądania RPC z wywołaniem procedury, SQL Server i tak dokończy operację zgodnie z logiką zawartą w procedurze. Nawet w takim skrajnym przypadku wszystkie blokady zostaną zdjęte, a jedyne, co faktycznie nie nastąpi - to zwrócenie informacji o wyniku.

Więcej informacji:
KB137983: How to troubleshoot orphaned connections in SQL Server
MSDN Blogs: Understand special TCP/IP property “Keep Alive” in SQL Server 2005
BOL 2000: Orphaned Sessions