Locking rows in oracle table without row selection
If I run a query SELECT ……. FOR UPDATE
on a table in oracle for which no rows are returned, a row level lock is created. For example, if the query below fails, no record is returned because there is no object_key that has the value "xxx" in the table.
select * from payment_detail where object_key = 'xxx' for update;
However, after the query is executed, there is a row-level lock created on the PAYMENT_DETAIL table, as evidenced by the query on the table v$locked_object
. Could you please suggest how this scenario creates a row level lock (when the record is not returned by the query)
source to share
This is 3 - ROW_X (SX): exclusive table lock
Here's a demo:
SQL> CREATE TABLE t AS
2 SELECT LEVEL COL
3 FROM dual
4 CONNECT BY LEVEL <= 10;
Table created.
SQL>
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
---------- ---------- ------------------------ -----------
92549 12 9012:6928 3
SQL> rollback;
Rollback complete.
SQL> SELECT object_id,
2 session_id,
3 process,
4 locked_mode
5 FROM v$locked_object;
no rows selected
SQL>
More information on whether other sessions can execute DML using an existing Row exception table lock
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: Refresh 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: Committing Problems
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 session_id 7 is now released from the lock. You can see object_id that in all cases this same table is locked.
So yes, other sessions can update since you don't have any locked rows to update.
But if any rows are returned by SELECT..FOR UPDATE, then those rows will be locked exclusively, and other sessions will not be able to DML on those rows and will continue to wait until the previous session ends or the change is rolled back.
Hope it helps!
source to share
Expanding on Lalit Kumar B's answer using his example:
CREATE TABLE t AS
SELECT LEVEL col
FROM dual
CONNECT BY LEVEL <= 10
/
Table created.
SELECT *
FROM t
WHERE col = 20
FOR UPDATE;
/
no rows selected
SELECT xidusn,
xidslot,
xidsqn,
object_id,
session_id,
locked_mode
FROM v$locked_object
/
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- -----------
0 0 0 777950 130 3
SELECT sid,
type,
id1,
id2,
lmode,
request,
ctime,
block
FROM v$lock
WHERE sid = 130
/
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
130 AE 100 0 4 0 2771 0
130 TM 777950 0 3 0 582 0
A query against v $ locked_object indicates that the table is locked in lock mode 3 (that is, in row-exclusive mode). Since XIDUSN, XIDSLOT and XIDSQN are all 0, this means that no row is locked.
This can be confirmed by a query against v $ lock. Ignoring the AE lock, the table has a TM / Enqueue lock to prevent structural changes to the table during a transaction. This is in locked mode 3 (i.e. row exclusive mode). But there are no TX Locks / Enqueues at the row level.
This can be compared to:
ROLLBACK
/
Rollback complete.
SELECT *
FROM t
WHERE col = 1
FOR UPDATE;
/
COL
----------
1
SELECT xidusn,
xidslot,
xidsqn,
object_id,
session_id,
locked_mode
FROM v$locked_object
/
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID LOCKED_MODE
---------- ---------- ---------- ---------- ---------- -----------
6 31 938022 777950 130 3
SELECT sid,
type,
id1,
id2,
lmode,
request,
ctime,
block
FROM v$lock
WHERE sid = 130
/
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
130 AE 100 0 4 0 3315 0
130 TM 777950 0 3 0 61 0
130 TX 393247 938022 6 0 61 0
The query against v $ locked_object again indicates that the table is locked in locked mode 3 (i.e. row exclusive mode). However, since XIDUSN, XIDSLOT and XIDSQN are all but 0, this indicates that rows are also locked.
This can be confirmed by a query against v $ lock. Again, the table has a TM lock / commit to prevent structural changes to the table during a transaction in locked mode 3 (i.e. row exclusive mode). There is also TX Lock / Enqueues in locked mode 6 (exclusive mode), so the string returned for update cannot be changed by other users.
Finally. Once you have issued a DML to modify rows in a table, or have issued a SELECT ... FOR UPDATE indicating your intent to modify rows in a table, Oracle will first make a table-level exclusive Row lock to ensure that no DDL is released to a structural table changes. It additionally additionally locks every modified row. If no rows are changed, the row level locks will not be taken, but the table level lock will exist and will not be released. It will not be released because in a two-phase lockout mode, the locks are released only during the commit or rollback phase.
source to share