MySQL事务ACID实现原理深度解析

-- 示例：更新操作生成的undo log
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- 此时生成undo log记录旧余额

UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
-- 此时生成undo log记录旧余额

-- 如果此时系统故障或执行ROLLBACK
-- InnoDB使用undo log恢复两个账户的余额

COMMIT;

-- 查看InnoDB状态信息，包括检查点位置
SHOW ENGINE INNODB STATUS;

-- 事务1
START TRANSACTION;
SELECT stock FROM products WHERE id = 1; -- 返回100
UPDATE products SET stock = stock - 1 WHERE id = 1;

-- 同时，事务2
START TRANSACTION;
SELECT stock FROM products WHERE id = 1; -- 在RR级别下，仍看到100
UPDATE products SET stock = stock - 1 WHERE id = 1; -- 等待锁

-- 事务1提交后，事务2继续执行
-- 此时InnoDB会重新读取最新值(99)并执行更新

-- 主键约束
ALTER TABLE accounts ADD PRIMARY KEY (id);

-- 外键约束
ALTER TABLE transactions 
ADD CONSTRAINT fk_account_id 
FOREIGN KEY (account_id) REFERENCES accounts(id);

-- 唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 检查约束（MySQL 8.0+）
ALTER TABLE accounts 
ADD CONSTRAINT chk_balance 
CHECK (balance >= 0);

CREATE TRIGGER check_balance_before_update
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
    IF NEW.balance < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '余额不足';
    END IF;
END;

-- 完整的事务示例
START TRANSACTION;

-- 检查账户A余额是否足够
SELECT balance INTO @current_balance FROM accounts WHERE id = 'A' FOR UPDATE;

IF @current_balance >= 100 THEN
    -- 扣减账户A余额
    UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
    
    -- 增加账户B余额
    UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
    
    -- 记录交易流水
    INSERT INTO transactions (from_account, to_account, amount, time)
    VALUES ('A', 'B', 100, NOW());
    
    COMMIT;
ELSE
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
END IF;

-- 查看AHI状态
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

-- 查看change buffer状态
SHOW ENGINE INNODB STATUS\G
-- 在输出中查找INSERT BUFFER AND ADAPTIVE HASH INDEX部分

-- 查看当前运行的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看事务历史
SELECT * FROM information_schema.INNODB_TRX_HISTORY;

-- 查看最近死锁信息
SHOW ENGINE INNODB STATUS\G
-- 在LATEST DETECTED DEADLOCK部分查看详情

-- 死锁预防策略
-- 1. 按固定顺序访问表
-- 2. 使用较低的隔离级别
-- 3. 为事务添加超时时间
-- 4. 避免长事务

-- XA事务示例
XA START 'xid1';
INSERT INTO account_transactions VALUES (1, 100);
XA END 'xid1';
XA PREPARE 'xid1';
XA COMMIT 'xid1';

-- 优化后的库存扣减方案
START TRANSACTION;

-- 使用悲观锁锁定行
SELECT stock FROM products WHERE id = 1001 FOR UPDATE;

-- 直接更新，避免先查后改的竞态条件
UPDATE products SET stock = stock - 1 
WHERE id = 1001 AND stock > 0;

-- 检查是否更新成功
IF ROW_COUNT() > 0 THEN
    INSERT INTO orders (product_id, user_id, create_time)
    VALUES (1001, 123, NOW());
    COMMIT;
ELSE
    ROLLBACK;
END IF;

-- 银行转账的完整ACID保障
START TRANSACTION;

-- 使用悲观锁确保一致性
SELECT balance FROM accounts WHERE id = 'A' FOR UPDATE;
SELECT balance FROM accounts WHERE id = 'B' FOR UPDATE;

-- 验证业务条件
SELECT balance INTO @balance_a FROM accounts WHERE id = 'A';
IF @balance_a >= 100 THEN
    -- 执行转账操作
    UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
    UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
    
    -- 记录审计日志
    INSERT INTO transfer_logs (from_acc, to_acc, amount, time)
    VALUES ('A', 'B', 100, NOW());
    
    COMMIT;
ELSE
    ROLLBACK;
    -- 返回错误信息
END IF;