MySQL锁

MySQL 常见的锁：行锁、表锁、共享锁、排他锁、意向锁...

MySQL InnoDB Locking (opens new window)

参考：面试必会系列之MySQL锁相关内容 (opens new window)

1 锁的分类

• 共享锁（shared lock，S锁）：假设事务 T1 对数据 A 加上共享锁，那么事务 T2 可以读数据 A，不能修改数据 A。
• 排他锁（exclusive lock，X锁）：假设事务 T1 对数据 A 加上排他锁，那么事务 T2 不能读取数据 A，不能修改数据 A。

T1获取记录的 S 锁，之后 T2 也可以获得该记录的 S 锁。

T1获取记录的 X 锁，之后 T2 即不可以获得该记录的 X 锁，也不可以获得 S 锁，都会被阻塞，直到 T1 提交事务释放 X 锁。

• 意向共享锁（intention shared lock，IS锁）：一个事务在获取（任何一行/或者全表）S 锁之前，一定会先在所在的表上加 IS 锁。
• 意向排他锁（intention exclusive lock，IX锁）：一个事务在获取（任何一行/或者全表）X 锁之前，一定会先在所在的表上加 IX 锁。

2 意向锁存在的目的

假设事务 T1，用排他锁来锁住了表上的几条记录，那么此时表上存在意向排他锁。那么此时事务 T2 要进行 LOCK TABLE ... WRITE 的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突，而不是去遍历所有的记录去查看有没有行级别的排他锁。

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

3 加锁的算法

下面三种情况都是行级别的锁，因为它们都是针对某一数据行或者某一范围的间隙进行加锁。

⭐ Record Lock，记录锁（行锁）

该锁是对索引记录进行加锁！行锁要锁在索引上。innodb一定存在聚簇索引，因此行锁会落到聚簇索引上。

例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE会阻止其他事务插入、修改或删除 c1 = 10 这行的数据。

⭐ Gap Lock：间隙锁

是对索引间隙加锁，间隙锁用于锁定一个范围，但不包含记录本身。它的作用是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生。在 RU 和 RC 隔离级别下，不会使用间隙锁。在 RR 和 Serializable 隔离级别下会使用间隙锁。

例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE会阻止其他事务插入 c1 = 15 的行数据。

⭐ Next-Key Lock：临键锁

是行锁 + 索引前面的间隙锁的结合。记住了，锁住的是索引的间隙！比如一个索引包含值，10，11，13 和 20。那么，间隙锁的范围如下

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对于最后一个间隔范围，索引锁住的是最大值之后的范围，也就是 (20, positive infinity)，postive infinity 并不是无穷大，而是一个高于记录中的任意数据的一个值。

InnoDB 通过临键锁解决幻读问题。

4 快照读和当前读

在 mysql 中 select 分为快照读和当前读。

快照读：

select * from table where id = ?;：读的是数据库记录的快照版本，不加锁的。

当前读（需要加锁）：

• select * from table where id = ? lock in share mode;：会对读取数据加共享锁。
• select * from table where id = ? for update：会对读取数据加排他锁。
• insert
• update
• delete

5 表级锁 or 行级锁

MyISAM 只支持表级锁；InnoDB 支持表级锁和行级锁。

表级锁：MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

通过LOCK TABLE ... READ和LOCK TABLE ... WRITE能申请表级别锁。

行级锁：MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

InnoDB 的行级锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

6 表级锁 or 行级锁分析

行级锁或表级锁，是由「隔离级别」「查询列的索引」「查询条件」共同决定的。

• 隔离级别：RC/RU，RR/Serialziable
• 查询列的索引：唯一索引，非唯一索引，无索引
• 查询条件：精确匹配（=），非精确匹配（>、 <、 like）

面试必会系列之MySQL锁相关内容 (opens new window)中共讨论了 40 种情况，对其中「当前读」进行简单的归纳：

注意：下面的查询都是当前读，需要加锁；而不是快照读。

⭐ 在 RC/RU 隔离级别下的当前读

隔离级别	查询列索引	查询条件	锁	锁的位置
RC/RU	无索引		记录锁	聚簇索引
RC/RU	唯一索引		记录锁	唯一索引和聚簇索引
RC/RU	非唯一索引		记录锁	聚簇索引

当唯一索引为聚簇索引时，那么记录锁只加在聚簇索引上；当唯一索引非聚簇索引时，记录锁分别加在该唯一索引和聚簇索引上。

⭐ 在 RR/Serializable 默认隔离级别下的当前读

隔离级别	查询列索引	查询条件	锁	锁的位置
RR/Serializable	唯一索引	精确匹配(=)	记录锁	唯一索引和聚簇索引
RR/Serializable	唯一索引	非精确匹配	记录锁 + 间隙锁	唯一索引，唯一索引的间隙
RR/Serializable	非唯一索引		记录锁 + 间隙锁	聚簇索引，非唯一索引，非唯一索引的间隙
RR/Serializable	无索引		表级锁	整表

当查询条件为非唯一索引时，如：

pId(int)	name(varchar)	num(int)
1	aaa	100
2	bbb	200
3	bbb	300
7	ccc	200

select * from table where num = 200 lock in share mode

在 pId=2，7 的聚簇索引上加 S 锁，在 num=200 的非聚集索引上加 S 锁，在 (100,200) (200,300) 加上 gap lock。

select * from table where num > 200 lock in share mode

在 pId=3 的聚簇索引上加 S 锁，在 num=300 的非聚集索引上加 S 锁。在 (200,300) (300,+∞) 加上 gap lock。

7 死锁的产生及解决策略

死锁：两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的线程，若无外力作用，它们都无法推进下去。

系统解决策略：

1. 直接进入等待，直到超时。整个超时时间可以通过参数 innodb_lock_wait_timeou来设置，默认为 50s。
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务的以继续执行。innodb_deadlock_detect的默认值本身就是 on。

人工解决策略：

1. 终止系统中的一个或多个死锁进程，打破循环。kill 12345(进程Id);
2. 抢占资源。从一个或多个进程中抢占足够数量的资源，分配给死锁进程，打破死锁状态。