Chapter 8 Key Takeaways

The Big Idea

DB2 locking is not something that happens to your programs — it's something you design. The difference between a system that handles 500 million transactions per day and one that wakes people at 2am is a deliberate, documented locking strategy.

Core Concepts

1. Lock Hierarchy and Modes

• DB2 locks at four levels: tablespace > table > page > row.
• Six lock modes exist: IS, IX, S, U, X, SIX. The compatibility matrix governs which locks can coexist.
• Intent locks (IS, IX) at higher levels signal activity at lower levels.
• The U (Update) lock prevents conversion deadlocks — always use FOR UPDATE OF when a cursor feeds positioned updates.

2. Isolation Levels

• UR (Uncommitted Read): No read locks. Dirty reads possible. Use for approximate reports only.
• CS (Cursor Stability): Read lock held only on current cursor position. The right default for most OLTP.
• RS (Read Stability): Read locks held on qualifying rows until COMMIT. Use when you must re-read and get the same result.
• RR (Repeatable Read): Read locks held on ALL accessed rows (qualifying or not) until COMMIT. Use only for regulatory/audit scenarios.
• Currently committed (DB2 12+): Readers under CS can see last committed value instead of waiting on writers. Enable it for read-heavy workloads.

3. Lock Escalation — The Threshold Concept

• When page/row lock count exceeds LOCKMAX, DB2 replaces all individual locks with a single tablespace/table lock.
• Escalation turns a concurrent system into a serial bottleneck.
• Prevention: COMMIT before reaching LOCKMAX. If LOCKMAX is 5,000, commit every 2,000–3,000 rows.
• If you see lock escalation in production, treat it as a design defect, not a tuning opportunity.

4. Deadlock Prevention

• Access resources in the same order in every program — the single most effective prevention technique.
• Minimize lock duration — acquire late, commit early, don't hold locks during non-DB2 work.
• Use the minimum necessary isolation level — each step up holds more locks longer.
• Handle -911 and -913 in every program — retry with full re-read of data (the entire UOW was rolled back).

5. Concurrent Online and Batch

• The batch window is dead. Design batch programs to coexist with online.
• Batch programs must: commit frequently, use WITH HOLD cursors, process in primary key order.
• Partition-level processing is the gold standard: lock one partition at a time, schedule high-contention partitions during low-activity windows.

6. Lock Avoidance and Optimistic Concurrency

• DB2's lock avoidance (claim/drain) automatically skips locks when no contention exists. Target 90%+ avoidance rate.
• Optimistic concurrency eliminates lock-holding during user think time: read without lock, save version, verify version before update.
• Use a timestamp or version column for conflict detection.

Critical Rules

Production Survival Checklist

Before any COBOL/DB2 program goes to production, verify:

• [ ] Isolation level is explicitly specified (plan-level or statement-level)
• [ ] All updateable cursors use FOR UPDATE OF
• [ ] Batch programs commit every N rows (N documented, N < LOCKMAX)
• [ ] Batch cursors use WITH HOLD
• [ ] SQLCODE -911 and -913 are handled with proper retry logic
• [ ] Retry logic re-reads data after rollback (doesn't just re-execute the failed statement)
• [ ] Table access order is documented and consistent with other programs
• [ ] Row access order within a table is consistent (primary key order preferred)
• [ ] Non-DB2 work (MQ, file I/O, network) is outside the lock window where possible
• [ ] LOCKMAX is known and commit frequency ensures it's never reached

Key Diagnostic Tools

One Sentence to Remember

If you don't design your locking strategy, DB2 will choose one for you — and you won't like its choice.