Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0303111811.3765e676_at_posting.google.com>...
> w.gamble_at_pentasafe.com (Wes Gamble) wrote in message news:<37fe2b65.0303111159.ca39183_at_posting.google.com>...
> > Thanks for any help in advance.
> >
> > I just learned about INITRANS and MAXTRANS and what their purpose is.
> > Why does information about the individual transactions have to be
> > stored in data blocks to achieve locking? Why can't you just keep
> > track of the number of transactions which are accessing data in the
> > block in order to manage locking? Do individual transactions have to
> > be included in the block in order to satisfy Oracle's read consistency
> > model?
> >
> > If it helps to understand my confusion, I am used to the Informix
> > world where lock data was centrally managed, not with the data.
> >
> > Pointers to relevant references are welcomed.
> >
> > Thanks,
> > Wes Gamble
>
> Wes, by using an interested transaction list, ITL, to manage locks in
> the actual data blocks Oracle can support an unlimited number of
> concurrent locks, while an rdbms manager that manages the locks in
> memory is limited to the amount of resource it can used. This is why
> DB2 has lock escalation and recommends frequent commit so lock
> resources can be fried up. Oracle on the other hand recommends you do
> as much work as possible before commiting (limited by available rbs
> space and concurrent DML activity against the same table/indexes).
>
> Initrans preallocates ITL work areas into the block so that Oracle
> does not have to try to allocate a work area while processing the
> block during an insert, update, or delete. If a free ITL does not
> exist to support a DML action Oracle will allocate one if space is
> available. This is relativey expensive and preallocating potentially
> saves time on objects subject to concurrent block access. Maxtrans
> limits the number of ITL entries that will be taken from a block.
> Each ITL consumes 24 bytes.
>
> Before 9 the default was 1 ITL per table block and 2 per index. I
> believe that with 9 or 9.2 Oracle upped the default to 2. Tables that
> use a key created via a sequence generator where the inserts, at least
> in the index, are going to be in the same block may benefit from
> having multiple ITL entries preallocated.
>
> HTH -- Mark D Powell --
My wording was apparently a little loose. I was aiming my response at
why Oracle tracked this in the block. In theory Oracle can support as
many row level locks as there are rows in the database unlike say DB2
which tracks locks in memory and can track only a finite number of row
locks at any one time. I did say that Oracle was "limited by
available rbs space and concurrent DML activity against the same
table/indexes)" Obviously a second session that attempts to update a
locked row will have to wait for the lock holder to commit or
rollback, and the lack of an ITL slot will also block a session.
Sorry about any confusion my wording created.
Received on Thu Mar 13 2003 - 08:33:21 CST
