test

# InnoDB事务支持

## 1. 事务（Transaction）及其ACID属性

### 1.1 ACID

~~~ bash
事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。
1. 原子性。要么都做，要么都不做。与原子操作不同，原子操作是不能打断的，而原子性是可以插入其他操作的。
2. 一致性。要么成功，要么回滚。
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是不一致的状态。
3. 隔离性。一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
4. 持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
~~~

### 1.2 并发事务带来的问题

~~~ bash
脏读
所谓的脏读，其实就是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。

不可重复读
事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。

幻读
事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。也就是说，当前事务读第一次取到的数据比后来读取到数据条目少

解决:
不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
~~~

### 1.2 事务的隔离级别

~~~ bash
1. Read Uncommitted 读取未提交的内容，由于会存在脏读，不可重复读以及幻读，所以基本不会用该隔离级别。该级别对任何操作都没有加锁。
2. Read committed 读取提交的内容，该级别对读不加锁，对写是加锁的，所以会出现不可重复读以及幻读。不可重复读是因为在两次读取数据中间可能会有其他事务新增或者修改数据。
3. Repeatable Read 可重读 mysql默认的事务隔离级别，对读和写都会加锁，但是无法避免幻读，因为读加锁，只是锁读的数据，不会锁表，无法阻止其他事务新增数据
4. Serializable 串行化
强制事务排序，事物之间不可能产生冲突

查看级别类型:
mysql> select @@transaction_isolation;
设置
vim /etc/my.cnf
transaction_isolation=READ-UNCOMMITTED
重启
/etc/init.d/mysqld restart
~~~

| 隔离级别 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
| -------- | -------------------------------------- | ---- | ---------- | ---- |
| RU | 最低级别，只能保证不读取物理上的坏数据 | 是 | 是 | 是 |
| RC | 语句级 | 否 | 是 | 是 |
| RR | 事务级 | 否 | 否 | 是 |
| SE | 最高级别，事务级 | 否 | 否 | 否 |

### 1.3 乐观锁与悲观锁

~~~ bash
悲观锁：读写加锁，上面的可重读实现方式，这样会占用大量的开销，减少并发量
乐观锁：使用数据版本MVCC，读数据会读取数据的版本，更新数据时会对版本加一。提交时，如果数据版本与当前版本不一致，会认为是过期数据。
~~~

## 2. 事务的生命周期管理

- **事务生命周期中，只能使用DML语句（select、update、delete、insert）**

### 2.1 开启事务

~~~ bash
begin;
DML
commit;
~~~

### 2.2 回滚事务

~~~ bash
begin;
DML
rollback;
~~~

### 2.3 事务生命周期演示

~~~ bash
commit提交事务以后DML语句才可以生效。
mysql> use world
mysql> begin;
mysql> delete from city where id=1;
mysql> update city set countrycode='CHN' where id=2;
mysql> commit;

只能在commit提交事务前，使用rollback回滚。
mysql> begin;
mysql> select * from city limit 10;
mysql> update city set countrycode='AFG' where id=2;
mysql> delete from city where id=3;
mysql> rollback;
~~~

### 2.4 自动提交事务机制

~~~ bash
查看提交机制
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
作用：
在没有显示的使用begin语句的时候，执行DML，会在DML前自动添加begin，并在DML执行后自动添加commit。
建议：
频繁事务业务场景中，关闭autocommit,或者每次事务执行时都加入begin和commit;

关闭方法：
临时：
mysql> set global autocommit=0;
退出会话，重新连接配置生效。

永久：
vim /etc/my.cnf
autocommit=0
重启生效。
~~~

### 2.5 隐式提交和回滚

~~~ bash
隐式提交情况:
一条事务语句没有提交，再次begin，会自动提交事务语句。
begin;
DML
begin;

开启自动提交:
SET AUTOCOMMIT = 1

导致提交的非事务语句:
begin;
DML
drop xxx
DDL语句: ALTER、CREATE 和 DROP
DCL语句: GRANT、REVOKE 和 SET PASSWORD
lock语句: LOCK TABLES 和 UNLOCK TABLES

导致隐式提交的语句示例:
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

隐式回滚:
会话窗口被关闭。
数据库关闭。
出现事务冲突（死锁）。
~~~

## 3. 锁类型

### 2PL：Two-Phase Locking

~~~ bash
传统RDBMS加锁的一个原则，就是2PL (二阶段锁)：Two-Phase Locking，相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。
~~~

