Hello friends,
One of our cleanup job on unused data is running into ORA-00060 once every few days, no specific pattern.
I have attached the lmd trace file here and below is a photo from the most recent execution.
When i check the v$active_session_history ( for event enq: TX - row lock contention) , during that time i see below data i.e. sessions 8474 and 8104 are locked on each other. Yellow part highlighting the fact that the sqls on both session was running into enq: TX - row lock contention and has one particular sql_exec_id starting ( i.e. 48:02,48:03) stuck for long.
Both sessions were running the same delete sql as mentioned in above trace file i.e. ( delete from APPUSER.TABLE_X_N where OBJID = :H01) .
Job is configured to delete separate rowids so this is strange issue.
Few details:
1. DB version:11.2.0.4.0
2. deletes on separate sessions are divided based on modulus%objid , so it doesnt seem feasible for same rowid getting accessed in separate sessions.
3. the data which is getting deleted is not getting touched by the application.
My question:
1. Why do we run into this error even though data being delete is different rowids?
2. Can anyone please suggest how lmd trace can be investigated for finding the root cause of the deadlock?
Regds.