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 <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
