Exercises — Chapter 32: Transaction Design Patterns

Exercise 32.1: Identifying Units of Work (Conceptual)

For each business operation below, identify the complete unit of work — all the database operations that must succeed or fail together.

  1. Bank wire transfer: Transfer $10,000 from GlobalBank account to an external bank via SWIFT.
  2. Insurance claim denial: Deny a claim, update claim status, create denial letter, send notification.
  3. Payroll processing: Calculate employee pay, deduct taxes, credit employee account, debit company account, create pay stub.
  4. Inventory order: Customer places order — reduce inventory, create order record, charge credit card, create shipment record.
  5. Account closure: Close a bank account — verify zero balance, archive transactions, delete account, update customer record.

For each, identify: (a) which operations are in a single UOW, (b) which might need to be separate UOWs (saga), (c) what compensating actions would be needed if a later step fails.

Exercise 32.2: COMMIT Frequency Analysis (Analysis)

A batch program processes 1,000,000 account records. Each record requires one SELECT and one UPDATE. The program currently has no explicit COMMITs.

  1. Calculate the rollback time if the program abends at record 999,000 (assume 0.5ms per rollback operation).
  2. If the commit interval is set to 1,000 records, what is the maximum number of records that would need to be reprocessed after an abend?
  3. If the commit interval is set to 1 record, estimate the total overhead if each COMMIT takes 2ms.
  4. What commit interval would you recommend, and why?
  5. Write the COBOL code for a processing loop with commit interval controlled by a WORKING-STORAGE variable.

Exercise 32.3: Resource Ordering (Coding)

Write a COBOL paragraph that transfers funds between two accounts using resource ordering to prevent deadlocks. The paragraph should:

  1. Accept source account, destination account, and amount as input
  2. Determine the lock order (lower account number first)
  3. Lock both accounts with SELECT FOR UPDATE
  4. Verify sufficient funds in the source
  5. Perform the debit and credit
  6. Write audit records
  7. COMMIT on success, ROLLBACK on any failure
  8. Handle SQLCODE -911 (deadlock/timeout) with up to 3 retries

Exercise 32.4: Optimistic Locking Implementation (Coding)

Write a CICS-style COBOL program that updates a customer's address using optimistic locking:

  1. Read the customer record (without lock), including a LAST_UPDATE timestamp
  2. Display the current address to the user (simulate with DISPLAY)
  3. Accept the new address (simulate with MOVE)
  4. Update the customer record, but only if LAST_UPDATE has not changed
  5. If the update affects zero rows, display "Data has been changed by another user" and re-read
  6. Include a maximum of 3 retry attempts before giving up

Exercise 32.5: Saga Pattern Implementation (Project)

Design and code a saga for a simplified order processing system:

Steps: 1. Create order record (status: CREATED) 2. Reserve inventory (decrement available quantity) 3. Charge credit card (create payment record) 4. Create shipment record (status: PENDING)

Compensations: 1. Cancel order (status: CANCELLED) 2. Release inventory (increment available quantity) 3. Refund credit card (create refund record) 4. Cancel shipment (status: CANCELLED)

Write the complete COBOL program with: - A saga executor that runs steps in order - A compensation executor that runs compensations in reverse - Each step commits independently - Each compensation is idempotent - A saga state log that tracks which steps have completed

Exercise 32.6: Idempotency Testing (Analysis)

Examine each SQL statement below and determine whether it is idempotent. If not, rewrite it to be idempotent.

1. UPDATE ACCOUNT SET BALANCE = BALANCE + 100
       WHERE ACCT_ID = '12345';

2. INSERT INTO AUDIT_LOG (LOG_ID, ACTION, TIMESTAMP)
       VALUES ('LOG001', 'TRANSFER', CURRENT TIMESTAMP);

3. UPDATE CLAIM SET STATUS = 'APPROVED'
       WHERE CLAIM_ID = 'CLM001';

4. DELETE FROM TEMP_TABLE WHERE PROCESS_DATE < '2024-01-01';

5. UPDATE COUNTER SET COUNT = COUNT + 1
       WHERE COUNTER_NAME = 'DAILY_TXN';

For each non-idempotent statement, explain the consequence of running it twice and provide an idempotent alternative.

Exercise 32.7: Transaction Anti-Pattern Identification (Analysis)

Review the following COBOL code and identify all transaction design anti-patterns. Explain each problem and provide the corrected approach.

       2000-PROCESS-TRANSFER.
           EXEC SQL
               SELECT BALANCE INTO :WS-SRC-BAL
               FROM ACCOUNT WHERE ACCT_ID = :WS-SRC
               FOR UPDATE
           END-EXEC
           EXEC SQL COMMIT END-EXEC

           EXEC SQL
               SELECT BALANCE INTO :WS-DST-BAL
               FROM ACCOUNT WHERE ACCT_ID = :WS-DST
               FOR UPDATE
           END-EXEC

           COMPUTE WS-SRC-BAL = WS-SRC-BAL - WS-AMOUNT
           COMPUTE WS-DST-BAL = WS-DST-BAL + WS-AMOUNT

           EXEC SQL
               UPDATE ACCOUNT SET BALANCE = :WS-SRC-BAL
               WHERE ACCT_ID = :WS-SRC
           END-EXEC
           EXEC SQL COMMIT END-EXEC

           EXEC SQL
               UPDATE ACCOUNT SET BALANCE = :WS-DST-BAL
               WHERE ACCT_ID = :WS-DST
           END-EXEC
           EXEC SQL COMMIT END-EXEC.