DB2 LUW Locking Troubleshooting: How to Find the Blocker and Waiter
Locking is one of the most common causes of application slowness in a production database environment.
From the application side, the symptom may simply appear as:
"The application is hanging."
or:
"The transaction is taking too long."
But from a Db2 DBA perspective, the real question is:
Who is waiting, who is holding the lock, and why?
In this article, I will walk through a practical DB2 LUW locking investigation using db2pd and Db2 monitoring table functions.
The examples are generic and can be adapted to DB2 LUW environments running on Linux, AIX or other supported platforms.
1. What is a DB2 lock wait?
Suppose two applications are accessing the same table.
Application A
|
| UPDATE
v
+-------------+
| CUSTOMER |
+-------------+
^
|
| waiting
|
Application BIf Application A has acquired a lock that conflicts with the lock requested by Application B, Application B has to wait.
In simple terms:
Application A = Lock Holder / Blocker
Application B = Lock Waiter
This distinction is extremely important during production troubleshooting.
Finding the waiter tells us who is experiencing the problem.
Finding the blocker tells us what is preventing progress.
2. Typical production symptoms
A locking issue can appear in many different ways:
Application response is slow
Transactions appear to hang
SQL requests remain active for a long time
Users report intermittent slowness
Batch jobs take longer than expected
Lock timeout errors occur
Deadlock errors occur
Multiple applications appear to be waiting
A common mistake is to immediately assume that high CPU or poor SQL performance is responsible.
Sometimes the SQL itself is fine.
The application is simply waiting for another transaction to release a lock.
3. First check — db2pd
When investigating an active locking issue, one of my first commands is:
db2pd -db SAMPLE -wlocks detailReplace SAMPLE with your database name.
The -wlocks option displays owner and waiter information for locks currently being waited on. With detail, Db2 can provide additional information such as the table and schema. IBM's documentation specifically describes G as granted/owner status and W as waiting status in this output.
A simplified example may look like:
Database Member 0 -- Database SAMPLE -- Active
Locks being waited on:
AppHandl TranHdl Type Mode Sts AppName AuthID
--------- -------- ---------- ----- ---- -------- --------
101 45 Row X G db2bp APPUSER
102 46 Row X W db2bp APPUSERThe important part is:
Sts = GThe application associated with this lock is the holder.
And:
Sts = Windicates the waiter.
4. Finding the table
With:
db2pd -db SAMPLE -wlocks detailDb2 can provide table and schema information associated with the waited-on lock.
For example:
AppHandl TranHdl Type Mode Sts TableNm SchemaNm
-------- ------- --------- ---- --- --------- --------
101 45 TableLock X G ORDERS SALES
102 46 TableLock X W ORDERS SALESNow the investigation becomes much more meaningful:
Database : SAMPLE
Schema : SALES
Table : ORDERS
Blocker : Application 101
Waiter : Application 102This is already enough information to start investigating the application owners.
5. Investigate using MON_GET_APPL_LOCKWAIT
Db2 also provides monitoring table functions for locking.
Two particularly useful functions are:
MON_GET_LOCKS
MON_GET_APPL_LOCKWAITIBM documents these functions as the current-lock monitoring interfaces; unlike some request/activity monitoring data, lock information is available without enabling a separate collection mechanism.
For a specific application:
SELECT
LOCK_WAIT_START_TIME,
LOCK_NAME,
LOCK_OBJECT_TYPE,
LOCK_MODE,
LOCK_CURRENT_MODE,
LOCK_MODE_REQUESTED,
LOCK_STATUS,
HLD_APPLICATION_HANDLE
FROM TABLE(
MON_GET_APPL_LOCKWAIT(102, -2)
)
WITH UR;Here:
102 = waiting application handle
-2 = all active database membersThe exact columns available should be checked against the Db2 release/fix pack in use.
MON_GET_APPL_LOCKWAIT provides information about both the lock being requested and the application currently holding the lock.
6. Identify the blocker
Suppose the query returns:
LOCK_WAIT_START_TIME : 2026-09-27 10:32:14
LOCK_MODE_REQUESTED : X
HLD_APPLICATION_HANDLE : 101We now know:
Waiter = 102
Blocker = 101The next step is to investigate Application 101.
7. Find application details
Use:
SELECT
APPLICATION_HANDLE,
APPLICATION_NAME,
SESSION_AUTH_ID,
CLIENT_APPLNAME,
CLIENT_USERID,
CLIENT_WRKSTNNAME,
CLIENT_IPADDR,
UOW_START_TIME
FROM TABLE(
MON_GET_CONNECTION(NULL, -2)
)
WHERE APPLICATION_HANDLE IN (101,102)
WITH UR;MON_GET_CONNECTION provides connection-level information including application handle, application name, client information and unit-of-work timing.
A result might conceptually look like:
APPLICATION_HANDLE APPLICATION_NAME SESSION_AUTH_ID CLIENT_IPADDR
------------------ ---------------- ---------------- -------------
101 JDBC APPUSER 10.10.10.21
102 JDBC APPUSER 10.10.10.22Now we can correlate the Db2 session with the application/server.
8. Check the transaction
A lock is often held because the transaction has not committed.
Use:
SELECT
APPLICATION_HANDLE,
UOW_ID,
UOW_START_TIME,
UOW_LOG_SPACE_USED,
ACT_COMPLETED_TOTAL
FROM TABLE(
MON_GET_UNIT_OF_WORK(NULL, -2)
)
WHERE APPLICATION_HANDLE IN (101,102)
WITH UR;MON_GET_UNIT_OF_WORK provides unit-of-work metrics, including the application handle and UOW start time.
Pay particular attention to:
UOW_START_TIME
UOW_LOG_SPACE_USEDFor example:
Application 101
UOW_START_TIME = 09:45
Current time = 10:40That is a transaction that has been open for approximately 55 minutes.
That does not automatically mean it is a problem.
The DBA should determine what the transaction is doing before taking action.
9. Check whether the blocker is still active
A common mistake during incident handling is to find a blocker and immediately terminate it.
First determine whether the application is actually working.
Questions to ask:
Is the transaction progressing?
Is it a batch job?
Is it performing a large UPDATE?
Is it waiting on another resource?
Is the application still connected?
Is the transaction expected to remain open?
Has the application owner confirmed the activity?This is particularly important for large production transactions.
10. A practical investigation flow
My preferred troubleshooting flow is:
Application reports slowness
|
v
Check current lock waits
|
v
Identify waiter
|
v
Identify blocker
|
v
Identify table/schema
|
v
Identify application
|
v
Check transaction/UOW
|
v
Identify SQL/activity
|
v
Determine root cause
|
v
Resolve safely
|
v
Verify lock clearedThis approach prevents the investigation from becoming:
"Application is slow"
|
v
"Kill a DB2 session"without understanding the actual cause.
11. Useful db2pd combination
During a more detailed investigation, the following can be useful:
db2pd -db SAMPLE \
-locks \
-transactions \
-applications \
-dynamicIBM's db2pd examples demonstrate combining lock, transaction, application and dynamic SQL information when diagnosing a lock wait.
This can help connect:
Application
|
v
Transaction
|
v
Lock
|
v
SQL
12. Finding the SQL
Once the application handle is known, the next objective is to identify what SQL the application is executing or has recently executed.
Depending on the situation, Db2 activity and package-cache monitoring can be used.
The investigation should answer:
Which SQL is running?
How long has it been running?
How many rows are being processed?
Is it waiting?
Is it consuming CPU?
Is it performing a large UPDATE/DELETE?
Is the SQL using the expected access path?This is where locking investigation starts becoming a performance investigation.
13. Locking vs deadlock
These two concepts should not be confused. Lock wait
A holds lock
B waitsEventually A commits/rolls back and B can continue.
Deadlock
A holds Lock 1
A waits for Lock 2
B holds Lock 2
B waits for Lock 1Both transactions are waiting for each other.
+-------------+
| |
v |
Application A --> Application B
^ |
| v
+-------------+Deadlocks require a separate investigation because Db2 has to break the cycle.
I will cover this in detail in Part 3 — DB2 Deadlocks: Investigation Using db2pd and Event Monitoring.
14. When should a DBA terminate the application?
There is no universal rule such as:
"If a transaction runs for 30 minutes, terminate it."
The correct action depends on the workload and business impact.
Before terminating a production application, consider:
1. Is the transaction expected?
2. Is it actively progressing?
3. Is it blocking critical applications?
4. Is the application owner available?
5. What will rollback involve?
6. Could the rollback itself take significant time?
7. Is there an approved incident/change procedure?The database should not be treated as the first place to terminate a process simply because another application is waiting.
15. Example incident
Consider this production scenario:
10:30 AM
Application team reports that ORDER processing is slow.
10:32 AM
DBA runs:
db2pd -db PRODDB -wlocks detail
Result:
Application 450 is waiting.
Application 421 is holding the lock.
10:34 AM
DBA identifies:
Schema = SALES
Table = ORDERS
10:36 AM
MON_GET_CONNECTION identifies Application 421
as a batch application.
10:38 AM
MON_GET_UNIT_OF_WORK shows that the transaction
started at 09:55 AM.
10:40 AM
Application owner confirms that a batch UPDATE
is still running.
10:45 AM
Application team decides whether to allow the
transaction to complete or terminate it based
on business impact.
10:50 AM
Lock is released.
10:51 AM
Waiting application resumes.Notice that the database investigation did not immediately assume that the batch transaction was wrong.
The DBA first established the facts.
16. Production DBA command cheat sheet
Current lock waits
db2pd -db SAMPLE -wlocks detail
Detailed lock investigation
db2pd -db SAMPLE -locks -transactions -applications -dynamic
Current locks
SELECT *
FROM TABLE(
MON_GET_LOCKS(CAST(NULL AS VARCHAR(32)), -2)
)
WITH UR;
Applications waiting for locks
SELECT *
FROM TABLE(
MON_GET_APPL_LOCKWAIT(NULL, -2)
)
WITH UR;
Connection information
SELECT
APPLICATION_HANDLE,
APPLICATION_NAME,
SESSION_AUTH_ID,
CLIENT_APPLNAME,
CLIENT_IPADDR,
UOW_START_TIME
FROM TABLE(
MON_GET_CONNECTION(NULL, -2)
)
WITH UR;
Unit of work
SELECT
APPLICATION_HANDLE,
UOW_ID,
UOW_START_TIME,
UOW_LOG_SPACE_USED
FROM TABLE(
MON_GET_UNIT_OF_WORK(NULL, -2)
)
WITH UR;
17. My production DBA checklist
When I receive a DB2 locking alert, I like to capture the following information before taking action:
[ ] Database name
[ ] Database member
[ ] Incident timestamp
[ ] Waiting application handle
[ ] Blocking application handle
[ ] Lock name
[ ] Lock mode
[ ] Lock status
[ ] Schema
[ ] Table/object
[ ] Application name
[ ] Application user
[ ] Client hostname/IP
[ ] Transaction start time
[ ] Transaction/log usage
[ ] SQL/activity
[ ] Business impact
[ ] Application owner confirmation
[ ] Resolution
[ ] Post-resolution validationThis also creates a useful incident record for future troubleshooting.
18. Key takeaways
The most important lessons from DB2 locking troubleshooting are:
1. Find the waiter
The waiter is the application experiencing the immediate impact.
2. Find the blocker
The blocker is the application preventing the waiter from progressing.
3. Identify the object
Knowing the table/schema often provides the first clue about the business operation involved.
4. Identify the transaction
A lock can remain because a transaction has been open for a long time.
5. Identify the SQL
SQL/activity information can help determine why the transaction is taking time.
6. Do not terminate blindly
Understand the transaction and business impact before taking disruptive action.
7. Document the root cause
A good DBA does not only resolve the current incident.
The goal is to prevent the same incident from happening again.
Conclusion
DB2 locking problems can initially look complicated, but a structured investigation makes them much easier to understand.
The basic relationship is:
WAITer
|
v
LOCK
|
v
BLOCKer
|
v
TRANSACTION
|
v
APPLICATION
|
v
SQLUsing db2pd, MON_GET_LOCKS, MON_GET_APPL_LOCKWAIT, MON_GET_CONNECTION and MON_GET_UNIT_OF_WORK, a DBA can progressively move from the symptom to the underlying transaction and application.
That is the real objective of database troubleshooting:
Don't just clear the lock — understand why it happened.
IBM Documentation
For production work, always validate commands and output against the exact Db2 version and fix pack running in your environment.
IBM Db2 db2pd — monitoring and troubleshooting:
IBM Db2 locking using table functions:
https://www.ibm.com/docs/en/db2/11.5?topic=monitoring-locking-using-table-functions
IBM Db2 MON_GET_APPL_LOCKWAIT:
IBM Db2 MON_GET_CONNECTION:
https://www.ibm.com/docs/en/db2/11.5.x?topic=functions-mon-get-connection-get-connection-metrics
IBM Db2 MON_GET_UNIT_OF_WORK:
https://www.ibm.com/docs/en/db2/11.5.x?topic=mmr-mon-get-unit-work-get-unit-work-metrics
About the Author
Jha Chandan is a Database Engineer with 10+ years of experience in database administration and production database operations, with a primary focus on IBM Db2 LUW, SQL Server, HA/DR, performance troubleshooting, database upgrades, monitoring and automation.
This series shares practical database administration concepts, troubleshooting approaches and lab-based examples from a DBA perspective.
Comments