Last month I was on call for a mid-sized retail client whose e-commerce checkout system crashed for 45 minutes straight, all because of an unaddressed DB2 deadlock. The on-duty junior admin had only checked basic application error logs, which only showed a vague "transaction rollback" message with no context for what caused the conflict. Knowing how to check deadlock in DB2 correctly can save you hours of downtime, lost revenue, and frustrated support tickets from end users. This guide is based on 8 years of working with enterprise DB2 deployments across retail, healthcare, and finance, and every method outlined uses native DB2 tools, no expensive third-party monitoring software required.
Prerequisites Before You Check Deadlock in DB2
You don't need any special tools to run deadlock checks, but you do need to have a few core permissions and configurations set up first. I've seen dozens of admins waste 30 minutes troubleshooting why their deadlock queries return no results, only to realize they don't have the right access level or their event monitor is turned off. If you're preparing to check deadlock in DB2 for the first time, don't skip these prerequisites, as missing any of them will leave you with incomplete or no data.
- Valid DB2 instance access with at least SYSMAINT authority (standard user accounts can't access lock diagnostic data)
- Deadlock event monitor configured and active for your target database (it's disabled by default in many lightweight DB2 deployments)
- Access to the DB2 command line processor (CLP) or IBM Data Studio console for running diagnostic queries
- Formal approval to run read-only diagnostic queries if you're working on a regulated production instance
Never make changes to event monitor settings on production without testing first on a staging instance, even if you're only turning on deadlock logging. You don't have to allocate a ton of storage for event monitor logs, either; 1GB of space is enough for most small to mid-sized instances to store 30 days of deadlock data. You don't need to restart your DB2 instance to enable an event monitor, which is a common myth I hear from new DBAs all the time.
Using the DB2 Event Monitor to Capture Deadlock Data
The DB2 deadlock event monitor is the most reliable source of data for past deadlock incidents, because it captures every detail of the conflict as it happens. When a deadlock occurs, DB2 automatically selects a lower-priority transaction to roll back to break the conflict, and all details of the two conflicting transactions get written directly to the event monitor if it's active. If you're learning how to check deadlock in DB2 for the first time, start with the event monitor method, since it gives you the most complete data without risking performance impact on running systems.
First, confirm you have an active deadlock event monitor by running this query in your CLP: SELECT eventmonname, eventmonstate FROM syscat.eventmonitors WHERE eventmontype = 'DEADLOCK'. If no results return, create a new event monitor with this command, replacing the file path with a location your DB2 instance has write access to: CREATE EVENT MONITOR dlmon FOR DEADLOCKS WRITE TO FILE '/var/db2/dllogs'. Turn it on with SET EVENT MONITOR dl_mon STATE 1 and you're ready to start capturing data.
When you need to pull deadlock reports, use the native db2evmon tool to format raw log files into a human-readable report: db2evmon -db YOURDBNAME -evm dlmon > last30daysdeadlocks.txt. The event monitor captures full transaction details, including the exact SQL statements that caused the conflict and the user accounts associated with each transaction, so you don't have to guess which application team to reach out to for fixes. You can filter the output to only show deadlocks from a specific time window if you have a lot of log data, so you don't waste time sifting through weeks of old records.
Running Real-Time Deadlock Checks with DB2 Snapshot Commands
The event monitor works great for past deadlocks, but what if you suspect a deadlock is happening right now, as you're getting paged about slow application performance? That's where DB2 snapshot commands come in. Snapshots give you a point-in-time view of all active locks and waiting transactions on your instance, so you can spot deadlocks as they happen before DB2's automatic resolver kicks in. This method is ideal if you want to know how to check deadlock in DB2 as it's happening, before the automatic deadlock resolver kicks in and rolls back the victim transaction.
Run the basic lock snapshot command with: GET SNAPSHOT FOR LOCKS ON DATABASE YOURDBNAME. Look for entries where the lock status is listed as "Waiting", and two separate transactions are holding locks that the other is waiting to acquire. For a simpler, pre-formatted view, you can query the SYSIBMADM.LOCKWAITS administrative view: SELECT * FROM SYSIBMADM.LOCKWAITS WITH UR. This view only shows current conflicting locks, so it cuts out all the noise of normal, non-conflicting lock activity.
Snapshot commands don't require an event monitor to be enabled, so they're perfect for on-call emergencies when you didn't have logging set up ahead of time. The only downside is that they only show current active locks, so if the deadlock already resolved itself before you run the command, you won't find any trace of it in the snapshot results. Always run diagnostic lock queries with the WITH UR isolation level to avoid adding extra lock contention to your already struggling instance, since default isolation levels can place temporary locks on system tables when you run diagnostic queries.
How to Interpret DB2 Deadlock Reports to Fix Root Causes
Finding the deadlock is only half the battle; if you don't fix the root cause, you'll keep getting the same deadlock over and over again, usually during your busiest peak traffic times. Once you learn how to check deadlock in DB2 and interpret the results, you'll notice that 90% of deadlocks have the same small set of root causes, so you'll be able to fix them very quickly. Don't make the common mistake of just clearing the deadlock and moving on, because it will come back.
Every deadlock report will show two transactions, each holding a lock that the other is waiting to acquire. First, check the lock type: if it's a table lock, that's almost always caused by an uncommitted DDL statement, or a poorly written query that does a full table scan with no WHERE clause, locking every row in the table. If it's a row lock, that's usually because two transactions are updating the same set of rows in reverse order. For example, one transaction updates order #123 then order #456, and the other updates order #456 then order #123, creating an unresolvable conflict.
The deadlock victim is not the cause of the deadlock, so don't waste time blaming the application team that owns the rolled-back transaction. The victim is just the transaction DB2 decided is lower priority, so you need to look at both transactions to find the root cause. Most recurring DB2 deadlocks can be fixed by adjusting transaction order or adding missing indexes, not by tweaking database lock settings. I once saw a team waste three weeks adjusting locktimeout and deadlock check frequency settings, when the real fix was adding a single index on a frequently updated customer ID column that was causing full table scans on every update.
Now that you know how to check deadlock in DB2 using both historical event monitor logs and real-time snapshots, you can cut down the time it takes to resolve deadlock incidents from hours to minutes. Don't skip the step of interpreting the report to fix the root cause, otherwise you'll keep dealing with the same deadlock over and over again, usually at the most inconvenient times. If you're still having trouble with recurring deadlocks, try running a trace of all transaction activity for a 2-hour window during peak load to spot conflicting query patterns you might have missed. No matter what, avoid making random changes to production DB2 settings without testing them first, even if you find them recommended on random online forums.