Chapter 8 Exercises: DB2 Locking, Concurrency, and Deadlock Resolution

Section 8.2 Exercises — Lock Hierarchy and Lock Modes

Exercise 1: Lock Compatibility (Recall)

Without looking at the compatibility matrix, determine whether the following lock requests will be granted or must wait:

a) Program A holds IS on a tablespace. Program B requests IX on the same tablespace. b) Program A holds S on a page. Program B requests X on the same page. c) Program A holds U on a row. Program B requests S on the same row. d) Program A holds IX on a table. Program B requests S on the same table. e) Program A holds SIX on a tablespace. Program B requests IS on the same tablespace.

Exercise 2: Lock Trace (Apply)

A DB2 trace shows the following lock state for page X of tablespace DBACCT.TSACCT:

Holder 1: PLAN=CNBINQ,  MODE=IS (tablespace), S (page)
Holder 2: PLAN=CNBUPD,  MODE=IX (tablespace), U (page)
Waiter 1: PLAN=CNBBATCH, MODE=IX (tablespace), X (page) — WAITING

a) Why is CNBBATCH waiting? b) If CNBINQ commits, will CNBBATCH's request be granted? Explain. c) What would happen if CNBUPD converts its U lock to X before CNBINQ commits?

Exercise 3: Intent Lock Escalation Chain (Analyze)

A COBOL program executes the following sequence without committing:

UPDATE TABLE1 SET COL1 = 'A' WHERE KEY = 100
UPDATE TABLE1 SET COL1 = 'B' WHERE KEY = 200
SELECT COL1 INTO :WS-VAL FROM TABLE2 WHERE KEY = 300
UPDATE TABLE2 SET COL1 = 'C' WHERE KEY = 300

TABLE1 and TABLE2 are in different tablespaces. LOCKSIZE is ROW for both. List every lock held after all four statements execute (include intent locks at tablespace and table levels).

Exercise 4: The Missing FOR UPDATE OF (Debug)

The following code produces intermittent -911 deadlocks in production:

       EXEC SQL
           DECLARE CSR1 CURSOR FOR
           SELECT ACCT_BAL
           FROM ACCT_MASTER
           WHERE BRANCH_ID = :WS-BRANCH
       END-EXEC

       EXEC SQL OPEN CSR1 END-EXEC
       PERFORM UNTIL SQLCODE = 100
           EXEC SQL FETCH CSR1 INTO :WS-BAL END-EXEC
           IF SQLCODE = 0
               COMPUTE WS-NEW-BAL = WS-BAL * 1.05
               EXEC SQL
                   UPDATE ACCT_MASTER
                   SET ACCT_BAL = :WS-NEW-BAL
                   WHERE CURRENT OF CSR1
               END-EXEC
           END-IF
       END-PERFORM
       EXEC SQL COMMIT END-EXEC

a) Identify the locking defect. b) Explain the exact deadlock scenario (which lock modes, which programs, which resources). c) Write the corrected cursor declaration.

Exercise 5: Lock Mode Hierarchy Diagram (Understand)

Draw a diagram showing the lock hierarchy for a single UPDATE statement against a row in a segmented tablespace. Show all locks acquired at every level (tablespace, table, page/row). Indicate the lock mode at each level.

Section 8.3 Exercises — Isolation Levels

Exercise 6: Isolation Level Selection (Evaluate)

For each scenario, recommend the appropriate isolation level and justify your choice:

a) A CICS transaction that displays an account balance to a customer. b) A batch program that calculates the total assets under management for a regulatory report filed with the SEC. c) A batch program that generates a daily summary email showing approximate transaction counts per branch. d) A CICS transaction that checks whether an account exists before creating a new linked account. e) A batch program that reads customer addresses to generate mailing labels.

Exercise 7: Phantom Reads (Analyze)

Program A runs:

SELECT COUNT(*) FROM ORDERS WHERE STATUS = 'PENDING'

Gets result: 42.

Program B then runs:

INSERT INTO ORDERS (ORDER_ID, STATUS) VALUES (99999, 'PENDING')
COMMIT

Program A runs the same SELECT again within the same unit of work.

a) What result does Program A get under CS isolation? b) What result does Program A get under RS isolation? c) What result does Program A get under RR isolation? d) Explain the mechanism that prevents the phantom in case (c).

Exercise 8: Isolation Level Lock Count (Calculate)

