REPEATABLE READ Phantom Inventory — DBMS Transactions

-- io.thecodeforge: Atomic Bank Transfer Example
BEGIN;

-- Step 1: Deduct from source
UPDATE io_thecodeforge.accounts 
SET balance = balance - 500, updated_at = NOW() 
WHERE account_id = 'ACC-001' AND balance >= 500;

-- Step 2: Add to destination
UPDATE io_thecodeforge.accounts 
SET balance = balance + 500, updated_at = NOW() 
WHERE account_id = 'ACC-002';

-- If a network timeout or constraint violation occurs here,
-- the entire block is invalidated.
COMMIT;

-- PRO TIP: In Spring Boot, use @Transactional(propagation = Propagation.REQUIRED)
-- to wrap these calls in a single proxy-managed transaction.

-- io.thecodeforge: Demonstrating Read Phenomena

-- 1. READ COMMITTED (Default for Postgres)
-- Prevents Dirty Reads but allows Non-Repeatable Reads.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 2. REPEATABLE READ (Default for MySQL InnoDB)
-- Uses MVCC (Multi-Version Concurrency Control) to ensure you see a snapshot.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 3. SERIALIZABLE (Highest Safety)
-- Prevents Phantoms by placing range locks or using predicate locking.
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Production Pattern: Use 'SELECT ... FOR UPDATE' to skip isolation logic
-- and force a row-level write lock immediately.
BEGIN;
SELECT balance FROM io_thecodeforge.accounts WHERE account_id = 'ACC-001' FOR UPDATE;
-- This row is now locked until we COMMIT.

-- io.thecodeforge: Deadlock Prevention via Deterministic Ordering

-- BAD: Locking in random order based on app logic
-- TX1: Update A, then B
-- TX2: Update B, then A  <-- High Deadlock Risk!

-- GOOD: Always sort IDs before locking
BEGIN;
-- By ordering our IDs, all transactions wait in the same 'queue'
SELECT * FROM io_thecodeforge.accounts 
WHERE account_id IN ('ACC-001', 'ACC-002') 
ORDER BY account_id ASC 
FOR UPDATE;

UPDATE io_thecodeforge.accounts SET balance = balance - 100 WHERE account_id = 'ACC-001';
UPDATE io_thecodeforge.accounts SET balance = balance + 100 WHERE account_id = 'ACC-002';
COMMIT;

-- io.thecodeforge: MVCC Snapshot Demonstration (PostgreSQL)

-- Session 1: Start a repeatable read transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM io_thecodeforge.accounts WHERE account_id = 'ACC-001';
-- Returns 1000

-- Session 2 (concurrent): Update and commit
BEGIN;
UPDATE io_thecodeforge.accounts SET balance = 800 WHERE account_id = 'ACC-001';
COMMIT;

-- Session 1: Still sees 1000 (its snapshot is from the start)
SELECT balance FROM io_thecodeforge.accounts WHERE account_id = 'ACC-001';
-- Returns 1000

COMMIT;

-- After Session 1 commits, new queries see 800.

// io.thecodeforge.service.TransferService
@Service
public class TransferService {

    @Transactional(isolation = Isolation.READ_COMMITTED, timeout = 5)
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        Account from = accountRepo.findByIdForUpdate(fromId);
        if (from.getBalance().compareTo(amount) < 0) {
            throw new InsufficientBalanceException();
        }
        Account to = accountRepo.findByIdForUpdate(toId);
        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));
        accountRepo.save(from);
        accountRepo.save(to);
        // If an exception is thrown here (e.g., optimistic lock failure),
        // the entire transaction rolls back automatically.
    }
}