文章目录

第1章 InnoDB事务的介绍与实践1.1 SQL语言的分类与事务提交1.2 通过实践简单理解事务流程1.2.1 事务整个流程中的关键字1.2.2 事务提交成功后不可回滚1.2.3 事务未提交进行全部回滚1.2.4 事务未提交回滚到某保存点 1.3 事务的相关概念基本说明1.3.1 事务的ACID理论说明1.3.2 并发事务出现的问题1.3.3 事务的几种隔离级别1.3.4 事务的隔离级别对比1.3.5 查看事务的隔离级别

第1章 InnoDB事务的介绍与实践

1.1 SQL语言的分类与事务提交

SQL语言的分类

各类SQL语句是怎样提交的

##### 首先说明 我们知道有一个变量autocommit，它的含义就是是否开启自动提交,有on和off两个值; 这个变量是可以在线修改(可以是全局和当前所在会话)的;但是有一些"类"的语句不会受 这个变量值的影响; #### 不受autocommit在全局和会话模式下的值的影响 DDL、DCL、TCL这三类语句不受autocommit值的影响，如下所例： 01：当前autocommit的值为off,表示事务不自动提交;用命令select @@global.autocommit,@@autocommit;查询 02：我创建一个chenliang库：create database chenliang;它会自动提交,因为DDL语言是自动提交的 #### 受autocommit在全局和会话模式下的值的影响 01：当前autocommit的值为off,表示事务不自动提交;用命令select @@global.autocommit,@@autocommit;查询 02：往表中插入数据,insert into test1(id) values(1); ^== 插入数据是成功了,至于其它会话能不能够看到,需要看你mysql的隔离级别了; ^== 你如果不执行commit;那么当你退出当前会话,之前插入到表中的数据就会消失; #### autocommit生产中怎么设置 01：autocomit主要是针对DML语句是否自动提交; 我生产中是开启的,即autocommit=on 02：但我们在在操作DML语句时,建议最后还是执行commit;命令,这要养成习惯;

1.2 通过实践简单理解事务流程

1.2.1 事务整个流程中的关键字

流程关键字

关键字说明start transaction;# 开始一个事务;等于begin;savepoint a;# 设置保存点为a,保存点名称不能以数字开头;rollback;# 回滚整个事务,放弃事务对数据的修改;rollback to a;# 回滚至事务的a保存点;commit;# 事务提交,整个事务结束;

注意事项

01：流程中的关键字属于TCL（事务控制语言）类型事务,不受autocommit在会话模式和全局模式下值的影响;但一定得按 照顺序来哈。 02：在设置保存(savepoint)点时,保存点的名称不要以"数字"开头; 03：若一个事务中设置了保存点,还没有提交(commit)，但在进行了rollback时没有指定保存点,就回到了事务最开始, 此时不可能再回退到保存点上了; 04：若一个事务中设置了多个保存点,还没有提交(commit),以下用举例来说明： A：设置了两个保存点,名称分别是A和B B：用rollbackup回到了保存点A，命令：rollbackup to A; C：此时不能再回到保存点B了;

1.2.2 事务提交成功后不可回滚

