Identifying Locked Rows in Oracle Using v$session

Identifying Locked Rows in Oracle Database using tables v$session and dba_objects

Purpose

At 09:00 a user reports that an order screen has frozen. The user pressed Save, and nothing happens. The session is not busy with work. It is waiting. Another transaction changed the same row and has not finished, so the database holds the second change back until the first one ends. You need to know which row, in which table, and which session is in the way.

The first query below answers the first two questions. It reads the row-wait columns of V$SESSION for the blocked session. It joins them to DBA_OBJECTS to get the table name. Then it uses DBMS_ROWID.ROWID_CREATE to build a ROWID, so you can select the full row. The third query reads BLOCKING_SESSION from V$SESSION to show who holds the lock.

Row-level waits and who blocks whom are the subject here. For locks at object level, see Identify Locked Objects in Oracle Database.

Code

 1-- Show which row is locked
 2
 3select  do.object_name
 4,       row_wait_obj#
 5,       row_wait_file#
 6,       row_wait_block#
 7,       row_wait_row#
 8,       dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,
 9                                ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
10from    v$session s
11,       dba_objects do
12where   sid=&sid
13and     s.ROW_WAIT_OBJ# = do.OBJECT_ID
14/
15
16-- Then select the row with that rowid...
17
18select * from <table> where rowid=<rowid>;
19
20-- Who blocks whom: list every session that waits on another session
21
22select  sid
23,       serial#
24,       blocking_session_status
25,       blocking_session
26,       event
27,       sql_id
28from    v$session
29where   blocking_session is not null
30order by blocking_session
31/

Code Breakdown

Query 1: locate the row

  • v$session s supplies the session data. The sid=&sid filter limits the output to the blocked session. SQL*Plus asks you for the session ID when you run it.
  • dba_objects do supplies the object name. The join s.ROW_WAIT_OBJ# = do.OBJECT_ID links the two views.
  • row_wait_obj# is the object ID of the table that holds the row.
  • row_wait_file# identifies the data file that holds the row.
  • row_wait_block# identifies the block that holds the row.
  • row_wait_row# is the row that is locked at this moment.
  • dbms_rowid.rowid_create (1, ...) builds a ROWID from the four numbers. The first argument, 1, asks for an extended ROWID. A value of 0 asks for a restricted ROWID, and then the object number is ignored. Use 1 here.

Query 2: fetch the row

select * from <table> where rowid=<rowid> returns all columns of the locked row. Replace <table> with the object_name from query 1. Replace <rowid> with the value from the last column.

Query 3: find the blocker

  • blocking_session holds the session ID of the blocker. It is meaningful only when blocking_session_status is VALID.
  • event shows what the blocked session waits for.
  • sql_id points to the statement that the blocked session runs now.
  • order by blocking_session groups waiters under the session that holds them, so one blocker with many waiters is easy to see.

Key Points

  • The row-wait columns are valid only in one case. The column is valid only if the session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1. For any other session, the output means nothing.
  • A row lock lasts until the transaction ends. The row lock exists until the transaction commits or rolls back. A session that updated a row and then went idle still holds the lock.
  • Writers block writers, readers do not. A writer of a row blocks a concurrent writer of the same row. A reader never blocks a writer, and a writer never blocks a reader. The exception is SELECT ... FOR UPDATE, which does lock the row that it reads.
  • A ROWID usually, not always, identifies one row. A rowid value usually identifies a row uniquely in the database. Rows in different tables that are stored together in the same cluster can have the same rowid.
  • The blocker column has its own status. BLOCKING_SESSION is valid only if BLOCKING_SESSION_STATUS has the value VALID. Always read the two columns together.

Insights and Best Practices

Start with the blocker, then look at the row

The row tells you what data is in conflict. The blocker tells you who can end the conflict. Run query 3 first. If several waiters share one blocker, that blocker is the session to deal with. Then run query 1 for one of the waiters to see the row and the table.

Read the row before you act

The select by ROWID shows the current state of the row. Look at the key columns and ask the application owner what the blocker is doing. A long batch job and a forgotten open form need different handling. Do not guess from the SQL ID alone.

Waiting is normal, long waiting is not

The blocked transaction waits for the blocking transaction to end and release its row lock. If the blocker rolls back, the waiter changes the row as if the other transaction never existed. If the blocker commits, the waiter makes its change to the newly changed row. Decide how long a wait you accept, and act when a wait goes past that point.

Be careful with the file number

The ROWID_CREATE parameter is named relative_fno, and the ROW_WAIT_FILE# column is described as the identifier for the data file. If the row you fetch does not look like the right data, test the same steps on a session you control, with a lock you created yourself. Do this on a test database first.

Other ways to see blockers

The V$LOCK view lists the locks currently held by the database and outstanding requests for a lock or latch. Its BLOCK column shows whether the lock blocks other processes. The V$SESSION_BLOCKERS view displays the blocker sessions for each blocked session. If a session is blocked by multiple sessions, there are multiple rows for that session. Use these views when one BLOCKING_SESSION value is not enough.

Plan for the end of the lock

Ask the owner of the blocking session to commit or roll back. This is the safe route. If you must end the blocker yourself, use ALTER SYSTEM KILL SESSION with the session ID and the serial number from V$SESSION. The clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. All uncommitted work of that session is lost, so check first what it is.

When to Run This

  • A user reports a frozen screen or a batch job that does not move.
  • The V$SESSION event for a session shows a wait on a row lock, and you need the row.
  • An application owner asks which record is in conflict so they can contact the other user.
  • A recurring lock appears at the same time every day, and you want to know which table and rows are involved.
  • Before you consider ending a session, to see what data it holds.

Troubleshooting Common Issues

Query 1 returns no rows. The session may not be waiting on a row lock now. The row-wait columns are valid only while it waits for another transaction to commit. Run the query again while the wait is live. Check that you typed the correct SID. Query 1 also joins ROW_WAIT_OBJ# to DBA_OBJECTS, so when ROW_WAIT_OBJ# is -1 the row drops out, and the row-wait columns are not valid in that case. Read the ROW_WAIT_OBJ# and EVENT columns of V$SESSION for that SID to see the raw value and what the session waits for.

Query 1 fails with ORA-00942. Your account cannot read DBA_OBJECTS. A DBA_ view can be read only with the SYSDBA privilege, the SELECT ANY DICTIONARY privilege, the SELECT_CATALOG_ROLE role, or a direct grant. Ask your DBA for one of these.

The ROWID returns no row, or an unexpected row. The lock may have been released between your two queries. Run query 1 again. If the row still looks wrong, see the note on the file number above.

blocking_session is empty but a session is stuck. Read blocking_session_status for the reason. It can show NO HOLDER, NOT IN WAIT or UNKNOWN instead of VALID. On a multi-instance system, query the GV$ views to find the instance of a session.

Two sessions block each other. This is a deadlock. The database detects deadlocks automatically and resolves them by rolling back one statement involved in the deadlock, which releases one set of the conflicting row locks. Usually the signaled transaction should be rolled back explicitly, but it can retry the rolled-back statement after waiting.

References

Posts in this series