Identify table locks and blocking transactions in DB2

Is the table locked, or is something waiting?

A save hangs. An update won't finish. A database change sits there doing apparently nothing.

"The table is locked" is a reasonable suspicion, but it leaves two questions: which lock, and who is waiting for it?

Use MON_GET_LOCKS to inspect locks, including locks with no current waiter. Use SYSIBMADM.MON_LOCKWAITS to identify current blocking relationships. A table appearing in a lock list doesn't automatically mean the whole table is unavailable.

These queries target DB2 for Linux, UNIX and Windows (LUW). Connect to the affected database first. MAXIMO.WORKORDER is an example: replace both names with your actual schema and table, using the case stored in the catalog.

If you're starting with a slow statement rather than a known table, begin with Find long-running SQL with MON_CURRENT_SQL.

List table-level locks on a specific table

SELECT
    application_handle,
    member,
    lock_object_type,
    lock_mode,
    lock_current_mode,
    lock_status,
    lock_name
FROM TABLE(
    SYSPROC.MON_GET_LOCKS(
        CLOB('<table_schema>MAXIMO</table_schema>
              <table_name>WORKORDER</table_name>
              <lock_object_type>Table</lock_object_type>'),
        -2
    )
) AS locks
ORDER BY application_handle, member;

The search argument restricts collection to this table and object type. -2 requests all active members; -1 requests the current member. Both schema and table keys are required when filtering by table. IBM documents the parameters and output in MON_GET_LOCKS.

These are live observations. A lock can disappear between this query and the next one.

An IX table lock isn't an exclusive lock on every row

Intent locks coordinate locking at different levels. An IX table lock can accompany exclusive locks on individual rows while other transactions work on different rows.

Common modes you'll encounter include:

Compatibility depends on the requested mode and resource. An X row lock and an X table lock have very different scope. IBM's lock attributes describe the modes and compatibility rules.

To inspect row locks as well, change the object-type search value to:

<lock_object_type>Table:Row</lock_object_type>

To include all resource types associated with the table, omit that key. This can produce much more output on a busy table. For partitioned or specialised tables, restricting the search to Table alone can miss relevant finer-grained locks.

Find the actual blocking relationship

Start without a table filter when the affected resource is uncertain:

SELECT
    req_application_handle AS waiting_handle,
    req_member AS waiting_member,
    hld_application_handle AS holding_handle,
    hld_member AS holding_member,
    lock_wait_elapsed_time AS wait_seconds,
    tabschema,
    tabname,
    lock_object_type,
    lock_mode AS holder_mode,
    lock_mode_requested AS requested_mode,
    SUBSTR(req_stmt_text, 1, 2000) AS waiting_sql,
    SUBSTR(hld_current_stmt_text, 1, 2000) AS holder_current_sql
FROM sysibmadm.mon_lockwaits
ORDER BY lock_wait_elapsed_time DESC
FETCH FIRST 50 ROWS ONLY;

The REQ_ columns describe the waiter; HLD_ describes the holder. Wait time is in seconds. Table names can be null for resources that don't reference a table. See IBM's MON_LOCKWAITS reference.

To focus on the example table, add this before ORDER BY:

WHERE tabschema = 'MAXIMO'
  AND tabname = 'WORKORDER'

That finds waits associated with the table, including row-level waits. Add AND UPPER(lock_object_type) = 'TABLE' only when you specifically want waits on table-level resources.

An empty result means no matching wait was observed at capture time. It doesn't mean the table has no locks, or that a wait didn't occur earlier. Conversely, a granted lock without a waiter isn't evidence of an ongoing blocking incident.

Trace the holding connection

Suppose the wait list identifies holding handle 12345. Inspect its connection:

SELECT
    application_handle,
    member,
    application_name,
    session_auth_id,
    client_applname,
    client_wrkstnname
FROM TABLE(
    SYSPROC.MON_GET_CONNECTION(CAST(12345 AS BIGINT), -2)
) AS connection_info;

Replace the example handle with the observed value. IBM's MON_GET_CONNECTION reference documents these connection fields. Application-supplied client details may be empty or generic; a shared Maximo JDBC login doesn't identify a particular Maximo user.

Then inspect its unfinished SQL activities, without filtering to EXECUTING:

SELECT
    coord_member,
    application_handle,
    uow_id,
    activity_id,
    elapsed_time_sec,
    activity_state,
    SUBSTR(stmt_text, 1, 2000) AS stmt_text
FROM sysibmadm.mon_current_sql
WHERE application_handle = 12345
ORDER BY elapsed_time_sec DESC NULLS LAST;

The long-running SQL guide explains those fields. A lock holder may have no unfinished SQL activity while its transaction remains open. The SQL currently visible for the holder may also differ from the earlier statement that acquired the lock.

If the holder is itself waiting, follow its handle through the wait list. The transaction causing the wider queue may be further up that chain.

Why a table lock might be there

Table locks can arise from explicit LOCK TABLE statements, isolation and access-plan choices, a table's LOCKSIZE setting, or lock escalation. Escalation replaces finer-grained locks with a broader table lock to reduce lock-list usage, potentially reducing concurrency. IBM discusses these causes in Locks and concurrency control.

An X table entry alone doesn't establish escalation. Check the transaction, workload and monitoring evidence before changing LOCKLIST, MAXLOCKS or isolation settings.

For recurring problems, investigate transaction size and duration, missing commit or rollback paths, and SQL that accesses more data than necessary. Any change to commit boundaries must preserve the application's transaction semantics.

Resolve the transaction, then check again

Capture the time, database, table, handles, members, modes and SQL before intervening. Correlate them with the affected application's logs.

Have the application owner complete or roll back the responsible transaction through the appropriate workflow. A COMMIT in your monitoring connection won't commit someone else's work. Forcing a connection off is disruptive and can trigger a lengthy rollback; use the incident procedure rather than disconnecting whichever handle appears first.

Re-run the wait query and confirm that the application operation progresses. A disappearing wait alone doesn't tell you whether it completed successfully, timed out or was cancelled.

These interfaces require monitoring privileges: appropriate SELECT access for the administrative views and EXECUTE access for the table functions, or a documented authority that permits them. Live polling won't recover an incident that has already ended; recurring or intermittent waits need a suitable monitoring or locking-event capture.