Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: INITRANS and MAXTRANS are confusing me?

Re: INITRANS and MAXTRANS are confusing me?

From: Nuno Souto <wizofoz2k_at_yahoo.com.au>
Date: 12 Mar 2003 18:26:48 -0800
Message-ID: <73e20c6c.0303121826.64b2c067@posting.google.com>


Richard Kuhler <noone_at_nowhere.com> wrote in message news:<a5Mba.79431$aa.21652285_at_twister.socal.rr.com>...

> of your posting. I think it's worth pointing out that Oracle does NOT
> "support an unlimited number of concurrent locks".

Well, actually it nearly does. Note that there was no qualification. Read on, I'll clarify what I mean. I hope...

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

Yes, but those are locks WITHIN a single, singular, block. That is a different situation from locks in a table. Which in Oracle is never escalated, meaning that there can be as many rows locked as there are rows in a table!

I think you'll find that abstracting from VERY large blocks with lots and lots of very small rows per block, the problem with INITRANS not being enough is very remote.

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

Hang on: deadlock is not caused by running out of TRANS entries in a block! You sure, positive, INITRANS is the problem? Am I missing something obvious?

Let's not confuse contention (caused by thrashing on trans entries in a block) with true deadlock (caused by alternating locking sequences in two or more concurrent programs).

> We're at the point that we may
> have to bump INITRANS up and rebuild the table (since the setting
> 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).

That's weird. Why not just partition the table so that your updates don't cause so much hassle? Spread the load, there goes your contention. If it is indeed deadlocking, then you need to look at the sequence of locking by all concurent updaters. Not INITRANS.

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

Absolutely true. But let's concentrate on what is REALLY a problem, no?

Cheers
Nuno Souto
nsouto_at_yahoo.com.au.nospam Received on Wed Mar 12 2003 - 20:26:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US