Hello Friends,

Today we learn about the very important concept of oracle i.e. “Locking”.

As we all are aware about that oracle is a multi-user platform, in which table used in database as a Global resource being shared by multiple users at the same time.

In such cases, there are chances of data inconsistency due to parallel processing\operations of data by multiple user at the same time.

At that time Locks plays an important role to maintain concurrency control ensuring data integrity of stored data in the database.

Types of Lock:

Following are the two different types of locking in Oracle:

1. Implicit Locking –Oracle automatically locks the rows whenever user performs DML operations.
2. Explicit Locking –Provide by user and can be row level and table level.

What is Row Level Lock?

It is used to lock selected number of records from table.

It is imposed by For Update clause in select.

What is table level Lock?

Used to lock complete table and can be done in below modes,

1. Shared Mode – This type of lock allow user to query data from a table but does not allow any changes.
2. Exclusive Mode – This is use when DML command is performed like insert\update\delete.

Only one exclusive lock at a time.

• Deadlock- When two or more sessions are waiting for data locked by each other, which results in all the session are blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement.      

Lock mode can be one of the following:

• EXCLUSIVE: allow the queries on the locked table.
• SHARE: allow queries but restricts UPDATE on a table.
• ROW EXCLUSIVE: allow concurrent access to the table by multiple users but restricts from locking table in exclusive or share mode.
• SHARE ROW EXCLUSIVE: allow to view the entire table records but restricts locking the table in share mode and also restricts UPDATE on a table.
• WAIT indicates that the oracle engine will wait till the resource is freely available.
• NOWAIT indicates that the oracle engine will not wait for resource to be available but would rather display the message to the user that Resource is Busy.

Syntax:

For update:

SELECT * FROM tablename WHERE condition For Update Nowait;

Ex. SELECT * from emp_detail where dept_name=’IT’ For Update;

LOCK TABLE EMP IN LOCK MODE WAIT\NOWAIT;