A deadlock happens when two transactions each wait for a row the other has locked: neither can go on. Oracle detects deadlocks by itself within seconds and breaks them by failing one statement with ORA-00060. Understanding how that looks, where Oracle records it, and how to avoid it keeps deadlocks rare.
Code for This Guide
The main examples are in the examples/transactions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
A Deadlock in One Script
The main transaction updates an aircraft row. An autonomous transaction then tries to update the same row: it waits for the main transaction, which in turn waits for the autonomous block to finish. That is a deadlock at once.
Example:
update aircraft set status = 'MAINTENANCE' where tail_number = 'A6-NAD'; declare pragma autonomous_transaction; begin -- waits for the row this session's main transaction holds: a deadlock update aircraft set status = 'RETIRED' where tail_number = 'A6-NAD'; end; / rollback;
Output:
1 row updated. declare * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at line 5 Rollback complete.
Oracle fails the waiting statement with ORA-00060. Only that statement is rolled back; the transaction that received the error is still open, holds its other locks, and should be rolled back or retried by the application. The example rolls back.
Where Oracle Records It
Every deadlock writes a message to the database alert log and a trace file with a deadlock graph: the sessions, the rows or objects they waited for, and the SQL statements involved. A DBA can read them from the diagnostic destination, or from V$DIAG_ALERT_EXT. When an application reports ORA-00060, the trace file shows which two statements collided.
How Deadlocks Arise
| Cause | Example |
|---|---|
| Rows updated in different orders | Session 1 updates account A then B; session 2 updates B then A |
| Unindexed foreign keys | Deleting a parent row locks the child table, which collides with concurrent child changes |
| Bitmap indexes on busy tables | A change locks a range of rows covered by the same bitmap |
| Autonomous transactions | A block updates a row its own caller holds, as in the example |
How to Avoid Them
- Update rows in the same order everywhere, for example by primary key.
- Index foreign key columns.
- Lock the rows you will need up front with SELECT ... FOR UPDATE, in a fixed order.
- Keep transactions short, and retry the transaction when ORA-00060 occurs.
Related Guides
Conclusion
Oracle detects deadlocks automatically and fails one waiting statement with ORA-00060, recording a deadlock graph in a trace file. Roll back or retry the affected transaction, and prevent deadlocks by changing rows in a consistent order, indexing foreign keys, and keeping transactions short.
