我们都知道,MyISAM引擎使用的是表锁,而innoDB最小粒度为行锁。但在实际使用中我们有时发现就算我们操作的是不同行的数据,还是会发生锁表。我们先来看一个例子。

session1开启事务并更新id=1的数据:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_表锁

session2开启事务,并更新id=2的数据,但session2被阻塞了:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_innoDB_02

不是说innoDB支持行锁吗,我们这里明明更新的不是同一条数据,为什么还会被阻塞。其实这是因为MySQL innoDB给数据加锁的方式和oracle不一样。oracle是给这条数据行加锁,而innoDB是给索引上的索引项加锁来实现的。简单的说就是:如果我们的语句无法命中索引,innoDB就会锁表。我们给innodb_lock表的id列加上索引:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_MySQL_03

session1开启事务并更新id=1的数据:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_innoDB_04

session2开启事务,并更新id=2的数据,此时更新成功:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_表锁_05




上面说到innoDB的行锁是针对索引加锁,而不是具体的记录。所以如果即使是不同的记录,只要访问的是同一个索引也会导致阻塞。还是用上面的例子,我们先插入一条id相同的数据。

session1开启事务并更新id=1 and name=my1的数据:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_MySQL_06

session2开启事务,并更新id=1 and name=my5的数据,但session2被阻塞了:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_MySQL_07

我们再尝试去更新name=my5的数据,但不用id去匹配,session2还是被阻塞。这种阻塞是因为name列上无索引,所以该update会尝试锁表,但由于已经存在行锁,所以锁表会被阻塞:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_表锁_08

从上面看出,innoDB锁的就是索引,而不是表中的具体数据。由于session1锁了id=1的索引,又由于name并无索引,所以他们虽然不是同一条记录,但他们id是一样的就会导致阻塞。




上面说的都是单列索引的情况,如果表上有多列都有索引会怎么样呢。当表有多个索引时,可以使用不同的索引锁定不同行。我们修改下表结构,加入一列addr,然后给id和name都加上索引。

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_表锁_09

我们先来看看使用一列索引的情况。session1更新id=1 and addr=addr1的行:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_表锁_10

session2去更新name=my4的行,被阻塞。因为虽然MySQL尝试去锁行但该行id=1的索引已经被锁定:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_表锁_11

session2再用addr=addr4去更新,被阻塞。这里被阻塞是该语句尝试去锁表,因为addr列上无索引:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_innoDB_12


我们再来看看使用多列索引的情况。session1更新id=1 and name=my1的行:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_innoDB_13

session2去更新name=my4的行,成功。因为id和name都有索引,所以当我们以它们为条件时相当于形成了一个联合索引,只精确的锁了id=1 and name=my1的联合索引。

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_表锁_14

session2再用addr=addr4去更新,还是被阻塞。这里被阻塞是该语句尝试去锁表,因为addr列上无索引:

MySQL innoDB引擎锁机制(一) —— 行锁和表锁_innoDB_15




总结:MySQL innoDB给数据加锁的方式和oracle不一样。oracle是给这条数据行加锁,而innoDB是给索引上的索引项加锁来实现的。简单的说就是:如果我们的语句无法命中索引,innoDB就会锁表。我们可以通过几个简单地步骤来判断innoDB会锁哪些表:1.去掉where条件中没有索引的列 2.用剩下的条件查询出来的记录就是innoDB会锁的行。