Shulou(Shulou.com)06/01 Report--

RAC how to carry out deadlock detection time, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

For a single instance database, deadlock detection is completed in seconds, while in RAC environment, the deadlock detection time is 1 minute by default.

If a deadlock occurs in a single instance environment, one of the processes is aborted immediately, and the user can quickly get an error return. For RAC, deadlock detection is not completed in real time, but takes about 60 seconds.

Session 1 execution:

SQL > create table t_deadlock (id number primary key, name varchar2 (30))

Table created.

Elapsed: 00:00:00.12

SQL > insert into t_deadlock values (1,'a')

1 row created.

Elapsed: 00:00:00.00

SQL > insert into t_deadlock values (2,'b')

1 row created.

Elapsed: 00:00:00.00

SQL > commit

Commit complete.

Elapsed: 00:00:00.00

SQL > update t_deadlock set name = 'a1' where id = 1

1 row updated.

Elapsed: 00:00:00.00

Session 2 execution:

SQL > set timing on

SQL > update t_deadlock set name = 'b2' where id = 2

1 row updated.

Elapsed: 00:00:00.00

SQL > update t_deadlock set name = 'a2' where id = 1

At this point, session 2 waits for the final action of session 1, and the following session 1 updates the row locked by session 2, causing a deadlock:

SQL > update t_deadlock set name = 'b1' where id = 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.12

As you can see, the deadlock timeout detection is 1 minute.

The detection time of this deadlock can be adjusted, and Oracle is controlled by the implicit parameter _ lm_dd_interval:

SQL > conn / as sysdba

Connected.

SQL > alter system set "_ lm_dd_interval" = 30 scope = spfile

System altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 281018368 bytes

Fixed Size 2095672 bytes

Variable Size 104859080 bytes

Database Buffers 167772160 bytes

Redo Buffers 6291456 bytes

Database mounted.

Database opened.

Test the deadlock detection time again, session 1:

SQL > update t_deadlock set name = 'a1' where id = 1

1 row updated.

SQL > set timing on

Session 2 performs updates:

SQL > set timing on

SQL > update t_deadlock set name = 'b2' where id = 2

1 row updated.

Elapsed: 00:00:00.02

SQL > update t_deadlock set name = 'a2' where id = 1

Session 1 performs an update that causes a deadlock:

SQL > update t_deadlock set name = 'b1' where id = 2

After about 30 seconds, session 2 reports an error ORA-60:

Update t_deadlock set name = 'a2' where id = 1

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

Elapsed: 00:00:30.27

In version 10.2.0.2, there is a bug in Oracle, which allows this parameter to be set to 0. After 10.2.0.3, the bug is modified. If set to 0, the database cannot start normally:

[oracle@node1 ~] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0-Production on Mon Jun 4 07:54:09 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL > startup

ORA-00067: invalid value 0 for parameter _ lm_dd_interval; must be at least 1

Finally, modifying the implied parameter is not recommended by Oracle, and modifying this parameter is bound to affect the normal working mode of RAC, which will increase the busy degree of LDM process, and may affect the stability and availability of RAC environment.

If it is true that the deadlock check time of the foreground is high, it is recommended that you test it in detail in the test environment before deploying to the production environment.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.