-- 測試表結(jié)構(gòu)
CREATE TABLE account (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2),
    INDEX idx_balance (balance)
);

-- 測試數(shù)據(jù)
INSERT INTO account VALUES 
(1, 'Alice', 1000.00),
(2, 'Bob', 2000.00),
(3, 'Charlie', 3000.00);

if (trx_id == creator_trx_id) {
    // 本事務(wù)修改的，可見
    return true;
} else if (trx_id < min_trx_id) {
    // 事務(wù)已提交，可見
    return true;
} else if (trx_id >= max_trx_id) {
    // 事務(wù)還未開始，不可見
    return false;
} else if (trx_id in m_ids) {
    // 事務(wù)未提交，不可見
    return false;
} else {
    // 事務(wù)已提交，可見
    return true;
}

-- 表中存在記錄id=10,20,30
-- 事務(wù)A執(zhí)行：
SELECT * FROM table WHERE id > 15 FOR UPDATE;

-- 鎖定的范圍包括：
-- (10,20)間隙鎖
-- 20記錄鎖
-- (20,30)間隙鎖
-- 30記錄鎖
-- (30,+∞)間隙鎖

import java.sql.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class PhantomReadSolution {
    private static final String URL = "jdbc:mysql://localhost:3306/bank";
    private static final String USER = "root";
    private static final String PASSWORD = "password";
    
    public static void main(String[] args) {
        // 初始化測試數(shù)據(jù)
        initTestData();
        
        // 創(chuàng)建線程池模擬并發(fā)
        ExecutorService executor = Executors.newFixedThreadPool(2);
        
        // 事務(wù)A：檢查并更新高余額賬戶
        executor.execute(() -> {
            try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
                // 設(shè)置為可重復(fù)讀隔離級別
                conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
                conn.setAutoCommit(false);
                
                System.out.println("【事務(wù)A】開始，隔離級別：REPEATABLE_READ");
                
                // 第一次查詢：獲取高余額賬戶
                System.out.println("【事務(wù)A】第一次查詢：余額>1500的賬戶");
                queryHighBalanceAccounts(conn);
                
                // 模擬處理時間
                Thread.sleep(2000);
                
                // 第二次查詢：再次檢查
                System.out.println("【事務(wù)A】第二次查詢：余額>1500的賬戶");
                queryHighBalanceAccounts(conn);
                
                // 執(zhí)行更新操作
                System.out.println("【事務(wù)A】執(zhí)行更新：將高余額賬戶的余額增加10%");
                updateHighBalanceAccounts(conn);
                
                conn.commit();
                System.out.println("【事務(wù)A】提交事務(wù)");
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
        
        // 事務(wù)B：插入新賬戶
        executor.execute(() -> {
            try {
                // 讓事務(wù)A先開始
                Thread.sleep(500);
                
                try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
                    conn.setAutoCommit(false);
                    System.out.println("【事務(wù)B】開始");
                    
                    // 插入新賬戶
                    System.out.println("【事務(wù)B】插入新賬戶：David，余額1800");
                    PreparedStatement stmt = conn.prepareStatement(
                        "INSERT INTO account (name, balance) VALUES (?, ?)");
                    stmt.setString(1, "David");
                    stmt.setDouble(2, 1800.00);
                    stmt.executeUpdate();
                    
                    conn.commit();
                    System.out.println("【事務(wù)B】提交事務(wù)");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
        
        executor.shutdown();
    }
    
    private static void initTestData() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            Statement stmt = conn.createStatement();
            stmt.execute("DROP TABLE IF EXISTS account");
            stmt.execute("CREATE TABLE account (" +
                         "id INT AUTO_INCREMENT PRIMARY KEY," +
                         "name VARCHAR(50)," +
                         "balance DECIMAL(10,2)," +
                         "INDEX idx_balance (balance))");
            stmt.execute("INSERT INTO account (name, balance) VALUES " +
                         "('Alice', 1000.00), ('Bob', 2000.00), ('Charlie', 3000.00)");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    private static void queryHighBalanceAccounts(Connection conn) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(
            "SELECT id, name, balance FROM account WHERE balance > 1500");
        
        System.out.println("高余額賬戶列表：");
        while (rs.next()) {
            System.out.printf("id=%d, name=%s, balance=%.2f%n",
                rs.getInt("id"), rs.getString("name"), rs.getDouble("balance"));
        }
        rs.close();
        stmt.close();
    }
    
    private static void updateHighBalanceAccounts(Connection conn) throws SQLException {
        // 使用FOR UPDATE加鎖，防止幻讀影響更新操作
        Statement stmt = conn.createStatement();
        int count = stmt.executeUpdate(
            "UPDATE account SET balance = balance * 1.1 " +
            "WHERE balance > 1500");
        System.out.println("更新了 " + count + " 條記錄");
        stmt.close();
    }
}

START TRANSACTION;
-- 快照讀
SELECT * FROM account WHERE balance > 1500; -- 看到2條記錄

-- 其他事務(wù)插入新記錄并提交

-- 當(dāng)前讀
SELECT * FROM account WHERE balance > 1500 FOR UPDATE; -- 看到3條記錄
-- 此時事務(wù)內(nèi)看到了"幻影行"

-- 添加版本號字段
ALTER TABLE account ADD COLUMN version INT DEFAULT 0;

-- 樂觀鎖更新
UPDATE account 
SET balance = balance * 1.1, version = version + 1
WHERE balance > 1500 AND version = #{oldVersion};