Chapter 26: Exercises — Locking, Concurrency, and Isolation Levels

Conceptual Understanding (Exercises 1-10)

Exercise 1: Identifying Concurrency Anomalies

For each of the following scenarios, identify which concurrency anomaly (dirty read, non-repeatable read, phantom read, or lost update) has occurred:

a) Transaction A reads an account balance of $5,000. Transaction B deposits $1,000 and commits. Transaction A reads the same account again and sees $6,000.

b) Transaction A reads all accounts with a balance over $100,000 and counts 42. Transaction B opens a new account with $150,000 and commits. Transaction A re-runs the same query and counts 43.

c) Transaction A reads an account balance of $5,000 (uncommitted insert by Transaction B). Transaction B rolls back. Transaction A has acted on a balance that never existed.

d) Transaction A reads a balance of $1,000 and adds $200. Transaction B also reads the balance of $1,000 and adds $300. Transaction A writes $1,200. Transaction B writes $1,300. The $200 deposit is lost.

Exercise 2: Lock Compatibility

Given the lock compatibility matrix, determine whether each of the following scenarios will result in Transaction B waiting or proceeding immediately:

a) Transaction A holds an S lock on row R1. Transaction B requests an S lock on R1. b) Transaction A holds an X lock on row R1. Transaction B requests an S lock on R1. c) Transaction A holds an IS lock on table T. Transaction B requests an IX lock on T. d) Transaction A holds a SIX lock on table T. Transaction B requests an IS lock on T. e) Transaction A holds a U lock on row R1. Transaction B requests an S lock on R1. f) Transaction A holds a U lock on row R1. Transaction B requests a U lock on R1.

Exercise 3: Lock Duration by Isolation Level

For each isolation level (UR, CS, RS, RR), describe when row-level read locks are acquired and when they are released. Create a table with columns: Isolation Level, Lock Acquired?, When Released.

Exercise 4: Choosing Isolation Levels

For each of the following Meridian Bank transactions, recommend the most appropriate isolation level and justify your choice:

a) A customer views their recent transaction history on a mobile app. b) A teller processes a withdrawal, which requires reading the current balance, validating sufficient funds, and debiting the account. c) An auditor generates a report that must show a consistent point-in-time snapshot of all account balances across all branches. d) An internal dashboard displays the total number of active accounts (approximate count is acceptable). e) A batch process generates monthly statements by reading each account's transaction history.

Exercise 5: Escalation Calculation

On LUW, the LOCKLIST is set to 4096 (4KB pages) and MAXLOCKS is set to 22%. Calculate:

a) The total lock memory in bytes. b) The maximum number of bytes a single application can use before triggering escalation. c) If each lock consumes approximately 72 bytes, approximately how many row locks can a single application hold before escalation?

Exercise 6: Why the U Lock Exists

Explain, using a step-by-step timeline of two concurrent transactions, why the U (Update) lock mode exists. Show: a) The deadlock that occurs when both transactions use S locks followed by X locks. b) How U locks prevent this deadlock.

Exercise 7: Currently Committed vs. Traditional CS

Explain the difference between traditional CS behavior and CS with Currently Committed semantics (LUW). For the following scenario, describe what Transaction B sees under each behavior:

• Transaction A updates account 1001 balance from $10,000 to $8,000 but has not committed.
• Transaction B reads account 1001 under CS isolation.

Exercise 8: SKIP LOCKED DATA Use Cases

List three real-world use cases (beyond the queue processing example in the chapter) where SKIP LOCKED DATA would be beneficial. For each, explain why traditional locking would cause problems.

Exercise 9: Deadlock vs. Timeout

Explain the difference between a deadlock and a lock timeout. For each: a) What causes it? b) What SQLCODE and reason code does the application receive? c) How does DB2 resolve it? d) What should the application do in response?

Exercise 10: Lock Avoidance

Explain how lock avoidance works on z/OS. Under what conditions can DB2 skip acquiring a lock? What information does DB2 use to make this determination?

Hands-On Practice (Exercises 11-20)

Exercise 11: Demonstrating Isolation Level Behavior

Using two concurrent database sessions, demonstrate each of the following. Record the exact SQL statements and the results at each step:

a) A dirty read under UR isolation. b) A non-repeatable read under CS isolation. c) RS isolation preventing a non-repeatable read. d) A phantom read under RS isolation.

Exercise 12: Creating and Resolving a Deadlock

Design and execute a deadlock scenario using two sessions:

a) Create two rows in a test table. b) In Session A, lock row 1. c) In Session B, lock row 2. d) In Session A, attempt to lock row 2 (will wait). e) In Session B, attempt to lock row 1 (should cause deadlock). f) Observe which session is chosen as the victim. g) Implement retry logic for the victim session.

Exercise 13: Monitoring Lock Escalation

a) Create a table with 100,000 rows. b) Determine the current LOCKLIST and MAXLOCKS settings (LUW) or NUMLKTS (z/OS). c) Execute a query under RS isolation that scans a large portion of the table. d) Monitor whether lock escalation occurs using the appropriate MON_GET function. e) If escalation occurs, increase the lock memory and verify that escalation no longer occurs.

