MySQL全局锁、表锁、行锁、间隙锁、临键锁超详细讲解

MySQL 锁机制超详细讲解

一、锁的分类概览

1.1 按锁的粒度分类

全局锁 → 数据库级别
表级锁  → 表级别
行级锁  → 记录级别
   ├── 记录锁 (Record Lock)
   ├── 间隙锁 (Gap Lock)
   └── 临键锁 (Next-Key Lock)

1.2 按锁的模式分类

• 共享锁 (S Lock)：读锁，允许多个事务同时读
• 排他锁 (X Lock)：写锁，只允许一个事务操作
• 意向锁：表级锁，表明事务打算在表中加行锁

二、全局锁 (Global Lock)

2.1 作用与使用场景

-- 加全局读锁
FLUSH TABLES WITH READ LOCK;

-- 释放锁
UNLOCK TABLES;

场景：全库逻辑备份，确保数据一致性

2.2 特点与注意事项

• 锁定整个数据库实例
• 所有表变为只读状态
• DDL、DML语句会被阻塞
• 谨慎使用，影响业务连续性
• InnoDB引擎推荐使用 mysqldump --single-transaction

三、表级锁 (Table Lock)

3.1 表锁分类

-- 手动加表锁
LOCK TABLES table_name READ;   -- 共享读锁
LOCK TABLES table_name WRITE;  -- 排他写锁

-- 释放表锁
UNLOCK TABLES;

3.2 MDL锁 (Metadata Lock)

-- 元数据锁，自动加锁
-- 当执行以下操作时自动添加：
ALTER TABLE ...        -- 等待所有事务提交
DROP TABLE ...         -- 等待所有事务提交
TRUNCATE TABLE ...     -- 等待所有事务提交

3.3 意向锁

-- 意向锁是表级锁，用于表明事务打算在表中加行锁
意向共享锁 (IS Lock)：事务打算给行加共享锁
意向排他锁 (IX Lock)：事务打算给行加排他锁

-- 锁兼容矩阵
     | X  | IX | S  | IS
-----|----|----|----|----
X    | 冲突 | 冲突 | 冲突 | 冲突
IX   | 冲突 | 兼容 | 冲突 | 兼容
S    | 冲突 | 冲突 | 兼容 | 兼容
IS   | 冲突 | 兼容 | 兼容 | 兼容

四、行级锁 (Row Lock)

4.1 记录锁 (Record Lock)

-- 锁定单条记录
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 只锁定id=1的记录

-- 加共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

4.2 间隙锁 (Gap Lock)

-- 锁定一个范围，但不包括记录本身
-- id列有索引：1, 3, 5, 7, 9

SELECT * FROM users WHERE id > 5 AND id < 9 FOR UPDATE;
-- 锁定范围：(5, 7) 和 (7, 9)
-- 防止其他事务插入id=6或id=8的记录

4.3 临键锁 (Next-Key Lock)

-- 记录锁 + 间隙锁
-- 默认的行锁实现方式
SELECT * FROM users WHERE id > 5 FOR UPDATE;
-- 锁定：
--   记录锁：id=7, id=9, ...
--   间隙锁：(5,7), (7,9), (9,+∞)

4.4 插入意向锁 (Insert Intention Lock)

-- 特殊的间隙锁，表示准备插入
INSERT INTO users (id, name) VALUES (6, 'test');
-- 会在(5,7)间隙上加插入意向锁

五、锁机制实战演示

5.1 场景一：记录锁演示

-- 会话1
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 持有id=1的排他锁

-- 会话2（会被阻塞）
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 等待会话1释放锁

5.2 场景二：间隙锁演示

-- 创建测试表
CREATE TABLE test_gap (
    id INT PRIMARY KEY,
    val INT,
    INDEX idx_val (val)
);

INSERT INTO test_gap VALUES (1,10), (3,20), (5,30), (7,40);

-- 会话1
START TRANSACTION;
SELECT * FROM test_gap WHERE val = 20 FOR UPDATE;
-- 锁定val=20的记录，并添加间隙锁(10,20)和(20,30)

