问题描述
客户环境中出现了ORA-60死锁错误,检查日志发现,持有锁和等待锁的是同一个会话。
一般来说构成死锁至少需要两个会话,而当前的问题是一个会话引发的:
Wed Nov 23 10:19:46 2011 ORA-00060: Deadlock detected. More info IN file /oracle/admin/db1/udump/db1_ora_3408686.trc.
对应的详细信息:
*** 2011-10-29 10:11:28.970 *** SERVICE NAME:(db1) 2011-10-29 10:11:28.960 *** SESSION ID:(5562.45) 2011-10-29 10:11:28.960 DEADLOCK DETECTED ( ORA-00060 ) [TRANSACTION Deadlock] The following deadlock IS NOT an ORACLE error. It IS a deadlock due TO USER error IN the design OF an application OR FROM issuing incorrect ad-hoc SQL. The following information may aid IN determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process SESSION holds waits process SESSION holds waits TX-000c0016-000499ad 16 5562 X 16 5562 X SESSION 5562: DID 0001-0010-00000092 SESSION 5562: DID 0001-0010-00000092 ROWS waited ON: SESSION 5562: obj - rowid = 00009050 - AAAJBQAAWAAArQ6AAG (dictionary objn - 36944, file - 22, block - 177210, slot - 6) Information ON the OTHER waiting sessions: END OF information ON OTHER waiting sessions.
专家解答
可以看到,等待的和持有锁的是同一个会话。
根据trace信息记录的对象,发现问题是自治事务导致的。
在主事务中如果更新了部分记录,这是启动自治事务更新同样的记录,就会造成死锁,下面通过一个简单的例子模拟了这个错误的产生:
SQL> CREATE TABLE t (id NUMBER, name varchar2(30)); TABLE created. SQL> INSERT INTO t SELECT rownum, tname FROM tab; 4 ROWS created. SQL> commit; Commit complete. SQL> CREATE OR REPLACE PROCEDURE p_test AS 2 pragma autonomous_transaction; 3 BEGIN 4 UPDATE t SET name = name WHERE id = 1; 5 commit; 6 END; 7 / PROCEDURE created. SQL> BEGIN 2 UPDATE t SET name = name WHERE id = 1; 3 p_test; 4 END; 5 / BEGIN * ERROR at line 1: ORA-00060: deadlock detected while waiting FOR resource ORA-06512: at "TEST.P_TEST", line 4 ORA-06512: at line 3
在使用自治事务的时候要避免当前事务锁定的记录和自治事务中锁定的记录相互冲突。
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。