Transaction Isolation Level Auditor

Transaction Isolation Level Auditor

Applies Fowler's Table 5.1 isolation-level matrix to audit a system's transaction configuration, map the current isolation level to its permitted anomaly classes, locate code-level vulnerabilities, and produce a structured fix plan with ACID compliance assessment at both system-transaction and business-transaction levels.

When to Use

Use this skill when you need to answer any of:

• "Are we exposed to lost updates with our current isolation level?"
• "Should we upgrade from READ COMMITTED to REPEATABLE READ or SERIALIZABLE?"
• "Is this codebase ACID-compliant at the system-transaction level? At the business-transaction level?"
• "We have a concurrency bug — which anomaly class is it and what fixes it?"

This skill targets single-request, database-managed system transactions. If the concern is multi-request business transactions (user edits over minutes), invoke offline-concurrency-strategy-selector for lock pattern selection after completing this audit.

Prerequisites: None. Codebase access improves coverage; a description of the transaction config is sufficient for a high-level audit.

Context and Input Gathering

Gather the following before auditing. Ask the user if not inferable from the codebase.

Required:

• Current isolation level: Check in this order — (1) config/database.yml, application.properties, appsettings.json; (2) ORM/framework config (@Transactional(isolation=...), connection.set_isolation_level(...), DbContextOptionsBuilder); (3) database-level default (SHOW TRANSACTION ISOLATION LEVEL, SELECT @@transaction_isolation).
• Transaction boundary pattern: How transactions are opened and closed — declarative (@Transactional), programmatic (session.begin(), BEGIN TRANSACTION), or request-scoped middleware.

Observable from codebase (Grep for):

• @Transactional, begin_transaction, BEGIN TRANSACTION, session.begin, session.begin_nested, getTransaction().begin(), SaveChanges()
• SELECT.*FOR UPDATE, LOCK IN SHARE MODE, WITH (UPDLOCK)
• Read-modify-write patterns: load → mutate → save without a version/optimistic check
• Version/ETag columns: version, optimistic_lock, row_version, etag

Defaults (when not found):

• PostgreSQL, Oracle, SQL Server: default to READ COMMITTED
• MySQL InnoDB: defaults to REPEATABLE READ
• No SELECT FOR UPDATE or version column = no lost-update protection beyond isolation level

Sufficiency check: If isolation level and at least one transaction boundary are known, proceed. Flag any gaps in coverage.

Process

Step 1 — Read the Current Isolation Level

Search config files and ORM annotations for explicit isolation level settings. If not found, assume the database vendor default (READ COMMITTED for most production databases).

WHY: The isolation level is the single configuration value that determines which anomaly classes the database prevents. Everything downstream depends on it.

Grep commands:

Grep "@Transactional" — find Spring/Java annotation-based transaction demarcation
Grep "isolation" — find explicit isolation level overrides
Grep "BEGIN\|begin_transaction\|session.begin" — find programmatic boundaries
Grep "SELECT.*FOR UPDATE\|NOWAIT\|SKIP LOCKED" — find pessimistic read locks
Grep "version\|optimistic_lock\|row_version\|etag" in schema/models — find optimistic check columns

Step 2 — Apply Table 5.1 to Map Permitted Anomalies

Look up the current isolation level in the matrix below and record which anomalies are permitted (not prevented):

WHY: This is Fowler's Table 5.1 — the authoritative SQL standard mapping. It tells you exactly what the database guarantees and what it does not. Anomaly classes not in this table (specifically Lost Update) require an additional application-level check regardless of isolation level.

Add Lost Update assessment separately:

• Lost Update is a write-write conflict, not covered by the SQL anomaly table.
• It occurs at any isolation level when read-modify-write is used without an optimistic version check or SELECT FOR UPDATE.
• Flag this independently of isolation level.

Step 3 — Scan for Code-Level Vulnerabilities

For each transaction in scope, check for the patterns below. Flag each finding with file location.

3a. Lost Update vulnerability — read-modify-write without optimistic check: Look for: entity is loaded → a field is modified → entity is saved, with no version comparison between read and write.

# Vulnerable pattern (Python/SQLAlchemy example)
item = session.query(Item).filter_by(id=item_id).one()
item.stock -= quantity   # based on stale read
session.commit()         # second concurrent writer overwrites first

# Safe pattern — version column checked
session.execute(
    "UPDATE items SET stock=:s, version=version+1 WHERE id=:id AND version=:v",
    {"s": item.stock - quantity, "id": item_id, "v": item.version}
)

3b. Unrepeatable Read vulnerability (if isolation < Repeatable Read): Look for: the same row is read twice within one transaction, and correctness depends on the values being stable. Flag when isolation level is READ COMMITTED.

3c. Phantom Read vulnerability (if isolation < Serializable): Look for: range queries (COUNT, SUM, WHERE date BETWEEN, WHERE status = 'pending') executed inside a transaction that also writes rows matching the same range. Flag when isolation < Serializable.

3d. Long transaction at high isolation: Look for: transactions that span user think-time, file I/O, or remote calls. These hold locks (Repeatable Read/Serializable) or accumulate snapshot overhead (MVCC), degrading throughput and risking deadlocks.

