Oracle Database Discussions

Hello members,


I am investigating a production issue and need your view on this. (I am developer doing 3rd line support)
Environment is 10.2.0.3, a 4-node RAC.

Unfortunately I do not have access to any RAC environment, so at this point I am just fishing
to get a mental picture of what might be going on.

I have two batch jobs, that when running at the same time, can cause ORA-04020: deadlock detected while trying to lock object MY.PAR


Here is a small test case which partially reproduce the problem.
(Partially because this small case is not RAC related, and real SES2 does not do DDL)

SES1> CREATE TABLE par (x) AS
 2  SELECT     CAST(LEVEL AS CHAR(40))
 3  FROM       DUAL
 4  CONNECT BY LEVEL <= 100000;

SES1> CREATE TABLE chld (x CHAR (2000));
SES1> ALTER TABLE par ADD CONSTRAINT par_pk PRIMARY KEY(x);
SES1> ALTER TABLE chld ADD CONSTRAINT chld_par_fk FOREIGN KEY(x) REFERENCES par; 

-- In Another session, do this (In real situation SES2 does a SELECT):
SES2> ALTER TABLE par MODIFY x CHAR(2000);

-- While above is running, do this in first session:
SES1> ALTER TABLE chld MODIFY CONSTRAINT chld_par_fk DISABLE;

--And SES1 will see:
ORA-04020: deadlock detected while trying to lock object KORT.PAR

Now this, ALTER TABLE par MODIFY x CHAR(2000), is only to produce the problem. The real situation is just a select

SELECT * FROM PAR, other_parent
  WHERE PAR.something = other_parent.something;

What I am trying to understand is why this SELECT could lead to library cache locks.
I have been handed a lmd0 trace file, that shows that object remastering might be
taken place. I say might, because I fail to understand these:

*** 2011-03-28 02:04:25.990
* kjdrchkdrm: found an RM request in the request queue 
  Dissolve pkey 8660379
DRM(1985) ignoring dissolve of 8660379
* kjdrchkdrm: found an RM request in the request queue 
  Dissolve pkey 8660382
DRM(1985) ignoring dissolve of 8660382
*** 2011-03-28 02:04:58.562
* kjdrchkdrm: found an RM request in the request queue 
  Dissolve pkey 8660385
DRM(1985) ignoring dissolve of 8660385
* kjdrchkdrm: found an RM request in the request queue 
  Dissolve pkey 8660388
DRM(1985) ignoring dissolve of 8660388
*** 2011-03-28 02:07:12.005
user session for deadlock lock 0xa5ca54158
..
..
-- Here follows information about the deadlock  - Please let me know if you want to see it {noformat} ;){noformat} 
..

What does this mean, Dissolve? - And ignoring dissolve?

What is pkey? - From what I've read so far my impression was Object_ID, whic it isn't. What is it then?



To sum up.
Can I from above say that Object Remastering is taking place?

Will Object Remastering involve library cache locks?

Can this explain the deadlock?


I have tried reading [ID 166924.1], with no heureka happening. As said, this is a fishing expedition, and any pointers are most welcome.


Regards
Peter