-- 会话2（会被阻塞）
INSERT INTO test_gap VALUES (2, 15);  -- 值15在(10,20)范围内
INSERT INTO test_gap VALUES (4, 25);  -- 值25在(20,30)范围内

5.3 场景三：临键锁演示

-- 会话1
START TRANSACTION;
SELECT * FROM test_gap WHERE id > 3 FOR UPDATE;
-- 锁定：记录锁id=5,7 + 间隙锁(3,5),(5,7),(7,+∞)

-- 会话2（会被阻塞）
INSERT INTO test_gap VALUES (4, 35);  -- id=4在(3,5)范围内
INSERT INTO test_gap VALUES (6, 45);  -- id=6在(5,7)范围内
INSERT INTO test_gap VALUES (8, 55);  -- id=8在(7,+∞)范围内

六、死锁分析与解决

6.1 死锁示例

-- 会话1
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 持有id=1的锁

-- 会话2
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 2;
-- 持有id=2的锁

-- 会话1（尝试获取会话2持有的锁）
UPDATE users SET balance = balance + 100 WHERE id = 2;
-- 等待会话2释放id=2的锁

-- 会话2（尝试获取会话1持有的锁）
UPDATE users SET balance = balance + 100 WHERE id = 1;
-- 等待会话1释放id=1的锁
-- 产生死锁！

6.2 死锁检测与解决

-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

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

-- 自动死锁检测
SET GLOBAL innodb_deadlock_detect = ON;

-- 死锁避免策略：
-- 1. 按相同顺序访问表
-- 2. 使用更细粒度的事务
-- 3. 添加合适的索引
-- 4. 降低事务隔离级别

七、锁的查看与监控

7.1 查看当前锁信息

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

-- 查看当前会话的锁
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 查看等待锁的线程
SHOW PROCESSLIST;

7.2 性能监控脚本

-- 查看锁等待统计
SELECT 
    waiting_trx_id,
    waiting_pid,
    blocking_trx_id,
    blocking_pid
FROM sys.innodb_lock_waits;

-- 查看当前持有锁的SQL
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

八、最佳实践与优化建议

8.1 锁优化原则

尽量使用行锁，减少锁的粒度 合理设计索引，让查询使用合适的索引 控制事务大小，避免长事务 按相同顺序访问表，避免死锁 使用低隔离级别，如READ COMMITTED

8.2 SQL编写建议

-- 不好的写法：全表扫描导致表锁
UPDATE users SET status = 1 WHERE name LIKE '%test%';

-- 好的写法：使用索引字段
UPDATE users SET status = 1 WHERE id IN (SELECT id FROM temp_ids);

-- 避免锁升级：分批更新
UPDATE large_table SET col = val WHERE id BETWEEN 1 AND 1000;
UPDATE large_table SET col = val WHERE id BETWEEN 1001 AND 2000;

8.3 配置优化

# my.cnf配置建议
[mysqld]
# 锁等待超时时间（秒）
innodb_lock_wait_timeout = 50

# 死锁检测
innodb_deadlock_detect = ON

# 锁信息输出级别
innodb_status_output_locks = ON

# 事务隔离级别
transaction-isolation = READ-COMMITTED

九、隔离级别与锁的关系

隔离级别	脏读	不可重复读	幻读	锁机制
READ UNCOMMITTED	可能	可能	可能	不加锁
READ COMMITTED	不可能	可能	可能	记录锁
REPEATABLE READ	不可能	不可能	可能	记录锁+间隙锁
SERIALIZABLE	不可能	不可能	不可能	记录锁+间隙锁+表锁

十、总结

MySQL的锁机制是多并发环境下保证数据一致性的核心。理解不同锁的特性、使用场景和相互之间的关系，对于：

设计高性能数据库系统 避免锁冲突和死锁 优化SQL查询性能 保证数据一致性

至关重要。在实际应用中，应根据业务需求选择合适的锁策略，并配合适当的监控手段，确保数据库系统稳定高效运行。