Diagnostyka locków i martwych punktów (deadlocks) w MySQL za pomocą Performance Schema

Problem z blokadą w MySQL często zaczyna się niewinnie: jedno zapytanie trwa dłużej, aplikacja przekracza limit czasu, a następnie kolejne żądania zaczynają oczekiwać. Po chwili widać już kilka lub kilkadziesiąt sesji w stanie oczekiwania, lecz sama lista procesów nie odpowiada na najważniejsze pytanie: która transakcja rzeczywiście trzyma blokadę i dlaczego nie została jeszcze zakończona? ^[4]

Jeszcze trudniejsza jest analiza deadlocka. W tym przypadku nie wystarczy znaleźć „najdłużej działającego” zapytania. Dwie lub więcej transakcji mogą posiadać zasoby potrzebne sobie nawzajem, tworząc cykl zależności. InnoDB wykrywa taki cykl i zwykle wycofuje jedną z transakcji, ale bez zebrania odpowiednich danych trudno ustalić, jakie operacje doprowadziły do konfliktu.

Performance Schema dostarcza informacji o bieżących blokadach, oczekiwaniach, transakcjach, wątkach i wykonywanych instrukcjach. Trzeba jednak wiedzieć, które tabele połączyć, jak odróżnić blokadę wiersza od blokady metadanych oraz kiedy dane z Performance Schema należy uzupełnić przez SHOW ENGINE INNODB STATUS, logi MySQL i informacje z aplikacji. Prawidłowa diagnostyka przebiega etapami: najpierw uchwycenie aktywnego oczekiwania, później identyfikacja właściciela blokady, a dopiero na końcu decyzja o przerwaniu sesji lub zmianie kodu.

Zdefiniuj problem: lock, blokada metadanych czy deadlock

Co właściwie trzeba ustalić

Słowo „lock” bywa używane jako określenie kilku różnych zjawisk. Sesja może czekać na blokadę rekordu InnoDB, na blokadę zakresu indeksu, na blokadę tabeli albo na metadata lock, czyli blokadę metadanych. Przyczyną opóźnienia może być też transakcja, która nie czeka na nic w danym momencie, lecz od dawna pozostaje otwarta i trzyma wcześniej zdobyte blokady.

Pierwszym zadaniem jest więc ustalenie, czy występuje aktywne oczekiwanie. Jeżeli w performance_schema.data_lock_waits znajdują się wiersze, mamy bezpośredni ślad relacji „sesja czeka na sesję”. Jeżeli tabela jest pusta, problem mógł zakończyć się chwilę wcześniej, oczekiwanie dotyczyło innego rodzaju zasobu albo Performance Schema nie zbiera wymaganych danych.

Drugi krok to ustalenie rodzaju obiektu. W przypadku InnoDB będą to najczęściej blokady rekordów i indeksów widoczne w data_locks. Dla zmian struktury tabeli, niektórych operacji DDL, otwierania lub zamykania obiektów oraz części operacji DML istotna może być tabela performance_schema.metadata_locks. Te dwa mechanizmy nie są zamienne. Zapytanie pokazujące blokady wierszy nie wyjaśni oczekiwania na MDL.

Trzeba również odróżnić zwykły lock wait od deadlocka. W zwykłym oczekiwaniu transakcja A posiada blokadę, a transakcja B chce ją zdobyć. Gdy A wykona COMMIT albo ROLLBACK, B może ruszyć dalej. Deadlock powstaje wtedy, gdy oczekiwania tworzą cykl: A czeka na zasób posiadany przez B, a B czeka na zasób posiadany przez A. InnoDB zazwyczaj wykrywa ten cykl i przerywa jedną transakcję, zwracając błąd, często znany aplikacji jako ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction.

Jak wygląda typowy łańcuch blokad

Wyobraźmy sobie trzy sesje. Sesja A rozpoczyna transakcję i aktualizuje rekord klienta o identyfikatorze 42, ale nie wykonuje jeszcze zatwierdzenia. Sesja B próbuje zmienić ten sam rekord i przechodzi w stan oczekiwania. Następnie sesja C również chce zaktualizować rekord 42. W rezultacie B i C czekają na A, choć w aplikacji jako „wolne” może wyglądać tylko pierwsze zapytanie, które rozpoczęło konflikt.

