Opisywanie blokowania i zamykania

  • 12 min

Blokowanie to kluczowa funkcja relacyjnych baz danych, niezbędna do utrzymania niepodzielności, spójności i właściwości izolacji modelu ACID. Wszystkie pakiety RDBMS blokują akcje, które naruszają spójność i izolację zapisów bazy danych. Programiści SQL muszą rozpoczynać i kończyć transakcje w odpowiednich punktach, aby zapewnić spójność danych. Silnik bazy danych zapewnia mechanizmy blokowania w celu ochrony logicznej spójności dotkniętych tabel, co jest podstawą modelu relacyjnego.

W programie SQL Server blokowanie występuje, gdy jeden proces utrzymuje blokadę na określonym zasobie (wiersz, strona, tabela, baza danych), a drugi proces próbuje uzyskać blokadę z niekompatybilnym typem blokady na tym samym zasobie. Zazwyczaj blokady są utrzymywane przez krótki okres, a gdy proces posiadający blokadę zwalnia ją, zablokowany proces może uzyskać blokadę i zakończyć swoją transakcję.

Program SQL Server blokuje najmniejszą ilość danych potrzebnych do ukończenia transakcji, co pozwala na maksymalną współbieżność. Jeśli na przykład program SQL Server zablokuje pojedynczy wiersz, wszystkie inne wiersze w tabeli pozostaną dostępne dla innych procesów, co umożliwia współbieżną pracę. Jednak każda blokada wymaga zasobów pamięci, więc nie jest opłacalna dla jednego procesu przechowywania tysięcy pojedynczych blokad w jednej tabeli. Aby zrównoważyć współbieżność z kosztami, program SQL Server używa techniki nazywanej eskalacją blokady. Jeśli w jednej instrukcji musi być zablokowanych więcej niż 5000 wierszy w jednym obiekcie, program SQL Server eskaluje wiele blokad wierszy do pojedynczej blokady tabeli.

Blokowanie jest normalnym zachowaniem i występuje często przez cały dzień. Staje się to problematyczne tylko wtedy, gdy powoduje blokowanie, które nie jest szybko rozwiązane. Istnieją dwa typy problemów z wydajnością spowodowane blokowaniem:

  • Proces przechowuje blokady w zestawie zasobów przez dłuższy czas przed ich udostępnieniem, powodując, że inne procesy blokują i obniżają wydajność zapytań i współbieżność.
  • Proces uzyskuje blokady na zestawie zasobów i nigdy ich nie zwalnia, co wymaga interwencji administratora w celu rozwiązania problemu.

Zakleszczenie to inny scenariusz blokowania, który występuje, gdy jedna transakcja posiada blokadę zasobu, a inna transakcja posiada blokadę w innym zasobie. Każda transakcja następnie próbuje uzyskać blokadę zasobu, który jest obecnie zablokowany przez inną transakcję, co prowadzi do nieskończonego oczekiwania, ponieważ żadna transakcja nie może zostać ukończona. Aparat programu SQL Server wykrywa te scenariusze i rozwiązuje impas, zabijając jedną z transakcji, na podstawie której transakcja wykonała najmniejszą ilość pracy, która musi zostać wycofana. Transakcja, która zginęła, jest znana jako ofiara zakleszczenia. Zakleszczenia są rejestrowane w system_health rozszerzonej sesji zdarzeń, która jest domyślnie włączona.

Ważne jest, aby zrozumieć koncepcję transakcji. Tryb Autocommit jest domyślnym trybem w SQL Server i Azure SQL Database, co oznacza, że zmiany wprowadzone przez następującą instrukcję zostaną automatycznie zarejestrowane w dzienniku transakcji bazy danych.

INSERT INTO DemoTable (A) VALUES (1);

Aby umożliwić deweloperom bardziej szczegółową kontrolę nad kodem aplikacji, program SQL Server umożliwia również jawne kontrolowanie transakcji. Poniższe zapytanie spowoduje zablokowanie wiersza w tabeli DemoTable, które nie zostanie usunięte do czasu zatwierdzenia transakcji odpowiednim poleceniem.

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

Odpowiedni sposób na napisanie następującego zapytania jest następujący:

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

Polecenie COMMIT TRANSACTION jawnie zapisuje rekord zmian w dzienniku transakcji. Zmienione dane ostatecznie przejdą do pliku danych asynchronicznie. Te transakcje reprezentują jednostkę pracy aparatu bazy danych. Jeśli deweloper zapomni wydać COMMIT TRANSACTION polecenie, transakcja pozostanie otwarta, a blokady nie zostaną zwolnione. Jest to jedna z głównych przyczyn długotrwałych transakcji.

Silnik bazy danych wykorzystuje jeszcze jeden mechanizm, który pomaga w zapewnieniu współbieżności bazy danych - wersjonowanie wierszy. Gdy dla bazy danych jest włączony poziom izolacji przechowywania wersji wierszy, aparat obsługuje wersje każdego zmodyfikowanego wiersza w bazie danych TempDB. Jest to zwykle używane w obciążeniach użycia mieszanego, aby zapobiec blokowaniu zapytań odczytu zapisywanych w bazie danych.