#### 环境说明和准备 -- 事务在全局和当前会话模式下是自动提交的 -- 事务隔离级别在全局和当前会话下都是读已提交(read-committed) mysql> select @@global.autocommit,@@autocommit; +---------------------+--------------+ | @@global.autocommit | @@autocommit | +---------------------+--------------+ | 1 | 1 | +---------------------+--------------+ 1 row in set (0.00 sec) mysql> select @@global.transaction_isolation,@@transaction_isolation; +--------------------------------+-------------------------+ | @@global.transaction_isolation | @@transaction_isolation | +--------------------------------+-------------------------+ | READ-COMMITTED | READ-COMMITTED | +--------------------------------+-------------------------+ 1 row in set (0.00 sec) -- 创建chenliang库,并进入到chenliang库下面 create database if not exists chenliang character set utf8 collate utf8_general_ci; show databases like "chenliang"; use chenliang; -- 创建test1表 create table `test1` ( `id` int(10) unsigned not null auto_increment comment '序列', `name` varchar(20) not null comment '姓名', `age` tinyint(3) unsigned not null comment '年龄', primary key (`id`) ) engine=innodb default charset=utf8 comment='测试表1'; #### 事务一但提交成功,不可再进行回滚的验证 -- 开始事务 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) -- 往test1表中插入数据,并提交事务 mysql> insert into test1(name,age) values("chenliang01",24); Query OK, 1 row affected (0.00 sec) mysql> select * from test1; +----+-------------+-----+ | id | name | age | +----+-------------+-----+ | 1 | chenliang01 | 24 | +----+-------------+-----+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) -- 回滚事务,看是否会生效 mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from test1; +----+-------------+-----+ | id | name | age | +----+-------------+-----+ | 1 | chenliang01 | 24 | +----+-------------+-----+ 1 row in set (0.00 sec) #### 实践总结 事务一但提交(commit),无法进行回滚;

1.2.3 事务未提交进行全部回滚

#### 环境说明和准备 -- 事务在全局和当前会话模式下是自动提交的 -- 事务隔离级别在全局和当前会话下都是读已提交(read-committed) mysql> select @@global.autocommit,@@autocommit; +---------------------+--------------+ | @@global.autocommit | @@autocommit | +---------------------+--------------+ | 1 | 1 | +---------------------+--------------+ 1 row in set (0.00 sec) mysql> select @@global.transaction_isolation,@@transaction_isolation; +--------------------------------+-------------------------+ | @@global.transaction_isolation | @@transaction_isolation | +--------------------------------+-------------------------+ | READ-COMMITTED | READ-COMMITTED | +--------------------------------+-------------------------+ 1 row in set (0.00 sec) -- 创建chenliang库,并进入到chenliang库下面 create database if not exists chenliang character set utf8 collate utf8_general_ci; show databases like "chenliang"; use chenliang; -- 创建test2表 create table `test2` ( `id` int(10) unsigned not null auto_increment comment '序列', `name` varchar(20) not null comment '姓名', `age` tinyint(3) unsigned not null comment '年龄', primary key (`id`) ) engine=innodb default charset=utf8 comment='测试表2'; #### 事务未提交,回滚至事务开始的实践 -- 开始1个事务 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) -- 往test2表中插入数据,不提交事务哈,且查看插入到表中的数据 mysql> insert into test2(name,age) values("cl01",26); Query OK, 1 row affected (0.00 sec) mysql> select * from test2; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | cl01 | 26 | +----+------+-----+ 1 row in set (0.00 sec) -- 回滚事务(回滚至事务开始) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select * from test2; Empty set (0.00 sec) -- 结束这个事务 mysql> commit; <== 提交这个事务（之前的数据已回滚哈） Query OK, 0 rows affected (0.00 sec) mysql> select * from test2; <== 查看test2表中的数据 Empty set (0.00 sec) #### 实践总结 事务未提交（commit）可以进行回滚;

1.2.4 事务未提交回滚到某保存点

