关于Oracle数据库的死锁（转书摘）_orcal数据库死锁-优快云博客

本文探讨了数据库中死锁的现象及其原因，特别是外键未加索引所带来的全表锁问题，以及如何通过索引优化来解决这些问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://book.youkuaiyun.com/bookfiles/82/index.html

6.2.6 死锁

如果你有两个会话，每个会话都持有另一个会话想要的资源，此时就会出现死锁（ deadlock ）。例如，如果我的数据库中有两个表 A 和 B ，每个表中都只有一行，就可以很容易地展示什么是死锁。我要做的只是打开两个会话（例如，两个 SQL*Plus 会话）。在会话 A 中更新表 A ，并在会话 B 中更新表 B 。现在，如果我想在会话 B 中更新表 A ，就会阻塞。会话 A 已经锁定了这一行。这不是死锁；只是阻塞而已。我还没有遇到过死锁，因为会话 A 还有机会提交或回滚，这样会话 B 就能继续。

如果我再回到会话A，试图更新表 B，这就会导致一个死锁。要在这两个会话中选择一个作为“牺牲品”，让它的语句回滚。例如，会话B中对表 A的更新可能回滚，得到以下错误：

想要更新表 B 的会话A还阻塞着，Oracle不会回滚整个事务。只会回滚与死锁有关的某条语句。会话B仍然锁定着表 B中的行，而会话A还在耐心地等待这一行可用。收到死锁消息后，会话B必须决定将表 B上未执行的工作提交还是回滚，或者继续走另一条路，以后再提交。一旦这个会话执行提交或回滚，另一个阻塞的会话就会继续，好像什么也没有发生过一样。

Oracle认为死锁很少见，而且由于如此少见，所以每次出现死锁时它都会在服务器上创建一个跟踪文件。这个跟踪文件的内容如下：

显然，Oracle认为这些应用死锁是应用自己导致的错误，而且在大多数情况下，Oracle的这种看法都是正确的。不同于许多其他的RDBMS，Oracle中极少出现死锁，甚至可以认为几乎不存在。通常情况下，必须人为地提供条件才会产生死锁。

根据我的经验，导致死锁的头号原因是外键未加索引（第二号原因是表上的位图索引遭到并发更新，这个内容将在第11章讨论）。在以下两种情况下，Oracle在修改父表后会对子表加一个全表锁：

q 如果更新了父表的主键（倘若遵循关系数据库的原则，即主键应当是不可变的，这种情况就很少见），由于外键上没有索引，所以子表会被锁住。

q 如果删除了父表中的一行，整个子表也会被锁住（由于外键上没有索引）。

在Oracle9 i及以上版本中，这些全表锁都是短期的，这意味着它们仅在DML操作期间存在，而不是在整个事务期间都存在。即便如此，这些全表锁还是可能（而且确实会）导致很严重的锁定问题。下面说明第二点，如果用以下命令建立了两个表：

然后执行以下语句：

到目前为止，还没有什么问题。但是如果再到另一个会话中，试图删除第一条父记录：

此时就会发现，这个会话立即被阻塞了。它在执行删除之前试图对表 C加一个全表锁。现在，别的会话都不能对 C中的任何行执行 DELETE、 INSERT或 UPDATE（已经开始的会话可以继续，但是新会话将无法修改 C）。

更新主键值也会发生这种阻塞。因为在关系数据库中，更新主键是一个很大的禁忌，所以更新在这方面一般没有什么问题。在我看来，如果开发人员使用能生成SQL的工具，而且这些工具会更新每一列，而不论最终用户是否确实修改了那些列，此时更新主键就会成为一个严重的问题。例如，假设我们使用了Oracle Forms，并为表创建了一个默认布局。默认情况下，Oracle Forms会生成一个更新，对我们选择要显示的表中的每一列进行修改。如果在 DEPT表中建立一个默认布局，包括3个字段，只要我们修改了 DEPT表中的任何列，Oracle Forms都会执行以下命令：

