第2章：事务与隔离级别原理

-- 查看当前活跃事务
SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.innodb_trx;

-- 查看事务历史
SELECT 
    thread_id,
    event_name,
    timer_start,
    timer_end,
    timer_wait
FROM performance_schema.events_transactions_current;

-- 查看长事务（超过60秒）
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

-- 查看Undo Log使用情况
SELECT 
    TABLESPACE_NAME,
    FILE_NAME,
    ROUND(INITIAL_SIZE / 1024 / 1024, 2) AS size_mb,
    STATUS
FROM information_schema.FILES 
WHERE TABLESPACE_NAME LIKE '%undo%';

-- 1. 准备阶段（Prepare）
XA START 'transaction_id';
UPDATE account SET balance = balance - 100 WHERE id = 1;
XA END 'transaction_id';
XA PREPARE 'transaction_id';

-- 2. 提交阶段（Commit）
XA COMMIT 'transaction_id';
-- 或回滚
-- XA ROLLBACK 'transaction_id';

-- 查看XA事务状态
SELECT 
    formatID,
    gtrid_length,
    bqual_length,
    data,
    state
FROM information_schema.innodb_xa;

-- 恢复未完成的XA事务
XA RECOVER;

-- 精确匹配主键
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 精确匹配唯一索引
SELECT * FROM users WHERE email = 'user@example.com' FOR UPDATE;

-- 自动加行锁的DML操作
UPDATE users SET name = 'new_name' WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- 假设表中有记录：1, 3, 5, 7
-- 执行范围查询
SELECT * FROM users WHERE id > 2 AND id < 6 FOR UPDATE;
-- 会锁定间隙：(1,3), (3,5), (5,7)
-- 防止插入 id=2,4,6 的记录

-- 假设表中有记录：1, 3, 5
-- 执行查询
SELECT * FROM users WHERE id >= 3 FOR UPDATE;
-- Next-Key Lock 锁定：
-- - 记录3（行锁）
-- - 间隙(1,3)（间隙锁）
-- - 记录5（行锁）
-- - 间隙(3,5)（间隙锁）

-- 查看当前锁等待情况
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 查看死锁历史
SHOW ENGINE INNODB STATUS\G
-- 关注：LATEST DETECTED DEADLOCK 部分

-- 查看死锁统计
SHOW STATUS LIKE 'Innodb_deadlocks';

-- 调整死锁检测参数
SET GLOBAL innodb_deadlock_detect = ON;  -- 启用死锁检测
SET GLOBAL innodb_lock_wait_timeout = 50; -- 锁等待超时时间

-- 监控死锁频率
SHOW STATUS LIKE 'Innodb_deadlocks';

-- 创建测试表
USE mysql_learning;

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    balance DECIMAL(10,2)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO accounts (name, balance) VALUES 
('张三', 1000.00),
('李四', 2000.00);

-- 查看当前数据
SELECT * FROM accounts;

-- 测试原子性：模拟转账操作
START TRANSACTION;

-- 从张三账户扣除100元
UPDATE accounts SET balance = balance - 100 WHERE name = '张三';

-- 向李四账户增加100元
UPDATE accounts SET balance = balance + 100 WHERE name = '李四';

-- 查看事务中的数据（其他会话看不到）
SELECT * FROM accounts;

-- 提交事务
COMMIT;

-- 再次查看数据
SELECT * FROM accounts;

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别（需要重新连接才能生效）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 测试 READ COMMITTED
-- 会话1：开始事务但不提交
START TRANSACTION;
UPDATE accounts SET balance = 1500 WHERE name = '张三';
-- 不要提交，保持事务开启

-- 会话2：查看数据（应该看到原始数据，因为会话1未提交）
SELECT * FROM accounts;

-- 会话1：提交事务
COMMIT;

-- 会话2：再次查看（现在应该看到更新后的数据）
SELECT * FROM accounts;

-- 创建测试表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    version INT DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO products (name, price) VALUES ('商品A', 100.00);

