ORA-00054: resource busy and acquire with NOWAIT specified

The error message ORA-00054: resource busy and acquire with NOWAIT specified in Oracle Database indicates that a transaction or session is attempting to acquire a lock on a resource that is already being used by another transaction or session.
When the NOWAIT option is specified in the SQL statement, it instructs Oracle to return an error immediately if the requested resource is unavailable, rather than waiting for it to become available.

To handle this error, you have a few options:

1. Retry the operation: Since the resource is currently busy, you can implement a retry mechanism in your code. You can catch the ORA-00054 exception and retry the operation after a short delay. This approach allows your code to wait for the resource to become available before attempting the operation again.

2. Use the WAIT option instead of NOWAIT: Instead of specifying NOWAIT in your SQL statement, you can use the WAIT option. This will cause the statement to wait for the requested resource to become available instead of immediately returning an error. You can specify a timeout value if you want the statement to wait for a limited period before returning an error.

3. Optimize your code or transaction logic: Consider reviewing your code or transaction logic to minimize the duration and frequency of resource locking. This can involve optimizing queries, reducing transaction sizes, or modifying the locking strategy in your application.

4. Analyze the locking situation: You can investigate the cause of the resource contention by querying the relevant Oracle dictionary views. The views such as DBA_BLOCKERS, DBA_WAITERS, and DBA_DML_LOCKS provide information about the sessions involved in the locking situation. Analyzing the locking scenario can help identify the source of contention and allow you to take appropriate actions to resolve it.

The table is locked due to an ongoing query. The two main reasons for this are either the use of SELECT FOR UPDATE statements or the presence of uncommitted INSERT statements. To obtain further information about the locked SQL, including the table name, logon time, username, and machine information, you can execute the following SQL query: 

SELECT  O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME,S.USERNAME,S.MACHINE 
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;
If the blocking session is stuck or unresponsive, you may need to kill the session using the ALTER SYSTEM KILL SESSION command. To release table lock, Oracle provides an alter system command that can be used by passing the SID and SERIAL# of a particular session. 
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
To identify the process that is listening on a specific port, execute the following command:
netstat -ap | grep 1521
tcp 0 0 machine.name:1521 oracle.db.com:ncube-lm ESTABLISHED 9030/java

In this example, the process with ID 9030 is causing a table lock. You have the option to either kill the process to release the lock or debug it to find the cause of the issue.



Rate Your Experience

: 89 : 0

Online Tests
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Navigation Panel

All Rights Reserved @2023 | www.dbdocs.net | Privacy Policy | Disclaimer| Cookie Policy