-- MySQL事務(wù)隔離級別（從低到高）
-- 1. READ UNCOMMITTED（讀未提交）
-- 2. READ COMMITTED（讀已提交）
-- 3. REPEATABLE READ（可重復(fù)讀）-- MySQL默認級別
-- 4. SERIALIZABLE（串行化）

/**
 * 并發(fā)問題分類：
 * 1. 臟讀（Dirty Read）：讀取到其他事務(wù)未提交的數(shù)據(jù)
 * 2. 不可重復(fù)讀（Non-Repeatable Read）：同一事務(wù)內(nèi)兩次讀取結(jié)果不一致
 * 3. 幻讀（Phantom Read）：同一事務(wù)內(nèi)兩次查詢結(jié)果集不一致
 * 4. 丟失更新（Lost Update）：兩個事務(wù)更新同一數(shù)據(jù)，后提交的覆蓋了先提交的
 */

-- 設(shè)置隔離級別為讀未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 示例：臟讀問題演示
-- 事務(wù)A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
-- 此時 balance 已修改但未提交
-- 事務(wù)B（在另一個連接）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM account WHERE user_id = 1; 
-- 會讀取到事務(wù)A未提交的修改（臟讀）
-- 如果事務(wù)A回滾，事務(wù)B讀取的數(shù)據(jù)就是錯誤的

-- 設(shè)置隔離級別為讀已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 示例：不可重復(fù)讀問題演示
-- 事務(wù)A
START TRANSACTION;
SELECT balance FROM account WHERE user_id = 1;
-- 第一次讀?。篵alance = 1000
-- 事務(wù)B
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
COMMIT; -- 提交修改
-- 事務(wù)A再次讀取
SELECT balance FROM account WHERE user_id = 1;
-- 第二次讀?。篵alance = 900（不可重復(fù)讀）
COMMIT;

-- MySQL在READ COMMITTED下的實現(xiàn)
-- 每行數(shù)據(jù)有隱藏字段：
-- DB_TRX_ID：最后修改該記錄的事務(wù)ID
-- DB_ROLL_PTR：回滾指針，指向undo log中的舊版本
-- DB_ROW_ID：行ID（隱藏主鍵）
-- 事務(wù)可見性規(guī)則：
-- 1. 版本號小于當前事務(wù)ID的記錄
-- 2. 刪除版本號未定義或大于當前事務(wù)ID
-- 3. 在READ COMMITTED下，每次查詢都重新生成ReadView

-- MySQL默認隔離級別
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 示例：解決不可重復(fù)讀
-- 事務(wù)A
START TRANSACTION;
SELECT balance FROM account WHERE user_id = 1;
-- 第一次讀取：balance = 1000
-- 事務(wù)B
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
COMMIT;
-- 事務(wù)A再次讀取
SELECT balance FROM account WHERE user_id = 1;
-- 第二次讀?。篵alance = 1000（可重復(fù)讀，讀取的是快照）
COMMIT;

-- 事務(wù)A：統(tǒng)計賬戶數(shù)量
START TRANSACTION;
SELECT COUNT(*) FROM account WHERE balance > 0;
-- 返回：5
-- 事務(wù)B：插入新賬戶
START TRANSACTION;
INSERT INTO account(user_id, balance) VALUES (6, 500);
COMMIT;
-- 事務(wù)A：再次統(tǒng)計
SELECT COUNT(*) FROM account WHERE balance > 0;
-- 在REPEATABLE READ下，返回仍然是：5（沒有幻讀）
-- 但如果執(zhí)行UPDATE/INSERT，可能會看到"幻影行"

-- 事務(wù)A
START TRANSACTION;
-- 使用SELECT ... FOR UPDATE添加間隙鎖
SELECT * FROM account WHERE id > 100 FOR UPDATE;
-- 這會鎖定id>100的所有記錄和間隙
-- 事務(wù)B試圖插入
INSERT INTO account(id, user_id) VALUES (101, 6);
-- 會被阻塞，直到事務(wù)A提交
-- 查看當前鎖信息
SHOW ENGINE INNODB STATUS;

-- 串行化隔離級別
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 所有SELECT語句都會隱式添加LOCK IN SHARE MODE
-- 事務(wù)A
START TRANSACTION;
SELECT balance FROM account WHERE user_id = 1;
-- 自動加共享鎖
-- 事務(wù)B
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
-- 會被阻塞，直到事務(wù)A提交