-- 会话1：开始事务并更新
START TRANSACTION;
UPDATE products SET price = 120.00, version = version + 1 WHERE id = 1;

-- 会话2：查看数据（应该看到原始价格，因为会话1未提交）
SELECT * FROM products WHERE id = 1;

-- 会话1：提交
COMMIT;

-- 会话2：现在应该看到更新后的价格
SELECT * FROM products WHERE id = 1;

-- 准备测试数据
INSERT INTO accounts (name, balance) VALUES 
('王五', 500.00),
('赵六', 800.00);

-- 会话1：按顺序锁定记录
START TRANSACTION;
SELECT * FROM accounts WHERE name = '张三' FOR UPDATE;
-- 等待几秒
SELECT SLEEP(2);
SELECT * FROM accounts WHERE name = '李四' FOR UPDATE;

-- 会话2：反向顺序锁定记录（同时执行）
START TRANSACTION;
SELECT * FROM accounts WHERE name = '李四' FOR UPDATE;
SELECT * FROM accounts WHERE name = '张三' FOR UPDATE;

-- 查看死锁信息
SHOW ENGINE INNODB STATUS\G

-- 设置锁等待超时时间
SET innodb_lock_wait_timeout = 10;

-- 会话1：锁定记录
START TRANSACTION;
SELECT * FROM accounts WHERE name = '张三' FOR UPDATE;

-- 会话2：尝试锁定同一记录（会等待）
SELECT * FROM accounts WHERE name = '张三' FOR UPDATE;
-- 10秒后会超时并报错

-- 查看当前锁信息
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

-- 查看 Binlog 格式
SHOW VARIABLES LIKE 'binlog_format';

-- 查看 Redo Log 状态
SHOW ENGINE INNODB STATUS\G

-- 执行事务并观察日志
START TRANSACTION;
UPDATE accounts SET balance = balance + 50 WHERE name = '张三';
COMMIT;

-- 查看 Binlog 内容
SHOW BINLOG EVENTS;

-- 创建银行账户表
CREATE TABLE bank_accounts (
    account_id VARCHAR(20) PRIMARY KEY,
    account_name VARCHAR(100),
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    version INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 创建转账记录表
CREATE TABLE transfer_records (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    from_account VARCHAR(20),
    to_account VARCHAR(20),
    amount DECIMAL(15,2),
    status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_from_account (from_account),
    INDEX idx_to_account (to_account)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO bank_accounts (account_id, account_name, balance) VALUES 
('ACC001', '张三', 10000.00),
('ACC002', '李四', 5000.00);

-- 转账存储过程
DELIMITER //
CREATE PROCEDURE transfer_money(
    IN p_from_account VARCHAR(20),
    IN p_to_account VARCHAR(20),
    IN p_amount DECIMAL(15,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 检查余额是否足够
    IF (SELECT balance FROM bank_accounts WHERE account_id = p_from_account) < p_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    
    -- 扣除转出账户余额
    UPDATE bank_accounts 
    SET balance = balance - p_amount,
        version = version + 1
    WHERE account_id = p_from_account;
    
    -- 增加转入账户余额
    UPDATE bank_accounts 
    SET balance = balance + p_amount,
        version = version + 1
    WHERE account_id = p_to_account;
    
    -- 记录转账记录
    INSERT INTO transfer_records (from_account, to_account, amount, status)
    VALUES (p_from_account, p_to_account, p_amount, 'success');
    
    COMMIT;
END //
DELIMITER ;

-- 测试转账
CALL transfer_money('ACC001', 'ACC002', 1000.00);

-- 查看结果
SELECT * FROM bank_accounts;
SELECT * FROM transfer_records;

-- 问题代码（会导致超卖）
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;

-- 方案1：使用悲观锁
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- 检查库存
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

-- 方案2：使用乐观锁
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND stock > 0 AND version = @current_version;

-- 方案3：使用原子操作
UPDATE products 
SET stock = GREATEST(stock - 1, 0) 
WHERE id = 1 AND stock > 0;