MySQL-锁机制

为什么需要锁？

假设你的银行账户余额1000元，两笔转账同时到账：

没有锁保护的情况：

sql

-- 时间点1: 转账程序A读取余额
SELECT balance FROM account WHERE id = 123;  -- 读到1000元

-- 时间点2: 转账程序B也读取余额  
SELECT balance FROM account WHERE id = 123;  -- 也读到1000元

-- 时间点3: 程序A计算并更新(1000+500=1500)
UPDATE account SET balance = 1500 WHERE id = 123;

-- 时间点4: 程序B计算并更新(1000+300=1300)，覆盖了A的更新！
UPDATE account SET balance = 1300 WHERE id = 123;

结果：你损失了500元！

有锁保护的情况：

sql

-- 转账程序A
START TRANSACTION;
SELECT balance FROM account WHERE id = 123 FOR UPDATE;  -- 加排他锁，读到1000
UPDATE account SET balance = 1500 WHERE id = 123;
COMMIT;  -- 释放锁

-- 转账程序B（必须等A完成后才能执行）
START TRANSACTION;
SELECT balance FROM account WHERE id = 123 FOR UPDATE;  -- 读到1500
UPDATE account SET balance = 1800 WHERE id = 123;
COMMIT;

结果：余额正确为1800元

这就是锁存在的意义：保证并发操作的数据一致性。

MySQL 锁的分类

按锁粒度分类

全局锁：锁住整个MySQL实例
表锁：锁住单张表
行锁：锁住具体的数据行

按锁模式分类

共享锁(S) - 读锁，多个事务可同时持有
排他锁(X) - 写锁，只能一个事务持有

按实现算法分类

记录锁 - 锁定索引记录
间隙锁 - 锁定索引记录间的空隙
Next-Key锁 - 记录锁+间隙锁

全局锁

加锁语法

sql

FLUSH TABLES WITH READ LOCK;  -- 加全局读锁
-- 执行备份操作
UNLOCK TABLES;  -- 释放锁

实际演示

sql

-- 会话1：执行全局锁
FLUSH TABLES WITH READ LOCK;

-- 会话2：所有写操作被阻塞
INSERT INTO users (name) VALUES ('张三');    -- 阻塞等待
UPDATE users SET name = '李四' WHERE id = 1; -- 阻塞等待
DELETE FROM users WHERE id = 2;              -- 阻塞等待

-- 会话2：读操作正常
SELECT * FROM users;  -- 执行成功

现代备份方案

sql

# 推荐使用一致性备份，无需全局锁
mysqldump --single-transaction --master-data=2 database_name > backup.sql

表锁

MyISAM的基础，InnoDB的辅助

手动表锁

读锁演示

sql

-- 会话1：获取读锁
LOCK TABLES users READ;

SELECT * FROM users;                          -- 成功
INSERT INTO users (name) VALUES ('张三');     -- 失败：当前会话不能写
UPDATE users SET name = '李四' WHERE id = 1;  -- 失败：当前会话不能写

-- 会话2：其他会话的表现
SELECT * FROM users;                          -- 成功：可以读
INSERT INTO users (name) VALUES ('王五');     -- 阻塞：不能写

-- 会话1：释放锁
UNLOCK TABLES;

写锁演示

sql

-- 会话1：获取写锁
LOCK TABLES users WRITE;

SELECT * FROM users;                          -- 成功：当前会话可读写
INSERT INTO users (name) VALUES ('张三');     -- 成功：当前会话可读写

-- 会话2：其他会话完全被阻塞
SELECT * FROM users;                          -- 阻塞等待
INSERT INTO users (name) VALUES ('李四');     -- 阻塞等待

-- 会话1：释放锁
UNLOCK TABLES;

自动表锁场景

MyISAM引擎

sql

-- MyISAM表的所有操作都自动使用表锁
SELECT * FROM myisam_table;                   -- 自动加读锁
UPDATE myisam_table SET name = '张三';        -- 自动加写锁

InnoDB的DDL操作

sql

-- 这些DDL操作会使用表锁
ALTER TABLE users ADD COLUMN email VARCHAR(100);
CREATE INDEX idx_name ON users(name);
DROP INDEX idx_name ON users;

行锁

InnoDB的核心优势

共享锁(读锁)

sql

-- 会话1：获取共享锁
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR SHARE;  -- 或 LOCK IN SHARE MODE

-- 会话2：可以读，不能写
SELECT * FROM users WHERE id = 1;            -- 成功：可以读
SELECT * FROM users WHERE id = 1 FOR SHARE;  -- 成功：可以加共享锁
UPDATE users SET name = '张三' WHERE id = 1; -- 阻塞：不能写

-- 会话1：提交释放锁
COMMIT;

排他锁(写锁)

sql

