Re: Doubt related do INITRANS
Date: Thu, 17 Dec 2020 11:34:28 +0000
Message-ID: <CAGtsp8nw_FfQMBaodMo5Zyc+ENRQZod=QFh09Xqj9O8HOko6Hw_at_mail.gmail.com>
Of course, if you have 20 rows in this block of your "sequences" table and
a couple of hundred users who are constantly trying to increment one or
other of the sequences then you might need to set initrans to 20 to make
sure that every row in the block can be locked by a different transaction
at the same time. (At that point you might find that you run into problems
with "buffer busy wait" waits - as Oracle can do on its SEQ$ table if
someone creates a load of sequences with a low (or zero) sequence cache
size.
Regards
On Thu, 17 Dec 2020 at 11:30, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:
> Eriovaldo,
Jonathan Lewis
>
> The purpose of INITRANS (pre-allocating an "interested transaction list")
> is to ensure that N different transactions can change (at least) N
> different rows in the block at the same time. If you're going to have only
> one row in the block then at most one tranaction can change the row at any
> one moment, any other transactions that want to change that row will queue
> up on the "transaction id" - i.e. the transaction table entry in the undo
> segment header - of the transaction that is currently modifying the row and
> wait (showing a TX lock waiting (usually) for a mode 6 lock) until the
> first transaction commits.
>
> This means, among other things, that you won't need to set initrans to N
> because you think there will be N transactions trying to update that row,
> leaving it to default will be perfectly adequate. Try the experiment -
> create the table , insert the row, try to update it from 20 different
> sessions without committing. then commit each one in turn as it get to the
> front of the queue. Then dump the block - you'll find that the ITL still
> consists of two entries.
>
> Regards
> Jonathan Lewis
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 17 2020 - 12:34:28 CET