Ćwiczenia
> Transakcje w MS SQL Server 2000 > Zapobieganie zakleszczaniu się transakcji |
![]() ![]() |
||||||||||
6.4 Zapobieganie zakleszczaniu się transakcji![]() Transakcje mogą ulegać blokowaniu (ang. blokowanie). Wzajemne blokowanie dwóch lub więcej transakcji nazywamy zakleszczeniem (ang. deadlock). Blokady najłatwiej jest obsługiwać (likwidować) przy użyciu konsoli programu Enterprise Manager. W niniejszej lekcji zobaczysz przykład blokowania jednej transakcji przez drugą i rozwiązania takiego problemu. Dowiesz się także, jak SQL Server rozwiązuje problemy zakleszczania. Krok 1 - Zablokuj transakcję przy pomocy innej transakcji1. Uruchom program Query Analyzer.2. Zaloguj się używając uwierzytelnienia systemu Windows. 3. W menu głównym programu wybierz File - Open. 4. W oknie Open Query File wybierz plik demo_6_4_1.sql. 5. Uruchom skrypt (klawisz F5).
Powyższy skrypt jest przykładem jawnie otwartej transakcji, która jednak nie zostaje wykonana (brak COMMIT TRAN), ale jednocześnie nie zostaje cofnięta (brak ROLLBACK TRAN - transakcja "wisi" na serwerze blokując zasoby). Operacją, która w zamyśle użytkownika ma być wykonana w transakcji jest modyfikacja wartości w kolumnie ContactName w tabeli Customers (baza danych Northwind) wszędzie tam, gdzie w kolumnie CustomerID znajduje się wartość ANATR. Dodatkowo skrypt ten wyświetla informacje o blokadach (procedura składowana sp_lock) dla wszystkich procesów serwera: spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 52 6 0 0 DB S GRANT 52 6 2073058421 0 TAB IX GRANT 52 6 2073058421 1 KEY (d4002255fc59) X GRANT 52 6 2073058421 1 PAG 1:391 IX GRANT 52 1 85575343 0 TAB IS GRANTJak należy odczytać powyższy wynik: - kolumna spid zawiera identyfikatory procesów SQL Servera (połączeń), - kolumna dbid zawiera identyfikatory baz danych (1 - to zapewne baza danych master, zaś pod 6 z pewnością kryje się baza Northwind, z której korzystamy), - kolumna ObjId zawiera identyfikatory blokowanych obiektów baz danych (tabel itd.), - kolumna IndId zaiera identyfikatory indeksów, - kolumna Type mówi o zakresie blokady, - kolumna Resource zawiera informacje o zasobach blokowanych, które odpowiadają wartościom w tabeli syslockinfo w kolumnie restext, - kolumna Mode zawiera informacje o typie blokady, - kolumna Status informuje, czy blokada została ustawiona (GRANT), czy może blokuje inny proces (WAIT), lub może jest konwertowana na inną blokadę (CNVT). Zasięg blokady jest opisywany przez następujące skróty: DB = Database (baza danych), FIL = File (plik), IDX = Index (indeks), PG = PAGE (strona danych), KEY = Key (klucz), TAB = Table (tabela), EXT = Extent (zakres), RID = Row identifier (identyfikator wiersza). Typ blokady jest opisywany przez następujące skróty: S = Shared (używane do operacji nie modyfikujących danych, np. odczytu, żadna inna transakcja nie może zmodyfikować zasobu, na którym jest blokada tego typu), U = Update (używane z zasobami, które mogą być modyfikowane, zapobiegają zakleszczeniom), X = Exclusive (używane z poleceniami INSERT, UPDATE i DELETE, zapobiegają jednoczesnemu modyfikowaniu tych samych danych przez wiele transakcji), IS = Intent Shared (wskazuje na próby odczytu przez transakcję pewnych, ale nie wszystkich, zasobów znajdujących się niżej w hierarchii, poprzez umieszczenie na tych zasobach blokad typu S - np. nałożenie na tabelę blokady IS mówi o możliwej próbie odczytu niektórych stron tabeli), IX = Intent Exclusive (j.w. z tym, że próby modyfikacji danych przez nałożenie blokad typu X), SIX = Shared with Intent Exclusive (wskazuje na próby odczytu przez transakcję nie wszystkich zasobów znajdujących się niżej w hierarchii oraz próby modyfikacji wybranych, lecz nie wszystkich, z tych zasobów), Sch-M = Schema Modification (używane, gdy wykonywane są polecenia języka DDL dotyczące tabel), Sch-S = Schema Stability (używane, gdy zapytanie jest kompilowane, zapobiegają modyfikacjom tabel w tym czasie, nie blokują innych transakcji), BU = Bulk Update (używane podczas operacji szybkiego wstawiania danych do tabel, pozwalają na jednoczesne wykonywanie wielu operacji szybkiego wstawiania, ale blokują dostęp do zasobów innym operacjom). 6. W menu głównym programu wybierz File - Connect.... 7. Zaloguj się używając uwierzytelnienia systemu Windows. 8. W menu głównym programu wybierz File - Open. 9. W oknie Open Query File wybierz plik demo_6_4_2.sql. 10. Uruchom skrypt (klawisz F5).
Powyższy skrypt jest jak widać powtórzeniem skryptu pierwszego, ale tym razem dopisano zakończenie transakcji (COMMIT TRAN). Okazuje się jednak, że skrypt zostaje uruchomiony i nie kończy pracy ("zawiesza się"). Dzieje się tak dlatego, że w tle ciągle działa transakcja z pierwszego skryptu, która blokuje dostęp do zasobów (konkretnie do tabeli Customers). Krok 2 - Odblokuj drugą transakcję wycofując pierwszą1. Uruchom program Enterprise Manager.2. Rozwijaj drzewo po lewej stronie okna programu i wejdź do folderu Management. 3. W folderze Management wejdź w pozycję Current Activity - Locks / Process ID. Po prawej stronie okna Enterprise Managera zobaczysz ikony procesów, z których dwie będą oznaczone na czerwono Pod jednym procesem zobaczysz informację o tym, że jest on procesem blokującym, pod drugim, że jest on procesem blokowanym. ![]() Rys. 6.4.1 Enterprise Manager - blokowanie między procesami 4. Kliknij prawym przyciskiem myszy po prawej stronie okna na ikonie procesu blokującego (z podpisem Blocking) i z menu kontekstowego wybierz opcję Właściwości. ![]() Rys. 6.4.2 Enterprise Manager - właściwości blokującego procesu 5. W oknie Process Details obejrzyj kod blokującej transakcji, a następnie kliknij przycisk Send Message.
6. W oknie Send Message w polu tekstowym Message wpisz Proszę zamknąć transakcję, a następnie kliknij Send. ![]() Rys. 6.4.3 Enterprise Manager - wysyłanie wiadomości do właściciela procesu 7. Przeczytaj wiadomość w oknie Usługa Posłaniec i kliknij OK. W ten sposób poinformowaliśmy właściciela procesu o nieprawidłowości w działaniu jego transakcji. ![]() Rys. 6.4.4 Wiadomość informująca o blokującej transakcji 8. Zamknij okna Send Message oraz Process Details. 9. Wróć do okna programu Query Analyzer i z menu głównego programu wybierz Window. Na dole menu wybierz pozycję zawierającą ścieżkę do pliku demo_6_4_1.sql. 10. Przełącz się na zakładkę Editor. Zaznacz i uruchom (F5) kod cofnięcia transakcji - ROLLBACK TRAN. Zauważ, że transakcja zakończyła swoje działanie (aczkolwiek nie dokonała żadnych zmian w bazie danych). 11. Korzystając z menu Window przełącz się do kodu skryptu demo_6_4_2.sql. Zauważ, że skrypt zakończył swoje działanie (wcześniej był blokowany). 12. Wróć do okna programu Enterprise Manager. Kliknij prawym przyciskiem myszy na pozycji Current Activity w lewej części okna i wybierz z menu kontekstowego opcję Odśwież. 13. Wejdź w pozycję Current Activity - Locks / Process ID. Zauważ, że tym razem nie ma już informacji o blokowaniu procesów. Udało nam się odblokować proces poprzez zamknięcie transakcji blokującej zasoby.
Krok 3 - Jak SQL Server rozwiązuje problem zakleszczeńMS SQL Server wykrywa zakleszczenia automatycznie i automatycznie rozwiązuje ich problem. Gdy serwer zidentyfikuje zakleszczenie, wybiera automatycznie wątek, którego zamknięcie może zlikwidować zakleszczenie. Taki wątek nazywa się potocznie ofiarą zakleszczenia (ang. deadlock victim). Transakcja ofiary jest wycofywana (ROLLBACK TRAN), zaś do klienta zwracana jest informacja o błędzie (numer 1205). Wycofywane są także żądania klienta, zaś pozostałe transakcje (nieblokujące) kontynuują działanie.Zazwyczaj MS SQL Server wybiera jako ofiarę zakleszczenia transakcję, której koszt wycofania jest najniższy. Uzytkownik ma jednak możliwość ustawienia priorytetu zakleszczeń dla sesji. Np. pisząc: SET DEADLOCK_PRIORITY LOWustawiasz priorytet bieżącej sesji (i wszystkich prowadzonych w niej transakcji) na niski. W takim przypadku Twoja sesja staje się potencjalną ofiarą ewentualnego zakleszczenia z udziałem jednej z Twoich transakcji. Poziom priorytetu może być następujący: LOW (odpowiada mu liczba 3), NORMAL (odpowiada mu liczba 6) lub określony dowolnym pojedynczym znakiem (zazwyczaj wystarczają dwa poziomy LOW i NORMAL, ale można ich ustawić więcej).
Przejdź dalej ![]() |
|||||||||||
![]() Ćwiczenia > Transakcje w MS SQL Server 2000 > Zapobieganie zakleszczaniu się transakcji |