Database Transactions at LLD Level

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

public class TransferService {
    private final DataSource dataSource;
    
    public void transfer(String fromAccount, String toAccount, BigDecimal amount) 
            throws TransferException {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            
            // BEGIN TRANSACTION - Atomicity starts here
            conn.setAutoCommit(false);
            
            // Step 1: Debit source account
            int debitedRows = debitAccount(conn, fromAccount, amount);
            if (debitedRows != 1) {
                throw new TransferException("Failed to debit source account");
            }
            
            // Step 2: Credit destination account
            int creditedRows = creditAccount(conn, toAccount, amount);
            if (creditedRows != 1) {
                throw new TransferException("Failed to credit destination account");
            }
            
            // Both steps succeeded - commit the atomic unit
            conn.commit();
            
        } catch (SQLException | TransferException e) {
            // Any failure: rollback ensures ATOMICITY
            // Neither debit nor credit will persist
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException re) {
                    // Log rollback failure
                }
            }
            throw new TransferException("Transfer failed, no changes made", e);
        } finally {
            closeConnection(conn);
        }
    }
    
    private int debitAccount(Connection conn, String accountId, BigDecimal amount) 
            throws SQLException {
        String sql = "UPDATE accounts SET balance = balance - ? " +
                     "WHERE account_id = ? AND balance >= ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, accountId);
            ps.setBigDecimal(3, amount);  // Ensure sufficient balance
            return ps.executeUpdate();
        }
    }
    
    private int creditAccount(Connection conn, String accountId, BigDecimal amount) 
            throws SQLException {
        String sql = "UPDATE accounts SET balance = balance + ? WHERE account_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, accountId);
            return ps.executeUpdate();
        }
    }
}

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

public class OrderService {
    /**
     * This method demonstrates maintaining APPLICATION-LEVEL consistency.
     * The database can't enforce that order total equals sum of line items;
     * that's our responsibility.
     */
    public void addLineItem(Connection conn, UUID orderId, LineItem item) 
            throws SQLException, BusinessRuleException {
        conn.setAutoCommit(false);
        
        try {
            // Step 1: Verify the order exists and is in valid state
            Order order = findOrderForUpdate(conn, orderId);
            if (order.getStatus() != OrderStatus.DRAFT) {
                throw new BusinessRuleException(
                    "Cannot modify order in status: " + order.getStatus()
                );
            }
            
            // Step 2: Check inventory availability - APPLICATION CONSISTENCY
            int availableStock = getAvailableStock(conn, item.getProductId());
            int alreadyReserved = getOrderedQuantity(conn, orderId, item.getProductId());
            
            if (availableStock - alreadyReserved < item.getQuantity()) {
                throw new BusinessRuleException(
                    "Insufficient stock for product: " + item.getProductId()
                );
            }
            
            // Step 3: Insert line item
            insertLineItem(conn, orderId, item);
            
            // Step 4: Recalculate order total - MAINTAINING INVARIANT
            BigDecimal newTotal = calculateOrderTotal(conn, orderId);
            updateOrderTotal(conn, orderId, newTotal);
            
            // Step 5: Reserve inventory - ANOTHER APPLICATION INVARIANT
            reserveInventory(conn, item.getProductId(), item.getQuantity());
            
            // All consistency rules maintained, commit
            conn.commit();
            
        } catch (SQLException | BusinessRuleException e) {
            conn.rollback();  // Restore to previous consistent state
            throw e;
        }
    }
    
    /**
     * Note the "FOR UPDATE" - this locks the row to prevent concurrent
     * modifications that could break consistency
     */
    private Order findOrderForUpdate(Connection conn, UUID orderId) 
            throws SQLException {
        String sql = "SELECT * FROM orders WHERE id = ? FOR UPDATE";
        // ... execute and map result
    }
    
    private BigDecimal calculateOrderTotal(Connection conn, UUID orderId) 
            throws SQLException {
        String sql = 
            "SELECT COALESCE(SUM(quantity * unit_price), 0) " +
            "FROM order_line_items WHERE order_id = ?";
        // ... execute and return
    }
}

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

/**
 * Demonstration of concurrency anomalies and how isolation levels prevent them
 */
