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

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired error occurs when you try to lock a resource that is already locked and busy with execution. If two Oracle connections attempt to lock a resource such as a table, view, or other resource, the first connection will lock the resource, and the second resource will be unable to obtain the lock for the resource. The error will be thrown immediately if the NOWAIT option is used to obtain the lock. If the WAIT option is used to obtain the lock, the lock command will wait until the timeout expires and then throw an error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The error will be displayed to Oracle users who attempt to execute a LOCK TABLE or SELECT FOR UPDATE command with the NOWAIT keyword.
If the user waits a few minutes before attempting to run the query again, the active session will have completed and the error message will not reappear. Another option is to find the active session and terminate it in order to free up the table and rerun the query. Reduce the likelihood of this error occurring in the future. Make the table read-only if it will not be used for inserting, updating, or deleting data.



The Problem

If two Oracle database sessions attempt to obtain a lock on a resource, one will obtain the lock for updating, while the other will throw this error. Two Oracle database connections must be created to reproduce this error. Execute the lock command for a table in the first session. With a write operation, the table will be locked. When you attempt to obtain a lock from the second session, the lock will throw this error.

Session 1

lock table employee in exclusive mode NOWAIT;

Session 1 output

Lock succeeded.

Session 2

lock table employee in exclusive mode NOWAIT;

Session 2 output

lock table employee in exclusive mode NOWAIT
Error report -
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
00054. 00000 -  "resource busy and acquire with NOWAIT specified or timeout expired"
*Cause:    Interested resource is busy.
*Action:   Retry if necessary or increase timeout.


Solution 1

When you run an Oracle query from the application, the lock is created before the query is executed. When the query execution is finished, it will either be committed or rolled back. The second thread will attempt to lock the same resource as the first thread during concurrent access from the application. This will result in the error. If this error occurs, modify your code to retry after a certain amount of time. This will fix the problem. In this case, the first lock will be completed after the query is executed.



Solution 2

If the error occurs from your application and the error is consistently reproducible, then the commit or rollback is not happening. Verify in your code make sure commit is done after successful execution. In case of failure, the roll back should be called explicitly. The commit or roll back will unlock the table for another oracle user to use.

Session 1

lock table employee in exclusive mode NOWAIT;

Session 1 output

Lock succeeded.

Session 2

lock table employee in exclusive mode NOWAIT;

Session 2 output

lock table employee in exclusive mode NOWAIT
Error report -
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
00054. 00000 -  "resource busy and acquire with NOWAIT specified or timeout expired"
*Cause:    Interested resource is busy.
*Action:   Retry if necessary or increase timeout.

Session 1

rollback;

Session 1 output

Rollback complete.

Session 2

lock table employee in exclusive mode NOWAIT;

Session 2 output

Lock succeeded.


Solution 3

The table is not required to lock if it is locked to execute a select statement for reading purposes. The table can be defined as read-only. Reduce the likelihood of this errors happening again in the future. If the table will not be used for inserting, updating, or deleting data, make it read-only.

alter table employee read only;

Execute the following command to restore the read-only table.

alter table employee read write;


Solution 4

If you are receiving the error from the second session of the application and are unable to obtain control of the first, you will be unable to execute commit or roll back to release the lock. The first session should be ended in this case by terminating the session. There are two ways to end a session: POST TRANSACTION and IMMEDIATE. After completing the current transaction, the POST TRANSACTION will terminate the session. The IMMEDIATE transaction will end the current session immediately without waiting for the current transaction to finish. The transactions will be rolled back in this case.

To identify the session that needs to be terminated, run the below command. the query will return the Sid and serial number.

select a.sid, a.serial# from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id and a.sid = b.session_id and OBJECT_NAME='EMPLOYEE';

Output

sid.    serial#
----------------
25      64920

The following command will terminate the session and the lock will be released.

alter system kill session '<Sid>, <serial>' POST_TRANSACTION | IMMEDIATE ;
alter system kill session '25, 64920' IMMEDIATE ;

or

alter system kill session '25, 64920' POST_TRANSACTION ;


Solution 5

If the first session query takes longer to complete and the second session executes before the first session is finished, the first session cannot be terminated. The first session is valid, and the second session should be postponed until the first session is finished. The second session should be set to wait until the first session is finished. For the lock timeout expiry, the timeout configuration should be increased. The following command will set the lock’s timeout. The lock command should not use the NOWAIT option in this case.

alter session set ddl_lock_timeout = 600;

Session 1

lock table employee in exclusive mode;

Session 1 output

Lock succeeded.

Session 2

lock table employee in exclusive mode;

Session 2 output

-- wait for a long period till the first session to complete

Session 1

rollback;

Session 1 output

Rollback complete.

Session 2

-- wait for a long period till the first session to complete

Session 2 output

Lock succeeded.

.



Leave a Reply