A query scans 5,000 pages. Of those, 200 pages contain rows that qualify for the result set. Calculate the number of page-level read locks held at the end of the query (before COMMIT) for each isolation level:

a) UR b) CS (cursor positioned on last qualifying page) c) RS d) RR

Exercise 9: Currently Committed Behavior (Analyze)

Program A updates a row in ACCT_MASTER (changing balance from $1,000 to $1,500) but has not yet committed. Program B reads the same row under CS with currently committed enabled.

a) What value does Program B see? Why? b) What value would Program B see without currently committed? c) Is the value Program B sees "correct"? Under what definition?

Exercise 10: Mixed Isolation Strategy (Design)

A COBOL batch program does the following: 1. Reads control parameters from a CONFIG table (read once at start) 2. Reads accounts from ACCT_MASTER one at a time 3. For each account, reads all transactions from TXN_HISTORY 4. Calculates interest based on transactions 5. Updates the account balance in ACCT_MASTER 6. Inserts an interest transaction into TXN_HISTORY

Design a mixed-isolation strategy using statement-level overrides. Specify the isolation level for each SQL statement and justify each choice.

Section 8.4 Exercises — Lock Escalation

Exercise 11: Escalation Threshold Calculation (Calculate)

A tablespace has LOCKSIZE ROW and LOCKMAX 5000. The table contains 50 rows per page. A batch program processes rows sequentially, updating each one, committing every N rows.

a) What is the minimum commit frequency to guarantee no escalation? (Assume all locks are held until COMMIT.) b) If the commit frequency is set to 3000, what percentage of LOCKMAX is used at peak? c) If LOCKSIZE is changed to PAGE, and the program updates rows that are spread across every page (worst case), what commit frequency prevents escalation?

Exercise 12: Escalation Impact Analysis (Analyze)

At 2:15pm, the DB2 statistics show the following for tablespace DBACCT.TSACCT:

LOCK ESCALATIONS: 3 (up from 0 at 2:00pm)
LOCK REQUESTS: 1,450,000
LOCK SUSPENSIONS: 12,500 (up from 400 at 2:00pm)
LOCK TIMEOUTS: 85 (up from 0 at 2:00pm)

a) What likely happened at approximately 2:00pm? b) Explain the causal chain from lock escalation to lock timeouts. c) What three pieces of information would you check first? d) Write the SQL to check the LOCKMAX setting for this tablespace.

Exercise 13: Batch Commit Design (Design)

You must write a batch program that updates all 2,000,000 rows in a table. The tablespace has LOCKSIZE ROW and LOCKMAX 10,000. The program runs while online is active. Design the commit strategy including:

a) Commit frequency b) Cursor type (WITH HOLD or not) c) Restart logic (how to resume after an abend) d) Estimated number of commits for the full run

Exercise 14: LOCKSIZE Trade-off Analysis (Evaluate)

A table has 80 rows per page. It's currently defined as LOCKSIZE PAGE. The DBA is considering changing to LOCKSIZE ROW. Analyze the trade-offs:

a) How does the maximum number of concurrent users who can update different rows on the same page change? b) How does the IRLM storage requirement change (assume the table has 100,000 pages)? c) Under what workload pattern is ROW locking clearly better? When is PAGE clearly better?

Section 8.5 Exercises — Deadlocks

Exercise 15: Deadlock Scenario Construction (Apply)

Construct a deadlock scenario involving three programs (A, B, C) and three resources (R1, R2, R3). Show the sequence of lock acquisitions that leads to a three-way deadlock. Provide a timeline showing each step.

Exercise 16: Deadlock Prevention Redesign (Design)

Two COBOL programs access the same three tables:

Program PGMA:

1. UPDATE CUSTOMER SET ... WHERE CUST_ID = :ID
2. INSERT INTO AUDIT_LOG VALUES (...)
3. UPDATE ACCT_MASTER SET ... WHERE ACCT_ID = :AID
4. COMMIT

Program PGMB:

1. UPDATE ACCT_MASTER SET ... WHERE ACCT_ID = :AID
2. UPDATE CUSTOMER SET ... WHERE CUST_ID = :ID
3. INSERT INTO AUDIT_LOG VALUES (...)
4. COMMIT

a) Show how these two programs can deadlock. b) Redesign both programs to eliminate the deadlock possibility. You may change the order of operations but not the operations themselves. c) If changing the order is not possible due to business logic, what alternative strategy would you use?