-- InnoDB MVCC數(shù)據(jù)結(jié)構(gòu)示例
CREATE TABLE account (
    id INT PRIMARY KEY,
    user_id INT,
    balance DECIMAL(10,2),
    -- 隱藏字段
    -- DB_TRX_ID: 6字節(jié)，最后修改事務(wù)ID
    -- DB_ROLL_PTR: 7字節(jié)，回滾指針
    -- DB_ROW_ID: 6字節(jié)，隱藏主鍵
    -- DELETE BIT: 刪除標記
);
-- ReadView創(chuàng)建時機：
-- READ COMMITTED: 每次SELECT都創(chuàng)建新的ReadView
-- REPEATABLE READ: 第一次SELECT時創(chuàng)建ReadView，整個事務(wù)復(fù)用
-- 可見性判斷算法：
-- 1. 如果DB_TRX_ID < up_limit_id，可見（事務(wù)開始前已提交）
-- 2. 如果DB_TRX_ID >= low_limit_id，不可見（事務(wù)開始后開始的）
-- 3. 如果DB_TRX_ID在活躍事務(wù)列表中，不可見（未提交）
-- 4. 否則可見

-- 假設(shè)原始數(shù)據(jù)
-- id=1, balance=1000, DB_TRX_ID=10
-- 事務(wù)20修改
UPDATE account SET balance = 900 WHERE id = 1;
-- 新版本：balance=900, DB_TRX_ID=20, 回滾指針指向舊版本
-- 事務(wù)30修改
UPDATE account SET balance = 800 WHERE id = 1;
-- 新版本：balance=800, DB_TRX_ID=30, 回滾指針指向事務(wù)20的版本
-- 版本鏈：
-- 當前版本（事務(wù)30） ← 事務(wù)20版本 ← 事務(wù)10版本

-- 問題場景：高并發(fā)下庫存扣減
-- 錯誤做法（存在超賣風險）
START TRANSACTION;
SELECT stock FROM product WHERE id = 1;
-- 假設(shè)stock = 10
IF stock > 0 THEN
    UPDATE product SET stock = stock - 1 WHERE id = 1;
END IF;
COMMIT;
-- 解決方案1：使用SELECT ... FOR UPDATE（悲觀鎖）
START TRANSACTION;
-- 加行鎖，阻止其他事務(wù)讀取
SELECT stock FROM product WHERE id = 1 FOR UPDATE;
-- 此時其他事務(wù)的SELECT ... FOR UPDATE會被阻塞
IF stock > 0 THEN
    UPDATE product SET stock = stock - 1 WHERE id = 1;
END IF;
COMMIT;
-- 解決方案2：使用樂觀鎖（版本控制）
ALTER TABLE product ADD version INT DEFAULT 0;
START TRANSACTION;
SELECT stock, version FROM product WHERE id = 1;
-- 假設(shè)stock=10, version=1
IF stock > 0 THEN
    UPDATE product SET 
        stock = stock - 1,
        version = version + 1 
    WHERE id = 1 AND version = 1;
    -- 如果影響行數(shù)為0，說明版本已變，需要重試
END IF;
COMMIT;
-- 解決方案3：直接UPDATE判斷
START TRANSACTION;
UPDATE product SET stock = stock - 1 
WHERE id = 1 AND stock > 0;
-- 返回影響行數(shù)，如果為0表示庫存不足
COMMIT;

-- 場景：A向B轉(zhuǎn)賬，需要保證原子性和一致性
-- 問題：并發(fā)轉(zhuǎn)賬可能導(dǎo)致余額錯誤
-- 解決方案：使用SERIALIZABLE隔離級別或精心設(shè)計的事務(wù)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 關(guān)鍵：按固定順序加鎖，避免死鎖
-- 總是先鎖id小的賬戶
SELECT * FROM account 
WHERE id IN (1, 2) 
ORDER BY id 
FOR UPDATE;
-- 檢查A賬戶余額
SELECT balance FROM account WHERE id = 1;
-- 執(zhí)行轉(zhuǎn)賬
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 記錄流水
INSERT INTO transfer_log(from_id, to_id, amount) VALUES (1, 2, 100);
COMMIT;

