Transcript 厦门大学数据库实验室赖明星

厦门大学数据库实验室
http://dblab.xmu.edu.cn
MySQL加锁处理分析
赖明星
2014年5月17日
目录

学习目标

背景知识

简单SQL语句加锁分析

复杂SQL语句加锁分析

总结
学习目标
•
•
•
•
理解锁的概念与锁的类型
理解加锁操作涉及到的考虑因素
理解lock与latch的区别
能够分析简单的SQL语句的加锁情况
1
目录

学习目标

背景知识

简单SQL语句加锁分析

复杂SQL语句加锁分析

总结
背景知识
1.
2.
3.
4.
5.
6.
事务的ACID特性
事务的隔离级别
2PL
MVCC
聚簇索引
锁类型与锁算法
2
2.1
背景知识
A
D
事务
I
C
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
1.
MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC，
MVCC最大的好处是“读不加锁，读写不冲突”。
2.
在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读
(current read)。快照读，读取的是记录的可见版本 ，不用加锁。当前读，读
取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他
事务不会再并发修改这条记录。
下面的SQL语句哪些是快照读，哪些是当前读？
1.
2.
3.
4.
5.
6.
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
X
不兼容
不兼容
S
不兼容
兼容
2.6
锁的类型
从保护资源的角度理解锁的类型
lock
latch
对象
事务
线程
保护
数据库内容
内存数据结构
持续时间
整个事务过程
临界资源
模式
行锁、表锁、意向锁
读写锁、互斥量
死锁
通过waits-for graph、time
out 等机制进行死锁检测
无死锁检测机制，如果出现死锁，
则说明数据库存在bug
存在于
Lock Manager的哈希表中
每个数据结构的对象中
锁的类型
从数据库设计者的角度理解锁的类型
• 表锁
• 行锁
• 意向共享锁（IS）和意向排他锁（IX）
2.6
目录

学习目标

背景知识

简单SQL语句加锁分析

复杂SQL语句加锁分析

总结
简单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列是二级非唯一索引，RC隔离级别

组合四：id列上没有索引，RC隔离级别

组合五：id列是主键，RR隔离级别

组合六：id列是二级唯一索引，RR隔离级别

组合七：id列是二级非唯一索引，RR隔离级别

组合八：id列上没有索引，RR隔离级别

组合九：Serializable隔离级别
3
简单SQL语句加锁分析
SQL1：select * from t1 where id = 10;

组合一：id列是主键，RC隔离级别

组合二：id列是二级唯一索引，RC隔离级别

组合三：id列是二级非唯一索引，RC隔离级别

组合四：id列上没有索引，RC隔离级别

组合五：id列是主键，RR隔离级别

组合六：id列是二级唯一索引，RR隔离级别

组合七：id列是二级非唯一索引，RR隔离级别

组合八：id列上没有索引，RR隔离级别

组合九：Serializable隔离级别
3
简单SQL语句加锁分析
SQL2：delete from t1 where id = 10;

组合一：id列是主键，RC隔离级别

组合二：id列是二级唯一索引，RC隔离级别

组合三：id列是二级非唯一索引，RC隔离级别

组合四：id列上没有索引，RC隔离级别

组合五：id列是主键，RR隔离级别

组合六：id列是二级唯一索引，RR隔离级别

组合七：id列是二级非唯一索引，RR隔离级别

组合八：id列上没有索引，RR隔离级别

组合九：Serializable隔离级别
3
简单SQL语句加锁分析
SQL2：delete from t1 where id = 10;

组合一：id列是主键，RC隔离级别
3
简单SQL语句加锁分析
SQL2：delete from t1 where id = 10;

组合二：id列是二级唯一索引，RC隔离级别
3
简单SQL语句加锁分析
SQL2：delete from t1 where id = 10;

组合三：id列是二级非唯一索引，RC隔离级别
3
简单SQL语句加锁分析
SQL2：delete from t1 where id = 10;

组合四：id列上没有索引，RC隔离级别
3
简单SQL语句加锁分析
SQL2：delete from t1 where id = 10;

组合五：id列是主键，RR隔离级别
 与组合一类似

组合六：id列是二级唯一索引，RR隔离级别
 与组合二类似
3
3
简单SQL语句加锁分析
SQL2：delete from t1 where id = 10;
GAP锁
 组合七：id列是二级非唯一索引，RR隔离级别
RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六
• 为了保证两次读之间没有新的记录插
中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防
入，MySQL引入了GAP锁
止幻读呢
• GAP锁定一个范围，但不包括记录本身
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;

组合五：id列是主键，RR隔离级别

组合六：id列是二级唯一索引，RR隔离级别
为什么组合五、组合六，也是RR隔离级别，却不需要加GAP
锁呢？
对于组合五，id是主键；对于组合六，id是unique键，都能够保证唯一性。一个
等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在
新插入进来，因此也就避免了GAP锁的使用。
简单SQL语句加锁分析
SQL2：delete from t1 where id = 10;

组合八：id列上没有索引，RR隔离级别
3
简单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隔离级别，读不加锁就不再成立，所有的读
操作，都是当前读。
目录

学习目标

背景知识

简单SQL语句加锁分析

复杂SQL语句加锁分析

总结
复杂SQL语句加锁分析
4
加什么锁？
SQL语句的三个阶段：
1. Index key：pubtime > 1 and puptime < 20
2. Index Filter：userid = ‘hdc’
3. Table Filter：comment is not NULL
复杂SQL语句加锁分析
4
Index Condition Pushdown 是MySQL 5.6
开始支持的一种根据索引进行查询的
优化方式。之前的MySQL数据库版本
不支持Index Condition Pushdown，当
进行索引查询时，首先根据索引记录
来查找记录，然后再根据WHEREguol4
记录，在支持Index Condition Pushdown
后，MySQL数据库会在取出索引的同时，
判断是否可以进行WHERE条件的过滤，
也就是将WHERE的部分过滤操作放到
了存储引擎层。在某些查询下，可以
大大减少上层SQL层对记录的索取
（fetch），从而提高数据库的整体性
能。
目录

学习目标

背景知识

简单SQL语句加锁分析

复杂SQL语句加锁分析

总结
死锁分析与总结
5
死锁分析与总结
5
死锁分析与总结
5
结论：死锁的发生与否，并不在于事务中有多少
条SQL语句，死锁的关键在于：两个(或以上)的
Session加锁的顺序不一致。
死锁分析与总结
总结：
1.MVCC
2.事务隔离级别
3.加锁操作的考虑因素
4.加锁的详细过程
5.死锁分析
5
THANKS