What is deadlock?
In the world of database management, a Deadlock is the ultimate stalemate. It’s a specific situation where two or more sessions are waiting for each other to release a lock, creating a circular dependency that can never be resolved on its own.
If a Row Lock is a "waiting line," a Deadlock is a "Mexican Standoff" where no one can move until someone else drops their guard.
Imagine two users, Alice and Bob, working on a banking database:
Alice updates Account A (Oracle places a lock on Account A).
Bob updates Account B (Oracle places a lock on Account B).
Alice tries to update Account B (She hangs, waiting for Bob).
Bob tries to update Account A (He hangs, waiting for Alice).
Now, Alice is waiting for Bob, and Bob is waiting for Alice. Without intervention, they would sit there forever.
Unlike many other performance issues where the database just "stays slow," Oracle is very proactive about deadlocks.
Detection: The database has a background process that constantly scans the "wait-for" graph.
The Sacrifice: When Oracle detects a circular dependency, it breaks the deadlock by choosing one of the sessions as the "victim."
The Error: Oracle rolls back the statement (not the whole transaction) of the victim and throws the famous error:
ORA-00060: deadlock detected while waiting for resource
The other session (the "survivor") continues to wait until the victim either commits or rolls back their entire transaction.
The most common cause is when the application updates tables in a different order. If App Part 1 updates Table A then B, but App Part 2 updates Table B then A, a deadlock is inevitable under high load.
As we’ve discussed in previous blogs, if you delete a parent record and the foreign key in the child table is not indexed, Oracle might place a full-table lock on the child. If two sessions do this simultaneously on different tables, they can easily deadlock.
Bitmap indexes are great for data warehousing but terrible for high-concurrency "Insert/Update" environments. A single bitmap index entry covers a range of rows; locking one row can effectively lock hundreds of others, leading to frequent deadlocks.
When an ORA-00060 occurs, Oracle automatically generates a Trace File in the ADR (Automatic Diagnostic Repository). This file is gold for DBAs. It contains:
The SQL statements being run by both sessions.
The rows/resources they were fighting over.
A "Deadlock Graph" showing the circular chain.
You can find the location of this trace file by checking the database Alert Log.
| Strategy | Action |
| Standardize Order | Ensure all application code updates tables in the exact same order (e.g., always Table A, then Table B). |
| Index Foreign Keys | This prevents "broad" table-level locks during DML. |
| Keep Transactions Short | The longer a transaction stays open, the higher the chance someone else will collide with it. |
| Avoid User Interaction | Never hold a lock while waiting for a human to click "OK" on a screen. |
It is important to remember that a deadlock is almost never an Oracle bug. It is a logical conflict in the way the application is interacting with the data. While the ORA-00060 error looks scary, it's actually Oracle doing its job by preventing the database from freezing up.
Deadlocks are the database's way of telling you that your processes are crossing paths in a dangerous way. Don't just ignore the error—find that trace file, identify the "Order of Operations," and straighten out the traffic flow.