CODEWITHSUNDEEP
oracle-databaseplsqldeadlockdatabase-deadlocks
user3224907
user3224907

Reputation: 768

Oracle 12c - catching the ORA-04020 deadlock error not working as expected

We are running into deadlocks frequently while processing XML files, and until we fix those we are addressing it by immediately reprocessing the file upon the deadlock.

This is what my exception handler looks like:

  EXCEPTION
     WHEN OTHERS
     THEN
     --Handle deadlocks by reprocessing the file
        IF SQLERRM LIKE '%ORA-04020%' THEN
           ROLLBACK;

           BEGIN 
              ..logic to reprocess file..
           END;

        PKG_ERROR_UTIL.write_error_log_p (
           'my_package',
           'my_procedure',
           1,
           SQLCODE,
           DBMS_UTILITY.FORMAT_ERROR_STACK() || CHR(10) || 'Error_Backtrace...' || CHR(10) || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() || ' - File: ' || in_file_name || ' - Action: file_status updated to N to reprocess.',
           NULL);
        ELSE
           ..log other errors..
        END IF;
   END;

If no exceptions occur, the file would process and get marked as successfully processed.

The issue I am having infrequently (compared to the amount of deadlocks we get) is this doesn't work. Even if the file results in a deadlock, the logic to reprocess the file is not happening. Instead the file is being marked as completed.

Here is the error stack:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00222: error received from SAX callback function
ORA-04020: deadlock detected while trying to lock object my_user.my_table

Error_Backtrace...
ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
ORA-06512: at "SYS.DBMS_XMLSTORE", line 88
ORA-06512: at "my_user.my_package", line 1000
 - File: my_file.xml - Action: file_status updated to N to reprocess.

The file gets reprocessed only When the file_status is N (new), but it is being marked completed instead.

Am I not catching the deadlock error properly when I say IF SQLERRM LIKE '%ORA-04020%'?

Upvotes: 0

Views: 2684

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Please see an example from the documention

DECLARE
  deadlock_detected EXCEPTION;
  PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
  ...
EXCEPTION
  WHEN deadlock_detected THEN
    ...
END;
/

BTW:
ORA-04020 means: deadlock detected while trying to lock object stringstringstringstringstring
ORA-00060: means: deadlock detected while waiting for resource
Please check in ORA error codes list here
The above example is for ORA-00060 deadlock.

Upvotes: 1

Related Questions