WHY: Code-level patterns determine actual exposure. The isolation level sets the floor, but read-modify-write without a guard lets Lost Update through at any level. Step 3 finds the specific call sites to fix.

Step 4 — Check System-Transaction ACID Compliance

For each ACID property, look for its implementation signal:

WHY: Each ACID property has a concrete implementation shape. Identifying the absence of that shape (no rollback on error, no version column) gives actionable fixes, not just warnings.

Step 5 — Identify Business-Transaction ACID Gaps

A business transaction spans multiple HTTP requests and multiple system transactions. Ask:

• Does the system have any multi-step workflows (user opens record → edits across multiple screens → saves)?
• If YES: the database provides no isolation between the user's reads and final commit. Lost updates and inconsistent reads are possible regardless of system-transaction isolation level.
• IF the workflow has no Optimistic Offline Lock (version column) or Pessimistic Offline Lock (record checkout) → flag as a business-transaction isolation gap.

WHY: Fowler explicitly distinguishes system transactions (RDBMS-managed) from business transactions (application-managed). Most ACID discussions focus on system transactions and miss the multi-request gap entirely. This is where the most severe real-world lost update bugs live.

For business-transaction gaps, cross-reference offline-concurrency-strategy-selector for pattern selection.

Step 6 — Produce Fix Recommendations

For each vulnerability found in Steps 3-5, assign one of:

WHY: Not every vulnerability needs the highest isolation fix. Recommending per-vulnerability fixes (rather than "always use Serializable") preserves throughput and minimizes lock contention.

Step 7 — Produce the Isolation Audit Report

Assemble findings into the output format.

Inputs

• Current isolation level (from config, code annotations, or database default)
• Transaction boundary locations (grep results or user description)
• Read-modify-write patterns found in code
• Presence or absence of version columns / SELECT FOR UPDATE
• Multi-request workflow description (for business-transaction check)

Outputs

Produce a markdown isolation audit report with these sections:

# Isolation Audit Report — [System Name]
Date: [date]

## Configuration
- Database: [vendor + version]
- Isolation Level: [level name]
- Transaction Demarcation: [declarative @Transactional / programmatic / request-scoped]

## Permitted Anomalies (Table 5.1 Mapping)
- Dirty Read: Prevented / Permitted
- Unrepeatable Read: Prevented / Permitted
- Phantom Read: Prevented / Permitted
- Lost Update (application-layer): Protected / VULNERABLE

## Vulnerabilities Found

### [VULN-01] Lost Update — [file:line]
Pattern: read-modify-write without version check
Risk: [describe the business consequence]
Fix: Add version column + optimistic check (see references/isolation-anomaly-matrix.md)

### [VULN-02] ...

## System-Transaction ACID Assessment
- Atomicity: [Green/Red] — [evidence]
- Consistency: [Green/Red] — [evidence]
- Isolation: [Green/Red] — [evidence]
- Durability: [Green/Red] — [evidence]

## Business-Transaction ACID Gaps
[List multi-request workflows with no offline lock; cross-reference offline-concurrency-strategy-selector]

## Recommended Actions (Prioritized)
1. [Highest risk fix]
2. ...

## Immutability Opportunities
[List read-heavy computations that could use snapshots or read replicas to sidestep concurrency entirely]

Key Principles

1. Table 5.1 is your baseline, not your complete answer. The SQL anomaly table covers Dirty Read, Unrepeatable Read, and Phantom. Lost Update — the most common application bug — is not in it. Treat the two separately: isolation level for read anomalies, application-level version check for write conflicts.

Why this matters: Engineers who only consult the isolation level often miss Lost Update entirely because "we're on READ COMMITTED" sounds safe.

2. READ COMMITTED + read-modify-write = Lost Update waiting to happen. The majority of enterprise databases default to READ COMMITTED. READ COMMITTED prevents dirty reads but allows Unrepeatable Reads, Phantoms, and Lost Updates. A read-modify-write without a version column is vulnerable at READ COMMITTED regardless of how short the transaction is.

Why this matters: Fowler frames Lost Update as the simplest concurrency problem to understand but the easiest to miss in production because the bug appears as "someone's changes just disappeared."

3. Raising isolation level trades liveness for correctness — do it per-transaction. Fowler: "You don't have to use the same isolation level for all transactions." Serializable on every transaction in a high-throughput system causes contention and deadlocks. Apply Serializable or Repeatable Read only to the specific transactions that need it (range-scan reports, correctness-critical reads).

Why this matters: A blanket "upgrade everything to Serializable" fix often causes worse production problems than the original bug.

4. Immutability eliminates the problem rather than managing it. Fowler identifies immutability as a concurrency-control strategy: data that cannot be modified needs no concurrency control. Read replicas, event log projections, CQRS read models, and cached snapshots exploit this. Before raising isolation level, ask whether the read path can be made immutable.

Why this matters: Immutability removes the tradeoff entirely — no liveness cost, no lock overhead, correct by construction.