Exercise 17: IFCID 0172 Analysis (Analyze)

Given the following simplified IFCID 0172 output:

DEADLOCK DETECTED AT 14:23:17
CONNECTION 1: PLAN=ONLTXN, CORRID=USERA
  HOLDS: X LOCK ON DBACCT.TSACCT PAGE 0x00001A2F
  WAITS: X LOCK ON DBACCT.TSACCT PAGE 0x00003B71
CONNECTION 2: PLAN=BATCHUP, CORRID=BATCH01
  HOLDS: X LOCK ON DBACCT.TSACCT PAGE 0x00003B71
  WAITS: X LOCK ON DBACCT.TSACCT PAGE 0x00001A2F
VICTIM: CONNECTION 1 (PLAN=ONLTXN)

a) What type of deadlock is this (inter-table or intra-table)? b) Both connections hold X locks. What does this tell you about the operations they were performing? c) Why might DB2 have chosen ONLTXN as the victim instead of BATCHUP? d) Propose two different solutions to prevent this deadlock.

Exercise 18: Retry Logic Review (Debug)

Find the bug in this deadlock retry logic:

       PERFORM UPDATE-ACCOUNT
       IF SQLCODE = -911
           PERFORM UPDATE-ACCOUNT
           IF SQLCODE = -911
               PERFORM UPDATE-ACCOUNT
               IF SQLCODE = -911
                   PERFORM ABEND-PROGRAM
               END-IF
           END-IF
       END-IF

       UPDATE-ACCOUNT.
           EXEC SQL
               UPDATE ACCT_MASTER
               SET ACCT_BAL = :WS-NEW-BAL
               WHERE ACCT_ID = :WS-ACCT-ID
           END-EXEC.

Identify all defects and write corrected code.

Exercise 19: Timeout Investigation (Analyze)

A production CICS transaction is experiencing -913 timeouts every weekday between 10:00am and 10:15am. The RESOURCE TIMEOUT is set to 30 seconds.

a) List five possible causes. b) For each cause, describe how you would confirm or rule it out. c) The DBA suggests increasing RESOURCE TIMEOUT to 120 seconds. Is this a good solution? Why or why not?

Section 8.6 Exercises — Concurrent Online/Batch

Exercise 20: Batch/Online Coexistence Design (Design)

A new batch program must process 5,000,000 customer records nightly. The CUSTOMER table is accessed by online transactions averaging 200/second during the batch window. Design the batch program's locking strategy, including:

a) LOCKSIZE recommendation b) Commit frequency c) Cursor type d) Access order e) How to handle the scenario where an online transaction modifies a row that the batch cursor hasn't reached yet

Exercise 21: Partition-Level Strategy (Design)

The ACCT_MASTER table has 12 partitions (by account number range). Daily online transaction volume by partition:

Partition Daily Txns Batch Processing Needed
1-3 50,000 each Yes, full scan
4-6 200,000 each Yes, full scan
7-9 500,000 each Yes, full scan
10-12 100,000 each Yes, full scan

Design a batch processing schedule that minimizes online impact. Specify the order of partition processing and the time windows.

Exercise 22: Lock Contention Heat Map (Analyze)

You have the following 1-hour lock wait statistics for a tablespace:

Pages with 0 lock waits:      48,500
Pages with 1-5 lock waits:     1,200
Pages with 6-20 lock waits:      250
Pages with 21-100 lock waits:      45
Pages with 100+ lock waits:         5

a) Calculate the percentage of pages that are "hot" (more than 20 lock waits). b) What does this distribution tell you about the contention pattern? c) Propose three strategies to reduce contention on the hot pages.

Section 8.7 Exercises — Lock Avoidance and Optimistic Concurrency

Exercise 23: Optimistic Concurrency Implementation (Code)

Write complete COBOL code for an optimistic concurrency pattern that:

a) Reads a customer record (CUST_ID, CUST_NAME, CUST_ADDR, LAST_MODIFIED_TS) b) Allows the user to modify CUST_NAME and CUST_ADDR c) Detects if another user modified the record during "think time" d) Retries up to 2 times if a conflict is detected e) Returns appropriate status codes to the calling program

Exercise 24: Lock Avoidance Analysis (Analyze)

A DB2 subsystem reports the following lock avoidance statistics:

Total page accesses:        10,000,000
Lock avoidance successful:   7,500,000  (75%)
Lock requests issued:        2,500,000

