Chapter 26: Key Takeaways — Locking, Concurrency, and Isolation Levels

The Big Ideas

1. Locking Is the Price of Consistency

Every multi-user database must prevent data anomalies (dirty reads, non-repeatable reads, phantom reads, lost updates). DB2 uses locks as its primary concurrency control mechanism. The fundamental tradeoff is between consistency (more locking) and throughput (less locking).

2. The Four Isolation Levels Are a Spectrum

• UR (Uncommitted Read): No read locks. Fastest, but can see dirty data. Use for dashboards and approximate reporting.
• CS (Cursor Stability): Lock the current row only. Default and best for most OLTP. Prevents dirty reads.
• RS (Read Stability): Lock all qualifying rows until COMMIT. Use when read data must not change during the transaction (e.g., funds transfers).
• RR (Repeatable Read): Lock all examined rows until COMMIT. Use only when phantoms must be prevented (rare).

3. Lock Escalation Is the OLTP Killer

When a transaction accumulates too many fine-grained locks, DB2 escalates to a table lock. This single event can halt all concurrent access to the table. Prevent it by committing frequently, using appropriate isolation levels, and sizing lock memory adequately.

4. Deadlocks Are Prevented by Consistent Lock Ordering

The most effective deadlock prevention technique is ensuring all transactions access tables and rows in the same order. If Transaction A and Transaction B both need rows R1 and R2, both must lock R1 first, then R2.

5. Currently Committed (LUW) and Lock Avoidance (z/OS) Reduce Lock Overhead

These features allow readers to proceed without waiting for writers, dramatically reducing lock contention in mixed workloads without sacrificing consistency.

Essential Commands and Syntax

-- Specify isolation level per statement
SELECT * FROM table WITH UR;
SELECT * FROM table WITH CS;
SELECT * FROM table WITH RS;
SELECT * FROM table WITH RR;

-- Lock a row for update (pessimistic locking)
SELECT * FROM table WHERE key = value FOR UPDATE;

-- Skip locked rows (queue processing pattern)
SELECT * FROM table WHERE status = 'N'
  FOR UPDATE SKIP LOCKED DATA;

-- LUW: Monitor lock activity
SELECT DEADLOCKS, LOCK_TIMEOUTS, LOCK_ESCALS, LOCK_WAITS
FROM TABLE(MON_GET_DATABASE(-2)) AS T;

-- LUW: Find lock waiters and holders
SELECT * FROM TABLE(MON_GET_APPL_LOCKWAIT(NULL, -2)) AS T;

Common Mistakes to Avoid

Decision Framework

Choosing an Isolation Level: 1. Is the data read-only and approximate values are acceptable? Use UR. 2. Is the transaction short (< 100ms) and reads are independent of writes? Use CS. 3. Must the data read remain stable for the duration of the transaction? Use RS. 4. Must the result set be stable (no phantoms) for the duration of the transaction? Use RR.

Choosing Pessimistic vs. Optimistic Locking: 1. Is there user think-time between read and write? Use optimistic. 2. Is the transaction entirely within the database (no external delays)? Use pessimistic. 3. Is contention on the target row expected to be high? Use optimistic (to avoid serializing all accessors). 4. Is absolute correctness critical and retries are unacceptable? Use pessimistic.

Platform Differences

Connection to Meridian Bank Project

The concurrency design for Meridian Bank follows these principles: - Balance inquiries use CS (fast, non-blocking) - Funds transfers use RS with consistent lock ordering (lower account number first) - Batch jobs use CS with COMMIT every 5,000 rows and partition-level processing - Dashboards use UR (never block OLTP) - Month-end reconciliation uses RR only during the final verification pass in the low-traffic window - All application code implements -911 retry logic with exponential backoff