Re: INITRANS and MAXTRANS are confusing me?

Tanel Poder wrote:

> It takes one ITL entry per transaction in block. It doesn't matter how many
> rows a transaction is modifying in it. In a row header you have a lock byte,
> which points to right ITL entry (which points to undo information).

My misunderstanding. But the number of locks are still in effect limited.  

> Deadlocks usually come from design problems, not RDBMS software issues.

Usually. That's debatable in this case since the deadlock is a result of transaction entries and not application logic.  

> > have to bump INITRANS up and rebuild the table (since the setting
>
> You see? Bad desgin.
> If you have to RE-build something, it obviously shows, that it wasn't built
> correctly the first time.

Granted, we were unaware that the blocks available free space limited the number of transactions (besides MAXTRANS). That led us to to underestimate how important it is to increase INITRANS. More surprising was that this scheme can actually lead to runtime errors (deadlocks).  

> How many concurrent transactions do you have in a block?
> Let say you got 8k blocks. That's 8192 bytes.
> Cache layer takes 20 bytes in a block.
> Transaction layers fixed part is 48.
> That leaves us 8124 bytes of which 10% is 812.4.
> That means youll have INITRANS 33.
>
> 33 concurrent transactions in one single block?
> If you, by chance are using 16kB or higher block size, then we'd be dealing
> with 68-69 ITL entries.

Correct, we run some loads in 64 parallel tracks.  

> Are you suggesting, that the cause for deadlocks is, that transactions have
> to wait on TX enqueue, because they can't allocate ITL entries and have to
> wait? I think that increasing INITRANS won't solve your problem, it might
> only releave it a bit.

Exactly, see Metalink Note:115467.1 "ORA-60 DEADLOCK DETECTED ON CONCURRENT INSERTS INITRANS/MAXTRANS" for a full description. Of course (as I pointed out) the only way to eliminate the deadlocks in Oracle's scheme would be to have INITRANS as high as the maximum number of transactions that could ever occur on a block.  

> And again, make yourself clear that transaction entry != lock. At least in
> Oracle's data block. Oracle has a separate lock byte for *every single row*
> in the database. And that's stored with row itself.

Thanks for the clarification.  

> It might not look so foolish, if you'd do some research and find out how
> things really work in Oracle.

I didn't say IT looked foolish. I said the IDEA that Oracle's scheme is flawless is foolish.  

> Tanel.

Richard Received on Wed Mar 12 2003 - 15:28:23 CST