a) Is 75% lock avoidance healthy? What is the typical target? b) What conditions cause lock avoidance to fail? c) The DBA changes LOCKSIZE from PAGE to ROW. Will this improve or worsen lock avoidance? Explain.

Exercise 25: Version-Based vs. Timestamp-Based Concurrency (Evaluate)

Compare two approaches to optimistic concurrency:

Approach A: Integer version column (VERSION_NUM INTEGER NOT NULL DEFAULT 1), incremented on every update.

Approach B: Timestamp column (LAST_MODIFIED TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP), set to CURRENT TIMESTAMP on every update.

Evaluate each approach on: a) Probability of false conflicts (detecting a "conflict" when none occurred) b) Storage overhead c) Performance impact on updates d) Behavior in a Parallel Sysplex data sharing environment (consider clock synchronization) e) Which would you recommend and why?

Integration Exercises

Exercise 26: Full Program Design Review (Evaluate)

Review the following COBOL batch program design and identify all locking problems:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BATCHUPD.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  WS-SQLCODE      PIC S9(9) COMP.
       PROCEDURE DIVISION.
           EXEC SQL
               DECLARE CSR1 CURSOR FOR
               SELECT ACCT_ID, ACCT_BAL
               FROM ACCT_MASTER
               WHERE ACCT_STATUS = 'ACTIVE'
               WITH RR
           END-EXEC
           EXEC SQL OPEN CSR1 END-EXEC
           PERFORM UNTIL SQLCODE = 100
               EXEC SQL
                   FETCH CSR1 INTO :WS-ACCT-ID, :WS-BAL
               END-EXEC
               IF SQLCODE = 0
                   COMPUTE WS-NEW-BAL = WS-BAL + WS-INTEREST
                   EXEC SQL
                       UPDATE ACCT_MASTER
                       SET ACCT_BAL = :WS-NEW-BAL
                       WHERE ACCT_ID = :WS-ACCT-ID
                   END-EXEC
               END-IF
           END-PERFORM
           EXEC SQL COMMIT END-EXEC
           STOP RUN.

List every locking deficiency and write a corrected version.

Exercise 27: Cross-Chapter Integration — Sysplex Locking (Analyze)

In a Parallel Sysplex data sharing group with two DB2 members:

a) Member 1 runs CNBBATCH (batch account updates). Member 2 runs CNBWIRE (online wire transfers). Both access ACCT_MASTER. Describe how the lock interaction differs from a single-member environment. b) What additional performance overhead exists for lock management in data sharing? c) How does the HA Banking system's locking strategy (Section 8.8) need to be modified for a data sharing environment?

Exercise 28: Cross-Chapter Integration — Optimizer Impact (Analyze)

A COBOL program has the following query bound with CS isolation:

SELECT ACCT_ID, ACCT_BAL
FROM ACCT_MASTER
WHERE BRANCH_ID = :HV1
  AND ACCT_STATUS = 'ACTIVE'

a) If DB2's optimizer chooses a tablespace scan, describe the locking behavior during execution. b) If the optimizer chooses an index access using an index on (BRANCH_ID, ACCT_STATUS), describe the locking behavior. c) After RUNSTATS, the optimizer switches from (b) to (a). The program hasn't changed, but deadlocks suddenly appear. Explain why. d) What should the DBA do?

Exercise 29: Production Incident Simulation (Evaluate)

At 3:00pm, the following symptoms appear simultaneously:

  • Online transaction response times jump from 200ms to 5+ seconds
  • DB2 LOCK SUSPENSION counter increases 10x
  • IRLM storage utilization jumps from 40% to 85%
  • Three batch jobs are running against ACCT_MASTER
  • No recent application deployments

Walk through your investigation process step by step: a) What is your immediate action? b) What DB2 commands and queries do you run? c) What do you look for in the output? d) What is the most likely root cause? e) What is the immediate fix vs. the long-term fix?

Exercise 30: Comprehensive Locking Strategy Document (Design)

You are the lead DB2 application programmer for Pinnacle Health. Write a one-page locking strategy document for the CLAIMS table that covers:

a) LOCKSIZE and LOCKMAX recommendations with justification b) Isolation level standards for online, batch-read, and batch-update programs c) Commit frequency standards for batch programs d) Access ordering rules for programs that access CLAIMS and related tables (MEMBER, PROVIDER, PAYMENT) e) Retry logic standards f) Monitoring and alerting thresholds