public class IsolationDemonstration {
    
    // ================================
    // DIRTY READ SCENARIO
    // ================================
    // READ UNCOMMITTED allows this; READ COMMITTED prevents it
    
    void dirtyReadScenario() {
        // Transaction A (Account Holder)
        // Time T1: Account balance is $1000
        // Time T2: Transaction B starts a withdrawal of $500
        //          UPDATE accounts SET balance = 500 WHERE id = 'A'
        //          (not yet committed)
        // Time T3: Transaction A reads balance - sees $500 (DIRTY READ!)
        // Time T4: Transaction B encounters an error and ROLLS BACK
        //          Balance is restored to $1000
        // Time T5: Transaction A still thinks balance is $500
        //          Makes decisions based on PHANTOM DATA
    }
    
    // ================================
    // LOST UPDATE SCENARIO
    // ================================
    // Even READ COMMITTED allows this; REPEATABLE READ prevents it
    
    void lostUpdateScenario() {
        // Initial: inventory = 100
        
        // Transaction A (Order #1)
        // T1: SELECT quantity FROM inventory WHERE product_id = 'X'
        //     Result: 100
        // T2: (calculates: 100 - 10 = 90)
        
        // Transaction B (Order #2) - CONCURRENT
        // T3: SELECT quantity FROM inventory WHERE product_id = 'X'
        //     Result: 100 (same as A saw)
        // T4: (calculates: 100 - 5 = 95)
        // T5: UPDATE inventory SET quantity = 95 WHERE product_id = 'X'
        // T6: COMMIT
        
        // Transaction A continues
        // T7: UPDATE inventory SET quantity = 90 WHERE product_id = 'X'
        //     A's update OVERWRITES B's!
        // T8: COMMIT
        
        // Final result: quantity = 90
        // LOST: B's update (should have been 85 after both orders)
    }
    
    // ================================
    // WRITE SKEW SCENARIO
    // ================================
    // Even REPEATABLE READ allows this in some DBs; SERIALIZABLE prevents it
    
    void writeSkewScenario() {
        // Business rule: At least one doctor must be on call
        // Currently: Dr. Alice is on call, Dr. Bob is on call (2 total)
        
        // Transaction A (Dr. Alice wants to go off-call)
        // T1: SELECT COUNT(*) FROM on_call WHERE on_call = true
        //     Result: 2 (Alice and Bob)
        // T2: "2 > 1, so if I leave, Bob is still on call. Safe!"
        // T3: UPDATE doctors SET on_call = false 
        //           WHERE name = 'Alice'
        
        // Transaction B (Dr. Bob wants to go off-call) - CONCURRENT
        // T4: SELECT COUNT(*) FROM on_call WHERE on_call = true
        //     Result: 2 (Alice and Bob - sees snapshot before A's update)
        // T5: "2 > 1, so if I leave, Alice is still on call. Safe!"
        // T6: UPDATE doctors SET on_call = false 
        //                WHERE name = 'Bob'
        
        // Both transactions commit
        // Result: NOBODY is on call!
        // Both individually made valid decisions, but combined: INVARIANT VIOLATED
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- PostgreSQL durability configuration settings
 
-- Ensure WAL is flushed to disk on every commit (default: on)
-- Setting to 'off' risks losing recent transactions after crash
SET synchronous_commit = 'on';
 
-- How much WAL to buffer before forcing a write
-- Larger values improve performance but risk more data loss
SHOW wal_buffers;  -- typically 64MB
 
-- For synchronous replication (extreme durability)
-- Commit blocks until replica confirms WAL receipt
SET synchronous_commit = 'remote_write';  -- replica received WAL
SET synchronous_commit = 'remote_apply';  -- replica applied WAL
 
-- Checkpoint behavior
-- More frequent checkpoints = faster recovery but lower performance
SHOW checkpoint_timeout;  -- default 5 minutes
SHOW max_wal_size;  -- triggers checkpoint when exceeded
 
-- View current WAL position and replication status
SELECT pg_current_wal_lsn();  -- current write-ahead log position
SELECT * FROM pg_stat_replication;  -- replica sync status