Key Takeaways — Chapter 32: Transaction Design Patterns

Core Principles

  1. A transaction is a promise — either all operations complete or none do. Transaction boundaries must align with business operations, not individual SQL statements.

  2. COMMIT makes changes permanent; ROLLBACK undoes them — these are your primary tools for maintaining data consistency across DB2, VSAM, and IMS.

  3. Two-phase commit coordinates multiple resource managers — when your program updates DB2 and VSAM in the same transaction, 2PC ensures both commit or both roll back.

  4. Resource ordering prevents deadlocks — always acquire locks in a consistent, predetermined order (e.g., lower account number first).

  5. Optimistic locking for low contention; pessimistic for high — choose your locking strategy based on how often concurrent transactions access the same data.

  6. The saga pattern handles long-running processes — break multi-day workflows into committed steps with compensating transactions.

  7. Idempotent design prevents duplicate processing — use unique transaction IDs and state checks so operations are safe to retry.

Practical Guidelines

  • Check SQLCODE after every SQL statement — detect failures before they cascade.
  • Commit at logical business boundaries — not after every statement (chatty) and not only at program end (mega-transaction).
  • Hold locks for the shortest possible time — calculate before locking, commit immediately after updating.
  • Design compensations to be idempotent — safe to execute multiple times without side effects.
  • Log everything — transaction IDs, timestamps, before/after values. You will need them for debugging and reconciliation.
  • Retry deadlocks — SQLCODE -911 is recoverable; retry up to 3 times before failing.

Anti-Patterns to Avoid

  • Chatty Transaction: Committing after each individual operation breaks atomicity.
  • Mega-Transaction: Never committing risks catastrophic rollback times.
  • Lock-and-Think: Holding locks during user think time kills concurrency.
  • Optimistic Failure Ignorer: Detecting concurrent modification but continuing anyway.
  • Non-idempotent Retry: Relative updates (ADD 100) that double-count on retry.

Connections

  • Chapter 31 (IMS/DB Basics): IMS CHKP/ROLB provides checkpoint/rollback for IMS databases
  • Chapter 33 (Debugging Strategies): Transaction failures produce specific abend codes and diagnostic data
  • Chapter 29 (CICS Fundamentals): EXEC CICS SYNCPOINT is the CICS transaction boundary