How to Diagnose and Resolve DB2 Lock Timeouts in Production

Introduction

DB2 lock management often causes lock‑timeouts and deadlocks when misunderstood. Capturing and analyzing these events is essential for DB2 DBAs.

Database Environment

The production system runs DB2 pureScale V10.5 FP5.

Problem Background

Every weekday between 15:40 and 15:50 the database reports lock‑timeout errors, coinciding with a batch‑processing window.

DB2 Lock‑Timeout Configuration

To capture the requestor and owner of a timed‑out lock, enable the following settings:

Enable timeout capture variable : db2set DB2_CAPUTRE_LOCKTIMEOUT=ON When a timeout occurs DB2 creates a file such as

~/sqllib/db2dump/DIAG0000/db2locktimeout.1.5493.2017-05-25-15-49-01

.

Enable the db2cos script located at ~/sqllib/bin/db2cos to collect richer application information.

After modifying the script, activate it with: db2pdcfg -catch 911,68 count=255 When a timeout occurs db2cos generates files named $pid.$eduid.$dbpart.db2pd.${database}.txt, e.g. 202484.5493.001.db2pd.PTSMGMDB.txt.

Analyzing the Lock‑Timeout Report

The db2locktimeout file provides:

Lock requestor and owner identifiers

Timestamp and lock type (Table lock)

Resource ID ( obj={4;136})

Using the tablespace ID, the locked table can be identified with a query such as:

SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TBSPACEID = 136;

The requestor process is db2bp (a CLP backend). The exact SQL statement is not in the timeout file but appears in the corresponding db2pd file.

The lock owner is a package named PTSDAYOFFPUB version V1.1.0_P25. Two statements (IDs 11 and 12) were executed; statement 11 accessed table T_BASE_QWDZLSB with isolation level CS, acquiring an IS lock.

Examining 202484.5493.001.db2pd.PTSMGMDB.txt shows:

Transaction handle 287 status G (holds lock)

Transaction handle 272 status W* (waiting)

Handle 272 maps to application handle 111774

The dynamic SQL section reveals the waiting statement:

LOCK TABLE t_base_qwdzlsb IN EXCLUSIVE MODE

Root Cause

The package PTSDAYOFFPUB executed SELECT * FROM T_BASE_QWDZLSB, acquiring an IS lock (CS isolation). Shortly after, an IMPORT utility issued LOCK TABLE t_base_qwdzlsb IN EXCLUSIVE MODE, requesting an X lock. IS and X locks are incompatible; the X lock waited beyond the 30‑second timeout, causing the lock‑timeout error.

Remediation

Rewrite the package cursor to enforce read‑only semantics and avoid CS isolation: SELECT * FROM T_BASE_QWDZLSB FOR READ ONLY WITH UR Adding WITH UR alone does not help because DB2 treats ambiguous cursors as updatable, which defaults to CS isolation.

Modify the IMPORT command to use a less restrictive lock mode. By default IMPORT uses ALLOW NO ACCESS, which blocks concurrent access. Use ALLOW WRITE ACCESS and adjust the commit count, for example:

IMPORT FROM xx.ixf OF ixf ALLOW WRITE ACCESS COMMITCOUNT 5000 INSERT INTO T_BASE_QWDZLSB

This permits other sessions to read the table while the import runs.

Key Skills

Capturing DB2 lock‑timeout diagnostics (db2set, db2locktimeout, db2cos)

Understanding DB2 isolation levels (UR, CS) and their effect on lock types

Interpreting DB2 lock reports (db2pd output, transaction handles)

Configuring IMPORT utility lock mode and commit behavior

References

IBM Knowledge Center – Lock timeout documentation: https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0004121.html

《循序渐进DB2-DBA系统管理、运维与应用案例》— 第6.3节 IMPORT

《运筹帷幄DB2——从Oracle运维转型》— 第五章 锁处理与并发控制