Set global deadlock priority

When the implicit parameter _ lm_dd_interval of global deadlock control is tested, this problem suddenly occurs.

 

The deadlock Determination of Oracle has no priority. That is to say, when two or more sessions are deadlocked, it is determined randomly by Oracle that it cannot specify which session to sacrifice.

However, for the RAC environment, the deadlock check is not implemented randomly internally. Oracle uses the implicit parameter _ lm_dd_interval to control the Deadlock Detection Time. More importantly, for the RAC environment, Oracle allows different instances to set different values. Different instances have different Deadlock Detection intervals, which means that priority is given.

If the default value is 60 seconds for instance 1 and 30 seconds for instance 2, the deadlock is always detected on instance 2 after a deadlock occurs. That is to say, the session on instance 2 will be sacrificed.

This is when this parameter is set to the same for two instances. Two sessions are connected to two instances respectively, resulting in a deadlock. Session 1 on instance 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

Connect Session 2 on instance 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;

Session 1 locks record 2, resulting in a deadlock:

I1S1> update t_deadlock set name = 'b1 'where id = 2;

The first time is that Session 2 on instance 2 is sacrificed:

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;

As you can see, Session 2 reports an error after waiting for 30 seconds. At this time, Session 2 executes the same statement to cause a deadlock again:

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;

This time Session 1 on instance 1 is sacrificed and an error is reported. We can see that tb, Session 1 has undergone two Deadlock Detection, so the execution time is 1 minute. Session 1 introduces deadlocks again:

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>

The sacrifice is changed to session 2.

In the above test, when the _ lm_dd_interval parameter settings of the two instances are the same, the parameter setting on instance 2 is changed to 5 seconds below:

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

After the instance 2 parameter takes effect, connect to the session to update the table. Modify the table before Session 1 on instance 1 is canceled and update it again:

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;

Next we will introduce a deadlock in session 2 on instance 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

Obviously, because the value of the _ lm_dd_interval parameter of different instances is different, every deadlock will be detected on instance 2 with a smaller value, and the session on instance 2 will be sacrificed every time. Try setting different parameter values to set the Deadlock Detection priority on different instances.