Deadlocks causados por foreign keys sin indices

A continuación un interesante post de Dan Jankowski en su blog: https://perfstat.wordpress.com

Here is a step-by-step analysis of the deadlock which occurs due to unindexed foreign keys. This analysis was performed on Oracle XE 11.2.0.2 – results may vary on other versions.

A typical deadlock graph arising from an unindexed foreign key is as follows.

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000508a-00000000        27     101    SX             28      12         SSX
TX-00090013-0000019b        28      12     X             27     101           S

This type of deadlock is characterised by at least one session holding a table lock of type TM (table lock), and another session waiting for the TM lock in SSX mode. To help make sense of this, I found it useful to first understand two underlying concepts; how to identify what a lock relates to, and how to trace locks.

Continuar leyendo ...