LOCK TABLE

 LOCK TABLE : The LOCK TABLE command is used to prevent concurrent processes from changing a table or from using it.

 The two Locking modes are

1)IN SHARE MODE: In which concurrent processes are allowed to perform only read-only operations.
2)IN EXCLUSIVE MODE:Prevents concurrent processes from performing any operation the table.

 Only the Owner of the table, DBA, or a user having ALTER, DELETE, INSERT, SELECT, UPDATE can lock a table. A table can have multiple SHARE LOCKS but only one EXCLUSIVE LOCK

 

Example: For obtaining EXCLUSIVE LOCK we write.

 SQL> LOCK TABLE STUD in EXCLUSIVE MODE;

 SQL> LOCK TABLE STUD in EXCLUSIVE MODE NOWAIT;

If we try to obtain a lock using the first statement then Oracle waits if the table is not available for locking. In second statement it returns immediately.

Example: For obtaining SHARE LOCK we write.

SQL> LOCK TABLE STUD in SHARE MODE; --- to obtain shared mode lock