DB2 Isolation Levels - Concurrency Crisis

"Your online banking CICS application has a critical issue:

Symptom:

• Customers report seeing $0.00 balance during transfers
• Balance shows correct amount after refresh
• Happens during high-volume periods (month-end, paydays)
• Complaint rate: 200+ calls/day

Current Code:

EXEC SQL
    SELECT ACCOUNT_BALANCE
    INTO :WS-BALANCE
    FROM ACCOUNTS
    WHERE ACCOUNT_NUMBER = :WS-ACCT-NBR
END-EXEC.

* Display balance on screen
MOVE WS-BALANCE TO SCREEN-BALANCE

* User initiates transfer
EXEC SQL
    UPDATE ACCOUNTS
    SET ACCOUNT_BALANCE = ACCOUNT_BALANCE - :WS-TRANSFER-AMT
    WHERE ACCOUNT_NUMBER = :WS-ACCT-NBR
END-EXEC.

Analysis shows:

• Transaction A reads balance ($5,000)
• Transaction B updates balance (withdrawal $500)
• Transaction B commits
• Transaction A displays $0 (invalid data)

Requirements:

• Fix the phantom read issue
• Maintain high concurrency (1000 TPS)
• No table-level locking (performance killer)
• Must work with existing CICS infrastructure

Which isolation level solves this? What's the trade-off?"

• Deep understanding of DB2 locking
• Concurrency control expertise
• Performance vs. consistency trade-offs
• Production problem-solving

1. Root Cause:

• Current isolation level: UR (Uncommitted Read) or no explicit isolation
• Allows dirty reads - reading data being modified by other transactions
• No row locking during SELECT
• High concurrency but inconsistent results

2. Solution:

Use CS (Cursor Stability) isolation level:

EXEC SQL
    SELECT ACCOUNT_BALANCE
    INTO :WS-BALANCE
    FROM ACCOUNTS
    WHERE ACCOUNT_NUMBER = :WS-ACCT-NBR
    WITH CS
END-EXEC.

Or set at bind time:

BIND PLAN(ACCTPLAN) ISOLATION(CS)

3. Isolation Level Comparison:

4. Why CS is Optimal:

• Locks row being read - prevents dirty reads
• Releases lock after moving to next row - maintains concurrency
• Minimal lock contention - only one row locked at a time
• Supports 1000 TPS requirement - proven in high-volume environments

5. Additional Improvements:

* Add FOR UPDATE to ensure lock held for entire transaction
EXEC SQL
    SELECT ACCOUNT_BALANCE
    INTO :WS-BALANCE
    FROM ACCOUNTS
    WHERE ACCOUNT_NUMBER = :WS-ACCT-NBR
    FOR UPDATE OF ACCOUNT_BALANCE
END-EXEC.

* Verify balance hasn't changed before update
EXEC SQL
    UPDATE ACCOUNTS
    SET ACCOUNT_BALANCE = ACCOUNT_BALANCE - :WS-TRANSFER-AMT
    WHERE ACCOUNT_NUMBER = :WS-ACCT-NBR
    AND ACCOUNT_BALANCE = :WS-ORIGINAL-BALANCE
END-EXEC.

IF SQLCODE NOT = 0
    DISPLAY 'Balance changed. Please retry.'
END-IF.

6. Monitoring:

• Track deadlocks (SQLCODE -911, -913)
• Monitor lock wait times
• Implement retry logic for timeouts

• ❌ Suggests RR (overkill, kills performance)
• ❌ Doesn't understand dirty read concept
• ❌ Can't explain difference between CS and RS
• ❌ Ignores performance requirements (1000 TPS)
• ❌ Doesn't mention FOR UPDATE option

• "What if you need to read 100 accounts and ensure no changes during processing?"
• "How do you handle SQLCODE -911 (deadlock)?"
• "Why not use RR if consistency is critical?"
• "What's the lock escalation concern with RS?"