厦门大学数据库实验室 http://dblab.xmu.edu.cn MySQL加锁处理分析 赖明星 2014年5月17日

目录 学习目标 背景知识 简单SQL语句加锁分析 复杂SQL语句加锁分析 总结

学习目标 1 理解锁的概念与锁的类型 理解加锁操作涉及到的考虑因素 理解lock与latch的区别 能够分析简单的SQL语句的加锁情况

目录 学习目标 背景知识 简单SQL语句加锁分析 复杂SQL语句加锁分析 总结

背景知识 2 事务的ACID特性 事务的隔离级别 2PL MVCC 聚簇索引 锁类型与锁算法

背景知识 2.1 事务 A C I D

背景知识 2.1 日志 锁 ACID

2.2 事务的隔离级别 READ UNCOMMITTED（未提交读）事务在这个级别下，事务所做的修改，即使没有提交，对其他事务也是可见的。 READ COMMITTED（提交读）READ COMMITTED下，只能“看见”已提交事务所做的修改，但是RC会出现一个问题，即同一事务两次读可能得到不一样的结果，因此，READ COMMITTED又称为不可重复读。 REPEATABLE READ（可重复读）REPEATABLE READ级别保证在同一个事务中多次读取同样的记录结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读的问题。 SERIALIZABLE（可串行化）最高的隔离级别，强制事务串行执行。 未提交读 提交读 可重复读 可串行化 解决脏读 解决不可重复读 解决幻读

2PL协议 2.3

MVCC 2.4 MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC，MVCC最大的好处是“读不加锁，读写不冲突”。 在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 ，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。 下面的SQL语句哪些是快照读，哪些是当前读？ select * from table where ?; select * from table where ? lock in share mode; select * from table where ? for update; insert into table values (…); update table set ? where ?; delete from table where ?;

2.4 MVCC 以MySQL InnoDB为例： 快照读：简单的select操作，属于快照读，不加锁。 select * from table where ?; 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁 select * from table where ? lock in share mode; select * from table where ? for update; insert into table values (…); update table set ? where ?; delete from table where ?; 所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

聚簇索引 2.5 在聚集索引中，叶结点也即数据结点，所有数据行的存储顺序与索引的存储顺序一致，innodb就是聚集索引（注意：一个表只能有一个聚集索引） 非聚集索引即为从属索引，索引在物理上与它描述的表文件分离

锁的类型 2.6 从资源竞争的角度理解锁的类型 X S 不兼容 兼容

2.6 锁的类型 从保护资源的角度理解锁的类型 lock latch 对象 事务 线程 保护 数据库内容 内存数据结构 持续时间 整个事务过程 临界资源 模式 行锁、表锁、意向锁 读写锁、互斥量 死锁 通过waits-for graph、time out 等机制进行死锁检测 无死锁检测机制，如果出现死锁，则说明数据库存在bug 存在于 Lock Manager的哈希表中 每个数据结构的对象中

锁的类型 2.6 从数据库设计者的角度理解锁的类型 表锁 行锁 意向共享锁（IS）和意向排他锁（IX）

目录 学习目标 背景知识 简单SQL语句加锁分析 复杂SQL语句加锁分析 总结

3 简单SQL语句加锁分析 下面的SQL语句加什么锁？ 加锁涉及到的因素： SQL1：select * from t1 where id = 10; SQL2：delete from t1 where id = 10; 加锁涉及到的因素： 前提一：id列是不是主键？ 前提二：当前系统的隔离级别是什么？ 前提三：id列如果不是主键，那么id列上有索引吗？ 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？ 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？

3 简单SQL语句加锁分析 不同前提与不同的隔离级别的组合： 组合一：id列是主键，RC隔离级别 组合二：id列是二级唯一索引，RC隔离级别 组合五：id列是主键，RR隔离级别 组合六：id列是二级唯一索引，RR隔离级别 组合七：id列是二级非唯一索引，RR隔离级别 组合八：id列上没有索引，RR隔离级别 组合九：Serializable隔离级别