-- 場景：生成財務(wù)報表時，數(shù)據(jù)被其他事務(wù)修改
-- 要求：統(tǒng)計期間數(shù)據(jù)必須一致
-- 解決方案1：使用REPEATABLE READ + 開始時間點
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 記錄開始時間
SET @report_time = NOW();
-- 統(tǒng)計邏輯（所有查詢看到的是同一時間點的快照）
SELECT SUM(balance) FROM account;
SELECT COUNT(*) FROM transfer_log WHERE create_time < @report_time;
COMMIT;
-- 解決方案2：使用備份或從庫查詢
-- 在從庫上使用REPEATABLE READ，不影響主庫性能
START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... 統(tǒng)計查詢
COMMIT;

-- 場景：消息重復(fù)消費，需要保證冪等性
-- 問題：重復(fù)處理同一消息
CREATE TABLE message_consumed (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    message_id VARCHAR(64) UNIQUE,  -- 消息唯一ID
    status TINYINT DEFAULT 0,
    consume_time DATETIME
);
-- 消費消息時的冪等處理
START TRANSACTION;
-- 方案1：先插入，利用唯一索引保證冪等
INSERT IGNORE INTO message_consumed(message_id, consume_time) 
VALUES ('msg_123', NOW());
-- 如果插入成功（影響行數(shù)>0），則處理消息
IF ROW_COUNT() > 0 THEN
    -- 執(zhí)行業(yè)務(wù)邏輯
    CALL process_business_logic('msg_123');
    UPDATE message_consumed 
    SET status = 1 
    WHERE message_id = 'msg_123';
END IF;
COMMIT;

-- 事務(wù)A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 持有id=1的鎖
-- 事務(wù)B（同時執(zhí)行）
START TRANSACTION;
UPDATE account SET balance = balance - 200 WHERE id = 2;
-- 持有id=2的鎖
-- 事務(wù)A繼續(xù)
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 等待事務(wù)B釋放id=2的鎖
-- 事務(wù)B繼續(xù)
UPDATE account SET balance = balance + 200 WHERE id = 1;
-- 等待事務(wù)A釋放id=1的鎖
-- 死鎖發(fā)生！

-- 查看死鎖日志
SHOW ENGINE INNODB STATUS;
-- 死鎖日志示例：
-- LATEST DETECTED DEADLOCK
-- *** (1) TRANSACTION:
-- TRANSACTION 3100, ACTIVE 2 sec starting index read
-- mysql tables in use 1, locked 1
-- LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
-- MySQL thread id 10, OS thread handle 1234, query id 100 updating
-- UPDATE account SET balance = balance + 100 WHERE id = 2
-- 預(yù)防死鎖策略：
-- 1. 按相同順序訪問資源
-- 2. 減少事務(wù)執(zhí)行時間
-- 3. 使用低隔離級別（READ COMMITTED）
-- 4. 添加合理的索引，減少鎖范圍
-- 代碼層面的解決方案
START TRANSACTION;
-- 總是按id順序加鎖
SELECT * FROM account 
WHERE id IN (1, 2) 
ORDER BY id 
FOR UPDATE;
-- 執(zhí)行更新操作
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 間隙鎖導(dǎo)致的死鎖場景
-- 表結(jié)構(gòu)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    KEY idx_user_id (user_id)
);
-- 事務(wù)A
START TRANSACTION;
-- 對user_id=100加間隙鎖（鎖定100-200之間的間隙）
SELECT * FROM orders WHERE user_id = 150 FOR UPDATE;
-- 事務(wù)B
START TRANSACTION;
-- 對user_id=200加間隙鎖（鎖定100-200之間的間隙）
SELECT * FROM orders WHERE user_id = 180 FOR UPDATE;
-- 事務(wù)A嘗試插入
INSERT INTO orders(id, user_id) VALUES (1, 160);
-- 等待事務(wù)B的間隙鎖
-- 事務(wù)B嘗試插入
INSERT INTO orders(id, user_id) VALUES (2, 170);
-- 等待事務(wù)A的間隙鎖
-- 死鎖！

-- 選擇合適隔離級別的決策流程
/**
 * 決策樹：
 * 1. 需要避免臟讀？是 → 至少READ COMMITTED
 * 2. 需要避免不可重復(fù)讀？是 → 至少REPEATABLE READ
 * 3. 需要避免幻讀？是 → SERIALIZABLE
 * 4. 并發(fā)性能要求高？是 → 考慮降低隔離級別
 * 5. 有明確的鎖策略？是 → 可以使用較低隔離級別+手動加鎖
 */
