MySQL 之 InnoDB Lock 浅谈

Q: MySQL 加锁时锁的是索引还是数据 ?

如果别人问你这个问题的话,你是成竹在胸还是不知所措? 当然，这都不是重点，重点是之后你只有一个答案那就是胸有成竹。

InnodB-Lock-Overview.png

锁类型

锁的名称真的有很多种，但从资源的使用来说就是两种，共享锁(Shared-Lock)和排它锁(Exclusive-Lock)。共享锁通常简写为 S, 而排它锁的简写却是 X (为什么是 X 而不是 E,希望知道答案的你与我分享 mailto: sftjun@outlook.com )。

InnoDB 引擎默认对普通查询不加锁，所以示例代码以手动加锁形式给出

共享锁

共享锁也称为读锁，因为读并不改变数据，所以把锁共享可以使资源的利用最大化。若事务 T1 对数据加上了 S 锁，T1 在该事务期间只能读取数据而不能修改数据。与此同时事务 T2 也只能够对数据加上 S 锁而不能加上 X 锁。在该事务期间加任何的 X 锁都会失败，只有当该数据的所有 S 锁释放之后，加 X 锁才会成功。

select * from table_name where ... lock in share mode;

排它锁

排它锁也称为写锁，当事务 T1 对数据加 X 锁之后，该事务就具备了对应的数据操作权限，可以对数据进行修改(update、delete etc)。 此时如果有事务 T2 想对加了 X 锁的数据进行加锁时，则不会成功(S锁也不行)，必须得事务 T1 释放 X 锁之方可加锁(X、S 都可以)。

select * from table_name for update;

查看锁

纸上来得终觉浅,绝知此事要躬行

• 关闭事务自动提交
• 开启事务
• 加锁
• 查看锁状态

set autocommit = 0;
start transaction;
select * from table_name ... for update; // X 锁
select * from table_name ... lock in share mode; // S 锁

操作的时候开启多个终端，每个终端为一个客户端连接到 MySQL 的服务端，按上面的步骤进行操作。为 T1 加 S 锁，然后再 T2 加 X 锁，显示如下的信息。

...省略...
------------
TRANSACTIONS
------------
Trx id counter 6418
Purge done for trx's n:o < 6415 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422149355321168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6417, ACTIVE 136 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 13, OS thread handle 140674203023104, query id 168 localhost root Sending data
select * from repl_tb1 for update
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 3 n bits 72 index PRIMARY of table `repldb`.`repl_tb1` trx id 6417 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
...省略...

从上面信息中我们可以看到事务的信息，有多少锁在使用中，比如 X 锁等待了多长时间等,这些信息有助于排查问题，优化数据库等。

锁级别

根据资源使用类型来对锁分类的话只有共享锁与排它锁，在数据库的锁分类里面还有按锁的级别进行分类，或者说是按锁的粒度进行分类，我们最常说的就是表级锁(Table-Level)和行级锁(Row-Level)，其实还有页面锁(Page-Level)，但是这个锁被提及的情况没有像前面两种那样广泛。

• 表级锁: 锁定整张表
• 行级锁: 锁定特定行
• 页面锁: 锁定数据页

表、行、页面锁也分 X 与 S 锁，X 锁期间仍然禁止其它事务的一切操作, S 锁期间允许加 S 锁进行数据的读取操作。

锁的不同级别相应的开锁和处理能力也各不相同，如下表所示:

类型	开销	速度	死锁	粒度	并发
表级锁	小	快	无	大	小
行级锁	大	慢	有	小	大
页面锁	中	中	有	中	中

表中的大小、快慢等均是以三个级别相较而言，但是它们之间的差距根据数据量的不同可能会有天差地别。如表级锁和行级锁并发量而言，表级锁的并发量只有1，但是行级锁则不一样。行级锁由你的数据库的连接大小决定，你的服务端能支撑多少个并发连接那并发量就能够达到多大(各连接操作不同的行数据,如果操作相同的行,那并发量就会相应的减小)。

InnoDB 锁

使用 MySQL 时，我们讲的锁如果不特殊说明，那么我们讲的一定是 InnoDB 引擎的锁，而这时的锁我们讲的其实是 X 锁(如果都是 S 锁的话那还有加锁的必要吗)，那 InnoDB 的 X 锁到底是如何实现的呢, 它到底锁住了什么呢?

InnoDB 引擎对不同的查询采用不同的加锁方式，如下:

• 不带索引条件, 采用表级锁( select * from table_name )
• 带有索引条件, 采用行级锁( select * from table_name where ...)

表级锁锁住了表, 行级锁锁住了行吗? 当然不是的，InnoDB 引擎的行级锁综合性能和并发得的考虑，行级锁锁住的查询时候使用的索引列的索引(Index)。当为表添加多个索引时，加锁时就可以通过不同的事务对不两只的记录进行加锁,而使用行锁或者是表锁与所建立的类型无关(pk,unique etc)。

思考一个问题, 是否可以通过不同的索引列对同一条数据同时进行加锁呢 ? ①

行锁类型

上面提到我们查询时用得基本都是行锁,那行锁对不同类型的查询又是如何加锁呢？要回答这个问题得先弄清楚查询有几种方式。查询不外乎精确匹配、范围匹配两大类，所以行锁分为几类:

• Record Locks

  记录锁,它锁住查询时索引列对应的索引,如:SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

• Gap Locks

  间隙锁，我自己称其为 区间锁, 当查询条件是 大于、小于、BETWEEN..AND 等时就彩此锁, 该锁是基于性能和并发的取舍而设计，且只在一些事务隔离级别才会使用。

  SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
  

  如上面的查询语句，它会锁住 10~20 的整个索引，而不管该区间内是否存在如 c1 = 15 等情况，也就是说如果没有 c1 = 15 的记录，那么此时其它事务若想插入 c1 = 15 的记录也是不允许的。

• Next-key

  Next-key 锁，我给不出一个准确的翻译名称，它是由 record locks 和 gap locks 联合组成的一类锁，它不仅锁定匹配的记录本身，它还锁定查询时带有的一定范围。

  SELECT c1 FROM t WHERE c1 >= 100 FOR UPDATE;
  

  如该查询语句，若此时表里面的记录没有 c1 的最大值就是 100, 那么该锁锁定的范围是 [100,positive infinity)。也就是这时如果插入 c1 = 101 是不允许的，无法加锁成功。

InnoDB 在事务隔离级别是 REPEATABLE READ 时，默认就采用该锁，从而阻止行的幻读( Phantom read)。

意向锁 (Intention Locks)

意向锁是什么呢? 其实意向锁就是解决一个问题，前面我们说行锁、表锁，但是都是独立开来讲，意向锁其实就是行锁和表锁共同融合的一类锁。意向锁简写 I，同时也分两类锁 IS 和 IX, 也就是意向共享锁和意向看它锁。

• IS: indicates that a transaction intends to set a shared lock on individual rows in a table.
• IX: indicates that a transaction intends to set an exclusive lock on individual rows in a table.

意向锁其实讲究的是兼容性，只要具备兼容性，那么就可以加锁成功。兼容性列表如下:

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

只要兼容，或者说只要不产生冲突，那么就可以加锁成功，获取操作权限。

AppendX

• ① 该问题与索引的实现有关，阅读 MySQL 之 InnoDB Index 浅谈 。