InnoDB 的 select 行锁还是表锁

前面有一篇文章说到了，InnoDB 的 select 查询会锁表，有的人不信。然而有的人学习能力很强，立马在官方网站上找到了，select 查询会锁表，就看你怎么使用，并不是说所有的 select 查询都会锁表。具体看你的事务隔离级别，和编写的查询语句的形式。

群里还是有很多大牛的，相关的聊天记录这里贴不了，我就不细说了。今天抽个时间，我们再来说说 select 锁表问题。

MySQL 最常见的坑就是 InnoDB 是行锁，这是大家都知道的事，但是有时候它却会锁表，你说奇怪不奇怪。

其实只要你懂它了之后，一点也不会觉得奇怪。只有你不懂，才会觉得它奇怪。InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。这一点和 Oracle 的行锁实现机制略有不同。

例如下面的表：

xttblog(id, title, url, text) innodb;

id PK（主键），无其他索引，即其他列都没有索引。

update xttblog set text='' where id=1;

命中索引，行锁。

update xttblog set text='' where id != 1;

未命中索引，表锁。

update xttblog set text='' where title='';

无索引，表锁。

启示：InnoDB 务必建好索引，否则锁粒度较大，会影响并发。

再说一下 select，如果查询没有命中索引，也将退化为表锁。下面我们结合 InnoDB 的三种锁（记录锁(Record Locks)、间隙锁(Gap Locks)、临键锁(Next-Key Locks)）来说明它。再讲这三种锁的前提条件是默认的事务隔离级别为可重复读(Repeated Read, RR)。

记录锁(Record Locks)

记录锁，它封锁索引记录，例如下面的查询语句：

select * from xttblog where id=1 for update;

它会在 id=1 的索引记录上加锁，以阻止其他事务插入，更新，删除 id=1 的这一行。

需要说明的是，如果是下面的查询语句：

select * from xttblog where id=1;

则是快照读(SnapShot Read)，它并不加锁。

间隙锁(Gap Locks)

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。例如下面的 SQL 语句：

select * from xttblog where id between 8 and 15 for update;

会封锁区间 id 为 8 到 15 的记录。以阻止其他事务，如：id=10 的记录插入。

如果不阻止 id=10 的记录插入，则会产生幻读。如果能够插入成功，同一个事务执行相同的 SQL 语句，会发现结果集多出了一条记录，即幻读数据。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。

如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

临键锁(Next-Key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。临键锁会封锁索引记录本身，以及索引记录之前的区间。

如果一个会话占有了索引记录 Record 的共享/排他锁，其他会话不能立刻在 Record 之前的区间插入新的索引记录。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

最后说一下，怎么测试当前的查询到底是行锁还是表锁呢？

以我们之前发生事故来说，首先是 select 查询不能有索引。然后 dev 环境和 sit 环境连接同一个数据库，dev 对某个事务中的查询取断点，让它停在查询操作上；sit 环境则对同一个表进行插入、更新、删除操作。查看日志，就会发现有 time out 日志。具体为事务无法提交，超时结束，因为这个表已经被锁住了，获取不到锁，就会发生超时。

总结：InnoDB 的锁，与索引类型，事务的隔离级别相关。InnoDB 到底是行锁还是表锁取决于你的 SQL 语句。如果查询没有命中索引，也将退化为表锁。InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。所以如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

： » InnoDB 的 select 行锁还是表锁