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 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, in theory a DBA or some admin function could be accessing the row, but on a system with 15m users, it was very unlikely. Much more likely was the user had somehow connected to the system and managed to fire off two sessions doing the same thing. Or maybe there sessions failed at some point leaving a session still connected to the database.
The following explains how I tracked down the problem
Step-By-Step
1. I need to find out if the table myTable has any locks
2. First find the object_id for table myTable
* SQL> select object_id,xidusn from dba_objects where object_name='myTable';
3. Using the object Id look for any active transactions
* SQL> select * from v$locked_object where object_id=<value from dba_objects>
4. This returned a lot of rows that looked to be changing quite often. This is what you would expect for a health system. But makes it quite tricky to see what is changing and what is not. So better to capture some of the output into a table
* SQL> create table mytemptable as select * from v$locked_object where object_id=<value from dba_objects>
5. Now lets see what is changing if anything from one minute to the next using the undo ID from v$locked_objects and msrlock
* SQL> select * from v$locked_objects where object_id=<value from dba_objects> and xidusn in (select xidusb from msrlock);
6. The above showed 3 entries that were not changing. Suggesting there were 3 transactions which were quite long.
7. So lets check v$transaction to see what time the transactions started
* SQL> select * from v$transaction order by start_tme desc
8. The above showed the 3 transactions showing up in v$locked_object. But one of them had been running for 2 months!!
9. Back to v$locked objects and matching the undo IDs with the undo IDs in v$transaction, I grabbed the session ID and killed the long running session
Published 5th January 2023
