Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle row locks and row level locking

by Donald Burleson

Scott Martin has an excellent description of Oracle row level locking:

When a transaction begins, it opens an exclusive lock on a resource named after the transaction ID of the newly started transaction. This resource will then serve as a wait point for any other transaction which needs to wait for the completion of the newly started transaction.

In our example, any transaction that wishes to update row #2 would detect that the row is still locked by transaction ID 0005.00e.0000013a. This concurrent transaction would then request a lock in exclusive mode on the resource named after the as yet uncommitted transaction. When the first transaction commits (or rollbacks), it releases the exclusive lock. Once this lock is released the second transaction's request for the lock succeeds allowing it to proceed.

Mark Bobak published this excellent description of Oracle row-level locking:

  1. Transaction begins: Either explicitly via 'set transaction ...' or implicitly, due to any
    DML (select for update, update, insert, or delete)

  2. Slot is allocated in rollback segment header: This is either chosen round-robin,
    or, if specified, taken from 'set transaction use rollback segment ...' statement.

  3. Statement is executed, blocks to be changed are identified: This is done via the
    execution plan, utilizing whatever access paths are specified there. As each block
    is identified rows are taken as follows:

  4. Now, transaction looks for an empty ITL slot, and allocates it. If no slots are
    available, one will be allocated from free space in the block, if available, and
    not limited by MAXTRANS. If that fails, transaction will wait on a TX enqueue in
    mode 4. When the ITL slot has been allocated it will be set to point to the
    rollback segment header that was previously reserved by this transaction.

  5. Once an ITL slot has been allocated in the block, specific rows must be marked
    as locked. This is done in the row directory. The lock byte will be set to point to
    ITL slot of this transaction. This is how an actual row-level lock is maintained
    by Oracle. Once that's done, the row may be updated, (or not, if it's just a select
    for update). If changes to the block are done, the before images are recorded in
    the rollback segment where the header slot has already been reserved.

  6. Finally, a commit or rollback will release the lock. Note that in the case of commit,
    the only thing that *must* happen to release the lock is the rollback segment header
    slot is marked as committed. Cleanup of the block itself can, any in many cases will,
    be delayed to a later date.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.