Blokady to jedna z najczęstszych przyczyn nagłych spowolnień aplikacji, a ich skuteczna diagnostyka wymaga więcej niż znajomości kilku widoków słownikowych. Z doświadczenia wiem, że kluczem jest zrozumienie mechanizmów współbieżności i umiejętność szybkiego dotarcia do źródła problemu.
- V$LOCK i V$SESSION to podstawa, ale prawdziwa diagnostyka wymaga analizy łańcuchów blokad
- TX Locks powstają przy konfliktach na poziomie wierszy, TM Locks przy operacjach DDL
- ASH i AWR pozwalają analizować problemy z blokadami, które już się zakończyły
- Zabijanie sesji blokującej bez zrozumienia przyczyny to najczęstszy błąd DBA
Mechanizmy współbieżności Oracle
Oracle realizuje współbieżność poprzez kombinację blokad (locks) i mechanizmu wielowersyjności (MVCC). Blokady chronią przed konfliktami przy modyfikacji danych, natomiast MVCC oparte na UNDO pozwala czytającym nie blokować piszących i odwrotnie. To fundamentalna różnica w porównaniu z SQL Server czy MySQL, gdzie SELECT może zablokować UPDATE.
W Oracle wyróżniamy dwa główne typy blokad: enqueue locks (kolejkowane, jak TX i TM) oraz latche (niskopoziomowe, chroniące struktury pamięci SGA). Dla DBA zajmującego się diagnostyką aplikacyjną kluczowe są te pierwsze.
V$LOCK jako punkt wyjścia
Widok V$LOCK zawiera informacje o wszystkich aktywnych blokadach w instancji. Najważniejsze kolumny to: SID (identyfikator sesji), TYPE (typ blokady), ID1 i ID2 (identyfikatory zasobu) oraz LMODE i REQUEST. Kolumna LMODE określa tryb posiadanej blokady, REQUEST tryb żądany.
Podstawowa zasada interpretacji: jeśli LMODE > 0 i REQUEST = 0, sesja posiada blokadę. Jeśli LMODE = 0 i REQUEST > 0, sesja czeka na blokadę. To pozwala szybko zidentyfikować relacje blokujący/blokowany.
W praktyce produkcyjnej rzadko analizuję sam V$LOCK. Zamiast tego łączę go z V$SESSION i V$SQL, bo sama informacja o blokadzie bez kontekstu sesji i wykonywanego SQL jest bezużyteczna.
TX Locks: najczęstszy problem
Blokady transakcyjne TX powstają, gdy dwie sesje próbują zmodyfikować ten sam wiersz. Sesja, która pierwsza wykonała UPDATE, trzyma blokadę w trybie exclusive (mode 6) do momentu COMMIT lub ROLLBACK. Druga sesja czeka z REQUEST = 6.
Typowe przyczyny TX Locks to: długie transakcje bez COMMIT, aplikacje trzymające otwarte transakcje podczas oczekiwania na input użytkownika, batch joby modyfikujące duże zbiory danych bez podziału na mniejsze partie.
Identyfikacja obiektu i wiersza wymaga dekodowania ID1 i ID2. Dla TX Lock ID1 zawiera numer rollback segmentu i slot, ID2 numer sekwencji. Do znalezienia konkretnego obiektu potrzebny jest V$LOCKED_OBJECT.
TM Locks: blokady obiektów
Blokady TM chronią tabele podczas operacji DML i DDL. Każdy INSERT, UPDATE, DELETE zakłada TM Lock w trybie Row Exclusive (mode 3), co pozwala innym sesjom wykonywać DML, ale blokuje DDL wymagający exclusive access.
Problemy z TM Locks pojawiają się najczęściej podczas: ALTER TABLE w godzinach pracy, dodawania constraintów, tworzenia indeksów bez klauzuli ONLINE. W Oracle 12c i nowszych DDL ONLINE znacząco redukuje te konflikty.
Identyfikacja łańcuchów blokad
W rzeczywistych incydentach rzadko mamy do czynienia z prostą relacją jeden blokujący, jeden blokowany. Częściej widzimy łańcuchy: sesja A blokuje B, B blokuje C, D i E. Kluczowe jest znalezienie korzenia łańcucha.
Od wersji 11g Oracle udostępnia kolumnę BLOCKING_SESSION w V$SESSION, co znacząco upraszcza analizę. Rekurencyjne zapytanie z CONNECT BY pozwala zbudować pełne drzewo zależności w jednym SELECT.
Wait Events w diagnostyce
Zdarzenie enq: TX row lock contention to sygnał konfliktu na poziomie wierszy. Inne istotne eventy to: enq: TM contention (konflikty na poziomie tabel), enq: TX index contention (konflikty przy wstawianiu do indeksów z sekwencją), enq: TX allocate ITL entry (brak miejsca w Interested Transaction List).
Analiza V$SESSION_WAIT lub V$ACTIVE_SESSION_HISTORY pozwala skorelować oczekiwania z konkretnymi sesjami i SQL.
ASH i AWR: analiza historyczna
Active Session History próbkuje aktywne sesje co sekundę, zapisując informacje o oczekiwaniach, SQL i blokadach. To nieocenione źródło przy analizie incydentów, które już się zakończyły.
Zapytanie do DBA_HIST_ACTIVE_SESS_HISTORY z filtrem na BLOCKING_SESSION IS NOT NULL pozwala odtworzyć historię blokad z ostatnich dni. Raporty AWR sekcja Top 5 Timed Events i Wait Events wskazują, czy blokady były istotnym problemem w analizowanym okresie.
Deadlocki: ORA-00060
Oracle automatycznie wykrywa deadlocki i przerywa jedną z transakcji błędem ORA-00060. Jednocześnie generuje plik trace w katalogu diagnostic_dest zawierający szczegółowy opis cyklu: sesje, SQL, obiekty, wiersze.
Analiza trace wymaga identyfikacji wzorca: czy deadlock jest powtarzalny, czy losowy. Powtarzalne deadlocki zwykle wskazują na problem w logice aplikacji, kolejności modyfikacji tabel lub brakujące indeksy na kluczach obcych.
Praktyczny skrypt diagnostyczny
Podstawowe zapytanie łączące informacje o blokadach z kontekstem sesji:
SELECT s.sid, s.serial#, s.username, s.program, s.blocking_session, s.seconds_in_wait, s.event, q.sql_text FROM v$session s LEFT JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.blocking_session IS NOT NULL OR s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);
To zapytanie pokazuje zarówno sesje blokowane, jak i blokujące, wraz z wykonywanym SQL i czasem oczekiwania.
Najczęstsze błędy DBA
Zabijanie sesji blokującej bez analizy to błąd numer jeden. Często sesja blokująca to krytyczny proces batch, a jej przerwanie powoduje większe szkody niż chwilowe spowolnienie.
Drugi błąd to ignorowanie przyczyny biznesowej. Jeśli użytkownik A zawsze blokuje użytkownika B o 10:00, problem może leżeć w procesach biznesowych lub harmonogramie zadań, nie w bazie danych.