-- 各隔離級別適用場景總結(jié)：
-- READ UNCOMMITTED: 統(tǒng)計類只讀查詢，允許臟數(shù)據(jù)
-- READ COMMITTED: 大多數(shù)OLTP系統(tǒng)，需要實時數(shù)據(jù)
-- REPEATABLE READ: 財務(wù)系統(tǒng)，對賬系統(tǒng)，需要穩(wěn)定視圖
-- SERIALIZABLE: 銀行核心交易，需要絕對一致性

-- 實踐1：保持事務(wù)短小
-- 錯誤示例：長事務(wù)
START TRANSACTION;
-- 復(fù)雜業(yè)務(wù)邏輯
-- 網(wǎng)絡(luò)調(diào)用
-- 文件操作
-- 大量計算
COMMIT; -- 事務(wù)持續(xù)時間太長
-- 正確示例：拆分事務(wù)
-- 事務(wù)1：數(shù)據(jù)準備
START TRANSACTION;
INSERT INTO temp_data SELECT ...;
COMMIT;
-- 事務(wù)2：業(yè)務(wù)處理
START TRANSACTION;
UPDATE ...;
COMMIT;
-- 實踐2：避免在事務(wù)中執(zhí)行SELECT ... FOR UPDATE時掃描大量數(shù)據(jù)
-- 錯誤示例
START TRANSACTION;
SELECT * FROM orders WHERE create_time > '2023-01-01' FOR UPDATE;
-- 可能鎖定大量行，導(dǎo)致性能問題
-- 正確示例：分批處理
DECLARE done INT DEFAULT FALSE;
DECLARE batch_size INT DEFAULT 100;
DECLARE offset INT DEFAULT 0;
WHILE NOT done DO
    START TRANSACTION;
    SELECT * FROM orders 
    WHERE create_time > '2023-01-01' 
    LIMIT batch_size OFFSET offset 
    FOR UPDATE;
    -- 處理邏輯
    COMMIT;
    SET offset = offset + batch_size;
    -- 檢查是否完成
END WHILE;

-- 監(jiān)控當前事務(wù)
SELECT * FROM information_schema.INNODB_TRX;
-- 監(jiān)控鎖信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
-- 監(jiān)控長事務(wù)
SELECT 
    trx_id,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_seconds,
    trx_state,
    trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60  -- 超過60秒的事務(wù)
ORDER BY trx_started;
-- 設(shè)置長事務(wù)超時
SET SESSION innodb_lock_wait_timeout = 50;  -- 鎖等待超時50秒
SET SESSION innodb_rollback_on_timeout = ON; -- 超時自動回滾