To przypomina korek na skrzyżowaniu. Samochód stojący na końcu kolejki nie jest przyczyną zatoru, nawet jeśli to jego kierowca najgłośniej sygnalizuje problem. W diagnostyce trzeba dotrzeć do transakcji, która jako pierwsza zdobyła blokadę i nadal ją posiada. Zabicie sesji B może uwolnić C, ale nie rozwiąże przyczyny, jeśli A nadal pozostanie otwarta.

Deadlock wygląda inaczej. Transakcja A aktualizuje rekord X, a następnie próbuje zmienić rekord Y. W tym samym czasie transakcja B zmienia Y i próbuje zmienić X. A ma X i czeka na Y, B ma Y i czeka na X. Żadna z nich nie może postąpić naprzód. Nie ma sensu czekać, aż użytkownik „zwolni” zasób, bo każda transakcja czeka na drugą.

Dlaczego deadlock nie jest po prostu zawieszoną sesją

Deadlock jest zdarzeniem logicznym, a nie tylko długim czasem wykonania. InnoDB ma mechanizm wykrywania cykli i wybiera transakcję do wycofania, zwykle na podstawie kosztu rollbacku. Druga transakcja może wtedy kontynuować pracę. Aplikacja musi jednak obsłużyć błąd i zdecydować, czy ponowić całą transakcję.

Oznacza to, że po wystąpieniu deadlocka dane w data_lock_waits mogą już nie pokazywać niczego interesującego. Cykl został rozwiązany, zanim administrator uruchomił zapytanie diagnostyczne. Dlatego diagnostyka powtarzających się deadlocków wymaga także historii, logów i mechanizmu rejestrującego zdarzenie w chwili jego wystąpienia.

Nie należy automatycznie zabijać najdłużej oczekującej sesji. Sesja z długim czasem trwania może być właścicielem blokady, ale może też tylko czekać na kogoś innego. Przerwanie transakcji może wywołać kosztowny rollback, pozostawić aplikację z błędem i przenieść problem na kolejne żądania. Najpierw trzeba poznać relację między właścicielem blokady i oczekującą sesją.

Przygotuj Performance Schema do obserwacji blokad

Sprawdzenie wersji i dostępnych tabel

Zakres danych Performance Schema zależy od wersji MySQL, konfiguracji serwera oraz używanego silnika. Widoki data_locks i data_lock_waits są przede wszystkim narzędziem do analizy blokad InnoDB. Przed użyciem gotowych zapytań trzeba sprawdzić, czy konkretna instancja je posiada. ^[2]

SELECT VERSION();

SHOW TABLES FROM performance_schema
WHERE Tables_in_performance_schema IN (
    'data_locks',
    'data_lock_waits',
    'metadata_locks',
    'threads',
    'events_transactions_current',
    'events_transactions_history',
    'events_statements_current',
    'events_statements_history'
);

W niektórych środowiskach historia długoterminowa może być wyłączona, a nazwy lub zakres kolumn mogą różnić się między wydaniami. Przed napisaniem zapytania łączącego wiele tabel pomocne jest:

DESCRIBE performance_schema.data_locks;
DESCRIBE performance_schema.data_lock_waits;
DESCRIBE performance_schema.threads;
DESCRIBE performance_schema.metadata_locks;

To drobny krok, który zapobiega częstemu błędowi: skopiowaniu zapytania z dokumentacji innej wersji MySQL i uznaniu, że brak wyniku oznacza brak blokad. Czasem problemem jest po prostu inna nazwa kolumny, inny typ identyfikatora albo nieobecność konkretnego widoku.

Kontrola konsumentów i instrumentów

Performance Schema składa się między innymi z instrumentów oraz konsumentów. Instrument określa, jakie zdarzenie może być obserwowane, natomiast konsument decyduje, gdzie i jak długo informacja o nim jest przechowywana. Włączenie samej historii instrukcji nie gwarantuje jeszcze pełnej historii transakcji, a aktywna tabela bieżąca nie oznacza, że zdarzenia zakończone kilka sekund wcześniej będą dostępne.

