InnoDB 的 select 行锁还是表锁

SQL herman 283浏览 0评论

前面有一篇文章说到了,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 的行锁是实现在索引上的,而不是锁在物理行记录上。所以如果访问没有命中索引,也无法使用行锁,将要退化为表锁

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加QQ1群:135430763(2000人群已满),QQ2群:454796847,QQ3群:187424846。QQ群进群密码:xttblog,想加微信群的朋友,可以微信搜索:xmtxtt,备注:“xttblog”,添加助理微信拉你进群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作可添加助理微信进行沟通!

本文原文出处:业余草: » InnoDB 的 select 行锁还是表锁