3 简单SQL语句加锁分析 SQL1：select * from t1 where id = 10; 组合一：id列是主键，RC隔离级别 组合五：id列是主键，RR隔离级别 组合六：id列是二级唯一索引，RR隔离级别 组合七：id列是二级非唯一索引，RR隔离级别 组合八：id列上没有索引，RR隔离级别 组合九：Serializable隔离级别

3 简单SQL语句加锁分析 SQL2：delete from t1 where id = 10; 组合一：id列是主键，RC隔离级别 组合五：id列是主键，RR隔离级别 组合六：id列是二级唯一索引，RR隔离级别 组合七：id列是二级非唯一索引，RR隔离级别 组合八：id列上没有索引，RR隔离级别 组合九：Serializable隔离级别

简单SQL语句加锁分析 3 SQL2：delete from t1 where id = 10; 组合一：id列是主键，RC隔离级别

3 简单SQL语句加锁分析 SQL2：delete from t1 where id = 10; 组合二：id列是二级唯一索引，RC隔离级别 为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

3 简单SQL语句加锁分析 SQL2：delete from t1 where id = 10; 组合三：id列是二级非唯一索引，RC隔离级别 首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

简单SQL语句加锁分析 3 SQL2：delete from t1 where id = 10; 组合四：id列上没有索引，RC隔离级别

3 简单SQL语句加锁分析 SQL2：delete from t1 where id = 10; 组合五：id列是主键，RR隔离级别 与组合一类似 组合六：id列是二级唯一索引，RR隔离级别 与组合二类似

3 简单SQL语句加锁分析 SQL2：delete from t1 where id = 10; GAP锁 组合七：id列是二级非唯一索引，RR隔离级别 RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢 GAP锁 为了保证两次读之间没有新的记录插入，MySQL引入了GAP锁 GAP锁定一个范围，但不包括记录本身 Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。  

3 简单SQL语句加锁分析 SQL2：delete from t1 where id = 10; 组合五：id列是主键，RR隔离级别 组合六：id列是二级唯一索引，RR隔离级别 为什么组合五、组合六，也是RR隔离级别，却不需要加GAP锁呢？ 对于组合五，id是主键；对于组合六，id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在新插入进来，因此也就避免了GAP锁的使用。

3 简单SQL语句加锁分析 SQL2：delete from t1 where id = 10; 组合八：id列上没有索引，RR隔离级别 在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

3 简单SQL语句加锁分析 SQL2：delete from t1 where id = 10; 组合九：Serializable隔离级别 对于SQL2：delete from t1 where id = 10; 来说，Serializable隔离级别与Repeatable Read隔离级别完全一致。 对于SQL1：select * from t1 where id = 10; 这条SQL，在RC，RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。 结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

目录 学习目标 背景知识 简单SQL语句加锁分析 复杂SQL语句加锁分析 总结

4 复杂SQL语句加锁分析 加什么锁？ SQL语句的三个阶段： Index key：pubtime > 1 and puptime < 20 Index Filter：userid = ‘hdc’ Table Filter：comment is not NULL

4 复杂SQL语句加锁分析 Index Condition Pushdown 是MySQL 5.6 开始支持的一种根据索引进行查询的 进行索引查询时，首先根据索引记录 来查找记录，然后再根据WHEREguol4 记录，在支持Index Condition Pushdown 后，MySQL数据库会在取出索引的同时， 判断是否可以进行WHERE条件的过滤， 也就是将WHERE的部分过滤操作放到 了存储引擎层。在某些查询下，可以 大大减少上层SQL层对记录的索取 （fetch），从而提高数据库的整体性 能。

目录 学习目标 背景知识 简单SQL语句加锁分析 复杂SQL语句加锁分析 总结

死锁分析与总结 5

死锁分析与总结 5

死锁分析与总结 5 结论：死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

死锁分析与总结 5 总结： MVCC 事务隔离级别 加锁操作的考虑因素 加锁的详细过程 死锁分析

THANKS