#### 环境说明和准备 -- 事务在全局和当前会话模式下是自动提交的 -- 事务隔离级别在全局和当前会话下都是读已提交(read-committed) mysql> select @@global.autocommit,@@autocommit; +---------------------+--------------+ | @@global.autocommit | @@autocommit | +---------------------+--------------+ | 1 | 1 | +---------------------+--------------+ 1 row in set (0.00 sec) mysql> select @@global.transaction_isolation,@@transaction_isolation; +--------------------------------+-------------------------+ | @@global.transaction_isolation | @@transaction_isolation | +--------------------------------+-------------------------+ | READ-COMMITTED | READ-COMMITTED | +--------------------------------+-------------------------+ 1 row in set (0.00 sec) -- 创建chenliang库,并进入到chenliang库下面 create database if not exists chenliang character set utf8 collate utf8_general_ci; show databases like "chenliang"; use chenliang; -- 创建test3表 create table `test3` ( `id` int(10) unsigned not null auto_increment comment '序列', `name` varchar(20) not null comment '姓名', `age` tinyint(3) unsigned not null comment '年龄', primary key (`id`) ) engine=innodb default charset=utf8 comment='测试表3'; #### 事务未提交,回滚至某个保存点 -- 开始事务 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) -- 往test3表中插入一条数据,并设置保存点"lili" mysql> insert into test3(name,age) values("lili",24); Query OK, 1 row affected (0.00 sec) mysql> select * from test3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lili | 24 | +----+------+-----+ 1 row in set (0.00 sec) mysql> savepoint lili; Query OK, 0 rows affected (0.00 sec) -- 再往test3表中插入一条数据,设置保存点为"lanlan" mysql> insert into test3(name,age) values("lanlan",24); Query OK, 1 row affected (0.00 sec) mysql> select * from test3; +----+--------+-----+ | id | name | age | +----+--------+-----+ | 1 | lili | 24 | | 2 | lanlan | 24 | +----+--------+-----+ 2 rows in set (0.00 sec) mysql> savepoint lanlan; Query OK, 0 rows affected (0.00 sec) -- 回滚至保存点"lili" mysql> rollback to lili; Query OK, 0 rows affected (0.00 sec) mysql> select * from test3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lili | 24 | +----+------+-----+ 1 row in set (0.00 sec) -- 提交事务 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from test3; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | lili | 24 | +----+------+-----+ 1 row in set (0.00 sec) #### 实践总结 事务未提交(commit),可回滚至某个保存点(前提是要设置保存点);

1.3 事务的相关概念基本说明

1.3.1 事务的ACID理论说明

#### 原子性(Atomicity) 事务开始后所有操作，要么成功，要么失败，不可能说成功了一半,事务执行过程中出错，会回 滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体， 就像化学中学过的原子，是物质构成的基本单位。 #### 一致性(consistency) 事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转帐，不可能A扣 了钱，B却没收到。 #### 隔离性(lsolation) 同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正 在从一张银行卡中取钱，在A取钱的过程前，B不能向这张卡转钱。 #### 持久性(Durability) 事务提交后，事务对数据库的所有更新将被保存到数据库，不能回滚。

1.3.2 并发事务出现的问题

更新丢失

#### 举例理解 01：A事务和B事务同时修改数据 02：A事务将数值改为1并提交； 03：B事务将数值改为2并提交；此时A事务做的工作相当于白做了; 04：从逻辑上来说这种操作是正常的哈; ### 需求说明 我现在要想对表中某些数据修改,在修改未完成之前,我不想让其它人(事务)对其修改; 只有待我把这些数据修改成功,且事务提交了以后,才允许其它人(事务)对其做修改; ### 解决方法 对要修改的数据进行加锁,待数据修改成功且提交后,对其进行解锁;

脏读

#### 举例理解 01：A事务改变表中的某些数据,此时A事务还没有提交啊； 02：B事务就读取到了A事务改变的数据,并返回给客户端； 03：A事务发现数据改变有误,就进行回滚,那么A事务实际上就没有改变数据; 04：B事务之前读取到的数据就是所谓的脏数据; #### 总结： 一个事务读取到了另一个事务未提交的数据,就是脏读

不可重复读

#### 举例理解 01：A事务查看到陈同学的工资=10000,此时A事务还没有提交; 02：B事务此时将陈同学的工资修改成了20000,且进行提交,陈同学的实际工资已变成20000了; 03：A事务再次查看陈同学的工资,结果变成20000了; #### 总结： 一个事务在未提交前多次查询,返回的是不同的数据值,这是由于在查询的间隔期间

幻读

#### 举例理解 01：陈同学的工资是10000; 02：A发起事务查询表中"工资"等于10000的有多少人,事务提交;结果是8人; 03：B发起事务将陈同学的工资修改成了20000,事务提交,陈同学的实际工资变成了20000; 04：A再发起事务,查询表中"工资"等于10000的有多少人,事务提交,结果是7人,没有陈同学; ### 总结： 多个独立的事务查询的数据结果不一样,这是由于在查询的间隔中,数据被其它事务给修改了