Exercise 14: SKIP LOCKED DATA Implementation

Implement a work queue system:

a) Create a work queue table with 20 items. b) Write a "worker" query that picks up the next available item using SKIP LOCKED DATA. c) Open two concurrent sessions simulating two workers. d) Verify that each worker gets a different item without waiting. e) Process all 20 items using both workers and verify none were missed or duplicated.

Exercise 15: Optimistic Locking Implementation

a) Create a table with a version_num column. b) In Session A, read a row (including the version number). c) In Session B, read the same row and update it successfully (incrementing the version number). d) In Session A, attempt to update the row using the stale version number. e) Verify that Session A's update affects 0 rows. f) Implement retry logic: re-read and retry the update.

Exercise 16: Lock Wait Monitoring

a) Create a scenario where Session B is waiting for Session A's lock. b) While Session B is waiting, use the MON_GET_APPL_LOCKWAIT function (LUW) or DISPLAY DATABASE LOCKS (z/OS) to identify the holder and waiter. c) Record the lock type, lock mode, table name, and wait duration. d) Release Session A's lock and verify Session B proceeds.

Exercise 17: Comparing COMMIT Frequencies

a) Create a table with 50,000 rows. b) Write a batch update that processes all rows, committing every 100 rows. Measure total elapsed time and peak lock count. c) Write the same batch update committing every 10,000 rows. Measure total elapsed time and peak lock count. d) Write the same batch update with a single COMMIT at the end. Measure total elapsed time and peak lock count. e) Compare the results. Which approach would you recommend for a production batch job and why?

Exercise 18: FOR UPDATE vs. Regular SELECT

a) In Session A, execute SELECT ... FOR UPDATE on a row. b) In Session B, attempt to read the same row under CS, RS, and UR isolation. c) Record which reads succeed immediately and which wait. d) In Session B, attempt to update the same row. What happens? e) Explain the lock modes involved at each step.

Exercise 19: Measuring Lock Impact on Throughput

a) Create a small test table with 10 rows. b) Write a script that executes 1,000 SELECT + UPDATE pairs against a single row under CS isolation. Measure throughput (transactions per second). c) Run the same script against the same row from two concurrent sessions. Measure throughput. d) Run the same script from three concurrent sessions. Measure throughput. e) Graph the results. At what point does concurrency stop improving throughput?

Exercise 20: Lock Configuration Tuning

a) Set LOCKTIMEOUT to 5 seconds (LUW). b) Create a scenario where a lock wait exceeds 5 seconds. c) Verify that the waiter receives SQLCODE -911 with reason code 68. d) Set LOCKTIMEOUT to 60 seconds and repeat. Does the waiter now succeed? e) Reset LOCKTIMEOUT to 30 seconds (a reasonable production default).

Scenario-Based Problems (Exercises 21-30)

Exercise 21: The Nightly Batch Problem

Meridian Bank's nightly interest calculation job runs from 11 PM to 2 AM. During this window, online banking customers report slow balance inquiries. The batch job uses RS isolation and commits only at the end.

a) Explain why the batch job is causing slow balance inquiries. b) Propose three different solutions, ranked by effectiveness. c) Implement your preferred solution and explain how you would verify the improvement.

Exercise 22: The Deadlock Pattern

A new feature at Meridian Bank allows customers to transfer funds between their own accounts. Since launch, deadlock rates have increased from 2/hour to 30/hour. Investigation shows that most deadlocks involve the ACCOUNTS table and occur between concurrent transfer transactions.

a) Explain the likely deadlock pattern. b) Design a lock ordering strategy that prevents this deadlock. c) Write the corrected funds transfer SQL.

Exercise 23: Lock Escalation Crisis

During month-end processing, the reconciliation batch triggers lock escalation on the TRANSACTIONS table. This locks out all OLTP transactions that need to insert new transaction records.

a) What lock mode does the escalated table lock acquire? b) Why does this block OLTP inserts? c) Propose a solution that allows reconciliation to run without blocking OLTP.

Exercise 24: The Phantom Problem

An audit report counts the number of accounts at each branch and verifies the total against a control table. Occasionally, the audit report shows a mismatch because new accounts are opened between the time the report reads the accounts table and the time it reads the control table.

a) Which concurrency anomaly is this? b) Which isolation level would prevent it? c) What is the performance cost of using this isolation level? d) Is there an alternative approach that avoids the high isolation level?

Exercise 25: Optimistic vs. Pessimistic for Web Application

Meridian Bank is building a web application where customers can update their mailing address. The workflow is: display current address, customer edits the address, customer clicks save (potentially minutes later).

a) Why is pessimistic locking (FOR UPDATE) inappropriate for this use case? b) Design an optimistic locking strategy using a version number. c) Write the SQL for the read, the update, and the conflict detection logic. d) What should the application do when a conflict is detected?

Exercise 26: Queue Processing Design

