Tracking Down Transactions from a SQL

Introduction

A user recently contacted me saying they were seeing lots of failures on a statement similar to the following:

select myValue from myTable where myId = ? for update nowait

The ORA error they were receiving was resource busy and acquire with NOWAIT specified or timeout expired.

Knowing the application quite well, I was aware that each user has a row in table myTable. As such, the only user looking at a row in that table, when the select is restricted to myID = ?, is the user that owns the row.  That is, no one other than user ? would be accessing the row on that table. 

Okay, so in theory a DBA or some admin function could be accessing the row, but on a system with 15 million users, it was very unlikely. Much more likely was that the user had somehow connected to the system and managed to fire off two sessions doing the same thing, or maybe an earlier session had failed at some point, leaving it still connected to the database and still holding the row.

The following explains how I tracked down the problem.

Step By Step

1. Establish whether the table has any locks on it at all

Before digging further, the first thing to confirm was whether myTable had any locks against it right now. If nopt, then its likely the problem was elsewhere.

2. Find the object ID for the table

SQL> select object_id from dba_objects where object_name='myTable';

Everything from this point on uses this object ID, since V$LOCKED_OBJECT and related views identify objects by ID rather than name.

3. Use the object ID to look for any active transactions

SQL> select * from v$locked_object where object_id=<value from dba_objects>;

This is essentially the same details covered by Blockers and Waiters with ROWID and Blocking Locks (Verbose), though here I was working directly against V$LOCKED_OBJECT rather than running either of those scripts in full, since I only needed to confirm activity against one specific object rather than get a general picture of blocking across the whole instance.

4. Capture a snapshot, since the output changes constantly

This returned a lot of rows that looked to be changing quite often. That is what you would expect on a busy system, but it makes it quite tricky to see what is genuinely stable and long running versus what is just normal churn. So it was better to capture a snapshot of the output into a table rather than try to eyeball a moving result set.

SQL> create table mytemptable as select * from v$locked_object where object_id=<value from dba_objects>;

5. Compare the snapshot against current locked objects to find what is not changing

Now let's see what is changing, if anything, from one minute to the next, using the undo segment number (XIDUSN) as the thing to compare between the captured snapshot and the live view.

SQL> select * from v$locked_object where object_id=<value from dba_objects> and xidusn in (select xidusn from mytemptable);

6. Identify the stable transactions

The above showed 3 entries that were not changing between runs. This suggested there were 3 transactions that had been open for some time, rather than short lived transactions.

At this point, if I had wanted to see exactly what those 3 sessions were, what program and machine they were connecting from, and what SQL they were running, Blocking Locks (Verbose) or Blockers and Waiters with ROWID would have helped, since both show details of session, program and SQL detail in one query rather than requiring a manual join. Likewise, SQL Statement for a Session (V$OPEN_CURSOR) would have shown the actual SQL text used by any one of those sessions, and Objects Accessed by Session (V$ACCESS) would have shown what else, if anything, each session was accessing in the schema.  But in this case I was trying to check the actual transaction blocking the SQL.  So I moved on to v$transaction.

7. Check V$TRANSACTION to see when each transaction started

SQL> select * from v$transaction order by start_time desc;

8. Finding the Right Transaction

The above showed the 3 transactions showing up in V$LOCKED_OBJECT. But one of them had been running for 2 months.

I can also at this point, check back in the ASH views around the time it first session started, rather than just what it is holding right now, Session Details for a Given Time Period and the related script, would have shown the session history

9. Matching the undo IDs killing the long running session

Using to V$LOCKED_OBJECT, I was able to match the undo segment numbers (XIDUSN) against the corresponding entries in V$TRANSACTION, I was able to identify the session ID responsible for the 2 month old transaction, confirm the object , and kill the long running session.

Once that session was terminated, the row lock it had been holding since it started was released immediately, and the failures the user had been seeing on the for update nowait statement stopped.

Published on 

Published 25th September 2026