|Author's name: Mark J. Bobak
Additions by: Rajendra Jamadagni
Author's Email: mark
at bobak dot net
|Date written: November 11th, 2002
Updated: February 27th 2003
Oracle version(s): 7.x, 8.x, 9.x
|How can an end-user see whether a row is
locked, and who is locking it? This would help in
my application design, if I could allow the users to see
what rows are locked by users, and who holds what lock.
Back to index of questions
The short answer is: Sorry, it's not possible.
For the longer answer, please continue reading.
In principle, there is no scalable way to see what rows are locked or who holds a lock on a particular row. That's because there is no centralized list of locked rows anywhere in Oracle. This is by design, and is a feature, not a bug. One of the Oracle database engine's most powerful features is it's ability to scale to very large volumes of data and numbers of users. Much of it's ability to do so is integral to the locking model it employs.
As previously mentioned, there is no way to determine who is locking a row without attempting to lock it yourself. To see if a particular row is locked, you may attempt to lock it. If you succeed, then no one else had it locked, but you do now. (If it's not your intent to update it, don't forget to rollback and release the lock.) If you fail to lock it, one of two things will happen. If you have chosen to attempt the lock in NO WAIT mode, then your session will return an ORA-0054 "resource busy" error, and you will not have a lock on the row(s). If you have attempted the lock in WAIT mode, (which is the default for DML statements), your session will begin waiting on the transaction which currently holds the lock on the row you are trying to lock. As soon as that transaction is commited or rolled back, your session will be freed, and will lock the row or (if the data changed and your where clause no longer applies) may fail to retrieve the row.
While a session is waiting for a specific row, it's possible to identify the row being waited on, however, this information is only available while a session is actviely waiting for a lock on a row. For the duration of the wait, you may examine V$SESSION, and in particular, the following columns may be of interest: ROW_WAIT_OBJECT#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, and ROW_WAIT_ROW#. Note that these columns are only populated for a session that is actively waiting on a specific row. Sessions not waiting on a resource, or waiting on a non-row resource, will have null values for these columns. However, this method of determining who holds locks on what rows, is cumbersome at best, and is not feasible for an end-user. Also, there is the race condition to consider. Even if you are able to implement any kind of tool or application based on the above information, it's likely that by the time you process and interpret the results, those results are out-of-date.
A quick note about a MetaLink document outlining how to do this: A MetaLink analyst has cobbled together a bit of Pro*C and PL/SQL, that can identify locked rows on a specific table. A review of the code will reveal that he simply attempts to lock each row and returns those rows that failed to lock. If you read the introduction closely, the author admits that the method is "very slow and is NOT bullet-proof". I strongly recommend you not attempt implementing this method, at all, at the very least, it certainly should not be part of a larger application where the application would have to rely on the functionality. However, if you are still interested in the code used, see the hyperlink in the Further Reading section below.
I was browsing the FAQ and stumbled across this entry by Mark ... http://www.jlcomp.demon.co.uk/faq/locked_rows2.html about locked rows. I thought maybe I'll show you guys my take on simplifying the task of querying up (and quickly) the rows being waited on.
In my application we run into these problems, and the scripts used to see locked rows wasn't consistent either. So finally I created following objects for them (developers), which eases the pain a bit for developers.
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKED_OBJECTS (OBJECT_NAME, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, SQL_ACTIONS, LOCK_MODE) AS SELECT /*+ no_merge(lo) */ DO.object_name, lo.SESSION_ID, lo.oracle_username, lo.OS_USER_NAME, DECODE(locked_mode, 1, 'SELECT', 2, 'SELECT FOR UPDATE / LOCK ROW SHARE', 3, 'INSERT/UPDATE/DELETE/LOCK ROW EXCLUSIVE', 4, 'CREATE INDEX/LOCK SHARE', 5, 'LOCK SHARE ROW EXCLUSIVE', 6, 'ALTER TABLE/DROP TABLE/DROP INDEX/TRUNCATE TABLE/LOCK EXCLUSIVE') sql_actions, DECODE(locked_mode, 1, 'NULL', 2, 'SS - SUB SHARE', 3, 'SX - SUB EXCLUSIVE', 4, 'S - SHARE', 5, 'SSX - SHARE/SUB EXCLUSIVE', 6, 'X - EXCLUSIVE') Lock_mode FROM sys.V_$LOCKED_OBJECT lo, DB$OBJECTS DO WHERE DO.object_id = lo.object_id;
CREATE PUBLIC SYNONYM DB$LOCKED_OBJECTS FOR SYSTEM.DB$LOCKED_OBJECTS; GRANT SELECT ON SYSTEM.DB$LOCKED_OBJECTS TO PUBLIC;
CREATE OR REPLACE FORCE VIEW SYSTEM.DB$LOCKS (OBJ_OWNER, OBJ_NAME, OBJ_TYPE, OBJ_ROWID, DB_USER,SID, LOCK_TYPE, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#) AS SELECT owner obj_owner, object_name obj_name, object_type obj_type, dbms_rowid.rowid_create(1, row_wait_obj#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) obj_rowid, a.username db_user, a.SID SID, a.TYPE lock_type, a.row_wait_file#, a.row_wait_block#, a.row_wait_row# FROM DB$OBJECTS, (SELECT /*+ no_merge(a) no_merge(b) */ a.username, a.SID, a.row_wait_obj#, a.ROW_WAIT_FILE#, a.ROW_WAIT_BLOCK#, a.ROW_WAIT_ROW#, b.TYPE FROM sys.V_$SESSION a, sys.V_$LOCK b WHERE a.username IS NOT NULL AND a.row_wait_obj# <> -1 AND a.SID = b.SID AND b.TYPE IN ('TX','TM') ) a WHERE object_id = a.row_wait_obj#; CREATE PUBLIC SYNONYM DB$LOCKS FOR SYSTEM.DB$LOCKS; GRANT SELECT ON SYSTEM.DB$LOCKS TO PUBLIC;
Where DB$OBJECTS is a MV that I refresh everyday at 6AM.
CREATE MATERIALIZED VIEW SYSTEM.DB$OBJECTS PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 STORAGE( MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) TABLESPACE TEMPTABS NOLOGGING NOCACHE NOPARALLEL USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(BUFFER_POOL DEFAULT) REFRESH COMPLETE WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT DISABLE QUERY REWRITE AS SELECT * FROM DBA_OBJECTS /
With this, selects from (mainly) db$locks and db$locked_objects is really fast and my developers love it. This is all on 9202 by the way ... so as I read Mark's article, this makes finding the 'rows being waited on' much faster, I think.
Further reading: How to know which row is locked by what user. (Requires MetaLink login)
Back to top
Back to index of questions