S databází Oracle celkem pravidelně pracuji již několik let, a byť jsem velký příznivec PostgreSQL tak Oracle považuji za výbornou databázi a některé její vlastnosti mi v PostgreSQL celkem chybí (a u jiných je to přesně naopak). Ne že bych Oracle znal dokonale - jsem primárně vývojář (mimo jiné mám i OCP pro PL/SQL), takže většina "DBA věci" jde víceméně mimo mne.

Sem tam mne ale v Oracle překvapí drobné nedomyšlenosti, jejichž důsledkem je ale většinou moje naprosto zbytečné zmatení a zbytečná ztráta času. Příkladem mohou být informace o zámcích poskytované v případě některých deadlocků.

Deadlock info

Pokud v databázi dojde k deadlocku (což většinou znamená chybu v designu aplikace), Oracle tuto situaci rozpozná (v grafu zámků najde smyčku), vyhodí ORA-00060 chybu a ukončí jeden z procesů (kterého určí jako viníka). Informace o deadlocku, užitečné zejména pro identifikaci příčin deadlocku a jejich odstranění z aplikace, samozřejmě zapíše do logu.

Kromě výpisu problematických SQL příkazů obsahuje záznam v logu i tzv. "deadlock graph" a seznam SQL příkazů pro jednotlivé sessions. Příklad deadlock grafu (které ho se týká i následující text):

Deadlock graph:
                       -----Blocker(s)----  -----Waiter(s)-----
Resource Name          session holds waits  session holds waits
TM-000037e6-00000000       139    SX            124    SX   SSX
TX-0005000a-0001f3aa       124     X            139           S
 
session 139: DID 0001-0020-00001046	session 124: DID 0001-0022... 
session 124: DID 0001-0022-00001DDD	session 139: DID 0001-0020...
 
Rows waited on:
  Session 139: obj - rowid = 00007A1D - AAAHodAAAAAAAAAAAA
  (dictionary objn - 31261, file - 0, block - 0, slot - 0)
  Session 124: obj - rowid = 000037DC - AAADfcAALAAAQxGAAA
  (dictionary objn - 14300, file - 11, block - 68678, slot - 0)

Tj. jedná se vlastně o seznam sessions účastnících se deadlocku (mají zamknut nějaký element čekají na element držený jinou session, přičemž tvoří cyklus),  s informací jaký zámek daná session drží (blocker) a na jaký zámek session naopak čeká (waiter). Nejmenší počet sessions pro deadlock je pochopitelně dvě. Druhou část informace tvoří "rows waited on" ve které jsou pomocí ID objektu přímo identifikovány objekty na které jednotlivé sessions čekají. A právě v této části je zakopán pes, jak za chvíli uvidíme ...

Obecný postup identifikace příčin deadlocků na základě ORA-00060 informací je tedy následující:

1. identifikujte SQL příkazy které v sessions v okamžiku deadlocku probíhaly
2. identifikujte objekty které se sessions snažily zamknout (pomocí ID objektů v části "rows waited on" a katalogu all_objects)

Bohužel ono to tak úplně jednoduché není, a v našem případě tento přímočarý postup vedl k naprosto scestným a matoucím závěrům o příčinách deadlocku.

Příklad z praxe

Představte si dvě tabulky - A a B, provázané jedním cizím klíčem (tabulka B se odkazuje na PK tabulky A a tento FK není indexován). Navíc v tabulce B existuje několik bitmapových indexů. Máme tedy zhruba toto schéma:

CREATE TABLE A (
    id INT PRIMARY KEY,
    ...
);

CREATE TABLE B (
    id INT PRIMARY KEY,
    a_id INT REFERENCES A(id),
    col_a NUMBER(1),
    ...
);

CREATE BITMAP INDEX b_col_a_bit_idx ON B(col_a);

Nad tímto schématem běží dvě sessions, spouštějící následujcí dva SQL příkazy

Session 1:
----------
UPDATE A SET id = :id, first_name = :fname, ...

Session 2:
----------
INSERT INTO B (ID, A_ID, COL_A, ...) VALUES (...)

což ale vede k deadlocku, jehož graf je uveden výše. Dalším krokem tedy automaticky je identifikace objektů které sessions nemohou zamknout - v "rows waited on" jsou uvedena dvě ID (31261 a 14300), a jednoduchým dotazem do katalogu all_objects se ukáže že ID 31261 odpovídá přímo tabulka B, zatímco 14300 je LOB segment (SYS_LOB0000....) náležející ke zcela nesouvisející tabulce (vzdálené od obou tabulek přes několik vazebních tabulek). WTF?

Zamknutí tabulky B dává smysl - obsahuje neindexovaný cizí klíč do tabulky A, jejíž primární klíč (tj. hodnota na kterou se odkazuje B) se mění. Je to aplikační chyba, ale vysvětluje to zámek na B.

Ale zatraceně proč je zamykána zcela nesouvisející tabulka? Představte si tuto strukturu

A --- [M:N] --- X --- [M:N] --- Y --- [M:N] --- B

tj. mezi tabulkami A a B je 5 dalších tabulek (X, Y a tři vazební tabulky pro M:N vazby), přičemž BLOB sloupec ke kterému patří zamknutý LOB segment náleží do prostřední vazební tabulky. Jak zatraceně může UPDATE A nebo INSERT INTO B vést k zámku na takhle vzdálené tabulce? Podotýkám že ani na jedné tabulce nejsou žádné triggery, všechny cizí klíče v těchto tabulkách jsou indexované atd. Tak jak je to promožné?

Přeskočme několik dní po které jsme si s tím neúspěšně lámali hlavu - příčina je jednoduchá ...

Pravda je totiž taková že TM zámek (v tomto případě TM-000037e6-00000000) není row-level lock, a tudíž informace uvedená v "rows waited on" je naprosto nesmyslná. Přesněji řečeno uvedené object ID s deadlockem naprosto nesouvisí - správné ID objektu je uvedeno přímo v první části ID zámku - hexa hodnota "37e6" odpovídá ID 14310 což je (nepřekvapivě) bitmapový index v tabulce B.

Záhada vyřešena drahý Watsone ...

Závěr

Chápu že pro některé typy zámků (které nezamykají jednotlivé řádky) je informace o zamykaném objektu dostupná jinak, problém mi to nečiní. Na druhou stranu naprosto nechápu proč v takovém případě Oracle tuto matoucí informaci v "rows waited on" vůbec vypisuje a nenahradí ji korektní informací (když už ji má v ID zámku) a nebo místo ní alespoň nevypíše "no row" (což ostatně v případě některých deadlocků stejně dělá).

Ale když to vezmu ze světlejší stránky - aspoň jsem se po nějaké době zase mohl trochu pohrabat ve vnitřnostech Oracle. A rozhodně to nebyla nuda ...