MySQL的锁分析

首先,我就先假设读者们都是了解数据库隔离级别的人,对所谓的RR,RC,RU,Serializable都是耳熟能详的。

如果不明白,可以看看这几篇文章,写的都很清楚,详细。

但是,知道了这些概念,我们怎么去优化我们的SQL语句呢。其实还有一小段路。

比如说,现在有这么两个SQL语句:

  • select * from t1 where id = 10;
  • delete from t1 where id = 10

MySQL会采用什么样的方式进行加锁处理,加什么样的锁?

某种情况下,这个问题可以这么回答:

  • 对于SQL1,MySQL不进行加锁处理,因为有MVCC的存在。
  • 对于SQL2,对id等于10的记录进行加锁处理(主键索引)。

但是,这个答案是有前提的,比如说,id是不是主键? 当前系统的隔离级别是什么? id上有没有索引?
抑或是SQL的执行计划是什么,全盘扫描还是索引扫描?
等等。。都是需要考虑的,没有这些前提,这个SQL的执行结果就无法预知。

我们就来假设几种情况:

1. id为主键,RC隔离级别

这个组合最简单,因为id主键,所以对于给定的 delete from t1 where d=10 语句,
我们只要给id=10的记录加上X锁即可。

2. id为二级索引,RC隔离级别

何为二级索引,我们都知道mysql中每个表都有一个聚簇索引(clustered index ),
除此之外的表上的每个非聚簇索引都是二级索引,又叫辅助索引(secondary indexes).
仅仅对于InnoDB来说,每个表都有一个特殊的索引称为聚集索引。
如果您的表上定义有主键,该主键索引是聚集索引。如果你不定义为您的表的主键时,
MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,
InnoDB使用它作为聚集索引。如果没有这样的列,InnoDB就自己产生一个这样的ID值,
它有六个字节,而且是隐藏的,使其作为聚簇索引。
聚簇索引主要是为了方便存储。。所以二级索引,是对聚簇索引的索引。

介绍完了二级索引,我们来看这个情况下的SQL语句执行。

这个组合中,id是unique索引,假设在t1中主列为name且id为10时,name=’d’,则,在扫描的过程中,
delete语句会在id处进行where条件的过滤,在找到id为10的时候,对id 进行加锁处理,当然,
这是X锁。然后回到主键索引,将name=’d’的对应的主键索引也加上X锁。为什么要这样进行加锁呢,
其实原因很简单,如果现在有一个update语句,通过主键索引进行更新,则update将感觉不到delete的存在,
就违反了在统一记录上修改需串行的原则。

一言以毕之,就是,如果id为unique列,则MySQL需要加上两把X锁,来确保在同一数据上的串行操作。

3. id为非唯一索引,RC隔离级别

想对于之前,这就是新的一个情况了,所谓的非唯一索引就是id列不再唯一,也就是存在多个一样
的值,只有一个普通的索引,他爸再也不是李刚了。现在delete from t1 where id = 10;, 依旧会在id列
上的索引进行过滤次,但是此时已经有了很大的改变了,首先,在id列,所有符合条件的记录有都会被加上
锁,而且此时对应的主键索引也会被加上X锁,原因同上,不同的是,上面是一条记录的两个锁,现在是n个
记录的2n个X锁。

4. id无索引,RC隔离级

id无索引,这就好玩了,因为,这种情况下,MySQL默认会对全表进行扫描,此时,你懂的,既然是全表的
搜索,那么,你觉得还有机会不加锁吗?但是MySQL对这种情况做了一定的优化,判断不满足条件的记录,
会解除锁限制。

5. id主键,RR隔离级

说完了RC,现在看看RR,RR可是SQL Server的默认隔离级啊,很牛逼的。

其实分析起来也很简单,针对delete这条语句,效果和RC+id主键是一样的。

6. id唯一索引,RR隔离级

和组合二一致。

7. id非唯一索引,RR隔离级

这边我们需要引入一个重量级的锁,传说中的GAP锁。GAP锁的目的就是防止幻影读,何谓幻影读,
我这边就不加累赘了,这应该是一个比较基础的问题。

为了防止幻影读,我们需要做的就是在第一次和第二次读取的时候,其他的事物不会插入新的数据
并且提交。在后续的insert进行插入的时候,会检查这个gap中是否存在GAP锁,如果有,则等待中。
在5,6中,为什么没有出现GAP锁呢,原因也很简单,因为一个是primary key一个是unique属性,不会
存在一表多个的情况。

所以在这个情况下,MySQL会加上N把索引上的X锁+主键索引上的N把X锁+N+1把GAP锁。

8. id无索引,RR隔离级

呵呵,这个情况和Java中的Full GC一样,Stop the world 换成了 Lock the world.

这种情况下,MySQL会对全表加上X锁,还有GAP锁,哈哈,这就是一个不小心,就会造车数据库的并发
能力指数下降。

9. Serializable隔离级

我只所以把这个列在这边,只是想来卖个萌,因为,不仅对于delete会Lock the world,而且对于select
语句,同样。

关于死锁

死锁,其实有个很好判断的方法,就是看两个SQL语句的加索顺序是否相同,如果相同,则基本
不会出现死锁问题,而加索的顺序相反,则很有可能出现死锁情况。按照本文的几种情况进行分析,可以
进行判断。

2 Replies to “MySQL的锁分析”

Leave a Reply

Your email address will not be published. Required fields are marked *