Stan konsumentów można sprawdzić tak:

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME LIKE '%events_transactions%'
   OR NAME LIKE '%events_statements%';

Instrumenty związane z instrukcjami i transakcjami można wyszukiwać następująco:

SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE 'transaction%'
   OR NAME LIKE 'statement/sql/%'
   OR NAME LIKE 'wait/lock/%'
   OR NAME LIKE 'wait/lock/metadata/sql/mdl';

Włączenie wybranych elementów powinno być decyzją operacyjną, a nie mechanicznym ustawieniem wszystkiego na YES. Przykładowo aktywowanie rozbudowanej historii długoterminowej na bardzo ruchliwym serwerze może zwiększyć koszt zbierania danych i utrudnić analizę przez ogromną liczbę nieistotnych wpisów.

Jeżeli środowisko wymaga zebrania historii transakcji, konfigurację zmienia się celowo, obserwując wpływ na serwer. Przykładowy wzorzec kontroli, bez zakładania, że każda instancja ma identyczne ustawienia, wygląda tak:

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME IN (
    'events_transactions_current',
    'events_transactions_history',
    'events_transactions_history_long',
    'events_statements_current',
    'events_statements_history',
    'events_statements_history_long'
);

W produkcji trzeba uwzględnić także ograniczenia uprawnień. Konto diagnostyczne powinno mieć dostęp do odpowiednich tabel Performance Schema, informacji o procesach oraz, jeśli przewidziano interwencję, uprawnienie pozwalające zakończyć sesję. Dostęp do treści SQL powinien być ograniczony, ponieważ zapytania mogą zawierać dane biznesowe, identyfikatory klientów albo wartości wrażliwe.

Ustal punkt obserwacji przed wystąpieniem problemu

Jeżeli problem pojawia się regularnie, jednorazowe uruchomienie zapytania po zgłoszeniu awarii może nie wystarczyć. Lock wait może trwać krócej niż czas potrzebny na zalogowanie się na serwer. Przydatny jest cykliczny monitoring, który zapisuje między innymi:

• czas obserwacji,
• identyfikator wątku Performance Schema,
• identyfikator połączenia lub proceslist ID,
• schemat i użytkownika,
• tekst bieżącej instrukcji, jeśli jest dostępny,
• czas trwania transakcji,
• relację oczekujący–blokujący,
• nazwę tabeli, indeksu i tryb blokady.

Sam Performance Schema nie zna wszystkich pojęć biznesowych. Nie wie, że dane połączenie obsługuje płatność, import katalogu albo aktualizację koszyka. Dlatego przydatne jest przekazywanie przez aplikację identyfikatora żądania, nazwy operacji lub identyfikatora korelacyjnego w logach. Później można połączyć moment powstania blokady z konkretnym żądaniem HTTP, zadaniem kolejki lub procesem wsadowym.

Historia ma ograniczoną pojemność. Wysokie obciążenie może szybko wypchnąć starsze zdarzenia, dlatego cykliczne zrzucanie kluczowych danych do zewnętrznego systemu monitoringu bywa skuteczniejsze niż poleganie wyłącznie na tabelach historii. Nie powinno się jednak zapisywać bez kontroli całych treści zapytań, jeśli zawierają dane, których nie należy utrwalać.

Krok 1 — znajdź aktywne oczekiwania i sesję blokującą

Zacznij od tabeli data_lock_waits

Najlepszym punktem startowym dla aktywnej blokady InnoDB jest performance_schema.data_lock_waits. Tabela opisuje relację między blokadą oczekiwaną a blokadą, która ją uniemożliwia. Nie należy zaczynać od listy wszystkich blokad w data_locks, ponieważ na obciążonym serwerze będzie ona zawierać wiele prawidłowych, przyznanych blokad. ^[1]

SELECT
    REQUESTING_ENGINE_LOCK_ID,
    REQUESTING_ENGINE_TRANSACTION_ID,
    BLOCKING_ENGINE_LOCK_ID,
    BLOCKING_ENGINE_TRANSACTION_ID
FROM performance_schema.data_lock_waits;

Wynik pokazuje identyfikatory używane do połączenia z data_locks. Praktyczna analiza wymaga zestawienia obu stron z opisem blokad:

SELECT
    w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
    w.BLOCKING_ENGINE_TRANSACTION_ID   AS blocking_trx,

    r.OBJECT_SCHEMA AS waiting_schema,
    r.OBJECT_NAME   AS waiting_table,
    r.INDEX_NAME    AS waiting_index,
    r.LOCK_TYPE     AS waiting_lock_type,
    r.LOCK_MODE     AS waiting_lock_mode,
    r.LOCK_STATUS   AS waiting_status,
    r.LOCK_DATA     AS waiting_lock_data,

    b.OBJECT_SCHEMA AS blocking_schema,
    b.OBJECT_NAME   AS blocking_table,
    b.INDEX_NAME    AS blocking_index,
    b.LOCK_TYPE     AS blocking_lock_type,
    b.LOCK_MODE     AS blocking_lock_mode,
    b.LOCK_STATUS   AS blocking_status,
    b.LOCK_DATA     AS blocking_lock_data
FROM performance_schema.data_lock_waits AS w
JOIN performance_schema.data_locks AS r
  ON r.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.data_locks AS b
  ON b.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID;

W zależności od wersji MySQL identyfikatory blokad mogą mieć format zależny od silnika, dlatego do połączenia najlepiej używać dokładnie kolumn ENGINE_LOCK_ID i odpowiednich pól z data_lock_waits, a nie próbować zgadywać relacji na podstawie numeru transakcji. Numer transakcji może wystąpić w wielu rekordach blokad.

Jak czytać status i tryb blokady

W tabeli data_locks kolumna LOCK_STATUS zwykle rozróżnia stan GRANTED i PENDING. W uproszczeniu blokada PENDING jest tą, na którą sesja czeka, a GRANTED należy do transakcji posiadającej zasób. Kolumna LOCK_MODE opisuje tryb, na przykład S, X, IS, IX lub warianty obejmujące luki indeksowe.

Przy planowaniu kolejnych kroków pomocny może być tekst: Jak zaplanować migrację do SSD NVMe, by faktycznie przyspieszyć infrastrukturę IT.

LOCK_TYPE może wskazywać, czy chodzi o blokadę tabeli, czy rekordu. OBJECT_SCHEMA, OBJECT_NAME i INDEX_NAME pozwalają ustalić, której tabeli i którego indeksu dotyczy konflikt. Jeżeli dostępne jest LOCK_DATA, można zobaczyć informacje o kluczu lub rekordzie, ale format tej wartości nie powinien być traktowany jako stabilny interfejs do automatycznych parserów.

Informacja	Znaczenie diagnostyczne
LOCK_STATUS = PENDING	Sesja nie zdobyła żądanej blokady i oczekuje.
LOCK_STATUS = GRANTED	Transakcja posiada daną blokadę.
LOCK_MODE = X	Blokada wyłączna, typowa dla modyfikacji rekordu.
INDEX_NAME	Indeks, przez który InnoDB identyfikuje blokowany rekord lub zakres.
LOCK_DATA	Dodatkowy opis klucza lub rekordu, zależny od dostępności i wersji.

Połącz blokadę z wątkiem i połączeniem

Identyfikator transakcji InnoDB nie mówi jeszcze, który użytkownik, proces aplikacji ani numer połączenia utrzymuje blokadę. Do tego potrzebne jest powiązanie z tabelą performance_schema.threads. W praktyce interesują Cię przede wszystkim:

• THREAD_ID — identyfikator wątku w Performance Schema,
• PROCESSLIST_ID — identyfikator widoczny zwykle jako Id w SHOW PROCESSLIST,
• PROCESSLIST_USER i PROCESSLIST_HOST — użytkownik oraz źródło połączenia,
• PROCESSLIST_DB — domyślna baza,
• PROCESSLIST_COMMAND i PROCESSLIST_INFO — bieżący stan połączenia i tekst aktualnej instrukcji.

W wielu wersjach MySQL tabela data_locks nie zawiera bezpośrednio THREAD_ID. Najpierw trzeba więc przejść przez informacje o transakcji. Aktualnie trwającą transakcję można wyszukać w events_transactions_current:

