Re: Oracle 8.16 Deadlock

"Peter Lasner" <plasner_at_de.imshealth.com> wrote in message news:<3c3d8bff_4_at_news2.prserv.net>...
> Does someone know any circumstances so that one session is blocking itself ?
> The trace says:
> ORA-04020: deadlock detected while trying to lock object
> T49PDGI.PDGI_COUNTRIES
> --------------------------------------------------------
> object waiting waiting blocking blocking
> handle session lock mode session lock mode
> -------- -------- -------- ---- -------- -------- ----
> 3d892524 3b643688 3bb2b8dc X 3b643688 38dbcae0 S
>
> I use Oracle 8.16 on AIX
> Thank you in advance

I ran into this a year or so ago. In my case, the underlying cause was due to the inability to get a slot in the ITL. This was a third party app and they had created the table with a PCTFREE of 10. Unfortunately, due to the nature of the app, the rows were inserted with minimal information and then grew later as more columns were updated. This led to a significant amount of row migration and very full blocks. INITRANS was set to 1 and MAXTRANS was 10 (or something along those lines).

I was able to fix the problem by rebuilding the table. I set pctfree to 20 and, after talking to the vendor about the nature of the app, I set INITRANS to 4. We still have some full blocks, but row migration has been minimized and we have not had a 'self-deadlocking' session since.

By the way, without more information it's difficult to be sure if this is your problem. I will say that the lock modes (held=x, requested=s) are similar to my situation. If you have access to MetaLink, you can refer to document 62354.1, it gives some background information on these lock types and situations that cause this combination.

HTH,
Patrick Received on Thu Jan 10 2002 - 12:37:32 CST