If you’ve worked as an Oracle DBA for even a few months, you’ve probably gotten that panicked late-night message from the operations team: core applications are timing out, users are getting transaction failure errors, and standard performance metrics like CPU and memory look totally normal. More often than not, if you rule out network glitches and unoptimized long-running queries, you’re dealing with a deadlock. A lot of new admins waste hours sifting through random logs trying to pinpoint the issue, but the process doesn’t have to be that complicated. If you’re wondering how to find deadlock in Oracle without unnecessary guesswork, this post covers every method I’ve used across 8 years of managing enterprise Oracle deployments for retail and SaaS clients.
How to Find Deadlock in Oracle Using Built-in Alert Logs First
The very first place you should check for deadlock evidence is your Oracle instance’s alert log. Oracle automatically logs all deadlock events with ORA-00060 error codes in the alert log by default, no extra configuration required. You don’t need any special permissions to access this log either, as long as you have access to the database server’s file system. To find the exact location of your alert log, run the query SHOW PARAMETER BACKGROUNDDUMPDEST in your SQL client, and navigate to the listed directory.
When you open the alert log, search for entries containing “ORA-00060” or “Deadlock detected”. Each deadlock entry will include core details like the session IDs (SID) of the two conflicting sessions, the transaction IDs for each deadlocked operation, and the names of the tables or objects involved in the lock. You’ll also see a note that the deadlock is not an Oracle error itself, but a result of application design or user SQL choices. One thing to remember here: don’t only check the most recent entries. Deadlocks can leave residual locks that cause performance issues for 10 to 15 minutes after the initial event, so you’ll want to scan back at least 20 minutes from the time users first reported issues. The only downside of relying solely on the alert log is that it won’t give you full SQL context or bind variable data, so you’ll usually need to do further investigation to find the root cause.
Use Dynamic Performance Views to Pull Real-Time Deadlock Data
If the alert log confirms a deadlock happened but you need more context to resolve it, Oracle’s dynamic performance (v$) views are your next best tool. These views pull real-time data about active sessions, locks, and transactions directly from the instance’s memory, so you can see exactly what’s happening on your database right now. You will need the SELECTCATALOGROLE permission to query these views, so make sure you’re logged in with an admin account before you start running queries. The three most useful views for deadlock detection are:
- v$session: Pull usernames, machine addresses, and SQL IDs of sessions holding or waiting for locks, so you can identify which users or applications are involved in the deadlock
- v$lock: Filter for type 'TX' locks (the transaction locks that cause 99% of Oracle deadlocks) and check the LMODE and REQUEST columns to see which session is holding a lock and which is waiting for that same lock
- v$transaction: Match transaction IDs from the alert log to get start times and undo block counts, so you can tell how long the deadlocked transaction has been running and how much data it’s modified
A simple query to find waiting lock sessions is SELECT s.sid, s.serial#, s.username, s.machine, l.lmode, l.request FROM v$lock l JOIN v$session s ON l.sid = s.sid WHERE l.type = 'TX' AND l.request > 0. This will immediately return all sessions that are waiting for a transaction lock, so you can match them to the entries you found in the alert log. For example, I used this exact query last quarter to resolve a deadlock on a retail client’s order processing system. The query returned two sessions: one from the inventory update service and one from the order submission service, both holding TX locks on tables the other was trying to access. I pulled their SQL IDs from v$session, checked the associated queries, and found the dev team had swapped the order of table updates in a recent code push, causing the circular lock.
Enable and Use Oracle Deadlock Traces for Full Context
If the alert log and v$ views don’t give you enough information to find the root cause — for example, if you need to see specific bind variables or call stacks for the deadlocked queries — you can enable Oracle’s deadlock tracing to capture full context. You can enable level 12 deadlock tracing to capture full SQL statements, bind variables, and call stacks for both deadlocked sessions, which makes it trivial to see exactly what data each transaction was trying to modify. To enable the trace, run ALTER SYSTEM SET EVENTS '60 TRACE NAME ERRORSTACK LEVEL 12' in your SQL client. Once enabled, any future deadlocks will generate a detailed trace file in the same BACKGROUNDDUMPDEST directory as your alert log.
The trace file will include a visual deadlock graph that shows exactly which session holds which lock, and which lock each session is waiting for. You’ll also see the full SQL text for both transactions, plus any bind variables that were used, so you can see the exact row IDs of the data being modified. It’s important to note that tracing does add minor overhead to your database, so you shouldn’t leave it enabled permanently. Once you’ve captured the deadlock data you need, turn tracing off with ALTER SYSTEM SET EVENTS '60 TRACE NAME ERRORSTACK OFF'. This method is especially useful for deadlocks that happen intermittently, where you can’t catch them in real time with v$ views. I’ve used it for cases where deadlocks only happened during peak traffic hours, and the extra bind variable data let me see that the conflicts were limited to specific high-volume product IDs in the inventory system.
Common Mistakes to Avoid When Detecting Oracle Deadlocks
Even if you follow all the steps above, it’s easy to make small mistakes that waste time or lead you to misdiagnose the issue. The most common mistake I see new DBAs make is confusing regular lock waits with deadlocks. A regular lock wait happens when one session holds a lock on a resource, and another session waits for that lock to be released. These usually resolve on their own once the holding session commits or rolls back. A deadlock, by contrast, is a circular lock where two sessions each hold a lock the other needs, so neither can ever resolve on their own. Oracle will automatically terminate one of the two deadlocked sessions to break the circle, but you still need to fix the root cause to prevent it from happening again.
Another common mistake is killing the waiting session and moving on without investigating the root cause. It’s tempting to just terminate the conflicting sessions and get the application back up as fast as possible, but if you don’t fix the underlying issue — usually an application that updates tables in inconsistent order across different services — the deadlock will just happen again. I once worked with a client that had been killing deadlocked sessions every week for six months, until a Black Friday sale caused 12 deadlocks in an hour and took their checkout system down for three hours. Spending 15 extra minutes to find the root cause after the first deadlock would have saved them hundreds of thousands of dollars in lost revenue.
You also don’t want to run broad, disruptive commands when you’re investigating deadlocks. A lot of new admins try to run ALTER TABLE ... UNLOCK when they see a lock, but that will kill all sessions accessing that table, not just the deadlocked ones, and can cause far more downtime than the original deadlock. Stick to targeting only the specific deadlocked sessions you identify through logs and v$ views to minimize impact on other users.
Deadlocks are an unavoidable part of managing high-traffic Oracle databases, but they don’t have to cause hours of downtime or frustrated users. You don’t need fancy third-party tools to track them down either; all the methods covered in this post use native Oracle features that work on every supported version of the database. Knowing how to find deadlock in Oracle fast lets you resolve issues before they impact end users, and fix root causes to prevent the same deadlocks from repeating down the line. Next time you get that panicked message about slow applications, start with the alert log, move to v$ views for real-time data, and only pull traces if you need extra context — you’ll have the issue fixed in a fraction of the time you used to spend.