Locks:

Locks are mechanism used to ensure data integrity. The oracle engine automatically locks j table
data while executing SQL statements like Select/insert/UPDATE/DELETE. This K type of locking is
called implicit locking 

There are two types of Locks 

1. Shared lock
2. Exclusive lock

Shared lock:

Shared locks are placed on resources whenever a read operation (select) is performed.
Multiple shared locks can be simultaneously set on a resource.

Exclusive lock:

Exclusive locks are placed on resources whenever a write operation (INSERT, UPDATE And DELETE) are performed.
Only one exclusive lock can be placed on a resource at a time.
i.e. the first user who acquires an exclusive lock will continue to have the sole ownership of the resource, and no other user can acquire an exclusive lock on that resource

Levels of Locks:

Oracle does not provide a field level lock.
Oracle provides the following three levels of Locking.

• Row level
• Page level
• Table level

Row Level locking

If the WHERE clause evaluates to only one row in the table, a row level lock is used.

Page Level locking

If the WHERE clause evaluates to a set of data, a page level lock is used.

Table Level locking

If there is no WHERE clause, the query accesses the entire table, a table level lock is used.
Can't update entire table data when update is done by other user.

Syntax:

 LOCK TABLE <tablename> [<tablename>]….. IN { ROW SHARE / ROW EXCLUSIVE / SHARE UPDATE / SHARE / SHARE ROW EXCLUSIVE / EXCLUSIVE}[NOWAIT]

Exclusive lock:

They allow query on the locked resource but prohibit any other activity 

Example:

Deadlock:

In a deadlock, two database operations wait for each other to release a lock.
A deadlock occurs when two users have a lock, each on a separate object, and, they want to acquire a lock on each other's object.
When this happens, the first user has to wait for the second user to release the lock, but the second user will not release it until the lock on the first user's object is freed. At this point, both the users are at an impasse and cannot proceed with their business.
In such a case, Oracle detects the deadlock automatically and solves the problem by aborting one of the two transactions.

example :

Transaction 1

BEGIN

UPDATE client_master SET salary = 500 WHERE client_no=’c1’;

UPDATE client_master SET salary = 500 WHERE client_no=’c2’;

END

Transaction 2

BEGIN

UPDATE client_master SET salary = 5000 WHERE client_no=’c2’;

UPDATE client_master SET salary = 500 WHERE client_no=’c1’;

END

Assume that Transaction 1 and Transaction2 begin exactly at the same time.
By default Oracle automatically places exclusive lock on data that is being updated.
This causes Transaction 1 to wait for Transaction2 to complete but in turn Transaction2 has to wait for Transaction 1 to complete.

---

• Prev
• Next

---

Question

---