SELECT
    t.THREAD_ID,
    t.EVENT_ID,
    t.STATE,
    t.TRX_ID,
    th.PROCESSLIST_ID,
    th.PROCESSLIST_USER,
    th.PROCESSLIST_HOST,
    th.PROCESSLIST_DB,
    th.PROCESSLIST_COMMAND,
    th.PROCESSLIST_TIME,
    th.PROCESSLIST_INFO
FROM performance_schema.events_transactions_current AS t
LEFT JOIN performance_schema.threads AS th
       ON th.THREAD_ID = t.THREAD_ID
WHERE t.TRX_ID IS NOT NULL;

Następnie identyfikator transakcji należy porównać z REQUESTING_ENGINE_TRANSACTION_ID albo BLOCKING_ENGINE_TRANSACTION_ID z data_lock_waits. Typ i format identyfikatora mogą różnić się między wersjami, dlatego przed użyciem zapytania produkcyjnego sprawdź definicje kolumn:

DESCRIBE performance_schema.events_transactions_current;
DESCRIBE performance_schema.events_transactions_history;
DESCRIBE performance_schema.data_lock_waits;

Przykładowe zapytanie łączące oczekiwanie z bieżącymi transakcjami i wątkami może wyglądać tak:

SELECT
    w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
    wt.THREAD_ID                       AS waiting_thread_id,
    wt.PROCESSLIST_ID                  AS waiting_processlist_id,
    wt.PROCESSLIST_USER                AS waiting_user,
    wt.PROCESSLIST_HOST                AS waiting_host,
    wt.PROCESSLIST_DB                  AS waiting_db,
    wt.PROCESSLIST_INFO                AS waiting_sql,

    w.BLOCKING_ENGINE_TRANSACTION_ID  AS blocking_trx,
    bt.THREAD_ID                      AS blocking_thread_id,
    bt.PROCESSLIST_ID                 AS blocking_processlist_id,
    bt.PROCESSLIST_USER               AS blocking_user,
    bt.PROCESSLIST_HOST               AS blocking_host,
    bt.PROCESSLIST_DB                 AS blocking_db,
    bt.PROCESSLIST_INFO               AS blocking_sql

FROM performance_schema.data_lock_waits AS w
LEFT JOIN performance_schema.events_transactions_current AS tw
       ON CAST(tw.TRX_ID AS CHAR) =
          CAST(w.REQUESTING_ENGINE_TRANSACTION_ID AS CHAR)
LEFT JOIN performance_schema.threads AS wt
       ON wt.THREAD_ID = tw.THREAD_ID
LEFT JOIN performance_schema.events_transactions_current AS tb
       ON CAST(tb.TRX_ID AS CHAR) =
          CAST(w.BLOCKING_ENGINE_TRANSACTION_ID AS CHAR)
LEFT JOIN performance_schema.threads AS bt
       ON bt.THREAD_ID = tb.THREAD_ID;

Jeżeli w danej wersji wartości nie łączą się bezpośrednio, nie oznacza to automatycznie braku sesji. Możliwą przyczyną jest różny typ kolumn, brak bieżącego wpisu transakcji albo zakończenie zdarzenia pomiędzy kolejnymi odczytami. W takim przypadku trzeba użyć historii transakcji lub wykonać dwa odczyty w ramach krótkiego, kontrolowanego monitoringu.

Ustal, jak długo trwa transakcja

Najważniejszą informacją o sesji blokującej nie jest samo PROCESSLIST_TIME. Ten czas może oznaczać czas trwania aktualnej instrukcji, a nie całej transakcji. Sesja może być bezczynna, lecz nadal posiadać blokady zdobyte wcześniej, ponieważ aplikacja nie wykonała jeszcze COMMIT ani ROLLBACK.

Do oceny transakcji wykorzystaj pola czasowe z events_transactions_current, o ile są dostępne w danej wersji:

SELECT
    t.THREAD_ID,
    t.EVENT_ID,
    t.STATE,
    t.TRX_ID,
    t.TIMER_START,
    t.TIMER_END,
    t.TIMER_WAIT,
    th.PROCESSLIST_ID,
    th.PROCESSLIST_USER,
    th.PROCESSLIST_HOST,
    th.PROCESSLIST_INFO
