MYSQL笔记-锁

1 锁

锁的目的是协调事务之间的读写顺序，保证数据的一致性与隔离性
在 InnoDB 中，“默认加锁”是智能的，但缺少索引会让锁悄悄扩大范围，引发性能与死锁问题

锁的两种分类：

• 操作类型划分

类型	描述
共享锁(S锁)	允许多个事务同时读，但不能写
排他锁(X锁)	只有一个事务能加，允许读写，其他事务不能加任何锁

• 按粒度划分

粒度	描述
表级锁	锁住整张表，力度大、冲突高、开销小
行级锁	锁住单行记录，粒度小、并发高、开销大

InnoDB引擎支持行级锁+表级锁，支持事务安全，MyISAM只支持表级锁，不支持事务，开发优先选择InnoDB引擎

查看当前锁信息：

-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看当前锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看当前锁持有者
SELECT * FROM information_schema.INNODB_LOCKS;

加锁技巧与优化技巧：

1. 使用FOR UPDATE和LOCK IN SHARE MODE加显式锁
2. 使用合理的索引，避免加锁范围扩大
3. 避免大事务和长时间持有锁
4. 严禁在程序中sleep()占用锁资源
5. 谨慎使用手动加锁
6. 避免隐式锁升级

MYSQL的默认加锁行为：

• InnoDB是MySQL默认的存储引擎，支持事务、行级锁、自动加锁
• 大部分时候InnoDB会根据传入的SQL类型自动决定加什么锁、锁多大范围

1. SELECT的默认行为

-- 1. 普通SELECT
SELECT * FROM user WHERE id = 1;
-- 不会加锁。使用的是一致性读(Consistent Read)，通过MVCC实现快照读。

-- 2. 带FOR UPDATE 或 LOCK IN SHARE MODE
SELECT * FROM user WHERE id = 1 FOR UPDATE; -- 加排他锁（X锁）
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE; -- 加共享锁（S锁）
-- 这种情况下如果有索引会加行锁，无索引会加表锁

2. UPDATE、DELETE默认行为

会自动加排他锁，以确保数据一致性

UPDATE users SET age = 25 WHERE id = 1;
DELETE FROM users WHERE id = 2;

-- InnoDB操作步骤如下：
--- 1. 根据条件查找目标行
--- 2. 给这些行加排他锁
--- 3. 如果没有合适索引，则扩大锁的范围

3. INSERT默认行为
   在新插入的记录上自动加排他锁，不影响其他行，除非存在唯一索引冲突

隐式锁升级：

InnoDB不是所有时候都能成功加到行锁
锁升级的本质：不是“行锁升级成表锁”，而是因为没有合适的索引导致锁的范围扩大

• 示例1：无索引导致锁升级

-- 表中没有age字段索引
UPDATE users SET age = aget + 1 WHERE age = 25;
-- InnoDB整表扫描查找age = 25的所有行，锁住所有记录（行锁退化为表锁）
-- 此时一旦事务提交慢，其他事务就会被挂起
-- 为age添加索引，则只锁住符合条件的行，实现真正的行锁

显式锁、隐式锁对比：

项目	显式锁	隐式锁（默认行为）
写法	FOR UPDATE、LOCK IN SHARE MODE	普通DML
控制力	开发者控制	数据库引擎控制
使用场景	需要事务控制，加锁精度	绝大多数读写操作
是否升级锁	仅无索引时会	同样无索引时会扩大锁范围

避免锁范围扩大的最佳实践：

1. 所有WHERE条件字段都要有索引
2. UPDATE 和 DELETE 尽量带索引字段条件
3. 检查慢SQL是否全表扫描
4. 在需要时配合FOR UPDATE 手动控制锁定范围
5. 使用SHOW ENGINE INNODB STATUS 或 information_schema检查锁等待