Re: Oracle 8.16 Deadlock

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 11 Jan 2002 10:08:39 -0000
Message-ID: <1010743617.8306.0.nnrp-01.9e984b29@news.demon.co.uk>

The deadlock is a library cache deadlock, not a data deadlock, so the problem may be an Oracle bug, or may be an unexpected side-effect from a pl/sql action.

Consider the following daft procedure:

create or replace procedure daft as
begin

execute immediate 'alter procedure daft compile'; end;

Under Oracle 9, if you execute procedure DAFT, then you will find yourself waiting for an exclusive library cache pin - which you will not be able to acquire because your session is holding the procedure with a share library cache pin whilst it executes it. After 5 minutes you get a time-out.

Perhaps something similar, but a little more obscure, is happening in your case, and 8.1.6 does not respond so gracefully.

What type of object is: T49PDGI.PDGI_COUNTRIES ? A package, procedure, function, table, IOT, partitioned table, partitioned IOT etc. ?

I have seen some very strange events in 8.1.6 when you try to do partition maintenance on partitioned IOTs - and if you try to execute partition maintenance on a partitioned IOT from with a procedure which is dependent on that partitioned IOT you might get the problem you are seeing.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Peter Lasner wrote in message <3c3ea093_2_at_news2.prserv.net>...


> I can't read this  trace. Can you give me some hints?

>What I really do no understand is that one and the same session is

blocking,


>I thought that there have to be two sessions and two tables

>involved to get a deadlock. Below the complete trace file.

>Regards Peter

>

>Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production

>With the Partitioning option

>JServer Release 8.1.6.0.0 - Production

>ORACLE_HOME = /usr/oracle/app/product/8.1.6

>System name: AIX

>Node name: r011009e

>Release: 3

>Version: 4

>Machine: 000238544C00

>Instance name: PPDGI49

>Redo thread mounted by this instance: 1

>Oracle process number: 35

>Unix process pid: 33282, image: oracle_at_r011009e (TNS V1-V3)

>

>*** 2002-01-09 23:21:13.432

>*** SESSION ID:(54.18253) 2002-01-09 23:21:13.203

>A deadlock among DDL and parse locks is detected.

>This deadlock is usually due to user errors in

>the design of an application or from issuing a set

>of concurrent statements which can cause a deadlock.

>This should not be reported to Oracle Support.

>The following information may aid in finding

>the errors which cause the deadlock:

>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

>--------------------------------------------------------

>---------- DUMP OF WAITING AND BLOCKING LOCKS ----------

>--------------------------------------------------------

>------------- WAITING LOCK -------------

>----------------------------------------

>SO: 3bb2b8dc, type: 33, owner: 3ba79f10, flag: INIT/-/-/0x00

>LIBRARY OBJECT LOCK: lock=3bb2b8dc handle=3d892524 request=X

>call pin=0 session pin=0

>user=3b643688 session=3b643688 count=0 flags=[00] savepoint=39020

>LIBRARY OBJECT HANDLE: handle=3d892524

>name=T49PDGI.PDGI_COUNTRIES

>hash=60e43856 timestamp=08-09-2001 19:07:44

>namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]

>kkkk-dddd-llll=0000-0005-0005 lock=S pin=S latch=6

>lwt=3d89253c[3bb2b8ec,3bb2b8ec] ltm=3d892544[3d892544,3d892544]

>pwt=3d892554[3d892554,3d892554] ptm=3d8925ac[3d8925ac,3d8925ac]

>ref=3d89252c[3d74e674,3d865d74]

>  LIBRARY OBJECT: object=3bf098f8

>  type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0

>  DATA BLOCKS:

>  data#     heap  pointer status pins change

>  ----- -------- -------- ------ ---- ------

>      0 37f5b72c 3754a9b0 I/P/A     0 NONE

>      2 3bf0998c 3d5fc288 I/P/A     1 NONE

>------------- BLOCKING LOCK ------------

>----------------------------------------

>SO: 38dbcae0, type: 33, owner: 3bb23aac, flag: INIT/-/-/0x00

>LIBRARY OBJECT LOCK: lock=38dbcae0 handle=3d892524 mode=S

>call pin=3bb27334 session pin=0

>user=3b643688 session=3b643688 count=2 flags=PNC/[04] savepoint=709

>LIBRARY OBJECT HANDLE: handle=3d892524

>name=T49PDGI.PDGI_COUNTRIES

>hash=60e43856 timestamp=08-09-2001 19:07:44

>namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]

>kkkk-dddd-llll=0000-0005-0005 lock=S pin=S latch=6

>lwt=3d89253c[3bb2b8ec,3bb2b8ec] ltm=3d892544[3d892544,3d892544]

>pwt=3d892554[3d892554,3d892554] ptm=3d8925ac[3d8925ac,3d8925ac]

>ref=3d89252c[3d74e674,3d865d74]

>  LIBRARY OBJECT: object=3bf098f8

>  type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0

>  DATA BLOCKS:

>  data#     heap  pointer status pins change

>  ----- -------- -------- ------ ---- ------

>      0 37f5b72c 3754a9b0 I/P/A     0 NONE

>      2 3bf0998c 3d5fc288 I/P/A     1 NONE

>--------------------------------------------------------

>This lock request was aborted.

>

Received on Fri Jan 11 2002 - 04:08:39 CST