MySql 加锁分析

首先概述几个概念：

1、锁类型

共享锁(S锁):假设事务T1对数据A加上共享锁，那么事务T2可以读数据A，不能修改数据A。
排他锁(X锁):假设事务T1对数据A加上共享锁，那么事务T2不能读数据A，不能修改数据A。
我们通过update、delete等语句加上的锁都是行级别的锁。只有LOCK TABLE … READ和LOCK TABLE … WRITE才能申请表级别的锁。
意向共享锁(IS锁):一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。
意向排他锁(IX锁):一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁。

2、隔离级别

• Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！
• Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据!
• Repeatable Read (RR):可重复读，加入间隙锁，一定程度上避免了幻读的产生！注意了，只是一定程度上，并没有完全避免!我会在下一篇文章说明!另外就是记住从该级别才开始加入间隙锁(这句话记下来，后面有用到)!
• Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。

3、MySql加锁算法

Record Locks：简单翻译为行锁吧。注意了，该锁是对索引记录进行加锁！锁是在加索引上而不是行上的。注意了，innodb一定存在聚簇索引，因此行锁最终都会落到聚簇索引上！
Gap Locks：简单翻译为间隙锁，是对索引的间隙加锁，其目的只有一个，防止其他事物插入数据。在Read Committed隔离级别下，不会使用间隙锁。这里我对官网补充一下，隔离级别比Read Committed低的情况下，也不会使用间隙锁，如隔离级别为Read Uncommited时，也不存在间隙锁。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁。
Next-Key Locks：这个理解为Record Lock+索引前面的Gap Lock。记住了，锁住的是索引前面的间隙！比如一个索引包含值，10，11，13和20。那么，间隙锁的范围如下

`(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)`

4、MySql索引原理

• innodb一定存在聚簇索引，默认以主键作为聚簇索引
• 有几个索引，就有几棵B+树(不考虑hash索引的情形)
• 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引，指向聚簇索引B+树。

5、快照读和当前读

快照读：读取的是记录数据的可见版本（可能是过期的数据），不用加锁

当前读：读取的是记录数据的最新版本，并且当前读返回的记录都会加上锁，保证其他事务不会再并发的修改这条记录

---

结论：

具体分类：

RC/RU+条件列非索引：即where后的字段非索引

RC/RU+条件列是聚簇索引：即where后的字段为主键索引

RC/RU+条件列是非聚簇索引：即where后的字段为常规索引

RR/Serializable+条件列非索引：RR级别需要多考虑的就是gap lock，他的加锁特征在于，无论你怎么查都是锁全表

RR/Serializable+条件列是聚簇索引：该情况的加锁特征在于，如果where后的条件为精确查询(=的情况)，那么只存在record lock。如果where后的条件为范围查询(>或<的情况)，那么存在的是record lock+gap lock。

RR/Serializable+条件列是非聚簇索引：这里非聚簇索引，需要区分是否为唯一索引。因为如果是非唯一索引，间隙锁的加锁方式是有区别的。先说一下，唯一索引的情况。如果是唯一索引，情况和RR/Serializable+条件列是聚簇索引类似，唯一有区别的是:这个时候有两棵索引树，加锁是加在对应的非聚簇索引树和聚簇索引树上！大家可以自行推敲!
下面说一下，非聚簇索引是非唯一索引的情况，他和唯一索引的区别就是通过索引进行精确查询以后，不仅存在record lock，还存在gap lock。而通过唯一索引进行精确查询后，只存在record lock，不存在gap lock。老规矩在num列建立非唯一索引

问题：

1、为什么条件列加不加索引，加锁情况是一样的？

ok,其实是不一样的。在RC/RU隔离级别中，MySQL Server做了优化。在条件列没有索引的情况下，尽管通过聚簇索引来扫描全表，进行全表加锁。但是，MySQL Server层会进行过滤并把不符合条件的锁当即释放掉，因此你看起来最终结果是一样的。但是RC/RU+条件列非索引比本例多了一个释放不符合条件的锁的过程！

具体案例：

select * from table where num = 200
select * from table where Id = 2  (聚簇索引)

RC/RU+条件列非索引：不加任何锁，是快照读。
RC/RU+条件列是聚簇索引：不加任何锁，是快照读。
RC/RU+条件列是非聚簇索引：不加任何锁，是快照读。

RR/Serializable+条件列非索引：在RR级别下，不加任何锁，是快照读；在Serializable级别下，在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
RR/Serializable+条件列是非聚簇索引：在RR级别下，不加任何锁，是快照读；在Serializable级别下，是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。

RC/RU+条件列是聚簇索引：不加任何锁，是快照读。
RR/Serializable+条件列是聚簇索引：在RR级别下，不加任何锁，是快照读；在Serializable级别下，是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock。

select * from table where num > 200
select * from table where Id > 2  (聚簇索引)

RC/RU+条件列非索引：不加任何锁，是快照读。
RC/RU+条件列是非聚簇索引：不加任何锁，是快照读。

RR/Serializable+条件列非索引：在RR级别下，不加任何锁，是快照读；在Serializable级别下，在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
RR/Serializable+条件列是非聚簇索引：在RR级别下，不加任何锁，是快照读；在Serializable级别下，是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock

RC/RU+条件列是聚簇索引：不加任何锁，是快照读。
RR/Serializable+条件列是聚簇索引：在RR级别下，不加任何锁，是快照读；在Serializable级别下，是当前读，在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where num = 200 lock in share mode
select * from table where Id = 2 lock in share mode  (聚簇索引)

RC/RU+条件列非索引：当num = 200，有两条记录。这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级S锁，采用当前读。
RC/RU+条件列是非聚簇索引：当num = 200，由于num列上有索引，因此先在 num = 200的两条索引记录上加行级S锁。接着，去聚簇索引树上查询，这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级S锁，采用当前读。

RR/Serializable+条件列非索引：在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
RR/Serializable+条件列是非聚簇索引：是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加S锁，在(100,200)(200,300)加上gap lock。

RC/RU+条件列是聚簇索引：在pId=2的聚簇索引上，加S锁，为当前读。
RR/Serializable+条件列是聚簇索引：是当前读，在pId=2的聚簇索引上加S锁，不存在gap lock。

select * from table where num > 200 lock in share mode
select * from table where Id > 2 lock in share mode  (聚簇索引)

RC/RU+条件列非索引：当num > 200，有一条记录。这条记录对应的pId=3，因此在pId=3的聚簇索引上加上行级S锁，采用当前读
RC/RU+条件列是非聚簇索引：当num > 200，由于num列上有索引，因此先在符合条件的 num = 300的一条索引记录上加行级S锁。接着，去聚簇索引树上查询，这条记录对应的pId=3，因此在pId=3的聚簇索引上加行级S锁，采用当前读。

RR/Serializable+条件列非索引：在pId = 1,2,3,7（全表所有记录）的聚簇索引上加S锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
RR/Serializable+条件列是非聚簇索引：是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加S锁。在(200,300)(300,+∞)加上gap lock。

RC/RU+条件列是聚簇索引：在pId=3，7的聚簇索引上，加S锁，为当前读。
RR/Serializable+条件列是聚簇索引：是当前读，在pId=3,7的聚簇索引上加S锁。在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where num = 200 for update
select * from table where id = 2 for update   (聚簇索引)

RC/RU+条件列非索引：当num = 200，有两条记录。这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级X锁，采用当前读。
RC/RU+条件列是非聚簇索引：当num = 200，由于num列上有索引，因此先在 num = 200的两条索引记录上加行级X锁。接着，去聚簇索引树上查询，这两条记录对应的pId=2，7，因此在pId=2，7的聚簇索引上加行级X锁，采用当前读。

RR/Serializable+条件列非索引：在pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
RR/Serializable+条件列是非聚簇索引：是当前读，在pId=2，7的聚簇索引上加S锁，在num=200的非聚集索引上加X锁，在(100,200)(200,300)加上gap lock。

RC/RU+条件列是聚簇索引：在pId=2的聚簇索引上，加X锁，为当前读。
RR/Serializable+条件列是聚簇索引：是当前读，在pId=2的聚簇索引上加X锁。

select * from table where num > 200 for update
select * from table where id > 2 for update   (聚簇索引)

RC/RU+条件列非索引：当num > 200，有一条记录。这条记录对应的pId=3，因此在pId=3的聚簇索引上加上行级X锁，采用当前读。
RC/RU+条件列是非聚簇索引：当num > 200，由于num列上有索引，因此先在符合条件的 num = 300的一条索引记录上加行级X锁。接着，去聚簇索引树上查询，这条记录对应的pId=3，因此在pId=3的聚簇索引上加行级X锁，采用当前读。

RR/Serializable+条件列非索引：在pId = 1,2,3,7（全表所有记录）的聚簇索引上加X锁。并且在聚簇索引的所有间隙(-∞,1)(1,2)(2,3)(3,7)(7,+∞)加gap lock
RR/Serializable+条件列是非聚簇索引：是当前读，在pId=3的聚簇索引上加S锁，在num=300的非聚集索引上加X锁。在(200,300)(300,+∞)加上gap lock

RC/RU+条件列是聚簇索引：在pId=3，7的聚簇索引上，加X锁，为当前读。
RR/Serializable+条件列是聚簇索引：在pId=3,7的聚簇索引上加X锁。在(2,3)(3,7)(7,+∞)加上gap lock

select * from table where pId = 6 [lock in share mode|for update]
RR/Serializable+条件列是聚簇索引：注意了，pId=6是不存在的列，这种情况会在(3,7)上加gap lock。

select * from table where num = 250 [lock in share mode|for update]
RR/Serializable+条件列是非聚簇索引：注意了，num=250是不存在的列，这种情况会在(200,300)上加gap lock。

select * from table where pId > 18 [lock in share mode|for update]
RR/Serializable+条件列是聚簇索引：注意了，pId>18，查询结果是空的。在这种情况下，是在(7,+∞)上加gap lock。

select * from table where num > 400 [lock in share mode|for update]
RR/Serializable+条件列是非聚簇索引：注意了，pId>400，查询结果是空的。在这种情况下，是在(400,+∞)上加gap lock。

作者：孤独烟 出处： http://rjzheng.cnblogs.com/
原文链接：https://www.cnblogs.com/rjzheng/p/9950951.html