-- 会话1：获取排他锁
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 会话2：读写都被阻塞
SELECT * FROM users WHERE id = 1;            -- 阻塞等待
SELECT * FROM users WHERE id = 1 FOR SHARE;  -- 阻塞等待  
UPDATE users SET name = '张三' WHERE id = 1; -- 阻塞等待

-- 会话1：提交释放锁
COMMIT;

自动行锁

sql

-- 这些DML操作自动获取排他锁
UPDATE users SET name = '张三' WHERE id = 1;  -- 自动对id=1加排他锁
DELETE FROM users WHERE id = 1;               -- 自动对id=1加排他锁
INSERT INTO users (name) VALUES ('李四');      -- 自动对新记录加排他锁

行锁的三种实现算法

记录锁(Record Lock)

只锁定具体的索引记录：

sql

-- 设置读已提交隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
-- 只锁定id=1这一条记录
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 测试效果：
-- 其他记录不受影响，可以正常修改id=2,3,4的记录
-- 但id=1的记录被锁定

间隙锁(Gap Lock)

锁定索引记录之间的空隙，防止插入：

sql

-- 可重复读隔离级别下(默认)
START TRANSACTION;

-- 假设users表中id字段有值：1, 5, 10
-- 执行范围查询会触发间隙锁
SELECT * FROM users WHERE id > 5 AND id < 10 FOR UPDATE;

-- 测试间隙锁效果：
-- 在另一个会话中尝试插入
INSERT INTO users (id, name) VALUES (7, '张三');   -- 阻塞：在锁定间隙内
INSERT INTO users (id, name) VALUES (3, '李四');   -- 成功：不在锁定间隙内
INSERT INTO users (id, name) VALUES (12, '王五');  -- 成功：不在锁定间隙内

Next-Key锁

记录锁+间隙锁的组合，InnoDB的默认锁定方式：

sql

START TRANSACTION;
-- 范围查询使用Next-Key锁
SELECT * FROM users WHERE id >= 5 FOR UPDATE;

-- 效果：
-- 1. 锁定id>=5的所有现有记录(记录锁)
-- 2. 锁定(5,+∞)的间隙(间隙锁)  
-- 3. 防止修改现有记录，也防止插入新记录

意向锁

协调表锁和行锁.意向锁是InnoDB自动管理的，无需手动操作：

sql

-- 当你执行
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- InnoDB自动执行：
-- 1. 对users表加意向排他锁(IX)
-- 2. 对id=1的行加排他锁(X)

-- 这样当有人想加表锁时：
LOCK TABLES users WRITE;
-- 系统能快速检测到冲突，无需扫描每一行

死锁

死锁产生的过程

sql

-- 事务A
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;    -- 获得id=1的排他锁
-- 此时尝试获取id=2的锁
SELECT * FROM users WHERE id = 2 FOR UPDATE;    -- 等待id=2的锁

-- 事务B(同时执行)
START TRANSACTION;  
SELECT * FROM users WHERE id = 2 FOR UPDATE;    -- 获得id=2的排他锁
-- 此时尝试获取id=1的锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;    -- 等待id=1的锁

-- 死锁形成：
-- 事务A持有id=1锁，等待id=2锁
-- 事务B持有id=2锁，等待id=1锁
-- MySQL检测到死锁，自动回滚其中一个事务

避免死锁的方法

统一资源访问顺序

sql

-- 所有事务都按相同顺序获取锁
-- 事务A和B都这样写：
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;    -- 先锁id小的
SELECT * FROM users WHERE id = 2 FOR UPDATE;    -- 再锁id大的
-- 进行业务操作...
COMMIT;

控制事务大小和时间

sql

-- 不好的做法：事务太大
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 执行大量业务逻辑...
callExternalAPI();  -- 调用外部接口
complexCalculation();  -- 复杂计算
UPDATE users SET status = 1 WHERE id = 1;
COMMIT;

-- 好的做法：缩小事务范围
-- 先执行不需要锁的操作
callExternalAPI();
complexCalculation();

-- 再执行需要锁的操作
START TRANSACTION;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET status = 1 WHERE id = 1;
COMMIT;

使用合适的隔离级别

sql

-- 降低隔离级别减少锁范围
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

实际业务场景应用

场景1：商品库存扣减

sql

-- 悲观锁方案：适用于高并发秒杀
START TRANSACTION;
SELECT stock FROM products WHERE id = 100 FOR UPDATE;  -- 锁定商品记录

-- 检查库存充足性
-- 假设查询结果stock = 10

UPDATE products SET stock = stock - 1 
WHERE id = 100 AND stock > 0;  -- 原子性扣减库存

-- 检查affected_rows确认扣减成功
COMMIT;

场景2：用户余额支付

sql

-- 必须使用悲观锁确保余额不为负
START TRANSACTION;
SELECT balance FROM accounts 
WHERE user_id = 12345 FOR UPDATE;  -- 锁定用户账户

-- 假设查询结果balance = 1000

-- 检查余额充足后扣减
UPDATE accounts SET balance = balance - 100 
WHERE user_id = 12345 AND balance >= 100;

