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

最新推荐文章于 2024-08-28 19:34:48 发布

Edison徐

最新推荐文章于 2024-08-28 19:34:48 发布

阅读量3k

点赞数

分类专栏： Oracle Java 文章标签： java 数据库索引

本文链接：https://blog.csdn.net/xeseo/article/details/11138305

版权

Java 同时被 2 个专栏收录

89 篇文章 0 订阅

订阅专栏

Oracle

3 篇文章 0 订阅

订阅专栏

今天遇到一个数据库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 get a 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