Re: how to determine cause of deadlock

INITRANS reserves space in each block
concurrent transactions to record their
transaction Ids, status, and rollback
pointers. This means that INITRANS 2,
for example, will always allow any
block in the table to have at least
two uncommitted transactions in
place at any time.

If the block is COMPLETELY packed,
which happens fairly rarely in most cases, a third transaction would have to wait
until one of the first two transactions
committed.

However, if there was some free space
in the block, the third transaction could use some of that space to extend the
'interested transaction list (ITL)', and proceed. Once the ITL has been extended it
never shrinks, but you can limit its
growth by setting MAXTRANS, which
defaults to 254.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Jay M. Scheiner wrote in message <396f0da0.201488760_at_news.erols.com>...


>I have seen many references to INITRANS but have yet to find an

>explantion that I can understand.  Can someone explain what this

>setting does?  Thanks.

>

>On Fri, 14 Jul 2000 08:43:21 +0100, "Jonathan Lewis"

><jonathan_at_jlcomp.demon.co.uk> wrote:

>

>>

>>You can get 'no row' deadlocks when the INITRANS

>>value on a table is too low.  If the block is full and has

>>INITRANS=1, and you get two processes trying to

>>change the block, then the second has to wait for

>>the first to commit so that it can acquire the 'ITL'

>>(interested transaction list) slot.  This is a little

>>unlikely with inserts only.

>

>

>_______________

>Jay M. Scheiner

>Programmer/Analyst

>Wolpoff & Abramson, LLP

>remove _nospm_ from email address

>Opinions are my own only!