Re: ITL and possible deadlocks

Ed,

as I understand, this can only happen if a table was created with small maxtrans. If a transaction can't allocate an ITL in a block, it has to wait. When two of them are waiting for each other ITL slots, it is still a deadlock and should be handled internally. In practice, I've never seen a fast deadlock resolution though.

I've made a test, creating a table with maxtrans = 1 and small rows, so the db block contained multiple rows of that table. 1st transaction updated the rows with id < 10, the 2nd was trying to update rows with id > 20 and just hanged, presumably waiting for the ITL slot...

HTH,
Michael Netrusov,
www.atelo.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, March 21, 2001 01:40

> Michael,
>
> thanks for your response.
> I meant the following situation. Transaction#1 updates a row#1 in block#1 of a
>table. In order to do
> it allocates a ITL slot.
> Transaction#2 updates a row#100 in block#100 of the same table and also allocates
>transaction slot.
> If there is no free space to allocate additional ITL slots in these blocks then
>other transactions
> can't modify these blocks until transaction#1 and transaction#2 do commit or
>rollback. Then
> transaction#1 tryes to update row#101 in the block#100 and transaction2 tryes to
> update row#2 in the block#1. Both transaction have to wait because there are no
>empty ITL slots. You
> can see that there are no share rows and each transaction updates its own rows.
> How Oracle resolve such case?
>
> Ed
>
>
> > Ed,
> >
> > Not sure that I understood it right, but I'll take a try.
> > So there are 2 transactions and each one is trying to update the row which was
>already updated by
> the other transaction?
> > IMHO, if you are trying to place an exclusive lock on the locked row ( it remains
>locked until
> commit ), you either wait or receive
> > an error if "no wait" was specified.
> > Behaviour of the 2nd transaction is trying to place an exclusive lock on the 1st
>transaction' row
> is just the same.
> > If both transactions wait, it's a deadlock situation and should be resolved by
>Oracle itself.
> >
> > HTH,
> > Michael Netrusov,
> > www.atelo.com
> >
> >
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> > Sent: Tuesday, March 20, 2001 10:40
> >
> >
> > > Hello List,
> > >
> > > it's rather a theoretical question. Let say two sessions
> > > update a table. Each transaction allocated a transaction
> > > slot in a block (I consider the two different blocks).
> > > Each block has only one slot and there is no free space
> > > in these blocks to allocate additional transaction slots.
> > > Then each transaction tryes to update a row in the block
> > > which has already been updated by the other transaction.
> > > How Oracle to do with such situation in case when there is
> > > no access to a common resource (I mean a common row) ?
> > >
> > >
> > > Thanks in advance
> > >
> > > Ed
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Shevtsov, Eduard
> > > INET: EShevtsov_at_flagship.ru
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > > San Diego, California -- Public Internet access / Mailing Lists
> > > --------------------------------------------------------------------
> > > 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.com
> > --
> > Author: Michael Netrusov
> > INET: mn_at_g-fax.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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.com
> --
> Author: Shevtsov, Eduard
> INET: EShevtsov_at_flagship.ru
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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.com
-- 
Author: Michael Netrusov
  INET: mn_at_g-fax.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).