Isolation Levels

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- PostgreSQL: Check and set isolation level
SHOW default_transaction_isolation;  -- Usually 'read committed'
 
-- Set for current transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... your operations ...
COMMIT;
 
-- PostgreSQL's REPEATABLE READ is actually Snapshot Isolation
-- It prevents phantoms (unlike SQL standard REPEATABLE READ)
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders WHERE status = 'PENDING';  -- Returns 5 rows
 
-- Another transaction inserts a PENDING order and commits
 
SELECT * FROM orders WHERE status = 'PENDING';  -- Still returns same 5 rows
-- (No phantoms visible in PostgreSQL REPEATABLE READ)
COMMIT;
 
-- Check for serialization failures (need retry logic!)
DO $$
BEGIN
  FOR i IN 1..3 LOOP
    BEGIN
      -- Your serializable transaction
      PERFORM * FROM accounts WHERE id = 'A' FOR UPDATE;
      -- ... 
      RETURN;
    EXCEPTION WHEN serialization_failure THEN
      -- Retry
      RAISE NOTICE 'Retry attempt %', i;
    END;
  END LOOP;
END $$;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

@Service
public class IsolationExamples {
    
    /**
     * Simple CRUD - READ COMMITTED is fine
     * Operations are atomic; no consistency requirements across reads
     */
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public User updateUserEmail(UUID userId, String newEmail) {
        User user = userRepository.findById(userId)
            .orElseThrow(() -> new UserNotFoundException(userId));
        user.setEmail(newEmail);
        return userRepository.save(user);
    }
    
    /**
     * Financial transfer - SERIALIZABLE required
     * Cannot allow any anomalies; must appear to execute serially
     */
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public TransferResult transfer(UUID fromAccount, UUID toAccount, BigDecimal amount) {
        Account source = accountRepository.findByIdForUpdate(fromAccount)
            .orElseThrow();
        Account destination = accountRepository.findByIdForUpdate(toAccount)
            .orElseThrow();
        
        if (source.getBalance().compareTo(amount) < 0) {
            throw new InsufficientFundsException();
        }
        
        source.debit(amount);
        destination.credit(amount);
        
        accountRepository.save(source);
        accountRepository.save(destination);
        
        return TransferResult.success(source, destination);
    }
    
    /**
     * Report generation - REPEATABLE READ for consistent snapshot
     * All queries see the same point-in-time state
     */
    @Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
    public MonthlyReport generateMonthlyReport(YearMonth month) {
        // All these queries see the same snapshot
        BigDecimal totalRevenue = orderRepository.sumRevenueForMonth(month);
        long orderCount = orderRepository.countOrdersForMonth(month);
        List<TopProduct> topProducts = productRepository.findTopSellingForMonth(month);
        
        // If new orders are committed during this method,
        // we won't see them - report remains consistent
        return new MonthlyReport(month, totalRevenue, orderCount, topProducts);
    }
    
    /**
     * Inventory reservation with explicit locking
     * READ COMMITTED + SELECT FOR UPDATE prevents lost updates
     */
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void reserveInventory(UUID productId, int quantity) {
        // FOR UPDATE acquires exclusive lock, preventing concurrent modifications
        Inventory inventory = inventoryRepository.findByIdForUpdate(productId)
            .orElseThrow();
        
        if (inventory.getAvailable() < quantity) {
            throw new InsufficientInventoryException();
        }
        
        inventory.reserve(quantity);
        inventoryRepository.save(inventory);
    }
    
