厦门大学数据库实验室 dblab.xmu

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

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

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

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

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

6. 背景知识 2.1

7. 背景知识 2.1

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

9. 2PL协议 2.3

10. 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 ?;

11. MVCC 2.4 以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锁 (排它锁)。

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

13. 锁的类型 2.6 从资源竞争的角度理解锁的类型

14. 锁的类型 2.6 从保护资源的角度理解锁的类型

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

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

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

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

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

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

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

22. 简单SQL语句加锁分析 3 SQL2：delete from t1 where id = 10; • 组合二：id列是二级唯一索引，RC隔离级别

23. 简单SQL语句加锁分析 3 SQL2：delete from t1 where id = 10; • 组合三：id列是二级非唯一索引，RC隔离级别

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

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

26. 简单SQL语句加锁分析 3 SQL2：delete from t1 where id = 10; • 组合七：id列是二级非唯一索引，RR隔离级别 RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢

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

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

29. 简单SQL语句加锁分析 3 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隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。

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

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

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

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

34. 死锁分析与总结 5

35. 死锁分析与总结 5

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

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

38. THANKS