Recently I came across a question in Stackoverflow about row exclusive lock after executing SELECT..FOR UPDATE with no rows selected. The fact was that the query returned no rows, but Oracle applies a Row exclusive table lock i.e. LOCKED_MODE 3. So, which are the rows exclusively locked by Oracle? Let’s see …


SQL> CREATE TABLE t AS
  2    SELECT LEVEL COL
  3    FROM   dual
  4    CONNECT BY LEVEL 
SQL> SELECT *
  2  FROM   t
  3  WHERE  col = 20
  4  FOR UPDATE;

no rows selected

SQL>
SQL> SELECT object_id,
  2         session_id,
  3         process,
  4         LOCKED_MODE
  5  FROM   v$locked_object;

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     92553        124 8784:7948                          3

So what does the above proves? Which are the rows locked by Oracle?

Common questions/myth is :

  1. Can other sessions update?
  2. Can this non-existing row be inserted by other sessions?
  3. Are any other DMLs allowed in this locked state?

The answer is YES. Actually there are no rows locked for update. So, yes other sessions can do the DML transactions. Let’s see :

Session 1:


SQL> SELECT *
  2  FROM   t
  3  WHERE  col = 20
  4  FOR UPDATE;

no rows selected

SQL>
SQL> SELECT object_id,
  2         session_id,
  3         process,
  4         locked_mode
  5  FROM   v$locked_object;

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     92551        124 8784:7948                          3

Session 2: Update the table


SQL> update t set col = 20 where col = 10;

1 row updated.

Session 1:


SQL> SELECT object_id,
  2         session_id,
  3         process,
  4         locked_mode
  5  FROM   v$locked_object;

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     92551          7 8036:1680                          3
     92551        124 8784:7948                          3

Session 2: Issues commit


SQL> commit;

Commit complete.

SQL>

Session 1:


SQL> select * from t;

       COL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        20

10 rows selected.

SQL> SELECT object_id,
  2         session_id,
  3         process,
  4         locked_mode
  5  FROM   v$locked_object;

 OBJECT_ID SESSION_ID PROCESS                  LOCKED_MODE
---------- ---------- ------------------------ -----------
     92551        124 8784:7948                          3

SQL>

So, the session_id 7 is now released from the lock. You can see the object_id, it is same table locked in all the cases.

So, yes other sessions can update, since you don’t have any rows locked for update. The same applies for any DML transactions for any other sessions.

But, if any rows are returned by the SELECT..FOR UPDATE, then those rows will be exclusively locked, and other sessions cannot do any DML on those rows and will keep waiting, until the previous session has committed or rolled back the changes.

Let’s get deeper into the reason.


SQL> CREATE TABLE t AS
  2    SELECT LEVEL col
  3    FROM   dual
  4    CONNECT BY LEVEL  SELECT *
  2  FROM   t
  3  WHERE  col = 20
  4  FOR UPDATE;

no rows selected

SQL> SELECT xidusn,
  2         xidslot,
  3         xidsqn,
  4         object_id,
  5         session_id,
  6         locked_mode
  7  FROM v$locked_object
  8  /

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- -----------
         0          0          0      92554          7           3

SQL> SELECT sid,
  2         type,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         ctime,
  8         block
  9  FROM   v$lock
 10  WHERE  sid = 7
 11  /

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
         7 TM      92554          0          3          0        121          0
         7 AE        133          0          4          0       6999          0
         7 TO      86406          1          3          0        185          0

The query against v$locked_object indicates that rows in the table are 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 again v$lock. Ignoring the AE lock, there is a TM Lock/Enqueue on the table, to prevent structural changes to the table during the transaction. This is in Locked Mode 3 (i.e. Row Exclusive Mode). But there are no TX Locks/Enqueues at row level.

This can be compared with:


SQL> rollback;

Rollback complete.

SQL> SELECT *
  2  FROM   t
  3  WHERE  col = 1
  4  FOR UPDATE;

       COL
----------
         1

SQL> SELECT xidusn,
  2         xidslot,
  3         xidsqn,
  4         object_id,
  5         session_id,
  6         locked_mode
  7  FROM v$locked_object
  8  /

    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- -----------
        10         33       1958      92554          7           3

SQL> SELECT sid,
  2         type,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         ctime,
  8         block
  9  FROM   v$lock
 10  WHERE  sid = 7
 11  /

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
         7 TX     655393       1958          6          0         54          0
         7 TM      92554          0          3          0         54          0
         7 AE        133          0          4          0       7124          0
         7 TO      86406          1          3          0        310          0

The query against v$locked_object again indicates that rows in the table are locked in Locked Mode 3 (i.e. Row Exclusive Mode). However, since the XIDUSN, XIDSLOT and XIDSQN are all non 0 this indicates that rows are also locked.

This can be confirmed by the query again v$lock. Again, there is a TM Lock/Enqueue on the table, to prevent structural changes to the table during the transaction in Locked Mode 3 (i.e. Row Exclusive Mode). There are also TX Lock/Enqueues in Locked Mode 6 (Exclusive Mode) so the row returned for update cannot be modified by other users.

In conclusion. Once you issue DML to modify rows in a table, or issue a SELECT…FOR UPDATE indicating your intention to modify rows in a table. Oracle will take a Row Exclusive Mode lock at the table level to ensure that no DDL is issued to structurally change the table. It will additionally Exclusively lock each row that has been modified. If no rows are modified the table level lock will still be taken.

Hope it helps!