Re: ORA-600 Deadlock Issues

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sat, 25 Jun 2005 05:47:04 +0000
Message-Id: <1119678424l.9138l.0l@medo.noip.com>

On 06/25/2005 12:34:02 AM, Egor Starostin wrote:
> Yes. By default, for every deadlock Oracle generates trace file in
> USER_DUMP_DEST (so, you don't need to explicitly 'alter session set
> events...').

The manual says so, but I did see versions of oracle which did not write down a trace file. If he is not getting a trace file, he may have one of those versions. Setting an event is the only way.

> You can find info about how to read deadlock graph in Metalink Note:62365.1
>
> Also note that hanganalyze command will not be helpful in your
> situation. Hanganalyze can only diagnose locks or internal Oracle
> deadlocks.

This is not true:

$ sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Jun 25 01:28:19 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> lock table emp in exclusive mode;

Table(s) Locked.

SQL> lock table dept in exclusive mode;

SQL*Plus: Release 10.1.0.4.0 - Production on Sat Jun 25 01:28:40 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> lock table dept in exclusive mode;

Table(s) Locked.

SQL> lock table emp in exclusive mode;

SCOTT sessions are 33 & 43. Now, take a look at the hanganalyze output below. What are the first two sessions and what are they waiting for?

HANG ANALYSIS:

Open chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :

<0/43/9/0x5bda3990/9376/SQL*Net message from client>

<0/33/21/0x5bda4dd0/9379/enq: TM - contention> Other chains found: Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/34/23/0x5bda52e0/9459/No Wait> Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/40/1/0x5bda3ea0/9357/Queue Monitor Wait> Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/41/42/0x5bda57f0/9461/wakeup time manager> Extra information that will be dumped at higher levels: [level 4] : 1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW]

Hanganalyze will go through all the sessions and try to connect the addresses that those sessions are waiting on into a graph. If the graph is circular, there is a problem. It catches everything. Checked and proven. The output of hanganalyze is not particularly useful, though, becuase it will only reveal sessions, not resources that the sessions are waiting on For that, there is a little table called V$SESSION, with hooks to V$SQL.

> Usual application deadlocks Oracle succesfully reveals by
> itself.

That is true, unless you have one of THOSE versions.

-- 
Mladen Gogala
Oracle DBA


--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jun 25 2005 - 01:52:23 CDT