Re: Re:RE: Deadlock

There is a deadlock here - but I confused the issue by making complete garbage of the last phrase. Instead of:

>> both X and Y might end up waiting for A.

I should have said

>> both Y and Z might end up waiting for X
  (which is when you won't get the deadlock)

The critical point comes in the previous paragraph though:

>> With a little luck, Y will be waiting for Z
>> and Z will be waiting for Y (i.e. DEADLOCK)

For Oracle 9, I have only introduced the X session to take out one ITL slot from each of the two blocks because Oracle 9 forces a minimum value of 2 entries per ITL.

This really is a deadlock - which will show a deadlock graph with holders in mode 6 and waiters in mode 4. (X and S if I've got the letters right - personally I prefer numbers).

Regards

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

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23

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

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 20 December 2002 22:45

>Jonathon,
>
>This produces ITL waits for sessions Y and Z; but this is not
deadlock. The
>deadlock occurs due to a situation where the Session 1 waits for
something
>to finish in Session 2, which in turn waits for Session 1 AND, this
is
>important, Oracle detects it and kills one of them, rolling back the
>changes, making a deadlock detected error. Is this not the true error
>message that occured in the original thread?
>
>In your example, sessions Y and Z will wait indefinitely until X
commits or
>rolls back. This is not going to be detected by Oracle nor killed by
it. So
>you wouldn't see a message DEADLOCK DETECTED in alert log. Therefore
setting
>INITRANS higher is not going to help at all. Rather the application
logic
>should be checked to remove a real locking conflict.
>
>Am I correct, or am I missing something here?
>
>Arup Nanda
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).