Long-running unit of work holds locks

Locking problems — contention, escalation, deadlocks — come from units of work that are too long or access patterns that collide.

What it means

• DB2 locks rows/pages to protect concurrent updates.
• Long units of work hold locks longer, raising collision odds.

Common causes

• Infrequent COMMITs in batch updates.
• Lock escalation from row to table level.
• Programs accessing the same tables in different order.

How to diagnose

• -DISPLAY THREAD(*) LOCK(*) shows current lock holders.
• Check commit frequency in the program.
• Look for escalation messages.

How to fix it

• Commit every N rows (e.g. 1000) in batch.
• Access tables in a consistent order across programs.
• Consider row-level locking or tuning MAXROWS.

Example: Commit periodically in batch loops

Periodic COMMIT shrinks the lock window dramatically.

Interview tip

• Q: How would you troubleshoot a Long-running unit of work holds locks in production?
• A: I would display lock holders, review commit frequency, and standardize table access order across the conflicting jobs.

Prevention checklist

• Commit frequently in batch.
• Consistent access order everywhere.
• Monitor for escalation.