sql 事务

一、简介

        事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

        比如：张三给李四转账1000 块钱，张三银行账户的钱减少 1000，而李四银行账户的钱要增加 1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

        正常情况：转账这个操作，需要分为以下这么三步来完成，三步完成之后，张三减少 1000， 而李四增加 1000，转账成功 ：

        异常情况：转账这个操作，也是分为以下这么三步来完成，在执行第三步是报错了，这样就导致张三减少 1000 块钱，而李四的金额没变，这样就造成了数据的不一致，就出现问题了。

        为了解决上述的问题，就需要通过数据的事务来完成，我们只需要在业务逻辑执行之前开启事务，执行完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。

        注意：默认 MySQL 的事务是自动提交的，也就是说，当执行完一条 DML 语句时，MySQL 会立即隐式的提交事务。

二、事务操作

2.1 数据准备

drop table if exists account;
	create table account(
	id int primary key AUTO_INCREMENT comment 'ID',
	name varchar(10) comment '姓名',
	money double(10,2) comment '余额'
) comment '账户表';

insert into account(name, money) VALUES ('张三',2000), ('李四',2000);

2.2 未控制事务

2.2.1 正常情况

        首先我们测试下正常的情况，执行下面的 sql 语句

-- 1. 查询张三余额
select * from account where name = '张三';

-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';

-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

        测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。 

2.2.2 异常情况

        接下来我们测试下异常的情况，执行下面的 sql 语句。先把数据都恢复到 2000，再执行下面的语句。

-- 1. 查询张三余额
select * from account where name = '张三';

-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';

出错了....

-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

        出错了.... 这句话不符合 SQL 语法，执行就会报错，检查最终的数据情况, 发现数据在操作前后不一致了。

2.3 控制事务方式一

        控制事务有两种方式，我们先讲解第一种方式，首先我们可以查看/设置事务的提交方式，命令如下：

-- 查看事务的提交方式
SELECT @@autocommit ;

-- 设置事务为手动提交的方式
SET @@autocommit = 0 ;

         如果等于 1 就是自动提交，如果等于 0 就需要手动提交。

        如果改为手动提交的方式，那么接下来执行增删改的操作之后，要想提交事务，那么必须要执行下面的指令：

-- 手动提交事务
COMMIT;

        如果在执行的过程中出现了异常，那么我们可以执行另外一条指令来回滚数据，命令如下：

-- 回滚事务
ROLLBACK;

        注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交，此时我们执行的 DML 语句都不会提交事务，需要手动的执行 commit 进行提交。

2.3.1 程序正常执行

        接下来我们使用手动提交事务的方式来测试下效果，执行下面的 sql 语句：

-- 1、把表里面的数据还原成每人 2000
update account set money = money + 1000 where name = '李四';

-- 2、设置事务的提交方式为手动
SET @@autocommit = 0 ;

-- 3. 查询张三余额
select * from account where name = '张三';

-- 4. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';

-- 5. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

         执行完上面的代码之后，打开一个新的查询窗口，查看下数据库里面的数据，如下，发现数据并没有发生变化

        如果想要把上面执行的操作提交到数据库，那么我们就需要执行一个指令来提交事务，

COMMIT;

        执行完 commit 命令之后，再次打开查询窗口，刷新下，发现事务执行完成了

2.3.2  程序异常执行

        接下来我们验证下，程序异常执行，手动提交事务该如何处理的情况，记得先把表里面的数据改成每人 2000，然后再执行下面的 sql 语句

-- 1. 查询张三余额
select * from account where name = '张三';

-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';

出错了....

-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

        我们发现程序报错了，如下图

         当程序执行报错的时候，此时我们需要执行回滚的命令 rollback，如下图：

ROLLBACK;

        此时再次查询表里面的数据，发现数据没有发生任何变化，如下：

2.4 控制事务方式二

        记得先把刚才事务的开启方式变为自动，再继续下面的操作，这种控制事务的操作有三个命令，如下所示：

-- 开启事务
START TRANSACTION 或 BEGIN TRANSACTION ;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

2.4.1 程序正常执行

        记得先把数据改为每人 2000 ，然后再执行下面的 sql 语句 

-- 1、开启事务
START TRANSACTION;

-- 2、查询张三余额
select * from account where name = '张三';

-- 3、张三的余额减少1000
update account set money = money - 1000 where name = '张三';

-- 4、李四的余额增加1000
update account set money = money + 1000 where name = '李四';

-- 5、执行提交操作，如果不执行 commit，则数据不会中存储
commit;

         执行结果如下所示，可以看到，程序是正常执行且生效的。

2.4.2 程序异常执行

        记得先把数据改为每人 2000 ，然后再执行下面的 sql 语句 

-- 1、开启事务
START TRANSACTION;

-- 2. 查询张三余额
select * from account where name = '张三';

-- 3. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';

出错了....

-- 4. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

-- 5、如果程序异常执行，就需要执行 ROLLBACK 命令来撤销上面的操作
ROLLBACK;

        此时查看数据库中的数据，如下图，没有发生变化。

三、事务的四大特性

        下面就是事务的四大特性，简称 ACID。

3.1 原子性

        原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

3.2 一致性

        一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

3.3 隔离性

        隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

3.4 持久性

        持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

