• While applying lock on data, Oracle determine two issues:
  1. Type of lock
  2. Level of Lock

1. Type of lock

  • Oracle user two different types of locks depending upon the operation begin performed:
  1. Shared locks
  2. Exclusive locks
  • Shared locks:
  • Shared locks are applied while performing Read operations. Read operations involve only viewing of data, mostly using SELECT statement.
  • Multiple shared locks can be placed simultaneously on a table or other object.
  • As read operation does not modify table data, multiple read operations can be performed simultaneously without any problem in data integrity.
  • This means, multiple user can simultaneously read the same data.
  • Exclusive locks:
  • Exclusive locks are applied while performing Write operations. Write operations involve modifying data using INSERT, UPDATE or DELETE statements.
  • Only one exclusive lock can be placed on a table or other object.
  • As write operation modifies table data, multiple write operation can affect the data integrity, and result inconsistent database.
  • This means, multiple users cannot modify the same data simultaneously.

2. Level of locks:       

  • Oracle provides three different levels to place an implicit lock.
  • These level are determined depending upon the WHERE clause used in SQL statements.
  • Row Level Lock:
  • This level lock is used when a condition given in WHERE clause evaluates to single row.
  • For example, ... WHEREano = ‘A01’;
  • In this case, only single row is locked.
  • Page Level:
  • This lock is used when a condition given in WHERE clause evaluates to a set of rows.
  • For example, ... WHERE bname = ‘vvn’;
  • In this case, only a particular set of rows are locked. 1other records of the table can be accessed by other users.
  • Table Level:
  • This lock is used when a SQL statement does not contain WHERE clause.
  • In this case, a query accesses entire table, and so, entire table is locked.
  • Due to this reason, no any other user an access other part of the table.