厦门大学数据库实验室httpdblabxmueducn

2.2
•READ UNCOMMITTED（未提交读）事务在这个级别下，事务所做的修改，即使 没有提交，对其他事务也是可见的。
•READ COMMITTED（提交读）READ COMMITTED下，只能“看见”已提交事务所 做的修改，但是RC会出现一个问题，即同一事务两次读可能得到不一样的结果， 因此，READ COMMITTED又称为不可重复读。
• 非聚集索引即为从属索引，索引在物理上与它描述的表文件分离
锁的类型
从资源竞争的角度理解锁的类型
X
S
X
不兼容
不兼容
S
不兼容
兼容
2.6
锁的类型
2.6
从保护资源的角度理解锁的类型
对象 保护 持续时间 模式 死锁
存在于
lock 事务 数据库内容 整个事务过程 行锁、表锁、意向锁 通过waits-for graph、time out 等机制进行死锁检测 Lock Manager的哈希表中
组合三：id列是二级非唯一索引，RC隔离级别
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;
组合四：id列上没有索引，RC隔离级别
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;
组合五：id列是主键，RR隔离级别
学习目标 背景知识 简单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），从而提高数据库的整体性 能。
• 对于SQL1：select * from t1 where id = 10; 这条SQL，在RC，RR隔离级别下，都 是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快 照读不复存在，MVCC并发控制降级为Lock-Based CC。
• 结论：在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是 隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作 ，都是当前读。
简单SQL语句加锁分析
3
不同前提与不同的隔离级别的组合：
组合一：id列是主键，RC隔离级别 组合二：id列是二级唯一索引，RC隔离级别 组合三：id列是二级非唯一索引，RC隔离级别 组合四：id列上没有索引，RC隔离级别 组合五：id列是主键，RR隔离级别 组合六：id列是二级唯一索引，RR隔离级别 组合七：id列是二级非唯一索引，RR隔离级别 组合八：id列上没有索引，RR隔离级别 组合九：Serializable隔离级别
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;
组合八：id列上没有索引，RR隔离级别
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;
组合九：Serializable隔离级别
• 对于SQL2：delete from t1 where id = 10; 来说，Serializable隔离级别与 Repeatable Read隔离级别完全一致。
取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他 事务不会再并发修改这条记录。
下面的SQL语句哪些是快照读，哪些是当前读？
1. select * from table where ?; 2. select * from table where ? lock in share mode; 3. select * from table where ? for update; 4. insert into table values (…); 5. update table set ? where ?; 6. delete from table where ?;
•REPEATABLE READ（可重复读）REPEATABLE READ级别保证在同一个事务中多次 读取同样的记录结果是一致的。但是理论上，可重复读隔离级别还是无法解决另 外一个幻读的问题。
•SERIALIZABLE（可串行化）最高的隔离级别，强制事务串行执行。
未提 交读
解决脏读
提交 读
解决不可重复读
latch 线程 内存数据结构 临界资源 读写锁、互斥量
无死锁检测机制，如果出现死锁， 则说明数据库存在bug 每个数据结构的对象中
锁的类型
从数据库设计者的角度理解锁的类型 • 表锁 • 行锁 • 意向共享锁（IS）和意向排他锁（IX）
2.6
学习目标 背景知识 简单SQL语句加锁分析 复杂SQL语句加锁分析 总结
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;
组合一：id列是主键，RC隔离级别
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;
组合二：id列是二级唯一索引，RC隔离级别
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;
学习目标 背景知识 简单SQL语句加锁分析 复杂SQL语句加锁分析 总结
目录
背景知识
2
1. 事务的ACID特性 2. 事务的隔离级别 3. 2PL 4. MVCC 5. 聚簇索引 6. 锁类型与锁算法
背景知识
2.1
A D 事务 C
I
背景知识
2.1
日志
ACID
锁
事务的隔离级别
• GAP锁定一个范围，但不包括记录本身
简单SQL语句加锁分析
3
SQL2：delete from t1 where id = 10;
组合五：id列是主键，RR隔离级别 组合六：id列是二级唯一索引，RR隔离级别
为什么组合五、组合六，也是RR隔离级别，却不需要加 GAP锁呢？
对于组合五，id是主键；对于组合六，id是unique键，都能够保证唯一性。 一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会在 新插入进来，因此也就避免了GAP锁的使用。
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并 发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享 锁)外，其他的操作，都加的是X锁 (排它锁)。
聚簇索引
2.5
• 在聚集索引中，叶结点也即数据结点，所有数据行的存储顺序与索引的 存储顺序一致，innodb就是聚集索引（注意：一个表只能有一个聚集索 引）
目录
简单SQL语句加锁分析
3
下面的SQL语句加什么锁？
SQL1：select * from t1 where id = 10; SQL2：delete from t1 where id = 10;
加锁涉及到的因素：
前提一：id列是不是主键？ 前提二：当前系统的隔离级别是什么？ 前提三：id列如果不是主键，那么id列上有索引吗？ 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？ 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？
可重 复读
解决幻读
可串 行化
2PL协议
2.3
MVCC
2.4
1. MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC， MVCC最大的好处是“读不加锁，读写不冲突”。
2. 在MVCC并发控制中，读操ead)与当前读 (current read)。快照读，读取的是记录的可见版本 ，不用加锁。当前读，读
学习目标 背景知识 简单SQL语句加锁分析 复杂SQL语句加锁分析 总结
目录
死锁分析与总结
5
死锁分析与总结
5
死锁分析与总结
5
结论：死锁的发生与否，并不在于事务中有多少 条SQL语句，死锁的关键在于：两个(或以上)的 Session加锁的顺序不一致。
死锁分析与总结
5
总结： 1. MVCC 2. 事务隔离级别 3. 加锁操作的考虑因素 4. 加锁的详细过程 5. 死锁分析
THANKS
简单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隔离级别