Design a work distribution system for Meridian Bank's check processing: - 500,000 checks arrive daily in a queue table - 10 worker threads process checks concurrently - Each check must be processed exactly once - Workers must not block each other

a) Design the queue table schema. b) Write the SQL for a worker to pick up the next check. c) Write the SQL for a worker to mark a check as processed. d) How do you handle a worker that crashes mid-processing?

Exercise 27: Isolation Level Audit

You are asked to audit all application programs at Meridian Bank and verify that each uses an appropriate isolation level. For each of the following programs, determine if the current isolation level is appropriate and recommend changes:

a) ACCT_INQUIRY: Uses RR isolation. Reads a single account row for display. b) WIRE_TRANSFER: Uses CS isolation. Reads two account balances, validates, and updates both. c) DAILY_REPORT: Uses RS isolation. Reads millions of rows for a summary report. d) ONLINE_STMT: Uses UR isolation. Reads transaction history for display to the customer.

Exercise 28: Lock Wait Investigation

You receive an alert that 50 transactions are currently waiting for locks. Write the diagnostic queries (for your platform) to answer:

a) Which table is the most contended? b) Which application holds the blocking lock? c) How long has the blocking transaction been running? d) What SQL statement is the blocking transaction executing? e) What is the appropriate resolution?

Exercise 29: Batch Window Optimization

Meridian Bank's batch window is shrinking as transaction volume grows. The current batch schedule is: - 11:00 PM: REORG ACCOUNTS table (45 min) - 11:45 PM: RUNSTATS on ACCOUNTS (15 min) - 12:00 AM: Interest calculation (3 hours) - 3:00 AM: Statement generation (2 hours) - 5:00 AM: Batch window ends

The interest calculation now takes 4 hours, pushing statement generation past the 5 AM deadline.

a) Identify which operations can be overlapped (run concurrently). b) What locking conflicts prevent overlapping certain operations? c) Redesign the batch schedule to fit within the window. d) What concurrency settings enable your redesigned schedule?

Exercise 30: Comprehensive Concurrency Design

You are designing the concurrency strategy for a new Meridian Bank product: instant peer-to-peer payments. Requirements: - 10,000 payments per hour at peak - Each payment debits one account and credits another - Payments must be atomic (both debit and credit succeed, or neither) - Maximum response time: 200ms at the 99th percentile - Zero tolerance for lost updates

Design the complete concurrency strategy: a) Isolation level for the payment transaction. b) Lock ordering strategy. c) Deadlock prevention or handling approach. d) Optimistic vs. pessimistic approach and justification. e) COMMIT strategy. f) Monitoring and alerting approach.

Challenge Problems (Exercises 31-35)

Exercise 31: Multi-Table Deadlock Analysis

Create a three-transaction deadlock involving tables CUSTOMERS, ACCOUNTS, and TRANSACTIONS. Show: a) The timeline of lock acquisitions. b) The circular wait graph. c) Which transaction DB2 would select as the victim and why. d) The lock ordering strategy that prevents this deadlock.

Exercise 32: Lock Escalation Threshold Calculation

On z/OS with NUMLKTS=2000, a transaction processes 50,000 rows from a partitioned tablespace with 10 partitions, reading 5,000 rows per partition.

a) Will lock escalation occur? At what point? b) If the transaction uses CS isolation, does the answer change? c) If the transaction uses RS isolation, does the answer change? d) Design a processing strategy that avoids escalation regardless of isolation level.

Exercise 33: Currently Committed Deep Dive

On LUW with CUR_COMMIT ON, analyze the following scenario:

Transaction A begins at T1 and updates a row (balance from $1,000 to $1,500) but does not commit. Transaction B reads the row at T2 under CS isolation.

a) What value does Transaction B see? b) Where does DB2 get this value? c) If Transaction A updates the row again (to $2,000) at T3, and Transaction C reads at T4, what does Transaction C see? d) When Transaction A commits at T5, what do subsequent readers see? e) What happens if the log records needed for the "currently committed" value have been overwritten?

Exercise 34: SKIP LOCKED DATA with Aggregates

Consider this query:

SELECT COUNT(*) FROM work_queue WHERE status = 'N' SKIP LOCKED DATA;

a) If there are 100 items with status='N' and 5 are currently locked by workers, what does COUNT return? b) Is this the "correct" count? Discuss. c) How would you get an accurate count of all new items including those being processed? d) Design a monitoring query that distinguishes between "new and available" vs. "new but being processed."

Exercise 35: Cross-Platform Concurrency Design

You are migrating Meridian Bank's OLTP workload from z/OS to LUW. On z/OS, the application uses: - LOCKSIZE PAGE on the tablespace - NUMLKTS = 1000 - Plan-level isolation of CS - IRLMRWT = 20

Design the equivalent LUW configuration: a) What replaces LOCKSIZE PAGE? b) What are the equivalent LOCKLIST and MAXLOCKS settings? c) How is isolation level specified differently? d) What replaces IRLMRWT? e) What LUW feature (not available on z/OS) can further reduce lock contention?