Isolation levels, anomalies

Why Isolation Levels Matter in Production

Most production data corruption incidents are not caused by bugs in business logic. They are caused by incorrect transaction boundaries and wrong isolation assumptions. Symptoms you see in production: - Negative account balances - Duplicate processing - Inventory going below zero - Inconsistent reporting - Random test failures under load Root cause: You trusted the default isolation level.

Incident Scenario: Lost Money in Banking Service

Two concurrent requests withdraw money from the same account. Balance = 100 Thread A reads 100 Thread B reads 100 Thread A writes 50 Thread B writes 50 Final balance = 50 Expected balance = 0 This is called a Lost Update. If you do not understand isolation, this WILL happen.

Transaction Basics in JDBC

By default, JDBC runs in auto-commit mode. That means: Every statement is its own transaction. Disable it explicitly:

Connection conn = dataSource.getConnection();
conn.setAutoCommit(false);

try {
    // business logic
    conn.commit();
} catch (Exception e) {
    conn.rollback();
    throw e;
}

If you forget rollback, you create partial writes. If you forget commit, nothing persists.

Isolation Levels Explained

READ_UNCOMMITTED - Can read uncommitted data - Allows dirty reads - Almost never safe READ_COMMITTED - Can only read committed data - Prevents dirty reads - Allows non-repeatable reads REPEATABLE_READ - Same row read twice returns same data - Prevents non-repeatable reads - May allow phantom reads (depends on DB) SERIALIZABLE - Full isolation - Transactions behave sequentially - Heavy locking - Can destroy performance Set explicitly:

conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

Never assume database default.

Dirty Read Example

Transaction A updates balance but does not commit. Transaction B reads that uncommitted value. If A rolls back, B just used invalid data. Dirty reads break financial systems.

Non-Repeatable Read

Transaction A reads row. Transaction B modifies and commits. Transaction A reads again — value changed. Your logic may depend on stability.

Phantom Read

Transaction A queries: SELECT COUNT(*) FROM orders WHERE status = PENDING Transaction B inserts new PENDING order. Transaction A runs the same query again. Count changed. This breaks: - Reporting - Pagination - Consistency guarantees

Lost Update and Proper Fix

Anti-pattern: Read → Modify → Write without locking. Correct approaches: 1. Pessimistic locking:

SELECT balance FROM accounts
WHERE id = ?
FOR UPDATE;

Prevents concurrent modification. 2. Optimistic locking: Add version column. UPDATE accounts SET balance = ?, version = version + 1 WHERE id = ? AND version = ? If affected rows = 0 → retry. Optimistic locking scales better.

Performance Tradeoffs

Higher isolation: - More locks - More blocking - Lower concurrency Lower isolation: - Better throughput - Risk of anomalies Production rule: Use lowest isolation that guarantees correctness. Blindly using SERIALIZABLE is not engineering. It is panic configuration.

Spring or JPA Users Beware

If you rely on frameworks: - Understand default isolation - Understand propagation settings - Understand rollback rules @Transactional without understanding isolation is dangerous.

Monitoring and Debugging

When you see: - Deadlocks - Lock wait timeout exceeded - Threads blocked Check: - Long running transactions - Missing indexes - Overly broad updates - Missing WHERE clauses Database logs tell the truth.

Checklist

- Disable auto-commit for multi-step logic - Explicitly set isolation level - Understand your database default - Use optimistic locking for scalability - Use SELECT FOR UPDATE when correctness demands it - Keep transactions short - Monitor lock wait metrics - Test concurrency with load tests Transactions are not just begin and commit. They define the integrity of your system. Get them wrong, and production will corrupt silently.