select for update语句造成ORA-00060 deadlock死锁问题分析

共计 11567 个字符，预计需要花费 29 分钟才能阅读完成。

测试环境出现问题

今天，收到项目组通知说发生了死锁，让我查一下死锁出现的原因。

首先 ，登录数据库，查看trace 日志所在路径

查看 alert 日志，搜索00060

找到 报错 trace 日志如下：

打开 其中一个 trace 文件，部分内容如下：

这里可以明确的是 select for update 语句造成了死锁。但是考虑到环境为测试环境，没开启归档，不能通过 logminer 挖掘故障期间的 SQL 事物，而且开发人员已经通过 kill session 的方式处理了死锁，所以需要在别的环境中重现该问题，并进行分析。

重现死锁问题

首先连上数据库，启用 HR 用户

选中 COUNTRIES 表作为测试对象

经过验证，COUNTRIES表中的 COUNTRY_ID 字段是唯一的。

用 COUNTRIES 表新建一个 test 表，在 test 表中做测试

情形1：

两个 select for update 语句想要同时锁定一条语句。

Session1：

Sessions2：

这时，session2会一直处于锁等待状态，而不会出现死锁。当把第一个事物 commit 或者 rollback 之后，第二个事物会继续执行。

Session1：

Session2：

显然，两个 select for update 语句想要同时锁定一条语句，并不会出现死锁，而会出现锁等待的现象。

情形2：

第一个 select for update 事物锁定 A row 后，想要再锁定 B row；第二个select for update 事物锁定 B row 后，想要锁定A row。

Session1：

Session 2：

如果 Session1 想要继续持有 Session2 正在持有的row：

Session1：

这时，会出现锁等待的现场，和上一场景类似。如果 Session2 也想持有 Session1 正在持有的row：

Session2：

这时，Session2还处于等待状态，但是 Session1 出现死锁：

Session1：

这时，死锁的现象已经重现，而且，场景 2 从头到尾只使用了一个 select for update 语句，只是换了参数而已。

定位死锁语句

通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。

1）用 dba 用户执行以下语句

如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：

OWNER：死锁语句所用的数据库用户，这里是 HR 用户。

OBJECT_NAME：产生死锁的对象，这里是 TEST 表。

OBJECT_TYPE：产生死锁的对象类型，这里是TABLE。

SID：SESSION标识，常用于连接 其它 列。

SERIAL#：SID有可能会重复，当两个 session 的SID重复时，SERIAL#用来区别session

LOCKWAIT：可以通过这个字段查询出当前正在等待的锁的相关信息，如果有内容表示被死锁或者有锁等待事件。

STATUS：用来判断 session 状态。ACTIVE:正执行 SQL 语句 。INACTIVE: 等待操作。KILLED:被标注为杀死。

OSUSER：客户端操作系统用户名。

MACHINE：客户端操作系统的机器名。

PROCESS：客户端进程的ID。

PROGRAM：客户端执行的应用程序。

2）用 dba 用户执行以下语句，可以查看到被死锁的语句。

或者

这里查出来出现死锁的语句和之前的测试结果一致。

3）分析 trace 日志文件

死锁发生时，可以再 trace 日志文件中找到如下语句：

打开 trace 文件，可以找到死锁的语句：

解决死锁问题

方法一（杀进程）

1）查找死锁的进程：

2）kill掉这个死锁的进程：

这里执行语句为：alter system kill session ‘29,69’;

原先造成死锁的进程被killed。

3）如果还不能解决，使用杀系统进程的方式处理：

这里为了测试，再次重现了死锁，并使用杀进程方式进行处理。

其中，XXX使用第一步查出来的 SID 参数替代

这里查出来的进程号为：4257

杀完进程之后，造成死锁的进程被杀死

查不到死锁进程

方法二（重启库）

查看死锁进程：

方法三（commit || rollback）

Session1：

Session2：

Session1：

Session2：

Session1：

这里 Session1 出现死锁，只要执行 commit 或者 rollback 就可以解除死锁，只不过事务中第一个 SQL 执行成功，第二个 SQL 执行失败。

Session1：

Session2：

此时，死锁状态解除：

问题总结

最后，通过与开发人员交流，得出的结论是出现该问题的原因不是程序设计的问题，而是在开发环境中，有人通过 debug 模式连到服务器上进行代码调试，有人通过客户端的形式访问服务器上的应用，当两者同时调试某一功能时（通过 select for update 的语句进行一张表中的数据访问），在特殊的情况下，出现了死锁的情况。

当然，在开发环境中比较容易出现这类情况，如果需要避免，就需要使用该开发环境的开发人员与测试人员做好沟通了。对于容易出现锁表的功能，可以要求测试人员在测试环境中测试，除特殊情况，尽量减少在开发环境中测试的次数，以免锁表影响开发测试进度。

更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-10/147436.htm