在这种情况下，如果 EMP表有 DEPT的一个外键，而且在 EMP表的 DEPTNO列上没有任何索引，那么更新 DEPT时整个 EMP表都会被锁定。如果你使用了能生成SQL的工具，就一定要当心这一点。即便主键值没有改变，执行前面的SQL语句后，子表 EMP也会被锁定。如果使用Oracle Forms，解决方案是把这个表的 UPDATE CHANGED COLUMNS ONLY属性设置为 YES。这样一来，Oracle Forms会生成一条 UPDATE语句，其中只包含修改过的列（而不包括主键）。

删除父表中的一行可能导致子表被锁住，由此产生的问题更多。我已经说过，如果删除表 P中的一行，那么在DML操作期间，子表 C就会锁定，这样能避免事务期间对 C执行其他更新（当然，这有一个前提，即没有人在修改 C；如果确实已经有人在修改 C，删除会等待）。此时就会出现阻塞和死锁问题。通过锁定整个表 C，数据库的并发性就会大幅下降，以至于没有人能够修改 C中的任何内容。另外，出现死锁的可能性则增加了，因为我的会话现在“拥有”大量数据，直到提交时才会交出。其他会话因为 C而阻塞的可能性也更大；只要会话试图修改 C就会被阻塞。因此，我开始注意到，数据库中大量会话被阻塞，这些会话持有另外一些资源的锁。实际上，如果其中任何阻塞的会话锁住了我的会话需要的资源，就会出现一个死锁。在这种情况下，造成死锁的原因是：我的会话不允许别人访问超出其所需的更多资源（在这里就是一个表中的所有行）。如果有人抱怨说数据库中存在死锁，我会让他们运行一个脚本，查看是不是存在未加索引的外键，而且在99%的情况下都会发现表中确实存在这个问题。只需对外键加索引，死锁（以及大量其他的竞争问题）都会烟消云散。下面的例子展示了如何使用这个脚本来找出表 C中未加索引的外键：

这个脚本将处理外键约束，其中最多可以有8列（如果你的外键有更多的列，可能就得重新考虑一下你的设计了）。首先，它在前面的查询中建立一个名为 CONS的内联视图（inline view）。这个内联视图将约束中适当的列名从行转置到列，其结果是每个约束有一行，最多有8列，这些列分别取值为约束中的列名。另外，这个视图中还有一个列 COL_CNT，其中包含外键约束本身的列数。对于这个内联视图中返回的每一行，我们要执行一个关联子查询（correlated subquery），检查当前所处理表上的所有索引。它会统计出索引中与外键约束中的列相匹配的列数，然后按索引名分组。这样，就能生成一组数，每个数都是该表某个索引中匹配列的总计。如果原来的 COL_CNT大于所有这些数，那么表中就没有支持这个约束的索引。如果 COL_CNT小于所有这些数，就至少有一个索引支持这个约束。注意，这里使用了 NVL2函数，我们用这个函数把列名列表“粘到”一个用逗号分隔的列表中。这个函数有3个参数：A、B和C。如果参数A非空，则返回B；否则返回参数C。这个查询有一个前提，假设约束的所有者也是表和索引的所有者。如果另一位用户对表加索引，或者表在另一个模式中（这两种情况都很少见），就不能正确地工作。

所以，这个脚本展示出，表 C 在列 X 上有一个外键，但是没有索引。通过对 X 加索引，就可以完全消除这个锁定问题。除了全表锁外，在以下情况下，未加索引的外键也可能带来问题：

q 如果有 ON DELETE CASCADE，而且没有对子表加索引：例如， EMP是 DEPT的子表， DELETE DEPTNO = 10应该 CASCADE（级联）至 EMP 。如果 EMP中的 DEPTNO没有索引，那么删除 DEPT表中的每一行时都会对 EMP做一个全表扫描。这个全表扫描可能是不必要的，而且如果从父表删除多行，父表中每删除一行就要扫描一次子表。

q 从父表查询子表：再次考虑 EMP/DEPT例子。利用 DEPTNO查询 EMP表是相当常见的。如果频繁地运行以下查询（例如，生成一个报告），你会发现没有索引会使查询速度变慢：

n select * from dept, emp

n where emp.deptno = dept.deptno and dept.deptno = :X;