Oracle SQL - Locking and Read Consistency


To understand how the Oracle RDBMS handles locking, we need to identify a difference between two categories of database users:

  • Readers: Users retrieving data (issuing SELECT statements)
  • Writers: Users changing data (issuing INSERT, UPDATE, DELETE, and MERGE commands)

Oracle RDBMS does not lock any data for retrieval.

Multiple database users trying to change the same rows need to wait for each other.

Each attempt to change a row tries to acquire the corresponding row-level lock first.

All row-level locks are released upon a COMMIT explicit or implicit or ROLLBACK.