SELECT 和 SELECT FOR UPDATE两个例子

在MySQL中，加锁行为取决于事务隔离级别和索引类型。以下是针对两个SQL语句的详细加锁分析：

sql

 

1. SELECT * FROM table WHERE id = 1;          -- 普通查询
2. SELECT * FROM table WHERE id = 1 FOR UPDATE; -- 加锁查询

 

---

一、普通查询 SELECT * FROM table WHERE id = 1

隔离级别	id为主键/唯一索引	id为非唯一索引	无索引
READ UNCOMMITTED	不加锁	不加锁	不加锁
READ COMMITTED	不加锁 (MVCC快照读)	不加锁 (MVCC快照读)	不加锁 (MVCC快照读)
REPEATABLE READ	不加锁 (MVCC快照读)	不加锁 (MVCC快照读)	不加锁 (MVCC快照读)
SERIALIZABLE	加共享锁 (S锁)	加共享锁 (S锁)	加表级共享锁

📌 关键点：

• 普通查询在 SERIALIZABLE 隔离级别下会加共享锁（S锁），其他级别通过MVCC实现无锁读。

• 无索引时，SERIALIZABLE 会退化为表级共享锁。

---

二、加锁查询 SELECT * FROM table WHERE id = 1 FOR UPDATE

1. 不同索引类型的加锁行为

索引类型	加锁细节
主键/唯一索引	在 id=1 的主键记录上加 X锁（排他锁）
非唯一索引	1. 在 id=1 的所有索引记录上加 X锁 2. 对应的主键记录加 X锁 3. 加间隙锁（Gap Lock）防止幻读
无索引	全表加X锁（实际是扫描到的所有行加行锁 + 间隙锁，效果类似表锁）

2. 不同隔离级别的差异

隔离级别	主键/唯一索引	非唯一索引	无索引
READ UNCOMMITTED	行级X锁	行级X锁 (无间隙锁)	表级X锁
READ COMMITTED	行级X锁	行级X锁 (无间隙锁)	全表行级X锁
REPEATABLE READ	行级X锁 + 间隙锁	行级X锁 + 间隙锁	全表行级X锁 + 全表间隙锁
SERIALIZABLE	同 REPEATABLE READ	同 REPEATABLE READ	同 REPEATABLE READ

📌 关键点：

1. 间隙锁（Gap Lock）：

   • 仅在 REPEATABLE READ/SERIALIZABLE 级别生效

   • 锁住 id=1 附近的间隙（如阻止插入 id=0.5 或 id=1.5）

   • 唯一索引等值查询若记录存在则不加间隙锁（因为不可能有重复值）

2. 无索引时的全表锁：

   • 实际是所有扫描到的行加X锁 + 所有间隙加间隙锁

   • 效果等同于表锁，会阻塞其他事务的所有写操作

---

二、REPEATABLE READ隔离级别为什么不能完全防止幻读

因为不加for update的读是不加锁的，用的是MVCC快照读。加了for update就会加排他行锁和间隙锁，并在这次事务结束解锁。
MVCC 的局限性： MVCC 保证了快照读的一致性视图，但写操作（当前读）必须基于最新数据，否则会导致更新丢失或逻辑错误

三、锁类型总结

锁类型	说明
行级锁（Row Lock）	锁定具体记录（主键或二级索引）
间隙锁（Gap Lock）	锁定索引记录之间的间隙（防止插入）
Next-Key Lock	行锁 + 间隙锁组合（InnoDB默认锁）
表级锁（Table Lock）	无索引时退化的锁（或显式LOCK TABLES）
共享锁（S锁）	允许其他事务读，但阻塞写（仅SERIALIZABLE的普通查询）
排他锁（X锁）	阻塞其他事务的读（FOR UPDATE）和写

---

四、示例场景分析

场景1：REPEATABLE READ + 主键索引

SELECT * FROM table WHERE id = 1 FOR UPDATE;

• 加锁：id=1 的主键记录加 X锁

• 无间隙锁（因为主键等值查询且记录存在）

场景2：REPEATABLE READ + 非唯一索引

-- 假设id是非唯一索引，且存在多条id=1的记录
SELECT * FROM table WHERE id = 1 FOR UPDATE;

• 加锁：

  1. 所有 id=1 的索引记录加 X锁

  2. 对应的主键记录加 X锁

  3. id=1 前后间隙加 间隙锁（如阻止插入 id=0.9 或 id=1.1）

场景3：READ COMMITTED + 无索引

SELECT * FROM table WHERE id = 1 FOR UPDATE; -- 无索引

• 加锁：全表扫描并给所有行加X锁（无间隙锁）

• 其他事务插入新记录不会被阻塞（可能产生幻读）

---

五、优化建议

1. 避免无索引查询：

   • 无索引时全表锁会严重降低并发性

   • 为WHERE条件字段添加索引

2. 控制事务范围：

   • 尽量缩短持有锁的时间（尽快提交事务）

3. 谨慎使用SERIALIZABLE：

   • 普通查询会加共享锁，可能引发死锁

💡 死锁案例：
事务A：SELECT * FROM t WHERE id=1 FOR UPDATE;
事务B：SELECT * FROM t WHERE id=1 FOR UPDATE;
在READ COMMITTED下，事务B会阻塞；
在SERIALIZABLE下，事务B的普通查询SELECT * FROM t WHERE id=1 会请求共享锁，导致死锁。