5. Business-transaction ACID requires application-level enforcement. The RDBMS provides ACID within a single BEGIN/COMMIT block. Across multiple HTTP requests, the database provides nothing. Multi-step user workflows need Optimistic Offline Lock (version column check at commit) or Pessimistic Offline Lock (record checkout before first read).

Why this matters: The most severe lost update bugs in enterprise systems live in multi-step workflows, not in individual database transactions.

6. Long transactions at high isolation are a double-edged fix. Running a long computation inside Serializable or Repeatable Read prevents anomalies inside that computation, but holds locks (or accumulates MVCC overhead) for the duration. This blocks concurrent writers and risks deadlocks. Prefer shorter transactions: read data first, perform computation, open transaction only for the write window.

Examples

Scenario 1 — Spring Boot service with @Transactional defaults

Trigger: Team reports intermittent "lost updates" on inventory records during high load. Stack: Spring Boot + PostgreSQL (READ COMMITTED default). No version columns.

Process:

1. Grep finds @Transactional on service methods with no isolation override → READ COMMITTED confirmed.
2. Table 5.1 mapping: Dirty Read prevented; Unrepeatable Read permitted; Phantom permitted.
3. Scan finds itemRepo.findById(id) → item.setStock(item.getStock() - qty) → itemRepo.save(item) with no version check in 3 locations.
4. No SELECT FOR UPDATE anywhere in the persistence layer.
5. ACID check: Atomicity — @Transactional handles rollback (green); Isolation — READ COMMITTED + no version column (red).
6. No multi-request business transaction detected (all operations complete in one request).

Output excerpt:

Permitted Anomalies: Unrepeatable Read (permitted), Phantom (permitted),
Lost Update (VULNERABLE — 3 locations)

VULN-01: ItemService.java:87 — read-modify-write without version check
Fix: Add version column to items table; use optimistic lock in UPDATE statement

Recommended: Add @Version column (JPA) to Item entity. PostgreSQL READ COMMITTED
default is adequate once lost-update protection is added at the application layer.
No isolation level upgrade needed.

Scenario 2 — Financial posting service needing range-scan consistency

Trigger: Audit requirement: nightly balance report must reflect a consistent snapshot of all postings. Stack: Python + SQLAlchemy + PostgreSQL. Report runs a SUM across millions of rows.

Process:

1. Config shows isolation_level="READ_COMMITTED" on the session factory.
2. Table 5.1: Phantom Read permitted at READ COMMITTED — range scans can return different totals if concurrent inserts occur mid-query.
3. Report query is a single large SELECT SUM … GROUP BY — no write path, no version check needed.
4. Phantom risk: concurrent posting inserts can change the aggregate mid-report.
5. ACID check: Durability — WAL enabled (green); Isolation for reports — READ COMMITTED insufficient (red for correctness requirement).
6. Immutability opportunity: Consider running the report against a read replica or using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ only for this session.

Output excerpt:

VULN-01: balance_report.py — Phantom Read exposure on SUM aggregates
Fix Option A: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ for this session only
Fix Option B: Run report against a read replica after a logical checkpoint (immutability escape hatch)

No Lost Update risk (report is read-only). No business-transaction ACID gap.

Scenario 3 — Multi-step checkout workflow with no offline lock

Trigger: E-commerce platform where users add items to cart over multiple pages before placing an order. Engineers ask whether the system is ACID-compliant.

Process:

1. Isolation level: MySQL InnoDB REPEATABLE READ (default).
2. Table 5.1 at Repeatable Read: Dirty Read prevented, Unrepeatable Read prevented, Phantom permitted.
3. No Lost Update vulnerability within individual system transactions (version columns present on Order entity).
4. Business-transaction check: checkout spans 4 HTTP requests (address → payment → review → confirm). No Optimistic Offline Lock or Pessimistic Offline Lock between session start and final confirm.
5. Between session start and confirm, inventory levels can change. No version check on inventory at confirm time → business-transaction Lost Update possible.

Output excerpt:

System-Transaction ACID: Green across all four properties.

Business-Transaction ACID Gap:
- Isolation: VULNERABLE — inventory levels not version-checked at order confirm.
  Two concurrent checkouts for the last item can both succeed.
- Fix: Add Optimistic Offline Lock on inventory.quantity. Check version at confirm step.
  See: offline-concurrency-strategy-selector for full lock pattern selection.

References

• references/isolation-anomaly-matrix.md — Full Table 5.1, anomaly definitions, default isolation levels by database vendor, business vs system transaction ACID breakdown, long-transaction risk guidance
• Source: PEAA Chapter 5 "Concurrency" — sections: Concurrency Problems, Isolation and Immutability, Transactions (pp. 81–94)

License

This skill is licensed under CC-BY-SA-4.0. Source: BookForge — Patterns of Enterprise Application Architecture by Martin Fowler, David Rice, Matthew Foemmel, Edward Hieatt, Robert Mee, Randy Stafford.

Related BookForge Skills

Install related skills from ClawhHub:

• clawhub install bookforge-offline-concurrency-strategy-selector
• clawhub install bookforge-data-access-anti-pattern-auditor

Or install the full book set from GitHub: bookforge-skills