Re: Do you ever really get row level locking?

Susan,

You need to do a little more reading on INITRANS and MAXTRANS. It is 23 bytes per block, not per row. INITRANS default is 1, but, in the event that INITRANS is exceeded, dynamic allocation occurs in the block up to MAXTRANS (default depends on OS and block size, max is 255). Consider the implication of 255 transactions simultaneously accessing one block in a database...! It is encouraging to see your inquisitiveness.

As for row level locking itself, this is far better than TABLE level locking, wouldn't you say? Unless manually locked or an update,insert, or delete is being done, the locks are READ SHARED. When updating, inserting, or deleting, only the row being operated on, rather than the entire table, is locked, allowing other users to perform similar transactions on the same table, different rows, simultaneously. ORACLE also provides for read consistent views.

Steve
804-262-6332

In <31B72D8D.173_at_eds.com> Susan Richards <lnuscsi.sricha01_at_eds.com> writes:
>
>All,
> I have been a DBA for several years but am new to Oracle.
>When ever I hear a discussion of the advantages of Oracle "row
>level locking" is always brought up. I am unable to see how
>true row level locking can be achieved. Isn't row level locking
>determined by the INITRANS parameter set in the table. If I
>have an initrans of 1 then only 1 transaction can update rows in
>a page. This is the default. You lose 27 bytes per data block
>for each increase of the initrans parameter. For example if I
>had 100 rows per data block(page) and I wanted true row level
>locking I would lose 2700 bytes of my data block just for
>locking. If I am wrong please enlighten me.
>
>Susan Richards
Received on Thu Jun 06 1996 - 00:00:00 CEST