Richard Foote wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:41a62c19$0$17539$afc38c87_at_news.optusnet.com.au...
>
>>qwerty wrote:
>>
>>>Hi,
>>>i need explanations about the initrans and maxtrans parameters, in
>>>particular what happens when the initrans is exceded when the maxtrans is
>>>still respected??.
>>
>>INITRANS is the *initial* number of transaction slots. It's designed to be
>>exceeded on a regular basis. It merely indicates the lowest level of
>>concurrency you are expecting for a segment... it doesn't mean Oracle
>>isn't expecting you to have higher degrees of concurrency.
>>
>>
>>>For example what happens when initrans = 1 and maxtrans = 100 and the
>>>number of transaction on a block is 2, as far i know, oracle can let 2
>>>transaction on the same block if there is enough space in the block in
>>>order to kepp track of the transaction.
>>
>>With INITRANS of 1, one transaction only is guaranteed to be built into
>>the header of every block of the segment. But provided there is free space
>>in the block which the block header can grow 'down' into, then more
>>transaction slots can be acquired. As many as are needed, in fact, until
>>the block runs out of space or you hit MAXTRANS.
>>
>>
>>>But what happen when there isn't enough space in the block ??? an
>>>exception occur ??
>>
>>A buffer busy wait. You queue up for access to a freed transaction slot.
>>That's measured as a wait on the system.
>>
>>
>>>may the low initrans cause a deadlock (2 session locking 2 blocks....) ??
>>
>>No. Deadlocks are caused by two transactions trying to lock each others
>>rows. Nothing to do with this at all.
>>
>
>
> Just in case the OP is somewhat confused by the above regarding the type of
> wait and the issue with deadlocks, and just to elaborate a little on
> Jonathan's post, below is a very simple demo that highlights how indeed
> deadlocks could have everything to do with initrans/maxtrans settings (run
> on 9.2.0.4):
>
> SQL> create tablespace bowie_test datafile 'c:\bowie\bowie_test01.dbf' size
> 100m segment space management manual;
>
> Tablespace created.
>
> NOTE: for this demo to work, it's important for the tablespace to have
> manual segment space management (although of course this issue could arise
> with ASSM as well)
>
> SQL> create table bowie (id number, name varchar2(30)) initrans 1 maxtrans 1
> tablespace bowie_test;
>
> Table created.
>
> SQL> create table ziggy (id number, name varchar2(30)) initrans 1 maxtrans 1
> tablespace bowie_test;
>
> Table created.
>
> NOTE: although we can set a value of initrans/maxtrans to 1, Oracle under
> the covers always creates 2 slots, even with tables since 9i. Now to insert
> some sample data ...
>
> SQL> insert into bowie values (1, 'LOW');
>
> 1 row created.
>
> SQL> insert into bowie values (2, 'HEROES');
>
> 1 row created.
>
> SQL> insert into bowie values (3, 'LODGER');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> insert into ziggy values (1, 'BOWIE');
>
> 1 row created.
>
> SQL> insert into ziggy values (2, 'REED');
>
> 1 row created.
>
> SQL> insert into ziggy values (3, 'POP');
>
> 1 row created.
>
> SQL> commit;
>
>
> In session 1:
> SQL> update bowie set name = 'TEST1' where id = 1;
>
> 1 row updated.
>
> SQL> update ziggy set name = 'TEST1' where id = 1;
>
> 1 row updated.
>
>
> In session 2:
> SQL> update bowie set name = 'TEST1' where id = 1;
>
> 1 row updated.
>
>
> In session 3:
> SQL> update ziggy set name = 'TEST3' where id = 3;
>
> 1 row updated.
>
> SQL> update bowie set name = 'TEST3' where id = 3;
>
> Note this session is now hung as it can't update table BOWIE as both trans
> slots are being used....
>
>
>
> Now go back to session 2:
>
> SQL> update ziggy set name = 'TEST2' where id = 2;
>
>
>
> And now it's hung as it can't update table ZIGGY as both trans slots are
> being used there as well....
>
> Result a deadlock as shown by the error message recorded (in this case ) in
> session 3:
>
> update bowie set name = 'TEST3' where id = 3
> *
> ERROR at line 1:
> ORA-00060: deadlock detected while waiting for resource
>
> So most certainly deadlocks can occur (albeit rarely) if insufficient trans
> slots are not allocated.
>
> Cheers
>
> Richard
I agree. I just wonder at the relevance to an OP that says he's set
INITRANS to 1 and MAXTRANS to 100 (IIRC) and exhibits a level of
confusion on the matter such that I suspect this sort of thing sails
straight past him.
But facts are facts, and you're right. Forgive me for over-simplifying.
Regards
HJR
Received on Fri Nov 26 2004 - 02:37:55 CST
