ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired & ORA-02292 - "integrity constraint (%s.%s) violated - child record found"

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

Row locking is a very common feature in database applications. Sometimes it is a design requirement that if a particular row is locked for processing, then any other session trying to access the same row, should not wait for the previous session to complete the transaction, but should exit gracefully or skip that record and take the next record for processing.

Below is the error that is thrown. A suitable way to handle this is to SELECT the record for UPDATE NOWAIT(to put an exclusive lock on the record). Now if the record is alreay locked by another process, Oracle will throw an exception (Exception number -54). This error can be caught and suitably handled.
The FOR UPDATE statement will put an exclusive lock on the record/set of records and all other processes trying to access these records will have to wait indefinitely till the transaction commits.
With FOR UPDATE NOWAIT, processing of this record can be bypassed or deferred and the error handled suitably.

SELECT FOR UPDATE NOWAIT 

is a very powerful feature in Oracle for taking an exclusive lock on a table or record. In many an application, it is a common scenario that multiple users want to work on the same record. If the entire transaction is going to take some time to complete then it is a good way to take an exclusive lock on that record so that no other process works on it concurrently. If the table in question is a frequently used transaction table, keep it free, dont put any locks on that as it will put many transactions from various services/modules on hold. Create a temporary table with the specific module, record(transaction Id) and status flag. For that particular module and transaction Id take an exclusive lock on the table using SELECT FOR UPDATE NOWAIT. Update that record to 'Processing' or 'In Process' and commit. Now the next process from the same module will select from that table for the same Module Id and Transaction Id, see that the status is 'In Process' and return. If it finds no such record or sees the status as 'Completed' it can take an exclusive lock for the process as explained above.

To simulate this error, you can run the below PLSQL block from 2 sessions. One session will put an exclusive lock on the table. Make sure you do not commit. Now from another session, execute the same block and you will get the below error. The PLSQL block is handling this error and displaying a suitable error message.


Error report -

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

ORA-06512: at line 10
00054. 00000 -  "resource busy and acquire with NOWAIT specified or timeout expired"
*Cause:    Interested resource is busy.
*Action:   Retry if necessary or increase timeout.

DECLARE
v_first_name    VARCHAR2(30);
v_last_name     VARCHAR2(30);

row_locked     EXCEPTION;
PRAGMA EXCEPTION_INIT(row_locked, -54);

BEGIN

  SELECT FIRST_NAME,LAST_NAME
  INTO v_first_name, v_last_name
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = 100
  FOR UPDATE NOWAIT;

EXCEPTION
  WHEN row_locked
  THEN
     raise_application_error(-20001, 'Row Locked for update ::' || SQLERRM);
END;

ORA-02292 - "integrity constraint (%s.%s) violated - child record found"

DELETE FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID = 100;

DELETE FROM HR.DEPARTMENTS WHERE DEPARTMENT_ID = 100
Error report -
SQL Error: ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found

02292. 00000 - "integrity constraint (%s.%s) violated - child record found"

*Cause:    attempted to delete a parent key value that had a foreign
           dependency.
*Action:   delete dependencies first then parent or disable constraint.


Find a list of all the dependent tables in which entry for department_id - 100 exists.
Delete these records and then delete from main table.


Below is the query to get a list of all the dependent tables for DEPARTMENTS table
LABEL 1. SELECT OWNER, TABLE_NAME FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME IN(
SELECT R_CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER = 'HR' AND TABLE_NAME = 'DEPARTMENTS' AND CONSTRAINT_TYPE = 'R');

Say you get the following output
HR EMPLOYEES
HR LOCATIONS

You can now delete the records from these tables where department_id is 100 and then delete from the main DEPARTMENTS table.


DELETE FROM EMPLOYEES WHERE DEPARTMENT_ID = 100;

DELETE FROM LOCATIONS WHERE LOCATION_ID = 1700;

These deletes will happen provided there are no further FK constraints on these tables. If they exist then repeat steps from LABEL 1 for all dependent tables.

You should now be able to successfully delete the records from the DEPARTMENTS table.