今天在 ORACLE ALLSTAR群里讨论了一个关于队列锁死锁检测的问题，原帖子在这里。 有同学指出对于enqueue lock的死锁检测应当是每3 秒钟检测一次，这样说的依据是通过一个简单可重复的实验可以证明在实际出现ora-00060 dead lock错前process等待了3s:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com
PROCESS A:
set timing on;
update maclean1 set t1=t1+1;
PROCESS B:
update maclean2 set t1=t1+1;
PROCESS A:
update maclean2 set t1=t1+1;
PROCESS B:
update maclean1 set t1=t1+1;
等待3s后 PROCESS A 会报
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:00:03.02
可以看到Process A在检测到死锁之前确实等待了 3s，而且这是一个可以重复的实验，很具有说服力。
事实真的是这样的吗？
来看下面的演示：
SQL> col name for a30
SQL> col value for a5
SQL> col DESCRIB for a50
SQL> set linesize 140 pagesize 1400
SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2 FROM SYS.x$ksppi x, SYS.x$ksppcv y
3 WHERE x.inst_id = USERENV ('Instance')
4 AND y.inst_id = USERENV ('Instance')
5 AND x.indx = y.indx
6 AND x.ksppinm='_enqueue_deadlock_scan_secs';
NAME VALUE DESCRIB
------------------------------ ----- --------------------------------------------------
_enqueue_deadlock_scan_secs 0 deadlock scan interval
SQL> alter system set "_enqueue_deadlock_scan_secs"=18 scope=spfile;
System altered.
Elapsed: 00:00:00.01
SQL> startup force;
ORACLE instance started.
Total System Global Area 851443712 bytes
Fixed Size 2100040 bytes
Variable Size 738198712 bytes
Database Buffers 104857600 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
PROCESS A:
SQL> set timing on;
SQL> update maclean1 set t1=t1+1;
1 row updated.
Elapsed: 00:00:00.06
Process B
SQL> update maclean2 set t1=t1+1;
1 row updated.
SQL> update maclean1 set t1=t1+1;
Process A:
SQL>
SQL> alter session set events '10704 trace name context forever,level 10:10046 trace name context forever,level 8';
Session altered.
SQL> update maclean2 set t1=t1+1;
update maclean2 set t1=t1+1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
