Tuesday, October 12, 2010

What is Row Level Lock in Oracle?

  • Oracle automatically acquires locks which are row-level locks. A transaction can held no of row locks and oracle does not work on it.
  • Row locking provides the lowest level of locking. It provides the best possible transaction concurrency.Readers of data do not wait for writers of the same data rows.  
  • A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.
  • If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.
  • A Transaction gets an exclusive DML row level lock for each row modified by any of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.