• Oracle, user can lock data in table on its own instead of automatic locking provided by Oracle. Such types of locks are called ‘Explicit Locks’.
  • An entire table or records of tablecan be explicitly locked by using one of these two commands:
  1. SELECT ... FOR UPDATE, and
  2. LOCK TABLE

   1. SELECT ... FOR UPDATE Statement

  •  

SELECT * FROM tablename FOR UPDATE [NOWAIT];

  •  
  • This statement is used to acquire exclusive locks for performing updates on records.
  • Based on the WHERE clause used with SELECT statement, level of locks will be applied.
  • If table is already locked by other user, then this command will simply wait until that lock is released. BUT, if NOWAT is specified, and table is not free, this command will return with error message indicating “resource is busy.”
  • Lock will be released on executing COMMIT or ROLLBACK.
  • Other clauses such as DISTINCT, ORADER BY, GROUP BY and set operations cannot be used here with SELECT statement.
  •  

SELECT * FROM Account WHERE bname = ‘vvn’;

    2. The LOCK TABLE Statement:

      Syntax:

LOCK TABLE tablename

IN lockmode MODE     [NOWAT];

        Description;

  • This Statement is used to acquire lock in one of several specified modes on a given table.
  • If NOWAIT is specified, and table is not free, this command will return with error message indicaing “resource is busy.”
  •  

LOCK TABLE Account

IN Exclusive MODE NOWAIT;