What are different types of locks in oracle

Question

Please anyone explain locking mode in Oracle i.e. Share, Exclusive and Update lock. I found many theories on this and according to that

Share lock : Nobody can change data,Read only purpose

Exclusive lock : Only one user/connection are allow to change the data.

Update lock : Rows are locked till user made commit/rollback.

Then, I tried shared to check how it works

SQL> lock table emp in share mode;

Table(s) Locked.

SQL> update emp set sal=sal+10;

14 rows updated.

Then, I found that, user can change data after share lock. Then, what makes it different from exclusive lock and update lock.

Another question, how Update lock and exclusive lock are different with each other, even they seems almost equivalent.

Solution 1

Posting explanation for future visitors, and it also gives the answer.

Shared lock

• Before I begin let me first say that there are 5 types of table locks - row shared, row exclusive, shared, shared row exclusive and exclusive. And shared lock is one among these. Also, please note that there are row locks, which are different than table locks. Follow the link I have provided in end to read about all this.
• A shared lock is acquired on the table specified in following statement – LOCK TABLE table IN SHARE MODE;
• This lock prevents other transactions from getting “row exclusive” (this lock is used by INSERT, UPDATE and DELETE statement), “shared row exclusive” and “exclusive” table locks, otherwise everything is permitted.
• So, this means that a shared lock will block other transactions from executing INSERT, UPDATE and DELETE statements on that table but will allow other transactions to update the rows using “SELECT … FOR UPDATE” statement because for this statement a “row shared” lock is required, and it is permitted when a “shared” lock is required.

Below table is a good summary of locks and what's permitted.

---

Since many users will follow this question so I decided to go one more step further and put my learning notes, I hope folks will be benefited from it:

---

Source of this information and also excellent reading about Oracle locks

Solution 2

It's very well explained in the documentation: http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502

In your example you locked the table in shared mode. This does not prevent other sessions locking the same object in shared mode, but it does prevent them from locking it in exclusive mode so you could not drop the table (which requires an exclusive lock) while it is being updated (which has a shared lock).

Comments

[Ravi]

I appreciate your link, but if you please explain how does it works with some examples. I will be thankful.