查看: 231|回复: 0

[Oracle数据库] select for update语句造成ORA-00060 deadlock死锁问题分析

发表于 7 天前
太阳http代理AD
测试环境出现问题

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

首先,登录数据库,查看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的语句进行一张表中的数据访问),在特殊的情况下,出现了死锁的情况。

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



太阳http代理AD
回复

使用道具 举报