Re: INITRANS and MAXTRANS are confusing me?

Nuno Souto wrote:
<liberal snips>  

> 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'll agree only if you qualify that statement to say 'as long as no block is out of transaction entries'. If there are 10 rows in a block and only room for 1 transaction entry then only 1 session can lock rows in that block. So Transaction 1 locks the first row and that's it, the rest of the rows cannot be locked now. All other transactions wanting to modify rows in that block must wait (or fail due to deadlock).  

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

As I said, we see it on a very regular basis in our large extremely parallel datawarehouse ETL processes.  

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

It's not obvious but you are missing something. Read Metalink Note:115467.1 "ORA-60 DEADLOCK DETECTED ON CONCURRENT INSERTS INITRANS/MAXTRANS". Basically transaction A has all the transaction entries from block 1 and is waiting on an entry from block 2 to free up, transaction B has all the transaction entries from block 2 and is waiting on an entry from block 1 to free up. They're deadlocked.  

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

All of our large tables are partitioned, but for query performance. We can't just try and stripe them to spread out the block update activity in an attempt to avoid transaction entry contention in the ETL process. The performance of the readers of the warehouse is far more important than ETL itself. If ETL fails we can resume it without much trouble but that doesn't mean it's not a nusance.

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

Richard Received on Wed Mar 12 2003 - 22:14:05 CST