Oracle Locks

Locks are the mechanism used to prevent destructive interaction between users, accusing the same resources simultaneously. A resource can either table or a specific row in a table. Thus locks provide a high degree of data concurrency. These are acquired at two levels.

1. Row Level Lock: In the row lock, a row is locked exclusively so that other users cannot modify the row until the transactions holding the lock is committed or rollback. The lock will be released by commit or rollback.

2. Table Level Lock: A table level lock will protect table data thereby guaranteeing data integrity when data si being accessed concurrently by multiple users.

A table lock can be several modes:
a. Share Lock: A share lock locks the table allowing other user to only query but not insert,update or delete rows in a table.


lock table emp in share mode;

b.Share Update Lock: It locks rows that are updated in a table. It permits users to concurrently query, inset, update or even lock other rows in the same table. It prevents other users from updating the row that has been locked.


lock table emp in share update mode;

c. Exclusive Lock: When issues by one user, it allows the other user to only query but not insert, delete or update rows in table. It is almost similar to share lock but only one use can place an exclusive lock on a table at a time, where as many users can place a share lock on same table at the same time.


lock table emp in exclusive mode;

Powered by k2schools