測試控制全局死鎖的隱含參數_lm_dd_interval時，突然想到這個問題。
RAC全局死鎖檢測時間：http://yangtingkun.net/?p=955
Oracle的死鎖判斷是沒有優先級的，也就是說，當兩個或多個會話發生死鎖的時候，無法指定犧牲哪個會話，而是由Oracle隨機決定。
不過對于RAC環境而言，死鎖的檢查不在是內部的隨機實現，Oracle通過隱含參數_lm_dd_interval來控制死鎖的檢測時間。更重要的是，對于RAC環境而言，Oracle允許不同實例設置不同的值。而不同實例的檢測死鎖間隔不同，就意味著優先級的出現。
如果實例1上設置該值為默認值60秒，而實例2設置為30秒，那么當發生死鎖后，永遠是實例2上先檢測到死鎖，也就是說，實例2上會話會被犧牲掉。
這是兩個實例上設置該參數相同的情況，兩個會話分別連接到兩個實例，產生死鎖。實例1上的會話1：
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select instance_number, instance_name from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
1 orcl1
SQL> set sqlp 'I1S1> '
I1S1> show parameter _lm
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_lm_dd_interval integer 30
I1S1> set timing on
I1S1> update t_deadlock set name = 'a1' where id = 1;
1 row updated.
Elapsed: 00:00:00.07
在實例2上連接會話2：
SQL> select name from v$database;
NAME
---------
ORCL
SQL> select instance_number, instance_name from v$instance;
INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
2 orcl2
SQL> set sqlp 'I2S2> '
I2S2> show parameter _lm
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_lm_dd_interval integer 30
I2S2> set timing on
I2S2> update t_deadlock set name = 'b2' where id = 2;
1 row updated.
Elapsed: 00:00:00.04
I2S2> update t_deadlock set name = 'a2' where id = 1;
會話1上鎖定記錄2，產生死鎖：
I1S1> update t_deadlock set name = 'b1' where id = 2;
第一次是實例2上的會話2被犧牲報錯：
update t_deadlock set name = 'a2' where id = 1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:00:32.15
I2S2> update t_deadlock set name = 'a2' where id = 1;
可以看到，會話2等待30秒后報錯，此時會話2執行同樣的語句再次引發死鎖：
update t_deadlock set name = 'b1' where id = 2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:01:00.39
I1S1> update t_deadlock set name = 'b1' where id = 2;
這次變成實例1上的會話1被犧牲報錯，可以看到tb，會話1經歷了兩次死鎖檢測，因此執行時間為1分鐘。會話1再次引入死鎖：
update t_deadlock set name = 'a2' where id = 1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:01:01.69
I2S2>
被犧牲的又變成了會話2。
上面這個測試是在兩個實例的_lm_dd_interval參數設置相同的情況下，下面修改實例2上的參數設置為5秒：
I2S2> alter system set "_lm_dd_interval" = 5 scope = spfile sid = 'orcl2';
System altered.
Elapsed: 00:00:00.09
I2S2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
I2S2> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2095672 bytes
Variable Size 121636296 bytes
Database Buffers 150994944 bytes
Redo Buffers 6291456 bytes
Database mounted.
Database opened.
I2S2> show parameter _lm
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_lm_dd_interval integer 5
I2S2> update t_deadlock set name = 'b2' where id = 2;
1 row updated.
Elapsed: 00:00:00.06
實例2參數生效后連接會話更新該表，實例1上的會話1取消之前的修改，重新進行更新：
1 row updated.
Elapsed: 00:10:08.98
I1S1> rollback;
Rollback complete.
Elapsed: 00:00:00.00
I1S1> update t_deadlock set name = 'a1' where id = 1;
1 row updated.
Elapsed: 00:00:00.01
I1S1> update t_deadlock set name = 'b1' where id = 2;
下面在實例2上的會話2，引入死鎖：
I2S2> update t_deadlock set name = 'a2' where id = 1;
update t_deadlock set name = 'a2' where id = 1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:00:06.07
I2S2> update t_deadlock set name = 'a2' where id = 1;
update t_deadlock set name = 'a2' where id = 1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:00:05.95
I2S2> update t_deadlock set name = 'a2' where id = 1;
update t_deadlock set name = 'a2' where id = 1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:00:06.63
I2S2> update t_deadlock set name = 'a2' where id = 1;
update t_deadlock set name = 'a2' where id = 1
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:00:05.89
顯然由于不同實例的_lm_dd_interval參數的值設置不同，現在每次死鎖都會在設置值更小的實例2上被檢測，實例2上的會話每次都會被死鎖犧牲掉。嘗試設置不同的參數值在不同實例上設置死鎖檢測優先級獲得成功。