When a record is locked in Oracle, can we know which record is locked?

Question

When a record is locked ,can we know which one is locked?

How can I get the record rowid or something else info?

---

I can get some info by this sql

SELECT c.ROW_WAIT_OBJ#,c.ROW_WAIT_FILE#,c.ROW_WAIT_BLOCK#,c.ROW_WAIT_ROW#
   FROM v$locked_object a, dba_objects b, v$session c    
WHERE a.object_id = b.object_id    
    AND a.SESSION_ID = c.sid(+) 

I found a method in web to get rowid by using function DBMS_ROWID.ROWID_CREATE()

But it doesn't seem to work.

Answer 1

You can't really list all rows that are being locked by a session. However, once a session is being blocked by another, you can find which session/row is blocking it.

Oracle doesn't maintain a list of individual row locks. Rather, locks are registered directly inside the rows themselves -- think of it as an extra column.

You can find which session has acquired a lock on an object through the V$LOCK view, but this will only list general information, not at the row level.

With this view you can also find if a session is being blocked by another. In that case, if a session is blocked by another session, the row information is displayed in the V$SESSION information.

You can retrieve the rowid, let's build an example with 2 sessions:

SESSION1> create table test as select * from all_objects;

Table created

SESSION1> select rowid from test where object_name = 'TEST' for update;

ROWID
------------------
AAMnFEAAaAAALTDAAz

/* setting identifiers to help with identifying this session later */
SESSION2> exec dbms_application_info.set_client_info('012345');

PL/SQL procedure successfully completed

SESSION2> select 1 from test where object_name = 'TEST' for update;
/*  this will block */

Session 2 is now waiting on Session 1. We can discover the blocking row with:

SESSION1> SELECT o.object_name,
       2         dbms_rowid.ROWID_CREATE (1,
       3                                  s.ROW_WAIT_OBJ#,
       4                                  s.ROW_WAIT_FILE#,
       5                                  s.ROW_WAIT_BLOCK#,
       6                                  s.ROW_WAIT_ROW#) rid
       7     FROM dba_objects o, v$session s
       8    WHERE o.object_id = s.row_wait_obj#
       9      AND s.client_info = '012345';

OBJECT_NAME     RID
--------------- ------------------
TEST            AAMnFEAAaAAALTDAAz

For further reading: a description of the process by Tom Kyte.