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.
No comments:
Post a Comment