"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:41a6eb58$0$17542$afc38c87_at_news.optusnet.com.au...
> 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.
>
Hi Howard,
The point is of course that if you only have a relatively small number of
concurrent transactions insert into a block (say 1 at most) and if you have
a very low pctfree (say 0) and you then fill the block up completely, then
having a maxtrans of 100 might not save you and locking or dead locking
issues might eventuate during any subsequent concurrent updates/deletes.
This is the scenario I thought the OP was questioning.
BTW, there's an error in the cut 'n' pasting with my demo, the second
session should be updating the id = 2 row in the first table, I copied the
details of the first session (although you probably still get a similar
result).
Cheers
Richard
Received on Fri Nov 26 2004 - 06:18:19 CST