四、并发事务问题

        并发事务问题指的是 A 事务和 B 事务，他们两个在同时操作某一张表时所引发的一些问题。

4.1 脏读

        赃读：一个事务读到另外一个事务还没有提交的数据。

        1、事务 A 需要执行 3 个操作，首先是 select，其次是 update 操作，只有这 3 个操作完成之后事务才会提交，事务 A 执行完了第二步之后会对数据库表的数据进行更新。

        2、此时事务 B 它要进行的就是一个查询操作，此时事务 A 刚好对 id=1 的数据进行了更新，事务 B 在查的时候就会把更新之后的数据就拿到了，而此时事务 A 还没有提交。

        3、所以事务 B 读取到了事务 A 还没有提交的数据，就产生了脏读。

4.2 不可重复读

        不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

        1、事务 A 有 4 个操作，首先是 select，然后是第二步操作。与此同时，事务 B 也来操作数据库了，它执行了一个 update 操作，会把 id=1 的数据进行更新，而且更新完毕之后，它会将这次事务的操作提交到数据库。

        2、此时事务 A 继续向下执行，执行第三步操作，第三步又是 select 操作，和第一步操作是一样的，都是查询 id=1 的数据。

        3、此时我们会发现，第三步查询出来的数据和第一步查询出来的数据不一样了，因为在执行第三步操作的时候，事务 B 会将更新之后的数据提交到数据库，就导致了这样的结果，这种现象就叫不可重复读。 

4.3 幻读

        幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 ”幻影“。

        1、事务 A 先去查询数据库是否存在 id=1 的数据，发现没有。此时并发的事务 B 也来操作这个数据库，它执行的是 insert 操作，执行完毕之后将事务提交到数据库，此时数据库就存在了 id=1 的数据了。

        2、接下来事务 A 执行第二步 insert 操作，此时是无法插入成功的，因为 id 为主键，事务 B 已经在表中插入了 id=1 的数据了，再插入就会出现主键冲突的问题了。即事务 A 执行第二步 insert 操作就会报错。

        3、而此时事务 A 再去查一次数据库，发现 id=1 的数据还是没有，因为我们已经解决了不可重复读的问题，这种现象就叫幻读。 

五、事务隔离级别

        为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种，其中 √ 表示会出现，× 表示不会出现。

隔离级别	脏读	不可重复读	幻读
Read uncommitted（读未提交）	√	√	√
Read committed（读已提交）（oracle 默认）	×	√	√
Repeatable Read（可重复读）（mysql 默认）	×	×	√
Serializable（串行化）	×	×	×

5.1 查看事务隔离级别

        命令如下：

SELECT @@TRANSACTION_ISOLATION;

5.2 设置事务隔离级别

        命令如下：

-- SESSION：会话级别，仅在当前会话窗口有效
-- GLOBAL: 全局级别，所有客户端窗口都有效
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

5.3 并发事务问题演示及解决

5.3.1 前期准备

        1、首先使用 cmd 命令行打开两个窗口，并登录 mysql ，用于模拟两个客户端，如下所示：

        2、输入 use itheima 命令切换数据库，如下：

5.3.2 脏读演示

        脏读问题只会出现在 Read uncommitted 这种隔离级别下，所以我们需要先设置下数据库的隔离级别为  Read uncommitted，命令如下：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

        执行上面的命令，并查询下 account 表的数据，如下所示： 

        事务 B 执行了一个更新操作，但是这个更新操作并没有提交。而事务 A 读取到了事务 B 未提交的数据，就称之为脏读。

        说明： 在 Read uncommitted 这种隔离级别下会出现脏读的问题。

5.3.3 解决脏读

        Read committed 可以解决脏读，首先执行下面的命令，更改数据库的隔离级别，如下：

SET SESSION TRANSACTION ISOLATION LEVEL Read committed;

        可以到到，Read committed 是可以规避掉脏读的问题。

5.3.4 不可重复读演示

        当前数据库的隔离级别为 Read committed 。

        对于下图左侧的事务来说，查询第一次和第二次数据一样，而查第二次和第三次数据就不一样了，即同样的 sql ，在一个事务当中，查询出来的结果不一样，这个问题就称之为不可重复读，如下图：

5.3.5 解决不可重复读

        Repeatable Read 可以解决不可重复读，首先执行下面的命令，更改数据库的隔离级别，如下：

SET SESSION TRANSACTION ISOLATION LEVEL Repeatable Read;

        可以看到， Repeatable Read 解决了不可重复读。

5.3.6 幻读演示

        当前数据库的隔离级别为 Repeatable Read 。

        对于下图左侧的事务来说，首先查询数据库表里面 id=3 的数据，发现没有，于此同时右侧的事务插入了一条 id=3 的数据，并提交了事务。

        此时左侧的事务也插入了一条 id=3 的数据，但发现插入失败了。再查一遍数据库表发现还是没有 id=3 的数据，即虽然没查到，但就是无法插入。这就是幻读。如下图：

5.3.7 解决幻读

        Serializable 可以解决不可重复读，首先执行下面的命令，更改数据库的隔离级别，如下：

SET SESSION TRANSACTION ISOLATION LEVEL Serializable;

5.3.8 注意

        事务隔离级别越高，数据越安全，但是性能越低。