RE: Re:RE: Deadlock

Jonathan,

        Thanks for the explanation. I've always addressed deadlocks as a row-level issue, but now I see how it can also be a block-level issue. What a way to start the holidays, with new knowledge (and some tests to run!).

Dan Fink

-----Original Message-----
Sent: Friday, December 20, 2002 10:10 AM To: Multiple recipients of list ORACLE-L

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: Fink, Dan
  INET: Dan.Fink_at_mdx.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).