ORA-00054: resource busy and acquire with NOWAIT specified

The ORA-00054: resource busy and acquire with NOWAIT specified error message usually happens when there are active transactions against an object in the database which mean other sessions cannot get the lock they require on the object to complete their transaction. There are two ways that a transaction can attempt to obtain a lock in Oracle; WAIT and NOWAIT:

  1. WAIT – this can be specified so that your transaction waits to get the lock if it is unable to get it on the first attempt
  2. NOWAIT – this is more often the case and your statement will fail with the ORA-00054 error message if it cannot obtain the lock

By default most transaction will use the no wait Oracle locking mechanism so that they return control to the user. No wait transactions will result in this error rather than have a session appearing to hang for a long time which would be the case if you specified wait for your transaction.

The good news is that you can find out which sessions are locking which objects in the database quite easily. Let’s take a look at which views you can use to find out which users are locking which objects.

How to use the V$LOCKED_OBJECT Oracle view

The Oracle database makes very frequent use of locks, and for good reason; without them, your data integrity would be compromised. So, it’s good to have a knowledge about how locking works so that you can design your applications with it in mind.

If you take a look at the V$LOCKED_OBJECT dynamic view you will see that there are many columns. The ones we are interested in at the moment are the SESSION_ID, OBJECT_ID and LOCKED_MODE columns. These columns show you that an object with OBJECT_ID has been locked by the user with a SESSION_ID at a particular lock level, LOCKED_MODE.

When someone comes along to try and take out a lock on an object, Oracle compares the lock mode requested with the lock mode that is currently on the object. If the requested lock mode is lower than the current lock mode the locks are compatible and the request will proceed. If the lock mode requested is higher than the current lock in place the transaction will either wait for the other transaction(s) to complete or error with the ORA-00054: resource busy and acquire with NOWAIT specified error. This is if the statement has the no wait Oracle parameter specified and is the default way for a locked transaction to be dealt with.

However, if the lock mode is equal to or higher than the current lock mode, you will have to wait until it is released. You may experience the following error:

ORA-00054: resource busy and acquire with NOWAIT specified

Use the following query to find out who has locked a specific object:

SELECT   obj.os_user_name
                , ses.sid
                , ses.serial#
                , ses.logon_time
                , ses.username
                , ses.osuser
                , ses.program
                , obj.locked_mode
                , ses.last_call_et
FROM v$locked_object obj, v$session ses
WHERE obj.session_id = ses.sid
AND object_id = 1179118;

If you find that you have blocking sessions in your database and a user session is hanging and not actually doing anything you can use the following query to find out what session is locking another one. This SQL can only be used on an Oracle 11g database:

SELECT    blocking_session,sid
                , serial#
                , username
                , machine
                , program
                , process
FROM v$session
WHERE sid = (SELECT blocking_session
                           FROM v$session
                           WHERE blocking_session_status='VALID');

Like it, share it...

Category: Database Monitoring

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *