MySQL间隙锁原理与幻读问题解决实战解析

-- 事务A
START TRANSACTION;
SELECT * FROM orders WHERE price BETWEEN 100 AND 200;
-- 返回3条记录
 
-- 此时事务B插入了一条新记录
-- INSERT INTO orders (id, price) VALUES (4, 150);
 
SELECT * FROM orders WHERE price BETWEEN 100 AND 200;
-- 返回4条记录（出现幻读）
COMMIT;

-- 假设表中有记录：10, 20, 30
SELECT * FROM t WHERE id > 15 AND id < 25 FOR UPDATE;

-- 创建商品库存表
CREATE TABLE inventory (
    id INT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    version INT DEFAULT 0,
    INDEX idx_product (product_id)
) ENGINE=InnoDB;
 
-- 插入测试数据
INSERT INTO inventory VALUES 
    (1, 100, 50, 0),
    (2, 200, 30, 0),
    (3, 300, 20, 0);

-- 事务A：检查并扣减库存
START TRANSACTION;
SELECT * FROM inventory WHERE product_id = 150;
-- 返回空，准备插入新记录
 
-- 事务B：同时执行相同操作
START TRANSACTION;
SELECT * FROM inventory WHERE product_id = 150;
-- 也返回空
 
-- 两个事务都认为需要插入新记录
INSERT INTO inventory (id, product_id, quantity) VALUES (4, 150, 100);
-- 导致主键冲突或数据重复

-- 事务A：使用FOR UPDATE加锁
START TRANSACTION;
SELECT * FROM inventory WHERE product_id = 150 FOR UPDATE;
-- 返回空，但锁定了间隙(100, 200)
 
-- 事务B：尝试插入
START TRANSACTION;
INSERT INTO inventory (id, product_id, quantity) VALUES (4, 150, 100);
-- 被阻塞，等待事务A释放间隙锁
 
-- 事务A完成操作
INSERT INTO inventory (id, product_id, quantity) VALUES (4, 150, 100);
COMMIT;
 
-- 事务B继续执行
-- 发现记录已存在，可以正确处理

-- 创建订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_amount (user_id, amount)
) ENGINE=InnoDB;

-- 业务需求：限制用户单日消费不超过10000元
 
DELIMITER $$
CREATE PROCEDURE check_and_create_order(
    IN p_user_id INT,
    IN p_amount DECIMAL(10,2)
)
BEGIN
    DECLARE total_amount DECIMAL(10,2);
    DECLARE exit handler for sqlexception
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 计算用户当日消费总额（加锁）
    SELECT COALESCE(SUM(amount), 0) INTO total_amount
    FROM orders
    WHERE user_id = p_user_id 
        AND DATE(created_at) = CURDATE()
        AND status = 'COMPLETED'
    FOR UPDATE;
    
    -- 检查是否超过限额
    IF total_amount + p_amount > 10000 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '超过单日消费限额';
    END IF;
    
    -- 创建订单
    INSERT INTO orders (user_id, amount, status)
    VALUES (p_user_id, p_amount, 'COMPLETED');
    
    COMMIT;
    
    SELECT '订单创建成功' AS result;
END$$
DELIMITER ;

-- 不好的设计：没有合适的索引
SELECT * FROM orders WHERE amount > 100 FOR UPDATE;
-- 可能锁定大量间隙
 
-- 优化后：创建复合索引
CREATE INDEX idx_amount_status ON orders(amount, status);
SELECT * FROM orders WHERE amount > 100 AND status = 'PENDING' FOR UPDATE;
-- 锁定范围更精确

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- 或在配置文件中设置
-- my.cnf
[mysqld]
transaction-isolation = READ-COMMITTED

-- 添加版本号字段
ALTER TABLE inventory ADD COLUMN version INT DEFAULT 0;
 
-- 更新时检查版本号
UPDATE inventory 
SET quantity = quantity - 10, 
    version = version + 1
WHERE product_id = 100 
    AND version = 0
    AND quantity >= 10;
 
-- 检查影响行数判断是否成功

-- 查看InnoDB锁等待情况
SELECT * FROM information_schema.INNODB_LOCKS;
 
-- 查看锁等待详情
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
 
-- 查看事务信息
SELECT * FROM information_schema.INNODB_TRX;

-- 启用性能模式
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%lock%';
 
-- 查看锁等待统计
SELECT * FROM performance_schema.data_lock_waits;

-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS\G
 
-- 在日志中查找死锁信息
-- 确保开启了innodb_print_all_deadlocks参数
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 为常用查询条件创建索引
CREATE INDEX idx_composite ON orders(user_id, status, created_at);
 
-- 避免过多索引影响写入性能
-- 定期分析索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 设置锁等待超时
SET GLOBAL innodb_lock_wait_timeout = 10;
 
-- 监控长事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;