Alhamdulillah, another interesting production incident to share. This one is a classic example of how a small code change — a single commented-out COMMIT — can stay silent for weeks until a new concurrent workload exposes it as an Oracle deadlock in production. If you are searching for Oracle deadlock troubleshooting or ORA-00060 trace file analysis in an Oracle EBS environment, this walkthrough covers the full investigation from symptom to fix.
Quick Summary
- Issue: ORA-00060 deadlocks after deployment of a new APEX DBMS_SCHEDULER job.
- Impact: Oracle Workflow Background Process intermittently failed, delaying order processing.
- Root Cause: A previously removed COMMIT caused row locks to be held far longer than intended, allowing concurrent sessions to deadlock.
- Resolution: Restored the correct transaction boundary, adjusted scheduler timing, and verified with Oracle Support that no product-side issue was involved.
- Result: No recurrence after deployment.
The Symptom
On a production EBS environment (EBS 12.1, Database 12.1.0.2), we started receiving alerts for ORA-00060 in the database alert log:
ORA-00060: Deadlock detected. More info in file
/u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_12345.trcAt the same time, the business reported sales order lines not progressing. The Oracle Workflow Background Process for the OM Order Line workflow (ONT) was erroring intermittently — a visible impact on order processing and a classic Oracle EBS performance troubleshooting scenario.
A quick note on deadlock behavior: Oracle detects deadlocks automatically and resolves them by rolling back one of the participating statements — the victim — allowing the remaining transaction to continue. Unlike simple blocking, only the offending statement is rolled back, not the victim’s entire transaction. The victim’s session receives ORA-00060 and a trace file is written to disk. That is why the application does not hang forever; instead, you see intermittent errors and trace files accumulating.
Step 1 – Read the Deadlock Trace
The trace file is always the starting point of ORA-00060 trace file analysis. The deadlock graph showed two sessions blocking each other on TX enqueues in row-exclusive mode:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-000a0015-0003c2d1 45 312 X 38 127 X
TX-0008001f-0004a1b2 38 127 X 45 312 XBoth sessions were waiting on enq: TX - row lock contention, each holding a row the other wanted — textbook Oracle row lock contention forming a deadlock cycle. The trace file also identifies which session was chosen as the victim and the exact SQL statement that was rolled back, which makes it the definitive evidence for the investigation.
Conceptually, the cycle looked like this:
Workflow Background Process
│
│ Locks Row A
▼
Reservation Row A
│
│ Waiting for Row B
▼
Reservation Row B
▲
│ Locked by
│
APEX Scheduler Job
Workflow waits for APEX.
APEX waits for Workflow.
→ Oracle detects a deadlock (ORA-00060)Step 2 – Identify the Two Sessions
SET LINES 200 PAGES 100
COL SID FORMAT 99999
COL SERIAL# FORMAT 999999
COL USERNAME FORMAT A12
COL PROGRAM FORMAT A28
COL MODULE FORMAT A28
COL ACTION FORMAT A20
COL SQL_ID FORMAT A14
SELECT s.sid, s.serial#, s.username,
NVL(s.program,'N/A') program,
NVL(s.module,'N/A') module,
NVL(s.action,'N/A') action,
NVL(s.sql_id,'N/A') sql_id
FROM v$session s
WHERE s.sid IN (312, 127);Session 1 was the Workflow Background Process (FNDWFBG, ONT item type) — making this an Oracle Workflow deadlock scenario. Session 2 was more interesting — a DBMS_SCHEDULER job session:
COL JOB_NAME FORMAT A25
COL OWNER FORMAT A12
COL STATE FORMAT A12
COL REPEAT_INTERVAL FORMAT A40
SELECT owner, job_name, state, repeat_interval
FROM dba_scheduler_jobs
WHERE job_name = 'APEX_ORDER_RESERVE';A newly deployed APEX-driven scheduler job, running every few minutes, calling a custom reservation package — the second half of an Oracle DBMS_SCHEDULER deadlock pattern.
Step 3 – Which Rows Were They Fighting Over?
From the trace file, the “Rows waited on” section gives the object number:
COL OWNER FORMAT A10
COL OBJECT_NAME FORMAT A30
COL OBJECT_TYPE FORMAT A12
SELECT owner, object_name, object_type
FROM dba_objects
WHERE object_id = &object_id_from_trace;Both sessions were colliding on reservation rows — the Workflow process updating them as part of order line progression, and the APEX job updating the same rows through the custom package.
Step 4 – The Root Cause
Reviewing the custom reservation package source, we found this:
UPDATE xx_order_reservations
SET status = 'RESERVED'
WHERE header_id = p_header_id;
-- COMMIT; <== commented out during a previous changeThe investigation identified that the custom package was holding row locks far longer than intended, because a COMMIT had been removed in an earlier code change. Instead of releasing locks per iteration, the scheduler job session held all of its row locks across the entire loop over order headers. It is worth being precise here: a missing COMMIT by itself does not cause a deadlock — a deadlock requires two sessions acquiring locks on overlapping rows in conflicting order. What the missing COMMIT did was dramatically widen the lock-holding window, so when the new APEX scheduler job started running concurrently with the Workflow Background Process against the same reservation rows, the probability of the two sessions interleaving into a deadlock cycle went from negligible to near-certain.
This also explains why the package ran in production for weeks without issue — until the APEX job was deployed, nothing else contended for those rows at that frequency. It also explains why testing never caught it: lower environments rarely generate the same level of concurrent activity as production, making lock-contention issues extremely difficult to reproduce before go-live. The extended lock duration was always present; the new concurrent workload exposed it.
The Fix
- Restored the COMMIT at the correct transactional boundary in the custom package (per-iteration, after each header’s reservation update), shrinking the lock-holding window.
- Redeployed the package during an approved change window.
- Rescheduled the APEX job to avoid peak Workflow Background Process cycles as an additional safety margin.
- Raised an SR with Oracle Support to confirm no product-side involvement — confirmed clean; purely custom code.
No ORA-00060 recurrence since the fix, ما شاء الله.
Lessons Learned
- An Oracle deadlock almost always has two contributors: the locking pattern AND the concurrency pattern. Fixing either breaks the cycle, but fix the code defect, not just the schedule.
- Commented-out COMMITs are silent time bombs. Code review for custom PL/SQL touching EBS transactional tables must treat transaction boundaries as seriously as the DML itself.
- New scheduler jobs (APEX, DBMS_SCHEDULER, concurrent programs) should be assessed for row-level contention with existing Oracle Workflow and concurrent processing before go-live.
- The deadlock trace file gives you everything: the sessions, the victim, the SQL, the rows. Start there, not with guesswork.
Conclusion
Production incidents often reveal issues that remain hidden during testing, because realistic concurrency is difficult to reproduce in lower environments. This incident reinforced a simple discipline: whenever a new workload is introduced — an APEX scheduler job, a concurrent program, or an integration — review the transaction boundaries of every custom PL/SQL object it touches, and ask what else updates those same rows.
In Oracle, deadlocks are rarely caused by a single statement — they are caused by the interaction of multiple sessions under concurrency. Understanding transaction boundaries is often the key to solving them.
Disclaimer: All environment names, hostnames, and identifiers in this post are anonymized. The views expressed are my own.
