You Asked

Hi,

here is my problem :

It's been weeks I'm trying to understand why two night jobs conflict with an ORA-04020 deadlock.

My two Oracle sessions (SPAIN - SP and PORTUGAL - PT) are working in their own schemes with their own packages and their own tables.

Though, Oracle traces are explicit : the two sessions traces indicate ORA-04020 between each other on 2 objects from one of the 2 schemes (SP).

ORA-04020: deadlock detected while trying to lock object SP_BOAS_OWN.STK_UNIT_STOCK_PRICE

------------- WAITING LOCK ------------- Session=0x11fd3cc28 Name=SP_BOAS_OWN.STK_UNIT_STOCK_PRICE
------------- BLOCKING LOCK ------------ Session=0x11fba0608 Name=SP_BOAS_OWN.STK_UNIT_STOCK_PRICE
------------- WAITING LOCK ------------- Session=0x11fba0608 Name=SP_BOAS_OWN.STK_UNIT_STOCK_ITEM
------------- BLOCKING LOCK ------------ Session=0x11fd3cc28 Name=SP_BOAS_OWN.STK_UNIT_STOCK_ITEM

After having investigated a lot of things such as wrong synonyms or misconceptions in package being called, I still don't understand what could be happening, because everything is designed to work in its' own scheme.

Would you have an idea of what could provoke a deadlock (library lock) between sessions working in different schemes, but deadlock on one scheme objects ?

thanks for your attention.

regards.

and Connor said...

A library cache deadlock is most commonly when manual (or automatic) recompilation of objects is performed, and there is either a circular or cross object dependency. MOS Note 166924.1 has some other potential causes.

So something is probably causing one or both of the procs to go invalid during their execution, or by something preceding their execution. In terms of diagnosis, you might want to set up something to monitor their status in quick session to see what's causing this.

You could also look at the doing something like:

alter session set events '4020 trace name errorstack, level 15';

at the start of each of the sessions to capture more diagnostic information when it occurs. If you cannot work it out, that trace file will also be useful in getting Support involved.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

laurent claverie, January 17, 2017 - 9:18 am UTC

Hi Connor !

Thanks for your answer.

I already set that event at instance level, but the problem has not happen again yet.

I already sent the two normal trace files to Support showing deadlock, but nothing very useful was answered by Oracle...

What I really can't understand is how 2 tables from only one scheme can be involved in a deadlock dealing with two sessions executing completely separately each of them on their own scheme...

I'll try to be very clear :

In the night whe execute in the same window, 2 $Universe sessions of feeding. Each execute on a country which is materialized by a seperate scheme.

.. and we get a DEADLOCK ! which indicates, that the second sessions is locking tables from the first, although each should deal ony with its' own objects.

How could it be possible ?

Maybe a misconception I haven't seen yet, but if you had another way to investigate or another idea, that would be great.

Again thank you for your first answers !

Regards.

January 18, 2017 - 12:46 am UTC

can you email the existing trace you have to asktom_us@oracle.com, with subject "question id 9532890700346280810"

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

ORA-04020 between 2 sessions dealing with 2 different schemes

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs