If you’ve ever been woken up at 2 a.m. by an alert that your e-commerce platform’s checkout flow is frozen, you know how damaging unaddressed Oracle deadlocks can be. These stuck transactions don’t just slow down users—they can lead to lost sales, corrupted data, and hours of unplanned troubleshooting. Figuring out why a deadlock happened first requires you to know how to check deadlock history in Oracle, so you can pinpoint the root cause instead of just restarting transactions and hoping the issue doesn’t come back. I’ve worked with Oracle databases for over 8 years across retail and SaaS use cases, and the steps I share below are the same ones I use to resolve 90% of deadlock incidents in under 20 minutes.
How to Check Deadlock History in Oracle Using Native Tools
You don’t need expensive third-party monitoring software to pull deadlock history, especially for immediate incident response. Oracle builds in several free tools that log all deadlock events by default, as long as your instance is using standard configuration settings. If you’re learning how to check deadlock history in Oracle for the first time, start with these tools, since they require no extra setup and hold all the core context you need to diagnose an issue. The most reliable native tools you can use are:
- Oracle Alert Log: The first place to check for deadlock events, as it records every deadlock occurrence with a timestamp and reference to the associated trace file.
- Trace File Viewer (TRCVR): Lets you open the deadlock-specific trace file to see exactly which transactions, queries, and rows were involved in the deadlock.
- Dynamic Performance Views: Views like V$DEADLOCK, DBAHISTACTIVESESSHISTORY, and V$LOCK let you pull historical deadlock data directly with SQL queries, no file access needed.
- Automatic Workload Repository (AWR): If you have an AWR license, you can generate reports that aggregate deadlock events over custom time ranges to spot patterns.
Just make sure you have the right permissions before you start: you’ll need SELECT access to the dynamic views, and read access to the diagnostic dest directory on the database server to pull trace files. If you don’t have those permissions, reach out to your senior DBA first, because attempting to access restricted directories can trigger security alerts in most enterprise environments.
Step-by-Step SQL Query to Pull Recent Deadlock History
Once you confirm there was a deadlock event in the alert log, running a targeted SQL query is the fastest way to get structured data without sifting through hundreds of lines of unformatted log text. I use a simple, lightweight query for this that pulls only the relevant details, including the deadlock time, the session IDs involved, and the SQL IDs of the conflicting queries, so I don’t waste time sorting through irrelevant data.
Always filter your query by a specific time range to avoid pulling thousands of irrelevant rows if your instance has been running for months. I usually start with a 24-hour range if I don’t know exactly when the deadlock happened, then narrow it down if I get too many results. Data in V$ views is purged every 7 to 30 days by default, depending on your instance configuration. If you’re looking for a deadlock that happened more than a month ago, you’ll need to pull the data from AWR or archived alert logs instead.
The query will return two rows for each deadlock, one for each of the conflicting sessions. You’ll be able to see which user ran each query, what object was locked, and the type of lock that caused the conflict. Most deadlocks I see are caused by two update queries running in reverse order on the same set of rows, so comparing the two SQL IDs from the results will usually show you that pattern immediately.
Common Mistakes to Avoid When Reviewing Oracle Deadlock History
A lot of new DBAs and developers make avoidable errors when checking deadlock history that lead them to misdiagnose the root cause, or miss critical context entirely. If you’re new to learning how to check deadlock history in Oracle, it’s easy to jump to conclusions without checking all the context first.
First, don’t stop at the first deadlock you find. If your application has poorly written transaction logic, you might have dozens of small, unreported deadlocks happening every hour before one causes a major outage. Always check for events 2 to 3 hours before the reported outage to see if there’s a pattern of repeated deadlocks on the same tables.
Another common mistake is ignoring the session context. You might see a deadlock between two queries and assume the query logic is bad, but the real issue could be that one of the sessions is running with an unnecessarily high transaction isolation level, or a user left an uncommitted transaction open for hours before the conflict happened. Always check the transaction start time for both sessions to rule out long-running idle transactions as the cause.
I also see people waste hours trying to fix a deadlock that was a one-off event caused by a manual ad-hoc query run by a data analyst, instead of focusing on repeated events that impact end users. If the deadlock only happened once and there’s no sign of it recurring, you don’t need to rewrite your entire transaction logic. Just note the event and keep an eye out for similar events in the future.
What to Do After You Find Deadlock History Records
Finding the deadlock history is only the first part of the process. You need to use that data to fix the issue and prevent it from happening again. First, document all the details: the timestamp, the sessions involved, the SQL IDs, and the locked objects, so you can reference it later if the same issue happens again.
If the deadlock was caused by conflicting transaction order, work with your dev team to adjust the application logic so all transactions update rows in the same order. That single fix resolves around 80% of recurring Oracle deadlocks, in my experience. If the deadlock was caused by long-running transactions, look for ways to optimize the queries, or break large transactions into smaller, faster ones that commit more frequently.
Never kill both sessions involved in a deadlock—Oracle automatically kills the session with the lower transaction cost to resolve the deadlock immediately, so you only need to take action if the issue keeps recurring. If you notice a pattern of deadlocks during peak usage times, you might also need to adjust your instance’s lock timeout settings, or add indexes to reduce the time rows stay locked during update operations. Just make sure you test any configuration changes in a staging environment first, because adjusting lock settings can have unintended side effects on other database operations.
Deadlocks are an inevitable part of running an Oracle database, but they don’t have to be a recurring headache. Once you know how to check deadlock history in Oracle properly, you can move past guessing why outages happen and start implementing targeted fixes that prevent future issues. Take the time to test the methods and queries we covered on a non-production instance first, so you’re comfortable using them when a real outage hits. Over time, you’ll even be able to spot deadlock patterns before they cause user-facing issues, saving you and your team hours of unplanned troubleshooting.