設定全域性死鎖優先順序

測試控制全域性死鎖的隱含引數_lm_dd_interval時，突然想到這個問題。

RAC全域性死鎖檢測時間：

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被犧牲報錯，可以看到，會話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上的會話每次都會被死鎖犧牲掉。嘗試設定不同的引數值在不同例項上設定死鎖檢測優先順序獲得成功。