特别注意： "不可重复"读侧重是查询,"幻读"则侧重于新增或删除; 一个是在一个单独的未提交的事务内; 一个是在多个独立的已提交的事务间；

1.3.3 事务的几种隔离级别

事务的隔离级别有： 读未提交(read-uncommitted)、读已提交(read-committed)、重复读(repeatable read）和串行读(serializable)。 生产中首先排除两种： 读未提交(read uncommited)、串行读(serializable)两种，因为MVCC不支持，MVCC后面会有介绍到； 生产中就就只剩两种： 读已提交(read committed)、重复读(repeatable read） 最终选择：读已提交(read-committed) 重复读(repeatable read)：MySQL默认的隔离级别 一个事务未提交,查看的数据都是一样的,即使数据被其它事务更改且提交了。若使用重复读(repeatable read)， 那么你的DML语句,就得严格按照事务的流程来编写；但生产中,开发程序里面的DML语句有没有严格按照事务 的流程来编写呢？恐怕开发人员也无法保证。 读已提交(read-committed) 只有当前事务提交了以后,其它事务才能看到当前事务整改的数据;

读未提交（read uncommitted）

## 概述： 一个事务可以读到了另一个事务未提交的数据; ## 举例： 01:A事务插入数据到表中,但不提交; 02:此时B事务是可以读到了A事务插入到表的数据;

读已提交（read committed）

## 概述： 只有当前事务提交了以后,其它事务才能够读取到这个事务提交的数据; ## 举例： 01:A事务插入数据表中,此时不提交; 02:B事务读取不到A事务插入到表中的数据的; 03:A事务提交（commit）； 04:B事务再次读到表中的数据,此时是可以看到A事务插入到表中的数据的;

重复读（repeatable read）

## 概述： 01：MySQL默认的隔离级别; 02:一个事务内（事务未提交）读到的数据都一样,不管数据有没有被另一个事务修改且提交; ## 举例： 01:会话A发起一个事务a,往表中插入数据1且提交(不提交,其它事务是看不到的哈); 02:会话B发起一个事务b,查看表中的数据,结果是数据1,此时b事务不提交; 03:会话A再发起一个事务aa,往表中插入数据2,且提交; 04:会话B在事务b的基础上再次查看数据,结果还是数据1,看不到aa事务插入的数据2; 05:若想在会话B中看到事务aa插入的数据2;需要做如下所示： (1):会话B中结束事务b,即commit;(2):会话B中重新发起一个事务bb,进行查看;

串行读（serializable）

## 概述： 01:隔离级别最高,牺牲了mysql的并发性,可以解决事务的所有问题 02:只有前面的事务操作完成后,后面排除的事务才能够操作数据; ## 举例: 01：A事务查看表中的数据,但事务不提交(A事务还会结束); 02：此时不管是B事务还是C事务都无法修改表中的数据,但可以读,读写/写读会阻塞; 03：此时A事务结束(commit); 04：B事务就可以修改表中的数据了,但是C事务还得排队; 05：以此类推;

1.3.4 事务的隔离级别对比

隔离级别脏读不可重复读幻读read-uncommitted（读未提交）会出现会出现会出现read-committed（读已提交）不会出现会出现会出现repeatable-read（重复读）不会出现不会出现会出现serializable（串行读）不会出现不会出现不会出现

1.3.5 查看事务的隔离级别

注意：事务的隔离级别是可以在线对全局和当前会话模式下进行更改,但生产中不要随便修改哈

## 查看当前mysql实例全局下的事务隔离级别和当前会话下的事务隔离级别 mysql> select @@global.transaction_isolation,@@transaction_isolation; +--------------------------------+-------------------------+ | @@global.transaction_isolation | @@transaction_isolation | +--------------------------------+-------------------------+ | READ-COMMITTED | READ-COMMITTED | +--------------------------------+-------------------------+ 1 row in set (0.00 sec) ^== 生产中应该设置成读已提交(read-committed)