Re: Do you ever really get row level locking?

On Fri, 7 Jun 96 09:24:38 -0500, Rick Rutt <rrutt_at_delphi.com> wrote:

>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

No, row level locking is not determined by INITRANS. INITRANS sets aside reserved space on each block and is used to tune. If more then INITRANS transactions hit a block, the transaction headers are dynamically allocated upto the MAXTRANS setting (max of 255). Hence a small table that is 'hot' (lots of updates) might want a larger then normal initrans setting to avoid the dynamic allocations.

The real magic is not only the row level locking but the queries that are never blocked by inserts/updates/deletes. Row level locking is only half the story.

>
>Independent of your question about INITTRANS is the issue of
>B-tree index node locking.
>
>Many record updates require changes to the value of an indexed field;
>deletions and insertions also update the index.
>These index updates require locking of a node in a B-tree.

Not in Oracle they don't.

>Until the transaction commits or rolls back, no other transaction
>can update the same node in the B-tree.
>

Again, not in Oracle...

>Each node in the B-tree affects multiple data records.
>The top node in a B-tree affects the entire table.
>Thus, even in a database with row-level locking,
>an update on a single record can effectively lock other,
>apparently unrelated, records.

No, this is wrong in the Oracle case as well. Oracle locks indexes at the lowest level. An index lock will lock a row, not all the entries on that block. There is no "adjacent key locking" or page locking involved here.

An update of a single record will lock but one record and one record only.

>
>As always, the goal is to release locks quickly, and avoid holding
>locks while waiting for user input.
>You can often reduce lock contention by "serializing" updates from
>multiple processes via a custom semaphore or spin-lock,
>so that only one process is updating at any point in time.
>
>-- Rick
>--
>
>(Rick Rutt is a system architect living and working in Midland, Michigan.)

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Sat Jun 08 1996 - 00:00:00 CEST