FROM performance_schema.events_transactions_current AS t
LEFT JOIN performance_schema.threads AS th
       ON th.THREAD_ID = t.THREAD_ID
WHERE t.TRX_ID IS NOT NULL;

Jeżeli transakcja jest nadal aktywna, TIMER_END może być puste, a czas należy liczyć od TIMER_START do chwili obserwacji. Jednostką timerów Performance Schema nie jest bezpośrednio sekunda; sposób przeliczenia zależy od typu timera i wersji. Do porównania transakcji między sobą można używać wartości względnych, natomiast przy prezentacji czasu operatorowi trzeba zastosować właściwe przeliczenie opisane dla danej wersji.

Warto odróżnić trzy sytuacje:

• transakcja blokująca wykonuje właśnie długą instrukcję,
• transakcja blokująca jest bezczynna, ale pozostawiła otwartą transakcję,
• transakcja oczekująca również posiada własne blokady i może blokować kolejne sesje.

Trzeci przypadek tworzy łańcuch oczekiwań. Dlatego nie kończ analizy na pierwszym wierszu z data_lock_waits. Zbuduj listę wszystkich relacji i sprawdź, czy ta sama transakcja występuje jako blokująca dla wielu oczekujących sesji.

Odczytaj ostatnią instrukcję sesji

threads.PROCESSLIST_INFO pokazuje stan bieżący i może być puste, gdy połączenie czeka między instrukcjami. Aby ustalić, co doprowadziło do zdobycia blokady, sprawdź także zdarzenia instrukcji. Dla aktywnego zdarzenia użyj events_statements_current: ^[3]

SELECT
    s.THREAD_ID,
    s.EVENT_ID,
    s.EVENT_NAME,
    s.SQL_TEXT,
    s.TIMER_START,
    s.TIMER_END,
    s.TIMER_WAIT,
    s.LOCK_TIME,
    s.ROWS_AFFECTED,
    s.ROWS_EXAMINED
FROM performance_schema.events_statements_current AS s
WHERE s.THREAD_ID IN (
    123,
    456
)
ORDER BY s.THREAD_ID, s.EVENT_ID DESC;

Numery 123 i 456 należy zastąpić identyfikatorami wątków znalezionymi w poprzednim kroku. W przypadku zakończonej instrukcji sprawdź events_statements_history, ale pamiętaj, że historia obejmuje tylko ograniczoną liczbę ostatnich zdarzeń dla wątku.

SELECT
    THREAD_ID,
    EVENT_ID,
    EVENT_NAME,
    SQL_TEXT,
    TIMER_START,
    TIMER_END,
    TIMER_WAIT,
    LOCK_TIME,
    ROWS_AFFECTED,
    ROWS_EXAMINED
FROM performance_schema.events_statements_history
WHERE THREAD_ID = 123
ORDER BY EVENT_ID DESC
LIMIT 20;

Najbardziej użyteczne jest zestawienie kilku ostatnich instrukcji, a nie tylko jednej aktualnie wykonywanej. Przykładowo blokada mogła zostać zdobyta przez UPDATE, po którym aplikacja wykonała kolejne operacje biznesowe, a następnie długo nie zamknęła transakcji. Sama pusta wartość SQL_TEXT nie dowodzi więc, że sesja nie jest właścicielem blokady.

Rozstrzygnij, czy sesję kończyć

Po wskazaniu właściciela blokady zbierz minimalny zestaw danych przed interwencją:

• identyfikator transakcji i wątku,
• identyfikator połączenia używany przez KILL,
• użytkownika, host oraz nazwę aplikacji, jeśli jest dostępna,
• tabelę, indeks i tryb blokady,
• czas trwania transakcji oraz czas oczekiwania innych sesji,
• ostatnie instrukcje z bieżącej i historycznej tabeli zdarzeń,
• informację, czy sesja jest elementem większego łańcucha.

Jeżeli konieczne jest przerwanie sesji, używa się identyfikatora połączenia, a nie THREAD_ID Performance Schema:

