Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 1 '13 at 14:12

This question came from our site for professional and enthusiast programmers.

1  
You can only see locks that some process is waiting for, not those that are held by a transaction. –  a_horse_with_no_name Mar 1 '13 at 14:09
    
@a_horse_with_no_name - v$lock shows you the locks held by a transaction –  Chris Saxon Mar 1 '13 at 16:03
    
@ChrisSaxon: You are right. I was referring to the fact that you cannot see which rows have been locked - I should have been more clear. –  a_horse_with_no_name Mar 1 '13 at 16:13
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.