    /**
     * On-call check with SERIALIZABLE to prevent write skew
     */
    @Transactional(isolation = Isolation.SERIALIZABLE)
    public void goOffCall(UUID doctorId) {
        long currentOnCall = doctorRepository.countOnCall();
        
        if (currentOnCall <= 1) {
            throw new BusinessRuleException(
                "Cannot go off-call: at least one doctor must remain on-call"
            );
        }
        
        Doctor doctor = doctorRepository.findById(doctorId).orElseThrow();
        doctor.setOnCall(false);
        doctorRepository.save(doctor);
        
        // Without SERIALIZABLE, two doctors could simultaneously pass the check
        // and both go off-call, violating the invariant
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- Explicit locking supplements isolation levels
 
-- SELECT FOR UPDATE: Exclusive lock, blocks other writers AND FOR UPDATE readers
BEGIN;
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE;
-- Other transactions trying to UPDATE this row will BLOCK
-- Other transactions with FOR UPDATE on this row will BLOCK
-- Other transactions with plain SELECT may proceed (with MVCC)
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
COMMIT;
 
-- SELECT FOR SHARE (FOR KEY SHARE in PostgreSQL): Shared lock
BEGIN;
SELECT * FROM orders WHERE id = '123' FOR SHARE;
-- Other transactions can also read FOR SHARE
-- Other transactions trying to UPDATE or DELETE will BLOCK
COMMIT;
 
-- NOWAIT: Fail immediately if lock cannot be acquired
BEGIN;
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE NOWAIT;
-- Throws error immediately if another transaction holds lock
-- Useful for try-lock patterns
COMMIT;
 
-- SKIP LOCKED: Skip rows that are locked by other transactions
BEGIN;
SELECT * FROM job_queue WHERE status = 'PENDING' 
    ORDER BY created_at 
    LIMIT 1 
    FOR UPDATE SKIP LOCKED;
-- Gets next unlocked job, even if some are locked by other workers
-- Great for queue processing without blocking
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101

/**
 * Pattern 1: Preventing Lost Updates with Optimistic Locking
 * Uses a version column to detect concurrent modifications
 */
@Entity
public class Account {
    @Id
    private UUID id;
    private BigDecimal balance;
    
    @Version  // JPA manages this automatically
    private Long version;
}
 
@Transactional
public void updateAccount(UUID accountId, BigDecimal newBalance) {
    Account account = repository.findById(accountId).orElseThrow();
    account.setBalance(newBalance);
    repository.save(account);  // Throws OptimisticLockException if version changed
}
 
// With retry logic:
public void updateAccountWithRetry(UUID accountId, BigDecimal delta) {
    int maxRetries = 3;
    for (int i = 0; i < maxRetries; i++) {
        try {
            doUpdateAccount(accountId, delta);
            return;
        } catch (OptimisticLockException e) {
            if (i == maxRetries - 1) throw e;
            // Retry with fresh data
        }
    }
}
 
@Transactional
private void doUpdateAccount(UUID accountId, BigDecimal delta) {
    Account account = repository.findById(accountId).orElseThrow();
    account.setBalance(account.getBalance().add(delta));
    repository.save(account);
}
 
/**
 * Pattern 2: Preventing Lost Updates with Pessimistic Locking
 * SELECT FOR UPDATE acquires lock immediately
 */
@Transactional
public void updateAccountPessimistic(UUID accountId, BigDecimal delta) {
    // Lock acquired at read time; other transactions wait
    Account account = repository.findByIdForUpdate(accountId).orElseThrow();
    account.setBalance(account.getBalance().add(delta));
    repository.save(account);
}
 
// Repository method:
@Query("SELECT a FROM Account a WHERE a.id = :id")
@Lock(LockModeType.PESSIMISTIC_WRITE)
Optional<Account> findByIdForUpdate(@Param("id") UUID id);
 
/**
 * Pattern 3: Atomic Update (Best for simple increments)
 * Single statement = atomic, no read-modify-write race
 */
@Transactional
public void updateAccountAtomic(UUID accountId, BigDecimal delta) {
    // This is a SINGLE atomic operation - no lost update possible
    repository.incrementBalance(accountId, delta);
}
 
// Repository method:
@Modifying
@Query("UPDATE Account a SET a.balance = a.balance + :delta WHERE a.id = :id")
void incrementBalance(@Param("id") UUID id, @Param("delta") BigDecimal delta);
 
/**
 * Pattern 4: Preventing Write Skew with Materialized Conflict
 * Add a row that must be locked, forcing serialization
 */
@Entity
public class OnCallSlot {
    @Id
    private String id = "SINGLETON";  // Only one row exists
    private int doctorsOnCall;
}
 
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void goOffCallWithMaterializedLock(UUID doctorId) {
    // Lock the slot row - forces serialization of on-call changes
    OnCallSlot slot = slotRepository.findForUpdate("SINGLETON");
    
    if (slot.getDoctorsOnCall() <= 1) {
        throw new BusinessRuleException("Someone must be on call");
    }
    
    Doctor doctor = doctorRepository.findById(doctorId).orElseThrow();
    doctor.setOnCall(false);
    doctorRepository.save(doctor);
    
    slot.decrementDoctorsOnCall();
    slotRepository.save(slot);
}