KILL CONNECTION 12345;

Wartość 12345 musi pochodzić z threads.PROCESSLIST_ID albo z równoważnego identyfikatora połączenia. THREAD_ID i PROCESSLIST_ID są różnymi identyfikatorami. Pomylenie ich może spowodować błąd albo próbę zakończenia niewłaściwego połączenia.

Przed zakończeniem sesji sprawdź właściciela aplikacyjnego procesu. Przerwanie połączenia może wycofać transakcję, uwolnić blokady i rozwiązać problem, ale może też przerwać import, płatność, migrację danych lub proces wymagający późniejszego wznowienia. Interwencja powinna być ostatnim etapem po zapisaniu danych diagnostycznych i potwierdzeniu wpływu biznesowego.

Analizuj blokady metadanych osobnym torem

Jeżeli zapytanie oczekuje na zmianę struktury tabeli, przygotowanie tabeli, zmianę indeksu albo inną operację DDL, sprawdź performance_schema.metadata_locks. Nie łącz tego przypadku wyłącznie z data_locks, ponieważ są to inne rodzaje obserwowanych blokad.

SELECT
    ml.OBJECT_TYPE,
    ml.OBJECT_SCHEMA,
    ml.OBJECT_NAME,
    ml.COLUMN_NAME,
    ml.LOCK_TYPE,
    ml.LOCK_DURATION,
    ml.LOCK_STATUS,
    ml.OWNER_THREAD_ID,
    ml.OWNER_EVENT_ID,
    th.PROCESSLIST_ID,
    th.PROCESSLIST_USER,
    th.PROCESSLIST_HOST,
    th.PROCESSLIST_DB,
    th.PROCESSLIST_INFO
FROM performance_schema.metadata_locks AS ml
LEFT JOIN performance_schema.threads AS th
       ON th.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.LOCK_STATUS IN ('PENDING', 'GRANTED')
ORDER BY
    ml.OBJECT_SCHEMA,
    ml.OBJECT_NAME,
    ml.LOCK_STATUS,
    ml.OWNER_THREAD_ID;

Najpierw znajdź rekord PENDING, a następnie w tej samej tabeli odszukaj przyznane blokady dotyczące tego samego obiektu. Zwróć uwagę na OBJECT_TYPE, OBJECT_SCHEMA i OBJECT_NAME. Przydatne jest również OWNER_EVENT_ID, które pozwala połączyć wpis z aktywnym zdarzeniem instrukcji.

SELECT
    ml.OBJECT_SCHEMA,
    ml.OBJECT_NAME,
    ml.LOCK_TYPE,
    ml.LOCK_DURATION,
    ml.LOCK_STATUS,
    ml.OWNER_THREAD_ID,
    ml.OWNER_EVENT_ID,
    s.EVENT_NAME,
    s.SQL_TEXT,
    th.PROCESSLIST_ID,
    th.PROCESSLIST_USER,
    th.PROCESSLIST_HOST
FROM performance_schema.metadata_locks AS ml
LEFT JOIN performance_schema.events_statements_current AS s
       ON s.THREAD_ID = ml.OWNER_THREAD_ID
      AND s.EVENT_ID  = ml.OWNER_EVENT_ID
LEFT JOIN performance_schema.threads AS th
       ON th.THREAD_ID = ml.OWNER_THREAD_ID
WHERE ml.LOCK_STATUS = 'PENDING';

Jeśli dokładne połączenie po OWNER_EVENT_ID nie zwróci instrukcji, sprawdź wszystkie bieżące instrukcje dla OWNER_THREAD_ID oraz ostatnie wpisy w historii. Sesja może oczekiwać, gdy instrukcja nie jest już prezentowana w prosty sposób w tabeli bieżącej, a zakres historii zależy od konfiguracji.

Krok 2 — odtwórz zakończony deadlock

Aktywny lock wait można obserwować w data_lock_waits, ale po wycofaniu jednej z transakcji relacja może zniknąć. Zakończony deadlock trzeba więc analizować z użyciem źródeł historycznych i logów.

Sprawdź SHOW ENGINE INNODB STATUS

