Re: Do you ever really get row level locking?

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
 

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. Until the transaction commits or rolls back, no other transaction can update the same node in the B-tree.  

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.  

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.) Received on Fri Jun 07 1996 - 00:00:00 CEST