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

2434t

我们都知道，MyISAM引擎使用的是表锁，而innoDB最小粒度为行锁。但在实际使用中我们有时发现就算我们操作的是不同行的数据，还是会发生锁表。我们先来看一个例子。
session1开启事务并更新id=1的数据：

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


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

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


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

---

上面说到innoDB的行锁是针对索引加锁，而不是具体的记录。所以如果即使是不同的记录，只要访问的是同一个索引也会导致阻塞。还是用上面的例子，我们先插入一条id相同的数据。
session1开启事务并更新id=1 and name=my1的数据：

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

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

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

---

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

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

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

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


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

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

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

---

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