-- 创建测试表
CREATE TABLE user_scores (
    id INT PRIMARY KEY,
    user_id INT,
    score INT,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO user_scores VALUES (1, 101, 90), (2, 102, 85), (3, 103, 95);

-- 事务1
START TRANSACTION;
UPDATE user_scores SET score = score + 5 WHERE user_id = 101;
-- 只锁定user_id=101的行

-- 事务2
START TRANSACTION;
UPDATE user_scores SET score = score + 3 WHERE user_id = 102;
-- 可以正常执行，因为锁定的是不同的行

-- 显式表锁
LOCK TABLES user_scores WRITE;
-- 此时整个表被锁定，其他会话无法访问
UNLOCK TABLES;

-- 索引失效导致的表锁（WHERE条件不使用索引）
START TRANSACTION;
UPDATE user_scores SET score = 100 WHERE score > 80;
-- 如果score字段没有索引，会导致全表锁定

-- 创建测试表
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2),
    INDEX idx_price (price)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO products VALUES (1, 10.00), (2, 20.00), (3, 30.00), (5, 50.00);

-- 设置隔离级别为REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务1
START TRANSACTION;
SELECT * FROM products WHERE price BETWEEN 25 AND 40 FOR UPDATE;
-- 锁定price在25-40范围内的间隙，包括(20,30)、(30,50)的区间

-- 事务2
START TRANSACTION;
INSERT INTO products VALUES (4, 35.00); -- 会被阻塞，因为落入了间隙锁范围
INSERT INTO products VALUES (6, 15.00); -- 可以正常执行

-- 事务1
START TRANSACTION;
SELECT * FROM products WHERE price > 15 AND price < 45 FOR UPDATE;
-- 锁定相关记录和间隙

-- 事务2
START TRANSACTION;
INSERT INTO products VALUES (6, 18.00); -- 可能被阻塞
INSERT INTO products VALUES (7, 42.00); -- 可能被阻塞
UPDATE products SET price = 25 WHERE id = 2; -- 可能被阻塞

-- 创建测试表（score字段无索引）
CREATE TABLE user_scores (
 id INT PRIMARY KEY,
 user_id INT,
 score INT  -- 此字段没有索引
) ENGINE=InnoDB;
-- 事务1
START TRANSACTION;
UPDATE user_scores SET user_id = 100 WHERE score > 80;
-- 由于score字段无索引，InnoDB无法精确定位行，会进行全表扫描并锁定整个表
-- 事务2（在另一个会话中）
START TRANSACTION;
INSERT INTO user_scores VALUES (5, 105, 75);  -- 此操作将被阻塞！

-- 1. 为查询条件添加索引
ALTER TABLE user_scores ADD INDEX idx_score (score);

-- 2. 使用分批更新代替大批量操作
-- 不好的做法
UPDATE large_table SET status = 'processed' WHERE condition;

-- 好的做法：分批处理
CREATE PROCEDURE batch_update()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE batch_size INT DEFAULT 1000;
    
    WHILE NOT done DO
        UPDATE large_table SET status = 'processed' 
        WHERE condition AND status != 'processed' 
        LIMIT batch_size;
        
        IF ROW_COUNT() < batch_size THEN
            SET done = TRUE;
        END IF;
        
        COMMIT;  -- 定期提交释放锁
        DO SLEEP(0.1);  -- 短暂间隔
    END WHILE;
END;

-- 创建带自增主键的表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_data JSON,
    created_at TIMESTAMP
) ENGINE=InnoDB;

-- 并发插入场景
-- 会话1
INSERT INTO orders (order_data, created_at) 
VALUES ('{"product": "A", "qty": 2}', NOW());

-- 会话2（同时执行）
INSERT INTO orders (order_data, created_at) 
VALUES ('{"product": "B", "qty": 1}', NOW());

-- 自增锁确保两个插入获得不同的ID：1和2

-- 查看当前自增锁模式
SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

-- 不同模式的行为差异：

-- 模式0（传统模式）：
-- 每次插入都持有表锁直到语句完成
-- 最安全但性能最低

