Re: Oracle detects deadlock (ORA-00060) with just one session open

Hi,

Please see below:

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:4125a6c0$1_at_olaf.komtel.net...
> Hi,
>
> On Ora9i_at_AIX I got a deadlock detected which only 1 connection is
> established. What I am doing is I use OCILobWrite to stuff data into a LOB
> and Oracle tells me:
>
> ORA-00060: deadlock detected while waiting for resource
>
> Then I had a look into the trace files and I can't make sense of it
> (extract from .trc is at the end of the message). Oracle tells me "The
> following deadlock is not an ORACLE error...." I wont post ther entire
> process state here because its pretty lengthy and I dont expect you guys
to
> go through it fully. SO what can be the cause of a deadlock when you just:
>
> * have 1 sole connection to the server
> * lock a row for a LOB update
> * perform OCILobWrite to attempt to load data but its refused with the
given
> error.
>
> If I remember my database lectures from university right, a deadlock can
> only occur through concurrent access when you have more than one session..
> right ?
>
> André Hartmann
> Berlin, Germany
>
> ### small extract from .trc file to follow ###
>
> *** 2004-08-19 15:17:38.625
> *** SESSION ID:(12.38577) 2004-08-19 15:17:38.624
> DEADLOCK DETECTED
> No current SQL statement being executed.
> The following deadlock is not an ORACLE error. It is a
> deadlock due to user error in the design of an application
> or from issuing incorrect ad-hoc SQL. The following
> information may aid in determining the deadlock:
> Deadlock graph:
> ---------Blocker(s)-------- ---------Waiter(s)----

--


> ---

> Resource Name          process session holds waits  process session holds

> waits

> TX-00030003-000006eb        12      12     X             12      12

> S

> session 12: DID 0001-000C-00000208 session 12: DID 0001-000C-00000208

> Rows waited on:

> Session 12: no row

> Information on the OTHER waiting sessions:

> End of information on OTHER waiting sessions.

> ===================================================

> PROCESS STATE

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

> Process global information:

>      process: 70000000c292b00, call: 70000000c336238, xact:

70000000cbaf0d8,


> curses: 70000000c2cb040, usrses: 70000000c2cb040

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

>   SO: 70000000c292b00, type: 2, owner: 0, flag: INIT/-/-/0x00

>   (process) Oracle pid=12, calls cur/top: 70000000c336238/70000000c336238,

> flag: (0) -

>             int error: 0, call error: 0, sess error: 0, txn error 0

>   (post info) last post received: 0 0 4

>               last post received-location: kslpsr

>               last process to post me: 70000000c290300 1 6



The self-deadlock similar to yours was happenning under Oracle 8i when
MAXTRANS was <3 on the lob index.    Allegedly,  it was fixed in 8.1.x.  A
work-around was to increase MAXTRANS in the lob index.


VC