Při provozu jsme registrovali, že se občas vyskytuje chyba ORA-00060: deadlock detected while waiting for resource, což je problém s uváznutím, kdy se databázové transakce uzamknou proti sobě a ani jedna nemůže pokračovat dál.
| Krok | Transakce 1 | Transakce 2 | Popis |
|---|---|---|---|
| 1 | UPDATE platy SET plat=1000 WHERE zamestanec_id=1 | Transakce 1 si v tabulce PLATY uzamkne záznam pro zaměstnance s ID 1 | |
| 2 | UPDATE odmeny SET castka=2000 WHERE zamestanec_id=10 | Transakce 2 si v tabulce ODMENY uzamkne záznam pro zaměstnance s ID 10 | |
| 3 | UPDATE odmeny SET castka=500 WHERE zamestanec_id=10 | Transakce 1 se pokouší v tabulce ODMENY uzamknout záznam pro zaměstnance s ID 10, což se ji nedaří, protože zámek drží transakce 2 a transakce 1 tedy musí čekat na dokončení transakce 2. | |
| 4 | UPDATE platy SET plat=300 WHERE zamestanec_id=1 | Transakce 2 se pokouší v tabulce PLATY uzamknout záznam pro zaměstnance s ID 1, což se ji nedaří, protože zámek drží transakce 1 a transakce 2 tedy musí čekat na dokončení transakce 1. |
V tento okamžik jsou obě transakce zacyklené. Transakce 1 čeká na dokončení transakce 2, ale ta zase čeká na dokončení transakce 1 a problém s uváznutím je na světě:
(obrázek z http://oracledbpro.blogspot.com/)
Malá odbočka. Ohledně deadlocku v Oracle panuje několik mýtů, které je vhodné vyjasnit:
- Oracle neprovede kill žádné session,
- Oracle provede rollback příkazu, ale neprovede rollback celé transakce,
- Oracle background proces PMON (Process Monitor) neuvolní předchozí zámky.
Zpět k naší aplikaci. Chyba ORA-00060 se nám občas vyskytovala v situaci, kdy uživatel, nebo automatický proces, mazal návrh na vklad. Vyskytovala se poměrně zřídka, ale časem začala narůstat na několik výskytů denně. 99 % bylo vyvoláno z automatického procesu, bez vlivu na uživatele, který chybu ani nepoznal.
Pokud se problém s deadlockem vyskytuje v aplikaci, tak je jeho příčina nejčastěji buď chyba v návrhu aplikace nebo chyba v datovém modelu. Většinou se špatně hledá.
Naše aplikace má jednoduchý datový model, který se skládá z cca 70 tabulek. Chyba se vyskytovala při mazání jedné z tabulek NEMOVITOSTI, STAVBY nebo PARCELY. Datový model této části vypadá zhruba takto:
část datového modelu
Při kontrole mazací procedury jsem nenarazil na problém, tak jsem se zaměřil na datový model. Ten jsem prošel, ale také mě nic do oka netrklo, tak jsem musel zkoumat dál. Oracle při výskytu chyby zapíše událost do alert logu (píši se zde všechny důležité události, která v databázi vznikly) a vytvoří trace soubor:
ukázka alert logu
ukázka trace (mívá jednotky až stovky MB)
Otevřel jsem trace a viděl, že problém v tomto případě vznikl při mazání záznamů z tabulky STAVBY:
mazané tabulky
Znovu jsem prověřil danou část modelu, ale vše vypadalo OK. Pátral jsem tedy dál. V trace je graf deadlocku:
Z něj jde vyčíst, že je problém v resource se jménem TM-003f719d-00000000 (viz (1)), kdy transakce má zámek typu SX a čeká na typ SSX. Zámek typu SX, na rozdíl od SSX, může získat pouze jedna transakce. Podle toho, že se nečekalo na uzamknutí řádků (viz (2)), bylo jasné, že se jednalo o problém přímo s uzamknutím celého databázovém objektu (tabulka, index, ...). Jak ho ale zjistit?
Struktura jména resource TM-003f719d-00000000 mi nic neříkala a nedokázal jsem odvodit, z čeho je složena. Oracle má sice metadata o svých objektech, ale jsou to ID typu NUMBER (sloupec OBJECT_ID), takže tudy cesta nevedla:
pohled DBA_OBJECTS
Po dalším pátrání na Oracle Metalinku se mi podařilo dohledat dokument Doc ID 62365.1, ve kterém se píše:
dokument Doc ID 62365.1
Takže TM je typ zámku a 003f719d je ID objektu, ale v HEX formátu (pozn.: také mě to mohlo napadnout :-) ). Převedl jsem tedy 003f719d do desítkové soustavy (4157853) a podíval se do metadat, kterému databázovému objektu patří:
identifikace objeku
Problém tedy vznikl při pokusu o uzamknutí tabulky PARCELY_PARCELY. Při kontrola dalších trace se vždy jednalo pouze o tuto tabulku. Struktura tabulky je jednoduchá, její účel je pouze vazba mezi původními a nově vznikajícími parcelami. Má jen dva sloupce VZNIKAJICI_PARCELY_ID a PUVODNI_PARCELY_ID a oba se přes cizí klíče (FK) odkazují do tabulky PARCELY. Proč se ale zamykala celá?
Podíval jsem se na indexy a bylo jasno. Tabulka měla sice složený index nad oběma sloupci, ale ne nad sloupcem PUVODNI_PARCELY_ID:
indexy na tabulce PARCELY_PARCELY
To je sice v pořádku pro logiku aplikace, protože se k tabulce vždy přistupuje i přes VZNIKAJICI_PARCELY_ID, ale problém vznikne, když se má mazat záznam v tabulce PARCELY (kde se maže i při mazání STAVBY nebo NEMOVITOSTI). Oracle se musí v ten okamžik podívat do tabulky PARCELY_PARCELY, zda tam nejsou záznamy, které by mazání bránily. Pokud se kontroluje obsah sloupce VZNIKAJICI_PARCELY_ID, tak použije index a je to OK. Ale pokud se kontroluje obsah sloupce PUVODNI_PARCELY_ID, tak se díky chybějícímu indexu uzamkne celá tabulka, což je problém. Aplikace sama o sobě index na tom sloupci nepotřebuje, ale Oracle už ano.
Řešení bylo jednoduché - vytvoření nového indexu nad sloupcem PUVODNI_PARCELY_ID. Oracle tak při kontrole FK využije index, uzamkne pouze dané záznamy a nikoli celou tabulku. Tohle pracovníkovi při návrhu databáze nedošlo a bývá to poměrně častá chyba. Po vytvoření indexu chyba zmizela, jako mávnutím kouzelného proutku. Problém byl vyřešen a tím mé pátrání skončilo.
Na závěr bych chtěl dodat, že by z toho mohl plynout závěr, že by se měl na každý sloupec, který obsahuje FK, udělat index a byl by pokoj. Mělo by tedy stačit zkontrolovat, např. jednoduchým dotazem do metadat Oracle, zda každý takový sloupec index má. To ale není dobrý přístup. Sice se vyhnete těmto problémům, ale budete nutit Oracle tento index udržovat, což stojí nějaké zdroje (disk, I/O, CPU). Je to třeba posuzovat případ od případu. Pokud se například odkazujete na číselníkovou položku, která se nemaže (číselníkové položky se typicky jen zneplatní), tak index potřeba není, protože tam žádná kontrola neprobíhá.
