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 <pre class="prettyprint"><code>SQL> lock table emp in share mode; Table(s) Locked. SQL> update emp set sal=sal+10; 14 rows updated. </code></pre> 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.

Posting explanation for future visitors, and it also gives the answer. <h3>Shared lock</h3> <ul> <li>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.</li> <li>A shared lock is acquired on the table specified in following statement – <code>LOCK TABLE table IN SHARE MODE;</code> </li> <li>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.</li> <li>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.</li> </ul> Below table is a good summary of locks and what's permitted. <img src="https://i.stack.imgur.com/fvzQ6.png" alt="enter image description here"> <hr> 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: <img src="https://i.stack.imgur.com/K6rgk.png" alt="enter image description here"> <img src="https://i.stack.imgur.com/L7gOj.png" alt="enter image description here"> <img src="https://i.stack.imgur.com/dVt60.png" alt="enter image description here"> <img src="https://i.stack.imgur.com/cxKP1.png" alt="enter image description here"> <img src="https://i.stack.imgur.com/dzEQn.png" alt="enter image description here"> <img src="https://i.stack.imgur.com/qwHi3.png" alt="enter image description here"> <hr> Source of this information and also excellent reading about Oracle locks.

What are different types of locks in oracle

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.

What are the different modes of lock?

At the table level, there are 5 different types of locks. i.e, Exclusive (X), Shared (S), Intent exclusive (IX), Intent shared (IS), and Shared with intent exclusive (SIX) and these locks have already been discussed above.

What is locked mode 3 in Oracle?

The query against v$locked_object indicates that the table is locked in Locked Mode 3 (i.e. Row Exclusive Mode). Since the XIDUSN, XIDSLOT and XIDSQN are all 0 this indicates that no rows are actually locked. This can be confirmed by the query against v$lock.

What are the two types of lock in database?

Two types of Database lock are present: Shared Lock. Exclusive Lock.

What is V lock in Oracle?

V$LOCK lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

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.

enter image description here

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:

enter image description here