How to Fix ORA-00060 Deadlock Detected While Waiting
To fix an Oracle deadlock, roll back the failed application transaction, inspect the generated trace, and correct the circular locking dependency before retrying the work. Oracle detects the deadlock automatically, but its statement rollback can leave earlier work and locks in the transaction intact. Use these symptoms to narrow the investigation; the trace determines which repair is appropriate.
| Symptom | Likely Cause | Appropriate Fix |
|---|---|---|
| Concurrent updates fail on overlapping records | Transactions acquire row or object locks in different orders | Apply a consistent locking order across every affected code path |
Parent-row deletes or key changes deadlock, with TM resources involved |
Foreign-key locking; a missing suitable child-table index is one possibility | Examine the constraints, indexes, and participating SQL before changing the schema |
| Failures appear when batch jobs or workers overlap | Multiple workers process the same business records | Partition record ownership or serialize the conflicting work |
| An index rebuild encounters a deadlock | Maintenance and other database operations form a resource cycle | Identify the competing operations and coordinate maintenance |
What an Oracle Deadlock Means
A deadlock is a circular dependency between transactions. For example:
- Session A holds a lock on account X and requests a lock on account Y.
- Session B holds a lock on account Y and requests a lock on account X.
Neither session can complete its current operation while the other retains the required lock.
This differs from ordinary blocking. A blocked session may simply be waiting for another transaction to finish, without any circular dependency. A long-running query or a row-lock wait does not, by itself, prove that a deadlock exists.
The error identifies a concurrency problem, not necessarily a broken database. Common contributors include inconsistent lock ordering, overlapping jobs, and additional SQL executed by triggers. The session receiving the error is a participant in the cycle, not automatically the only session whose code needs correction.
How to Fix an Oracle Deadlock
Step 1: Capture the Error and Locate Its Trace
- Record the application timestamp and time zone, database service, affected request or job, and complete database error:
ORA-00060: deadlock detected while waiting for resource - Check
alert.logimmediately. Find the matching deadlock entry and open the trace file named beside it. Preserve that file before log rotation or diagnostic cleanup removes it. - Make sure the trace belongs to the correct database instance. In a clustered deployment, an application connection may have reached a different instance from the one you are inspecting.
On Oracle Database installations using the Automatic Diagnostic Repository, the Oracle Database diagnostic information view exposes the trace directory:
SELECT value
FROM v$diag_info
WHERE name = 'Diag Trace';
Run this through an account authorized to read the view. If you do not have diagnostic access, ask the DBA for the matching alert-log entry and complete trace rather than requesting broad administrative privileges.
In legacy, non-ADR diagnostic layouts, the relevant directory is identified by USER_DUMP_DEST or BACKGROUND_DUMP_DEST, depending on the process that created the trace. These older settings are not the universal location on current Oracle releases; confirm the actual directory and filename from the alert-log entry.
Do not assume the default trace file for your diagnostic connection is the deadlock trace. It may belong to a different server process.
Step 2: Roll Back the Failed Application Transaction
For multi-row or multi-statement deadlocks, retrying only the failed statement is not a reliable fix.
Oracle normally rolls back the statement selected to break the deadlock, not the entire transaction. Locks acquired by earlier successful statements can remain held, leaving another participant blocked.
- Mark the application operation as failed.
- Roll back through the application’s transaction manager or ORM. If it already rolled back the transaction, do not interfere with its cleanup.
- For a manually managed SQL transaction, run this on the affected connection:
ROLLBACK; - Ensure the application does not return that connection to its pool with an unfinished transaction.
A rollback issued from a separate DBA connection does not roll back the application’s transaction.
Do not commit partial work merely to release locks. That can turn a recoverable concurrency failure into an incomplete business operation. Also avoid immediate, unbounded retries while the original transactions remain open.
Capture the incident identifiers promptly, but do not keep the failed transaction open while you spend time analyzing the trace. At the end of this recovery step, the failed attempt should be rolled back and its connection left in a clean state.
Step 3: Reconstruct the Lock Cycle From the Trace
The generated deadlock trace contains the holders, waiters, resources, and session information needed to reconstruct the cycle. Trace formatting varies by release; locate these parts even if the headings are not numbered exactly this way.
| Trace Section | What to Inspect |
|---|---|
| Section 1: Deadlock Graph | Match each resource’s lock holder to its waiter. Follow the dependencies until they return to the starting session. |
| Section 2: Rows waited on | For row-lock deadlocks, identify the records each session was trying to lock. Compare those records with the order used by the application. |
| Section 3: Information on OTHER waiting session(s) | Identify the other participants and their available SQL and session details. |
| Section 4: Information for this session | Inspect the current SQL and complete PROCESS STATE for the session that generated the trace. Match them to the resources and rows in the graph. |
Work through the evidence in this order:
- Map the entire cycle. More than 2 sessions can participate. Do not stop after finding the connection that received the error and its immediate blocker.
- Identify the resource types. A
TXresource is a transaction enqueue; aTMresource is a DML/table enqueue. Neither name alone establishes the root cause. - Match resources to application work. Use available object identifiers, row identifiers, SQL, and application request details to identify the competing operations.
- Recover the preceding transaction sequence. The current SQL may be waiting for a lock, while an earlier statement acquired the lock another session needs.
That last distinction matters. Fixing only the SQL printed beside the error can miss the statement that created the conflicting dependency.
Row information is useful for row-lock cycles, but it is not available or meaningful for every deadlock. If the trace does not identify usable row details, do not force a row-order explanation onto a different resource problem.
Step 4: Correct the Dependency Shown by the Trace
- Write down the transaction sequence for every participant, including statements executed before the failure.
- Choose the smallest change that removes the demonstrated dependency.
- Test that change under concurrent execution before deploying it.
The implementation depends on the evidence:
- For conflicting record order, define a shared ordering rule. For example, every transaction touching the same accounts can acquire their locks in ascending account-key order. If operations span several tables, the rule must cover table order as well as row order. Include background jobs, triggers, and less common application paths.
- Do not treat SQL text order as a lock-order guarantee. The order of values in an
INlist does not establish the order in which Oracle acquires row locks. Where explicit locking is necessary, gather the affected keys, sort them, and acquire the required locks in that agreed order before making the changes. - If a foreign key is implicated, inspect the actual index definition. An unrelated index on the child table does not solve foreign-key locking. Check whether the foreign-key columns are covered by a suitable leading-column index. Test a proposed index, assess its write overhead, and deploy it through normal schema-change controls.
- Include hidden database work. A trigger can update another table or record and introduce a dependency absent from the application’s visible SQL. Review the complete transaction path, not just the statement submitted by the client.
- For overlapping workers, establish ownership of conflicting work. Partition records by a stable business key or serialize processing for the affected keys. A process-local mutex is insufficient if other application instances or jobs can modify the same records.
Keep transactions focused on the work that must be atomic. Avoid leaving locks held while waiting for user input or slow external services, where the application design permits those activities to occur outside the transaction.
Do not scatter extra commits through the code to make locks disappear. Changing transaction boundaries can violate the business operation’s atomicity.
If the graph points to a resource problem you cannot explain through the SQL and transaction sequence, retain the complete trace and involve the DBA. Changing storage settings, rebuilding unrelated indexes, or terminating sessions without that explanation is not a reliable repair.
Step 5: Retry the Complete Operation Safely
- Start a fresh transaction after the failed attempt has been rolled back.
- Re-execute the complete logical business operation, not just its final failed statement.
- If it fails again, roll back, preserve the new incident details, and stop or retry according to a bounded policy.
For applications that encounter occasional contention, use capped retries with backoff and jitter. Choose the retry limit and delays to fit the application’s latency budget rather than relying on a universal Oracle setting. Repeated failures should produce an actionable error, not an endless retry loop.
Check whether the operation has effects outside Oracle. A database rollback does not undo an email, an external payment request, or a message already sent to another system. Use appropriate idempotency or coordination mechanisms before automatically replaying that work.
The recovery is complete only when the operation has either committed successfully or failed cleanly with no unfinished database transaction.
Deadlocks in Single Statements, SAP, and Index Rebuilds
Participating transactions, execution plans, affected rows, and competing sessions identify the locking conflict behind these deadlocks.
Deadlocks Within a Single SQL Statement
A transaction does not need several application-issued statements to deadlock. A single statement that changes multiple rows can participate in a cycle if concurrent executions reach overlapping rows in different orders.
Different access paths can contribute to that ordering difference. Combining several updates into one larger statement therefore does not automatically eliminate deadlocks.
For this pattern, compare the participating execution plans and affected rows. If the application requires controlled lock acquisition, redesign that acquisition consistently rather than depending on a particular optimizer plan or hint to remain unchanged.
A retry may succeed after the competing transaction finishes, but validate the revised operation under concurrency. A successful isolated execution does not show that the conflict has been removed.
Deadlocks Reported by SAP ABAP
An ABAP SQL message containing the Oracle deadlock error is reporting a database concurrency failure, not necessarily an ABAP syntax problem.
- Preserve the ABAP short dump or job log, including its timestamp and business-operation context.
- Have the Oracle DBA match that event to the database trace and identify all participating transactions.
- Review the owning application’s update sequence, locking design, and overlapping jobs through supported SAP development and operations processes.
- After the correction, rerun the affected business operation and verify its document or job state to prevent duplicate processing.
Do not replay an individual Open SQL statement merely to suppress the error. Recovery must respect the SAP application’s transaction boundaries.
Deadlocks During an Index Rebuild
A deadlock during an index rebuild does not, by itself, prove index corruption. Maintenance can participate in a locking cycle with other DDL, DML, or maintenance operations.
- Identify the index and competing sessions from the trace.
- Coordinate an appropriate maintenance window or prevent the demonstrated overlap.
- Let the competing transactions finish cleanly, then rerun the approved maintenance operation.
- Verify the operation’s completion and the status of the affected index or index partitions using the appropriate data dictionary views.
An online rebuild is not a universal deadlock fix; online operations still require synchronization and can encounter locking conflicts.
If the failure recurs without the expected competing workload, preserve the trace, exact maintenance operation, Oracle version, and patch information for DBA or vendor investigation. Avoid repeatedly rebuilding or killing sessions without establishing the dependency.
How to Confirm the Fix and Prevent Recurrence
Test the failure conditions, not merely the statement that returned the error.
Use this checklist before considering the incident resolved:
- Replay the competing operations together. Preserve the relevant transaction boundaries and overlapping records in a suitable test environment.
- Verify business correctness. Check that balances, status transitions, parent-child relationships, or other application invariants remain valid.
- Exercise the failure path. Confirm that a failed request rolls back and that pooled connections do not retain unfinished transactions.
- Check retry behavior. Confirm that retries are bounded and cannot duplicate external side effects.
- Review the diagnostic output. Look for fresh deadlock traces associated with the test workload. Ordinary blocking can still occur even after the circular dependency is removed.
For ongoing prevention, keep a documented lock-order convention for shared business operations. Review new jobs and triggers against that convention, and record enough application context, such as request identifiers, modules, and actions, to connect future traces to the responsible code paths.
Deploy the correction through the normal change process, then monitor the workload conditions that originally triggered the failure. If it returns, collect a new trace rather than assuming the new incident has the same cause.
Frequently Asked Questions
What Does SQLSTATE ‘61000’ With Error Code ’60’ Mean?
In Oracle error output, SQLSTATE 61000 together with native error code 60 identifies a deadlock. A driver or framework may wrap it in another exception, so preserve the underlying database error and exception chain. The SQLSTATE alone does not identify which records or transactions formed the cycle.
Can Increasing a Lock Timeout Fix a Deadlock?
No. A lock timeout changes how long an operation may wait; it does not remove a circular dependency between transactions. Timeout handling can be useful for ordinary blocking, but recurring deadlocks require a correction to the participating operations or their concurrency.
How Can I Check for a Deadlock After the Sessions Disappear?
Use the saved database trace and matching alert-log entry as the historical evidence. After recovery, a live blocking check may no longer show the original participants or locks. An empty live blocking check does not prove that the earlier deadlock never occurred.
