Re: INITRANS and MAXTRANS

"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 Received on Fri Nov 26 2004 - 02:12:15 CST