Re: Re:RE: Deadlock

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

<Original Post>
Hi
I have been noticing some times following error with one table during update.

DEADLOCK DETECTED
Current SQL statement for this session:
"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:"

Is chaning of INITTRANS would help ?
Thx
-Seema

</Original Post>

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, December 20, 2002 12:10 PM

>
> Set maxtrans to 2 on a table.
> Insert three rows into the same block
> and commit.
>
> Use three sessions to update one row
> each. The third transaction has to wait
> for one of the other two transactions to
> commit, as there are insufficient ITL
> (interested transaction list) entries for
> three concurrent transactions on the same
> block.
>
> Now repeat the experiment with:
> rows A1, B1, C1 in block 1
> rows A2, B2, C2 in block 2.
>
> Session X updates row A1 and A2,
> Session Y updates row B1
> Session Z updates row C2
> Session Y tries to update row B2
> and waits because the ITL is full
> Session Z tries to update row C1
> and waits because the ITL is full
>
> With a little luck, Y will be waiting for Z
> and Z will be waiting for Y (i.e. DEADLOCK)
> but you may have to fiddle with a more complex
> example, as both X and Y might end up waiting
> for A.
>
>
> It's easier to do this in 8.1 because MAXTRANS
> can be set to 1, so you need only use two
> sessions and two rows per block.
>
>
>
> 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 16:56
>
>
> >Jonathan,
> > What do you mean by ITL starvation? And how would it result in a
> >deadlock?
> >
> >Dan Fink
> >
>
>
> --
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.com

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).