![img](https://img-blog.csdnimg.cn/20200522113554821.jpg)

~~~ bash
从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。
~~~

## 4. 不同情况下锁处理

### 4.1 id主键+RC

~~~ bash
这个组合，是最简单，最容分析的组合。id是主键，read committed隔离级别。
sql: delete from t1 where id=10;
只需要将主键上id=10的记录加上X锁即可。如图:
~~~

![img](https://img-blog.csdnimg.cn/20200522115950704.jpg)

### 4.2 id唯一索引+RC

~~~ bash
这个组合id不是主键，而是一个unique的二级索引键值，那么在RC隔离级别下:
SQL:delete from t1 where id =10;需要加什么锁？图示
~~~

![img](https://img-blog.csdnimg.cn/20200522121016563.jpg)

~~~ bash
此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = 'd' 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想一下，如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = 'd'; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name='d',id=10]的记录。
~~~

### 4.3 id非唯一索引+RC

~~~ bash
对于组合一，二，组合三又发生了变化，隔离级别仍旧是RC，但是id列上的约束又降低了，id列不在唯一。只是一个普通的索引。SQL:delete from t1 where id =10;语句仍旧选择id列上的索引进行过滤，那么此时持有哪些锁？图示:
~~~

![img](https://img-blog.csdnimg.cn/20200522121715975.jpg)

~~~ bash
根据此图，可以看到，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。
~~~

### 4.4 id无索引 + RC

~~~ bash
相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id = 10的记录加上X锁。那么实际情况呢？请看下图：
~~~

![img](https://img-blog.csdnimg.cn/20200522132318513.jpg)

~~~ bash
由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。

有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束
~~~

### 4.5 id主键 + RR

~~~ bash
上面的四个组合，都是在Read Committed隔离级别下的加锁行为，接下来的四个组合，是在Repeatable Read隔离级别下的加锁行为。

组合五：id列是主键列，Repeatable Read隔离级别。
针对delete from t1 where id = 10; 这条SQL，加锁与组合一：[id主键，Read Committed]一致。
~~~

### 4.6 id唯一索引 + RR

~~~ bash
与组合五类似，组合六的加锁与组合二：[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。
~~~

### 4.7 id非唯一索引 + RR

~~~ bash
还记得前面提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢？问题的答案，就在组合七中揭晓。

组合七，Repeatable Read隔离级别，id上有一个非唯一索引，执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下面这幅图：
~~~

![img](https://img-blog.csdnimg.cn/20200522132732407.jpg)

~~~ bash
此图，相对于组合三：[id列上非唯一锁，Read Committed]看似相同，其实却有很大的区别。最大的区别在于，这幅图中多了一个GAP锁，而且GAP锁看起来也不是加在记录上的，倒像是加载两条记录之间的位置，GAP锁有何用？

其实这个多出来的GAP锁，就是RR隔离级别相对于RC隔离级别不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，第二次的当前读会比第一次返回更多的记录的现象 (幻象)。

如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生。

如图中所示，有哪些位置可以插入新的满足条件的项 (id = 10)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。记录[6,c]之前，不会插入id=10的记录；[6,c]与[10,b]间可以插入[10, aa]；[10,b]与[10,d]间，可以插入新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等；而[11,f]之后也不会插入满足条件的记录。因此，为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。

Insert操作如insert [10,aa]，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X锁)，与组合三类似。同时还是增加3把GAP锁，将可能插入满足条件记录的3个GAP给锁上，保证后续的Insert不能插入新的id=10的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

有心的朋友看到这儿，可以会问：既然防止幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是RR隔离级别，却不需要加GAP锁呢？

首先，这是一个好问题。其次，回答这个问题，也很简单。GAP锁的目的，是为了防止同一事务的两次当前读出现幻读的情况。而组合五，id是主键；组合六，id是unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会再新插入成功，因此也就避免了GAP锁的使用。其实，针对此问题，还有一个更深入的问题：如果组合五、组合六下，针对SQL：select * from t1 where id = 10 for update; 第一次查询，没有找到满足查询条件的记录，那么GAP锁是否还能够省略？此问题留给大家思考。

结论：Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
~~~

### 4.8 id无索引 + RR

~~~ bash
组合八，Repeatable Read隔离级别下的最后一种情况，id列上没有索引。此时SQL：delete from t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如下图所示：
~~~

![img](https://img-blog.csdnimg.cn/20200522133541614.jpg)

~~~ bash
如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个示例表，只有6条记录，一共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？

在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。

当然，跟组合四：[id无索引, Read Committed]类似，这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满足查询条件的记录，MySQL会提前放锁。针对上面的这个用例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistent read如何触发：要么是Read Committed隔离级别；要么是Repeatable Read隔离级别，同时设置了innodb_locks_unsafe_for_binlog 参数。

结论：在Repeatable Read隔离级别下，如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发更新/删除/插入操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。
~~~

### 4.9 Serializable

~~~ bash
针对前面提到的简单的SQL，最后一个情况：Serializable隔离级别。对于SQL2：delete from t1 where id = 10; 来说，Serializable隔离级别与Repeatable Read隔离级别完全一致，因此不做介绍。

Serializable隔离级别，影响的是SQL1：select * from t1 where id = 10; 这条SQL，在RC、RR隔离级别下，都是快照读，不加锁。但是在Serializable隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

结论：在MySQL InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是隔离级别相关的。Serializable隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。
~~~

### 4.10 复杂的SQL

~~~ bash
写到这里，其实MySQL的加锁实现也已经介绍的八八九九。只要将本文上面的分析思路，大部分的SQL，都能分析出其会加哪些锁。而这里，再来看一个稍微复杂点的SQL，用于说明MySQL加锁的另外一个逻辑。SQL用例如下：
~~~

![img](https://img-blog.csdnimg.cn/20200522153651253.jpg)

~~~ bash
如图中的SQL，会加什么锁？假定在Repeatable Read隔离级别下 (Read Committed隔离级别下的加锁情况，留给读者分析)，同时，假设SQL走的是idx_t1_pu索引。

在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分？在这里，我直接给出分析后的结果：

Index key：pubtime > 1 and puptime < 20，此条件用于确定SQL在idx_t1_pu索引上的查询范围。
Index Filter：userid = ‘hdc’ ，此条件可以在idx_t1_pu索引上进行过滤，但不属于Index Key。
Table Filter：comment is not NULL，此条件在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。
在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)，如下图所示：
~~~

![img](https://img-blog.csdnimg.cn/20200522153922532.jpg)

~~~ bash
从图中可以看出，在Repeatable Read隔离级别下，由Index Key所确定的范围，被加上了GAP锁；Index Filter锁给定的条件（即userid = ‘hdc’）何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown（简称ICP索引条件下推），因此Index Filter在MySQL Server层过滤，在5.6后支持了Index Condition Pushdown，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁（图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定）；而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

结论：在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；Table Filter过滤条件，无论是否满足，都需要加X锁。
~~~

### 4.11 总结

~~~ bash
写到这儿，本文也告一段落，做一个简单的总结，要做的完全掌握MySQL/InnoDB的加锁规则，甚至是其他任何数据库的加锁规则，需要具备以下的一些知识点：

了解数据库的一些基本理论知识：数据的存储格式（堆组织表 VS 聚簇索引表）；并发控制协议 (MVCC VS Lock-Based CC)；Two-Phase Locking；数据库的隔离级别定义 (Isolation Level)；
了解SQL本身的执行计划（主键扫描 VS 唯一键扫描 VS 范围扫描 VS 全表扫描）；
了解数据库本身的一些实现细节，过滤条件提取、Index Condition Pushdown、Semi-Consistent Read；
~~~

## 5.0 UNDO REDO在I,U,D操作如何工作

### 5.1 insert

~~~ bash

~~~

![image-20200924001505195](D:\MySQL的typora\10、MySQL InnoDB事务支持.assets\image-20200924001505195.png)

### 5.2 delete

~~~ bash

~~~

![image-20200924001738622](D:\MySQL的typora\10、MySQL InnoDB事务支持.assets\image-20200924001738622.png)

### 5.3 update 情景一

- **未修改聚簇索引键值，属性列长度未变化**

~~~ bash

~~~

![image-20200924002043757](D:\MySQL的typora\10、MySQL InnoDB事务支持.assets\image-20200924002043757.png)

### 5.4 unpdate 情景二

- **未修改聚簇索引键值，属性列长度发生变化**

~~~ bash

~~~

![image-20200924002128944](D:\MySQL的typora\10、MySQL InnoDB事务支持.assets\image-20200924002128944.png)

### 5.5 update 情景三

- **修改聚簇索引键值**

~~~ bash

~~~

![image-20200924002228959](D:\MySQL的typora\10、MySQL InnoDB事务支持.assets\image-20200924002228959.png)

## 6.0 mini-transaction in practice

~~~ bash

~~~

![image-20200924002432311](D:\MySQL的typora\10、MySQL InnoDB事务支持.assets\image-20200924002432311.png)

## 7.0 MVCC

~~~ bash

~~~

## 8.0 innoDB核心参数调整及状态监控

posted @ 2020-09-24 00:43 光怪露露阅读(137) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

光怪露露

test

公告