Oracle Locks with examples

LOCKS

Locks are the mechanisms used to prevent destructive interaction between users accessing same resource simultaneously. Locks provides high degree of data concurrency.

TYPES

Ø  Row level locks

Ø  Table level locks

ROW LEVEL LOCKS

In the row level lock a row is locked exclusively so that other cannot modify the row until the transaction holding the lock is committed or rolled back. This can be done by using select..for update clause.

Ex:

    SQL> select * from emp where sal > 3000 for update of comm.;

TABLE LEVEL LOCKS

A table level lock will protect table data thereby guaranteeing data integrity when data is being accessed concurrently by multiple users. A table lock can be held in several modes.

Ø  Share lock

Ø  Share update lock

Ø  Exclusive lock

SHARE LOCK

A share lock locks the table allowing other users to only query but not insert, update or delete rows in a table. Multiple users can place share locks on the same resource at the same time.

Ex:

     SQL> lock table emp in share mode;

SHARE UPDATE LOCK

It locks rows that are to be updated in a table. It permits other users to concurrently query, insert , update or even lock other rows in the same table. It prevents the other users from updating the row that has been locked.

Ex:

     SQL> lock table emp in share update mode;          

EXCLUSIVE LOCK

Exclusive lock is the most restrictive of tables locks. When issued by any user, it allows the other user to only query. It is similar to share lock but only one user can place exclusive lock on a table at a time.

Ex:

     SQL> lock table emp in share exclusive mode;

NOWAIT

If one user locked the table without nowait then another user trying to lock the same table then he has to wait until the user who has initially locked the table issues a commit or rollback statement. This delay could be avoided by appending a nowait clause in the lock table command.

Ex:

     SQL> lock table emp in exclusive mode nowait.

DEADLOCK

A deadlock occurs when tow users have a lock each on separate object, and they want to acquire a lock on the 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. In such a case, oracle detects the deadlock automatically and solves the problem by aborting one of the two transactions.