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语句的加索顺序是否相同，如果相同，则基本
不会出现死锁问题，而加索的顺序相反，则很有可能出现死锁情况。按照本文的几种情况进行分析，可以
进行判断。