Locks Mechanism in Oracle

---

LOCKS:
=====
         Lock is a mechanism whenever more than one user performs operations at the same record (or) on the same table simultaneously unless and until first user operation not at completed, it will not allow other users to perform the operations. Locks are devided into two types.

1. Implicit Lock
2. Explicit Lock

Implicit Lock:
=========
The locking mechanism which is performed by the oracle engine internally, those locks can be called as implicit lock.

• Implicit locks always perform row level lacking.

Explicit Lock:
========
The locking mechanism which is performed by the user manually, those locks can be called as explicit lock.
We can perform the locking mechanism in three levels.

• Row Level
• Page Level
• Table Level

ROW LEVEL: record locking is known as row level lock.
=========
Syntax   : select * from <table_name> where for update of <column_name>;
Example: select * from emp where ename=’MILLER’ for update of sal;

PAGE LEVEL: Specific records locking is known as row level lock.
=========
Syntax   : select * from  <table_name> where for update of <column_name>;
Example: select * from emp where ename=’MILLER’ or ename=’KING’ for update of sal;

TABLE LEVEL:
==========
It locks entire table, so that other users can’t perform any operations on any record in the table. It contains three types of models.
    1)Share Mode
    2)Share Update Mode
    3)Exclusive Mode

Share Mode:
--------------
        In this mode more than one user lock the same table at a time. If one user lock the table in share mode it will not allow the other user to perform any operations except select operation.

Syntax:  lock table  <table_name> in shared mode;

Exclusive Mode:
------------------
        In this mode, if one user lock the table it will not allow the other user to lock the same table, so that the rest of the users can’t perform any operations except select.

Syntax: lock table  <table_name> in exclusive mode;

Share Update Mode:
----------------------
         In this mode, if one user lock the table in share update mode, it also allow the other user to perform the operations except the record (or) records which is accessed by first user.

Syntax: lock table  <table_name> in share update mode;

---