Na serwerze, na którym niedawno wystąpił deadlock, uruchom:

SHOW ENGINE INNODB STATUSG

W sekcji LATEST DETECTED DEADLOCK InnoDB zwykle pokazuje transakcje uczestniczące w zdarzeniu, instrukcje, posiadane blokady oraz zasoby, na które czekały. Zapisz cały fragment, a nie tylko pierwszą linię błędu. Do ustalenia przyczyny potrzebne są między innymi:

• kolejność zdobywania blokad przez obie transakcje,
• tabela i indeks wskazane przy oczekiwaniu,
• tryb blokady oraz informacja, czy dotyczy rekordu, zakresu lub luki,
• instrukcja wycofanej transakcji,
• instrukcja transakcji, która pozostała aktywna.

To polecenie pokazuje najnowszy zapamiętany przypadek, a nie pełną historię wszystkich deadlocków. Jeżeli po wystąpieniu problemu doszło do kolejnego deadlocka, starszy opis mógł zostać zastąpiony.

Włącz rejestrowanie wszystkich deadlocków, jeśli problem się powtarza

Domyślnie log może nie zawierać pełnego opisu każdego deadlocka. Do czasowego rejestrowania wszystkich wykrytych przypadków służy ustawienie:

SET GLOBAL innodb_print_all_deadlocks = ON;

Po włączeniu sprawdź, gdzie instancja zapisuje log błędów. Lokalizacja zależy od konfiguracji serwera i sposobu uruchomienia MySQL. Po zakończeniu okna diagnostycznego można wyłączyć ustawienie, jeżeli liczba wpisów jest duża:

SET GLOBAL innodb_print_all_deadlocks = OFF;

Nie traktuj logowania jako zamiennika monitoringu. Logi powinny być korelowane z czasem, nazwą hosta, identyfikatorem żądania oraz logiem aplikacji. Sam tekst SQL z raportu InnoDB może nie wskazywać, który użytkownik końcowy wywołał operację.

Połącz błąd MySQL z logiem aplikacji

Aplikacja powinna rejestrować co najmniej:

• czas wystąpienia błędu i strefę czasową,
• kod 1213 dla deadlocka oraz kod 1205 dla przekroczenia czasu oczekiwania na blokadę,
• SQLSTATE, jeśli sterownik go udostępnia,
• nazwę operacji biznesowej i identyfikator korelacyjny,
• nazwę usługi, hosta lub workera,
• informację, czy transakcja została ponowiona.

Deadlock jest sytuacją, którą aplikacja często może obsłużyć przez ponowienie całej transakcji po krótkim opóźnieniu. Ponowieniu nie wolno poddawać tylko pojedynczej instrukcji bez uwzględnienia granicy transakcji, ponieważ pozostałe operacje mogły zostać już wycofane. Liczbę prób, odstęp oraz warunek przerwania trzeba ograniczyć, aby mechanizm retry nie zwiększył przeciążenia.

Najważniejsze punkty

• Najpierw trzeba ustalić, czy występuje aktywne oczekiwanie, a następnie rozpoznać jego rodzaj: blokadę InnoDB lub metadata lock.
• Tabele data_lock_waits i data_locks pomagają ustalić relację między sesją oczekującą a właścicielem blokady.
• Deadlock jest cyklem zależności między transakcjami, dlatego nie należy utożsamiać go ze zwykłą długo oczekującą sesją.
• Po rozwiązaniu deadlocka dane w tabelach bieżących Performance Schema mogą już nie zawierać pełnego obrazu zdarzenia.
• W powtarzających się problemach warto połączyć Performance Schema z logami MySQL, SHOW ENGINE INNODB STATUS oraz informacjami z aplikacji.
• Przed użyciem zapytań diagnostycznych należy sprawdzić wersję MySQL, dostępne tabele, kolumny, instrumenty, konsumentów i wymagane uprawnienia.

Źródła

• [1] MySQL Documentation – The data_lock_waits Table ↑
• [2] MySQL Documentation – Performance Schema Lock Tables ↑
• [3] MySQL Documentation – Using the Performance Schema to Diagnose Problems ↑
• [4] MySQL Documentation – The metadata_locks Table ↑