-- 检查affected_rows确认扣减成功
COMMIT;

场景3：订单流水号生成

sql

-- 方案1：表锁确保严格递增
LOCK TABLES order_sequence WRITE;
UPDATE order_sequence SET current_no = current_no + 1;
SELECT current_no FROM order_sequence;
UNLOCK TABLES;

-- 方案2：行锁性能更好
START TRANSACTION;
SELECT current_no FROM order_sequence WHERE id = 1 FOR UPDATE;
UPDATE order_sequence SET current_no = current_no + 1 WHERE id = 1;
SELECT current_no FROM order_sequence WHERE id = 1;
COMMIT;

场景4：高并发点赞

sql

-- 乐观锁方案：允许偶尔失败
UPDATE articles 
SET like_count = like_count + 1, version = version + 1
WHERE id = 100 AND version = @current_version;

-- 在应用层检查affected_rows
-- 如果为0则重试

锁监控与故障排查

查看当前锁状态

sql

-- 查看正在运行的事务
SELECT 
  trx_id,
  trx_state,
  trx_started,
  trx_mysql_thread_id,
  trx_query
FROM INFORMATION_SCHEMA.INNODB_TRX;

-- MySQL 8.0查看锁等待关系
SELECT 
  waiting.thread_id AS waiting_thread,
  waiting.object_name AS table_name,
  waiting.lock_mode AS waiting_lock_mode,
  blocking.thread_id AS blocking_thread,
  blocking.lock_mode AS blocking_lock_mode
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks waiting 
  ON w.requesting_engine_lock_id = waiting.engine_lock_id
JOIN performance_schema.data_locks blocking 
  ON w.blocking_engine_lock_id = blocking.engine_lock_id;

死锁分析

sql

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

-- 在输出中查找LATEST DETECTED DEADLOCK部分
-- 分析死锁涉及的事务和锁资源

解决锁等待问题

sql

-- 找到阻塞的会话
SHOW PROCESSLIST;

-- 杀掉长时间运行的会话
KILL CONNECTION 12345;  -- 12345是线程ID

锁优化最佳实践

1. 选择合适的隔离级别

sql

-- 读已提交：减少锁争用，提高并发
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 可重复读：防止幻读，但锁更多(默认)
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

2. 建立合适的索引

sql

-- 不好：没有索引导致锁表
SELECT * FROM users WHERE name = '张三' FOR UPDATE;

-- 好：有索引只锁相关行
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '张三' FOR UPDATE;

3. 优化事务设计

sql

-- 原则1：事务要短小
-- 原则2：避免长时间持锁
-- 原则3：按相同顺序访问资源

-- 实际应用：
START TRANSACTION;
-- 只放必须的数据库操作
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET status = 1 WHERE id = 1;
COMMIT;
-- 其他业务逻辑放在事务外

总结

MySQL锁机制的核心要点：

1. 理解锁的本质：保证并发操作的数据一致性
1. 掌握锁的层次：全局锁 > 表锁 > 行锁，粒度越小并发越好
1. 明确锁的模式：共享锁允许并发读，排他锁禁止一切并发
1. 熟悉锁的算法：记录锁精确，间隙锁防插入，Next-Key锁防幻读
1. 避免死锁：统一访问顺序，控制事务大小
1. 监控锁状态：及时发现和解决锁争用问题

记住：锁是双刃剑，既保证了数据一致性，也影响了系统性能。合理使用锁，在数据安全和系统性能之间找到最佳平衡点，这才是数据库优化的关键！

MySQL-锁机制 ​

为什么需要锁？ ​

MySQL 锁的分类 ​

按锁粒度分类 ​

按锁模式分类 ​

按实现算法分类 ​

全局锁 ​

加锁语法 ​

实际演示 ​

现代备份方案 ​

表锁 ​

手动表锁 ​

读锁演示 ​

写锁演示 ​

自动表锁场景 ​

MyISAM引擎 ​

InnoDB的DDL操作 ​

行锁 ​

共享锁(读锁) ​

排他锁(写锁) ​

自动行锁 ​

行锁的三种实现算法 ​

记录锁(Record Lock) ​

间隙锁(Gap Lock) ​

Next-Key锁 ​

意向锁 ​

死锁 ​

死锁产生的过程 ​

避免死锁的方法 ​

统一资源访问顺序 ​

控制事务大小和时间 ​

使用合适的隔离级别 ​

实际业务场景应用 ​

场景1：商品库存扣减 ​

场景2：用户余额支付 ​

场景3：订单流水号生成 ​

场景4：高并发点赞 ​

锁监控与故障排查 ​

查看当前锁状态 ​

死锁分析 ​

解决锁等待问题 ​

锁优化最佳实践 ​

1. 选择合适的隔离级别 ​

2. 建立合适的索引 ​

3. 优化事务设计 ​

总结 ​