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

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

wKiom1cXFPLQ06bWAAAgwAz71Jw721.png

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

wKiom1cXFZHgjW-TAAALsd29rWo848.png

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

wKiom1cXGNXDjn6gAAAK2QMVP2U594.png

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

wKiom1cXGRTRHLdXAAARm0Aju2Y514.png

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

wKioL1cXGkWCJt0AAAATUASIqGI518.png




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

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

wKiom1cXHdXSrbVFAAAwcLnYi-c972.png

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

wKioL1cXHueCTyqBAAANmh1EdQo713.png

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

wKioL1cXLiuTKf_GAAAHmQgYi_8712.png

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




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

wKioL1cXQb_BQV3jAAAU5IWIuKQ227.png

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

wKiom1cXQZbwLWzsAAAT7sN8kJI521.png

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

wKiom1cXQo3BK64iAAAGByJTHg4852.png

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

wKiom1cXQ2aCpMoXAAAFcHBcYXk792.png


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

wKioL1cXRYnCRFxUAAATuDyg-Lk576.png

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

wKioL1cXRlqCfUAvAAAfuHe3hpg377.png

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

wKiom1cXRgejVuCSAAAIc5_DrmI969.png




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