Re: INITRANS and MAXTRANS are confusing me?

Answers embd.

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:a5Mba.79431$aa.21652285_at_twister.socal.rr.com...
> I'm sure you realize that you made a misstatement here given the content
> of your posting. I think it's worth pointing out that Oracle does NOT
> "support an unlimited number of concurrent locks". In fact the original
> poster's question points this out since MAXTRANS limits the number of
> transaction entries (and hence locks) allowed per block. Besides the
> possibility that there's no more free space in the block (in which case
> you might only get INITRANS transaction entries/locks in that block).

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).

>
> We're actually having problems with this because of the large amount of
> parallel ETL activity on a data warehouse system I'm currently working
> on. Deadlock failures are becoming pretty common in some of our
> processes because of this limitation. We're at the point that we may

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

> 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.

> doesn't fix existing blocks). Unfortunately, this will mean reserving
> space in every block for something that may never actually be needed.
> Even then, the only way we can guarantee there won't be a deadlock would
> be to set INITRANS at least as high as the maximum parallelism. In our
> case, that would mean reserving about 10% of every block just for
> transaction entries (that translates into money).

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.

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.

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.

>
> While I see many of the advantages to the Oracle scheme, I think it's
> foolish to think it doesn't have it's own problems.

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

Tanel. Received on Wed Mar 12 2003 - 15:07:16 CST