-- 秒殺場景下的庫存扣減優(yōu)化
-- 表設(shè)計
CREATE TABLE seckill_stock (
    product_id BIGINT PRIMARY KEY,
    stock INT NOT NULL,
    version INT DEFAULT 0,
    sale_date DATE
);
-- 方案1：使用樂觀鎖+重試機制
DELIMITER //
CREATE PROCEDURE seckill_purchase(
    IN p_product_id BIGINT,
    IN p_user_id BIGINT,
    OUT p_result INT
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_version INT;
    DECLARE retry_count INT DEFAULT 0;
    DECLARE max_retry INT DEFAULT 3;
    purchase_retry: REPEAT
        START TRANSACTION;
        -- 查詢當前庫存和版本
        SELECT stock, version INTO v_stock, v_version
        FROM seckill_stock 
        WHERE product_id = p_product_id 
        FOR UPDATE;  -- 悲觀鎖，防止其他事務(wù)同時修改
        IF v_stock <= 0 THEN
            ROLLBACK;
            SET p_result = 0; -- 庫存不足
            LEAVE purchase_retry;
        END IF;
        -- 更新庫存
        UPDATE seckill_stock 
        SET stock = stock - 1,
            version = version + 1
        WHERE product_id = p_product_id 
          AND version = v_version;
        -- 檢查是否更新成功
        IF ROW_COUNT() = 1 THEN
            -- 創(chuàng)建訂單
            INSERT INTO seckill_order(product_id, user_id, create_time)
            VALUES (p_product_id, p_user_id, NOW());
            COMMIT;
            SET p_result = 1; -- 成功
            LEAVE purchase_retry;
        ELSE
            ROLLBACK;
            SET retry_count = retry_count + 1;
            -- 等待隨機時間后重試
            DO SLEEP(RAND() * 0.1);
        END IF;
    UNTIL retry_count >= max_retry END REPEAT;
    IF retry_count >= max_retry THEN
        SET p_result = -1; -- 重試失敗
    END IF;
END//
DELIMITER ;

-- 對賬系統(tǒng)需要數(shù)據(jù)一致性
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 記錄對賬開始時間
SET @reconcile_time = NOW();
-- 創(chuàng)建對賬快照表
CREATE TEMPORARY TABLE reconcile_snapshot AS
SELECT 
    a.account_no,
    a.balance as db_balance,
    b.balance as external_balance
FROM account a
LEFT JOIN external_system b ON a.account_no = b.account_no
WHERE a.update_time <= @reconcile_time;
-- 執(zhí)行對賬邏輯
SELECT 
    account_no,
    db_balance,
    external_balance,
    CASE 
        WHEN ABS(db_balance - external_balance) > 0.01 THEN 'MISMATCH'
        ELSE 'MATCH'
    END as status
FROM reconcile_snapshot;
-- 記錄對賬結(jié)果
INSERT INTO reconcile_log(reconcile_time, total_count, mismatch_count)
SELECT 
    @reconcile_time,
    COUNT(*),
    SUM(CASE WHEN ABS(db_balance - external_balance) > 0.01 THEN 1 ELSE 0 END)
FROM reconcile_snapshot;
COMMIT;

-- 點贊功能，需要避免重復(fù)點贊
CREATE TABLE likes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    post_id BIGINT NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_post (user_id, post_id)  -- 唯一約束防止重復(fù)
);
-- 點贊操作
START TRANSACTION;
-- 嘗試插入，利用唯一約束保證冪等性
INSERT IGNORE INTO likes(user_id, post_id) 
VALUES (123, 456);
-- 如果插入成功，更新帖子點贊數(shù)
IF ROW_COUNT() > 0 THEN
    UPDATE posts 
    SET like_count = like_count + 1 
    WHERE id = 456;
END IF;
COMMIT;
-- 取消點贊
START TRANSACTION;
DELETE FROM likes 
WHERE user_id = 123 AND post_id = 456;
-- 如果刪除成功，更新帖子點贊數(shù)
IF ROW_COUNT() > 0 THEN
    UPDATE posts 
    SET like_count = GREATEST(0, like_count - 1) 
    WHERE id = 456;
END IF;
COMMIT;

-- MySQL默認autocommit=1，每條語句都是一個事務(wù)
-- 可能導(dǎo)致意想不到的問題
-- 關(guān)閉自動提交
SET autocommit = 0;
-- 顯式控制事務(wù)
START TRANSACTION;
-- 業(yè)務(wù)邏輯
COMMIT; -- 或 ROLLBACK;
-- 恢復(fù)自動提交
SET autocommit = 1;

-- 以下語句會隱式提交當前事務(wù)：
-- 1. DDL語句（CREATE, ALTER, DROP等）
-- 2. 用戶權(quán)限管理語句
-- 3. 鎖表語句（LOCK TABLES, UNLOCK TABLES）
-- 錯誤示例
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 這個語句會提交事務(wù)！
CREATE INDEX idx_balance ON account(balance);
UPDATE account SET balance = balance + 100 WHERE id = 2;
-- 如果這里出錯，第一個UPDATE已經(jīng)提交，無法回滾！
COMMIT;

-- 大事務(wù)可能導(dǎo)致的問題：
-- 1. 鎖持有時間長，阻塞其他事務(wù)
-- 2. 產(chǎn)生大量undo log，占用磁盤
-- 3. 主從復(fù)制延遲
-- 4. 回滾時間長
-- 解決方案：拆分大事務(wù)
-- 原始大事務(wù)
START TRANSACTION;
-- 處理10萬條數(shù)據(jù)
UPDATE large_table SET status = 1 WHERE condition;
COMMIT; -- 可能執(zhí)行幾分鐘
-- 拆分為小批次
SET autocommit = 0;
SET @rows_affected = 1;
WHILE @rows_affected > 0 DO
    START TRANSACTION;
    UPDATE large_table 
    SET status = 1 
    WHERE condition 
    AND status = 0 
    LIMIT 1000;
    SET @rows_affected = ROW_COUNT();
    COMMIT;
    -- 短暫暫停，減少對系統(tǒng)影響
    DO SLEEP(0.1);
END WHILE;
SET autocommit = 1;