pip install pymysql

import pymysql
import sys

# 數(shù)據(jù)庫配置（請根據(jù)你的實際情況修改）
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'your_password',
    'database': 'test_db',
    'charset': 'utf8mb4'
}

def setup_database(cursor):
    """初始化測試表和數(shù)據(jù)"""
    try:
        cursor.execute("DROP TABLE IF EXISTS accounts")
        cursor.execute("""
            CREATE TABLE accounts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50),
                balance DECIMAL(10, 2)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """)
        # 插入初始數(shù)據(jù)：Alice有1000元，Bob有500元
        cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00)")
        cursor.execute("INSERT INTO accounts (name, balance) VALUES ('Bob', 500.00)")
        print("? 數(shù)據(jù)庫初始化完成：Alice=1000, Bob=500")
    except Exception as e:
        print(f"? 初始化失敗: {e}")

def transfer_money_with_rollback(from_user, to_user, amount):
    """
    模擬轉(zhuǎn)賬業(yè)務(wù)，并在發(fā)生錯誤時回滾
    """
    connection = None
    try:
        # 1. 建立連接
        connection = pymysql.connect(**DB_CONFIG)
        
        # 2. 關(guān)鍵步驟：關(guān)閉自動提交，開啟事務(wù)
        connection.autocommit(False)
        
        with connection.cursor() as cursor:
            # --- 步驟一：扣款 ---
            print(f"\n?? 正在從 {from_user} 扣除 {amount} 元...")
            sql_deduct = "UPDATE accounts SET balance = balance - %s WHERE name = %s"
            cursor.execute(sql_deduct, (amount, from_user))
            
            # 模擬查詢扣款后的余額（僅為了演示，實際業(yè)務(wù)中可能不需要）
            cursor.execute("SELECT balance FROM accounts WHERE name = %s", (from_user,))
            result = cursor.fetchone()
            print(f"   ?? 扣款后查詢 {from_user} 余額: {result[0]} (此時數(shù)據(jù)在內(nèi)存/Redo Log中，未永久落盤)")

            # --- 步驟二：模擬突發(fā)異常 ---
            # 比如：此時服務(wù)器斷電、網(wǎng)絡(luò)中斷、或者代碼邏輯錯誤
            print("??  模擬系統(tǒng)崩潰：準(zhǔn)備加款時發(fā)生除零錯誤！")
            error_simulation = 1 / 0  # 故意制造一個異常
            
            # --- 步驟三：加款（正常情況下會執(zhí)行，但上面報錯了就不會走到這） ---
            sql_add = "UPDATE accounts SET balance = balance + %s WHERE name = %s"
            cursor.execute(sql_add, (amount, to_user))
            
            # 3. 如果一切順利，提交事務(wù)
            connection.commit()
            print("? 轉(zhuǎn)賬成功，事務(wù)已提交！")

    except Exception as e:
        print(f"\n? 發(fā)生嚴(yán)重錯誤: {e}")
        if connection:
            # 4. 核心：發(fā)生任何異常，回滾所有操作
            print("?? 正在執(zhí)行回滾操作 (ROLLBACK)...")
            connection.rollback()
            print("??? 回滾成功！數(shù)據(jù)已恢復(fù)到事務(wù)開始前的狀態(tài)。")
            
    finally:
        if connection:
            # 5. 恢復(fù)自動提交模式并關(guān)閉連接
            connection.autocommit(True)
            connection.close()

def check_final_balance():
    """檢查最終結(jié)果"""
    conn = pymysql.connect(**DB_CONFIG)
    with conn.cursor() as cursor:
        cursor.execute("SELECT name, balance FROM accounts")
        results = cursor.fetchall()
        print("\n----- 最終賬戶余額 -----")
        for row in results:
            print(f"用戶: {row[0]}, 余額: {row[1]}")
        print("------------------------")
        # 驗證結(jié)果
        alice_balance = results[0][1] if results[0][0] == 'Alice' else results[1][1]
        bob_balance = results[0][1] if results[0][0] == 'Bob' else results[1][1]
        assert alice_balance == 1000, f"Alice余額錯誤！期望1000，實際{alice_balance}"
        assert bob_balance == 500, f"Bob余額錯誤！期望500，實際{bob_balance}"
        print("?? 驗證通過：數(shù)據(jù)一致，回滾生效！")
    conn.close()

if __name__ == "__main__":
    # 初始化
    conn_init = pymysql.connect(**DB_CONFIG)
    with conn_init.cursor() as cur:
        setup_database(cur)
        conn_init.commit()
    conn_init.close()

    # 執(zhí)行帶回滾的轉(zhuǎn)賬
    transfer_money_with_rollback('Alice', 'Bob', 200)

    # 檢查最終數(shù)據(jù)是否正確回滾
    check_final_balance()