ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Issue :

******

Our end user has faced this error while trying to approve a PO.

Solution :

********

I have found the SID and Serial# using the below query,

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

Then i have killed that process using below command

alter system kill session '953,40807';

This resolved my issue but we need to be careful before killing a process.

Finding and Fixing ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

---------------------------------------------------------------------------------------------------------

Recently we ran into an error Oracle error which says

Caused By: Error executing SQL ALTER TABLE ***_ALLOCATION ADD REGION_ID NUMBER(38):         

Caused By: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The most common reason for this are either 'SELECT FOR UPDATE ' or some uncommitted INSERT statements.

Combining the information I got from several Google searches, I ended up with this sql

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

This gave me info about the sql and the table which it has locked and the logon time.

OBJECT_NAME

SID

SERIAL#

SPID

PROGRAM

LOGON_TIME

SQL_FULLTEXT

TABLE_USER

953

40807

9179

JDBC Thin Client

26-Jul-12

INSERT INTO TABLE_USER VALUES (BLAH…BLAH)

TABLE _USER

953

40807

9179

JDBC Thin Client

26-Jul-12

INSERT INTO TABLE_USER VALUES (BLAH…BLAH)

TABLE _USER

953

40807

9179

JDBC Thin Client

26-Jul-12

INSERT INTO TABLE_USER VALUES (BLAH…BLAH)

The info on the user name and the machine name can also be obtained from the table V$SESSION.  Just add S.USERNAME and S.MACHINE to the above sql.

We have 3 options to fix this error

      Kill the DB session and get the tables unlocked

      Kill the application which holds this particular session(sql connection)

      The ideal solution is to get to the actual process(application) to debug/fix the issue

1. Killing the DB session

To kill the DB session execute the  sql

alter system kill session 'sid,serial#'

In my case it will be

                alter system kill session '953,40807'

2. Killing the Application which holds the session

Get the column value of MACHINE from the table V$SESSION and search for the running processes in that machine. 

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS; 

In my case I just had a look and I knew who the culprit was from the LOGON_TIME from the sql results

[bash]$ ps aux | grep java

user     30745  0.0  3.3 677336 63172 ?        Sl   Jul26   3:26 /usr/local/jdk1.6.0_14/jre/bin/java -jar /opt/ea/nova/jenkins/jobs/project/workspace/target/surefire/surefirebooter5649151821229923783.jar /opt/ea/nova/jenkins/jobs/ project /workspace/target/surefire/surefire1933027872679766101tmp /opt/ea/nova/jenkins/jobs/ project/workspace/target/surefire/surefire5320833548219373656tmp

Now that we got to the process, we can easily debug the problem (or just kill the process :P) I did a

    kill -15 30745

and puff!!!! Problem solved!!!! Yay!!

3. Getting to the Actual Process on the application server

The better way to find the process is to get the V$SESSION.PORT from the above sql  and find the process listening on that port.

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,S.MACHINE,S.PORT ,S.LOGON_TIME,SQ.SQL_FULLTEXT FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

To get to the process listening on the port, execute the below command.For me, the port number was  34465 

   netstat -ap | grep 34465

which gave me

tcp        0      0 machine.name:34465  oracle.db.com:ncube-lm ESTABLISHED 4030/java

Bingo!!! We got the process id 4030. Debug or Kill; Your Choice!!!