为什么外键最好加上索引？

今天遇到一个数据库DeadLock的问题，与同事讨论，一个同事忽然问了一句：“为什么外键没有索引会导致deadlock“?

为了回答这个问题，我直接翻了Oracle文档，里面两张图画的非常直接。

文章地址：http://docs.oracle.com/cd/B19306_01/server.102/b14220/data_int.htm#sthref3108

Unindexed foreign keys cause DML on the primary key to geta share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) on the foreign key table. This prevents DML on the table by other transactions. The SSX lock is released immediately after it is obtained. If multiple primary keys are updated or deleted, the lock is obtained and released once for each row.

Figure 21-8 Locking Mechanisms When No Index Is Defined on the Foreign Key

Indexed foreign keys cause a row share table lock(also sometimes called a subshare table lock, SS). This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the child table.

Figure 21-9 Locking Mechanisms When Index Is Defined on the Foreign Key