Oracle 中的外键与锁

文章目录
  1. 1. 未加索引的外键与锁
  2. 2. 加索引的外键与锁

算是接上篇吧。。。 内容主要来自 Oracle 官方文档,自己重新画了下图。图中配色来自大神 draveness 的文章,小清新的配色真是美美哒。看来我在学画图的路上还要修炼很久啊。。。。。

锁是一种可以防止多个事务错误的更新共享数据的机制,在维护数据库并发性和一致性方面起着关键的作用。在 Oracle 堆组织表中, 数据库锁的行为与外键列是否有索引有关。如果外键未加索引,那么子表可能会被频繁锁住,从而导致死锁,降低并发性。所以,Oracle 官方建议绝对多数情况都为外键加索引,除非父表的唯一键/主键绝对不会更新或删除。

未加索引的外键与锁

当以下条件都满足时,数据库会获取子表的全表锁:

  • 子表的外键列未加索引
  • 父表的主键被修改(比如:删除一行或主键被修改)或者合并到父表。在父表插入一条记录是不会锁住子表的。

假设 hr.departments 是父表,hr.employees 是子表, hr.employees 中的 department_id 是未加索引的外键列。下图展现了修改父表 department_id = 60 这一记录的主键时,数据库加锁的情况:

unindexed_foreign_key

在上图中,数据库在更新父表 department 60 这一记录的主键时,会获得子表 employees 的全表锁。这样其他会话可以查询子表但不允许更新子表。子表的表锁会在父表更新完成后立刻释放。如果修改父表多条记录,那么修改每一条都会获得子表的表锁并释放。

在 Oracle 9i 及以上版本中,全表锁都是短期的,仅在 DML 操作期间存在,而不是整个事务。但即便如此,还是要避免,因为全表锁可能会导致死锁。Tom 也曾说过,导致死锁的头号原因就是外键未加索引(第二号原因是表上的位图索引遭到并发更新)。

需要注意,子表的 DML 不会获得父表的表锁

加索引的外键与锁

当以下条件都满足时,数据库不会获得子表的全表锁。

  • 子表的外键列已加索引
  • 父表的主键正在被修改(比如:删除一行或主键被修改)或合并到父表

下面的图展示了子表 employees 的外键列 department_id 加了索引。 当一个事务从父表 department 中删除 department 280 时, 这一操作不会引起数据库获得子表的全表锁。

父表上的锁是为了防止其他事务获取表级排他锁,但不会阻止父表或子表上的 DML 操作。

indexed_foreign_key

如果子表指明了 ON DELETE CASCADE, 那么删除父表会导致删除子表对应的记录。比如删除父表 department 280 这一记录,那么子表 employees 中 department_id 为 280 的记录也会被删除。 在这种情况下,加锁的规则与删除完主表后再删除子表的记录是一样的。

这篇是不是有点水啊。。。。是时候放出我姑家的小霸王 cookie 宝宝撑下场了。

虽然我又咬人,又乱叫,还喜欢对着窗帘小便。但我知道我是个好狗狗。
————cookie

分享到 评论