Oracle Locks
My
OracleGuide

Oracle Locks

 About    Feedback Products

Lock ModeDescription
ROW SHARE ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.
ROW EXCLUSIVE ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
SHARE UPDATE See ROW SHARE.
SHARE SHARE permits concurrent queries but prohibits updates to the locked table.
SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.
EXCLUSIVE EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.



Different Lock Modes

ROW EXCLUSIVE MODE
SHARE ROW EXCLUSIVE
EXCLUSIVE MODE
SHARE MODE
ROW SHARE
NOWAIT

lock table in ROW EXCLUSIVE MODE

Lock the employees table in different modes from one session and
query v$lock table from another session and see the different lock modes

Session 1 

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

no rows selected

SQL> rollback;

Rollback complete.

SQL> lock table employees in ROW EXCLUSIVE MODE;

Table(s) Locked.

SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          3          0          0

1 row selected.
Session 2
The second session tries to lock the table 
SQL> select 1 from employees where rownum=1 for update;

For ROW EXCLUSIVE lock the session does not wait for the other session to release lock on table
Session 1
The Lock Information when session 2 is trying to update
See the value for BLOCK column for session 1 
SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          3          0          0
       281 TM          3          0          0
       281 TX          6          0          0

2 rows selected.

SQL> 
SQL> rollback;

Rollback complete.

The Lock Information when session 1 releases lock on table
There is no blocking session

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       281 TM          3          0          0
       281 TX          6          0          0

2 rows selected.

SQL> rollback;

Rollback complete.

lock table in SHARE ROW EXCLUSIVE MODE

Lock the employees table in different modes from one session and
query v$lock table from another session and see the different lock modes

Session 1 

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

no rows selected

SQL> rollback;

Rollback complete.

SQL> lock table employees in ROW EXCLUSIVE MODE;

Table(s) Locked.

SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          5          0          0

1 row selected.
Session 2
The second session tries to lock the table 
SQL> select 1 from employees where rownum=1 for update;

Session 2 waits for session 1 to release lock on table
Session 1
The Lock Information when session 2 is trying to update
See the value for BLOCK column for session 1 
SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          5          0          1
       281 TM          0          3          0

2 rows selected.

SQL> 
SQL> rollback;

Rollback complete.

The Lock Information when session 1 releases lock on table
There is no blocking session

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       281 TM          3          0          0
       281 TX          6          0          0

2 rows selected.

SQL> rollback;

Rollback complete.

lock table in EXCLUSIVE MODE

Lock the employees table in different modes from one session and
query v$lock table from another session and see the different lock modes

Session 1 

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

no rows selected

SQL> rollback;

Rollback complete.

SQL> lock table employees in EXCLUSIVE MODE;

Table(s) Locked.

SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          6          0          0

1 row selected.
Session 2
The second session tries to lock the table 
SQL> select 1 from employees where rownum=1 for update;

Session 2 waits for session 1 to release lock on table
Session 1
The Lock Information when session 2 is trying to update
See the value for BLOCK column for session 1 
SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          6          0          1
       281 TM          0          3          0

2 rows selected.

SQL> 
SQL> rollback;

Rollback complete.

The Lock Information when session 1 releases lock on table
There is no blocking session

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       281 TM          3          0          0
       281 TX          6          0          0

2 rows selected.

SQL> rollback;

Rollback complete.

lock table in SHARE MODE

Lock the employees table in different modes from one session and
query v$lock table from another session and see the different lock modes

Session 1 

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

no rows selected

SQL> rollback;

Rollback complete.

SQL> lock table employees in SHARE MODE;

Table(s) Locked.

SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          4          0          0

1 row selected.
Session 2
The second session tries to lock the table 
SQL> select 1 from employees where rownum=1 for update;

Session 2 waits for session 1 to release lock on table
Session 1
The Lock Information when session 2 is trying to update
See the value for BLOCK column for session 1 
SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          4          0          1
       281 TM          0          3          0

2 rows selected.

SQL> 
SQL> rollback;

Rollback complete.

The Lock Information when session 1 releases lock on table
There is no blocking session

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       281 TM          3          0          0
       281 TX          6          0          0

2 rows selected.

SQL> rollback;

Rollback complete.

lock table in ROW SHARE or SHARE UPDATE MOE

Lock the employees table in different modes from one session and
query v$lock table from another session and see the different lock modes

Session 1 

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

no rows selected

SQL> rollback;

Rollback complete.

SQL> lock table employees in ROW SHARE MODE;

Table(s) Locked.

SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          2          0          0

1 row selected.
Session 2
The second session tries to lock the table 
SQL> select 1 from employees where rownum=1 for update;

For ROW SHARE mode the session does not wait for the other session to release lock on table
Session 1
The Lock Information when session 2 is trying to update
See the value for BLOCK column for session 1 
SQL> 
SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       268 TM          2          0          0
       281 TM          3          0          0
       281 TX          6          0          0

2 rows selected.

SQL> 
SQL> rollback;

Rollback complete.

The Lock Information when session 1 releases lock on table
There is no blocking session

SQL> select sid, type, lmode, request, block from v$lock where sid in
  2  (select session_id from v$locked_object where object_id=81107);

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       281 TM          3          0          0
       281 TX          6          0          0

2 rows selected.

SQL> rollback;

Rollback complete.
NOWAIT

Specify NOWAIT if you want the database to return control to you immediately if the specified table, partition,
or table subpartition is already locked by another user. In this case, the database returns a message indicating
that the table, partition, or subpartition is already locked by another user.

If you omit this clause, then the database waits until the table is
available, locks it, and returns control to you.



There is no direct way to find out whether a particular row is being locked by another session.
The follwing PL/SQL code can be used to display locked rows.
The nowait clause with for update in the select statement is the key for this.

In another session update some rows of employees table

Now run this PL/SQL Code 

set serveroutput ON

declare
v_empid number;
cursor cur1 is select * from employees where employee_id=v_empid for update nowait;  
--  specify "for update" with "nowait" clause
row_locked exception;   -- Define exception
PRAGMA EXCEPTION_INIT(row_locked, -54);   -- Error code 
TYPE emptab is table of employees%rowtype;   -- Collection object type
e1 emptab;
begin
select * BULK COLLECT INTO e1 from employees;   -- Fetch rows to a collection
for n1 IN e1.first..e1.last LOOP  -- LOOP through each row
v_empid := e1(n1).employee_id;
begin
open cur1;   -- The error happens when the cursor is open
exception when row_locked then dbms_output.put_line(v_empid);
   --   Catch the error in the exception
end;
if cur1%ISOPEN then close cur1; end if;    -- close the cursor if already open
END LOOP;
end;
/