Aby monitorować otwarte transakcje oczekujące na zatwierdzenie lub wycofywanie, uruchom następujące zapytanie:

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

Poziomy izolacji

Program SQL Server oferuje kilka poziomów izolacji, które umożliwiają zdefiniowanie poziomu spójności i poprawności, które są gwarantowane dla danych. Poziomy izolacji umożliwiają znalezienie równowagi między współbieżnością a spójnością. Poziom izolacji nie ma wpływu na blokady podjęte w celu zapobiegania modyfikacji danych. Transakcja zawsze uzyska wyłączną blokadę danych, które modyfikuje. Jednak poziom izolacji może mieć wpływ na czas, w którym blokady są utrzymywane. Niższe poziomy izolacji zwiększają jednocześnie możliwość uzyskiwania dostępu do danych przez wielu użytkowników, ale zwiększają ryzyko spójności danych, które mogą wystąpić. Poziomy izolacji w programie SQL Server są następujące:

  • Nie zatwierdzone odczyty — najniższy dostępny poziom izolacji. Odczyty brudne są dozwolone, co oznacza, że jedna transakcja może zobaczyć zmiany wprowadzone przez inną transakcję, która nie została jeszcze zatwierdzona.

  • Read committed — pozwala transakcji odczytywać dane, które były wcześniej odczytane, ale niezmodyfikowane przez inną transakcję, bez oczekiwania na zakończenie pierwszej transakcji. Ten poziom zwalnia również blokady odczytu zaraz po wykonaniu operacji wybierania. Jest to domyślny poziom programu SQL Server.

  • Repeatable Read — Ten poziom przechowuje blokady odczytu i zapisu, które są uzyskiwane na wybranych danych do końca transakcji.

  • Serializable – jest to najwyższy poziom izolacji, gdzie transakcje są odizolowane. Blokady odczytu i zapisu są uzyskiwane na wybranych danych i nie są zwalniane do końca transakcji.

Program SQL Server zawiera również dwa poziomy izolacji, które obejmują przechowywanie wersji wierszy.

  • Odczyt zatwierdzonej migawki — na tym poziomie operacje odczytu nie używają żadnych blokad wierszy ani stron, a silnik prezentuje każdą operację z spójną migawką danych, taką jak istniała na początku zapytania. Ten poziom jest zwykle używany, gdy użytkownicy często uruchamiają zapytania raportowania względem bazy danych OLTP, aby uniemożliwić operacjom odczytu blokowanie operacji zapisu.

  • Migawka — ten poziom zapewnia spójność odczytu na poziomie transakcji poprzez wersjonowanie wierszy. Ten poziom jest podatny na konflikty aktualizacji. Jeśli transakcja działająca na tym poziomie odczytuje dane zmodyfikowane przez inną transakcję, aktualizacja wykonywana przez transakcję migawkową zostanie przerwana i cofnięta. Nie jest to problem z izolacją migawki zatwierdzonej do odczytu.

Poziomy izolacji są ustawiane dla każdej sesji za pomocą polecenia T-SQL #D0, jak pokazano poniżej:

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

Nie ma możliwości ustawienia globalnego poziomu izolacji wszystkich zapytań uruchomionych w bazie danych lub dla wszystkich zapytań uruchamianych przez określonego użytkownika. Jest to ustawienie poziomu sesji.

Monitorowanie problemów z blokowaniem

Identyfikowanie problemów blokujących może być trudne ze względu na ich sporadyczne charakter. Dynamiczny widok sys.dm_tran_lockszarządzania , po połączeniu z usługą sys.dm_exec_requests, zawiera informacje o blokadach przechowywanych przez każdą sesję. Bardziej efektywnym sposobem monitorowania problemów blokujących jest korzystanie z mechanizmu zdarzeń rozszerzonych na bieżąco.

Problemy blokujące zazwyczaj należą do dwóch kategorii:

  • Słaby projekt transakcyjny: na przykład transakcja bez COMMIT TRANSACTION nigdy się nie zakończy. Próba wykonania zbyt dużej ilości pracy w jednej transakcji lub posiadanie transakcji rozproszonej przy użyciu połączonego połączenia serwera może prowadzić do nieprzewidywalnej wydajności.
  • Długotrwałe transakcje spowodowane projektem schematu: często wiąże się to z aktualizacją kolumny z brakującym indeksem lub słabo zaprojektowanym zapytaniem aktualizacji.

Monitorowanie problemów z wydajnością związanych z blokowaniem pozwala szybko zidentyfikować obniżenie wydajności związane z blokowaniem.

Aby uzyskać więcej informacji na temat monitorowania blokowania, zobacz Opis i rozwiązywanie problemów z blokowaniem w programie SQL Server.