-- 模式1（连续模式，默认）：
-- 批量插入使用表锁，简单插入使用轻量级锁
INSERT INTO orders VALUES (NULL, 'data1', NOW()), (NULL, 'data2', NOW()); -- 可能用表锁
INSERT INTO orders (order_data) VALUES ('simple'); -- 用轻量级锁

-- 模式2（交错模式）：
-- 所有插入都使用轻量级锁，性能最好
-- 但自增值可能不连续（在statement-based复制中）

-- 1. 使用模式2获得最佳性能（如果使用row-based复制）
SET GLOBAL innodb_autoinc_lock_mode = 2;

-- 2. 避免批量插入时的锁竞争
-- 不好的做法：单条插入循环
START TRANSACTION;
FOR i IN 1..1000
    INSERT INTO log_table (message) VALUES (CONCAT('Log entry ', i));
END FOR;
COMMIT;

-- 好的做法：批量插入
START TRANSACTION;
INSERT INTO log_table (message) VALUES 
('Log entry 1'), ('Log entry 2'), ..., ('Log entry 1000');
COMMIT;

-- 3. 监控自增锁竞争
SHOW STATUS LIKE 'innodb_autoinc_lock%';
SELECT * FROM information_schema.INNODB_METRICS 
WHERE NAME LIKE 'lock_autoinc%';

-- 高并发订单系统优化
CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(20) NOT NULL UNIQUE,  -- 业务订单号
    -- 其他字段...
) ENGINE=InnoDB AUTO_INCREMENT = 1000000;

-- 使用批量插入减少锁竞争
INSERT INTO orders (order_no, ...) VALUES
('ORDER000001', ...),
('ORDER000002', ...),
('ORDER000003', ...);

-- 定期清理碎片化自增值（可选）
OPTIMIZE TABLE orders;

-- 会话1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 会话2
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;

-- 会话1
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 等待会话2释放锁

-- 会话2
UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- 死锁发生！

-- 查看最近死锁信息
SHOW ENGINE INNODB STATUS;

-- 设置死锁超时时间
SET SESSION innodb_lock_wait_timeout = 50;

-- 自动死锁检测（默认开启）
SET SESSION innodb_deadlock_detect = ON;

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

-- 查看正在等待锁的事务
SELECT * FROM information_schema.INNODB_TRX 
WHERE trx_state = 'LOCK WAIT';

-- 性能模式中的锁统计
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name 
WHERE EVENT_NAME LIKE '%wait/synch/mutex/innodb%';

-- 1. 确保使用索引
EXPLAIN SELECT * FROM large_table WHERE unindexed_column = 'value';

-- 2. 合理设计事务
-- 不好的做法：长事务
START TRANSACTION;
-- 多个复杂操作...
COMMIT;

-- 好的做法：短事务
-- 操作1
START TRANSACTION;
UPDATE table1 SET col1 = value1 WHERE id = 1;
COMMIT;

-- 操作2
START TRANSACTION;
UPDATE table2 SET col2 = value2 WHERE id = 2;
COMMIT;

-- 3. 使用合适的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 创建库存表
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    stock INT NOT NULL,
    version INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

-- 乐观锁实现
START TRANSACTION;
SELECT stock, version FROM inventory WHERE product_id = 1001;

-- 应用层判断库存是否充足
-- 如果充足，执行更新
UPDATE inventory 
SET stock = stock - 1, version = version + 1 
WHERE product_id = 1001 AND version = {刚才查询的version值};

-- 检查影响行数
SELECT ROW_COUNT();

-- 如果影响行数为0，说明版本号已变化，需要重试
COMMIT;

-- 不好的做法：逐行更新
START TRANSACTION;
UPDATE large_table SET status = 'processed' WHERE id = 1;
UPDATE large_table SET status = 'processed' WHERE id = 2;
-- ... 更多更新
COMMIT;

-- 好的做法：批量更新
START TRANSACTION;
UPDATE large_table SET status = 'processed' 
WHERE id IN (1, 2, 3, 4, 5) AND status = 'pending';
COMMIT;