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.
