客户环境中出现了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 |
在使用自治事务的时候要避免当前事务锁定的记录和自治事务中锁定的记录相互冲突。