SQL事务、隔离级别

一事务

1.1事务的概念

1.1.1什么是事务

如果一个业务操作中多次访问了数据库，必须保证每条SQL语句都执行成功。如果其中有一条执行失败，那么所有已经执行过的代码必须回滚(撤销)，回到没有执行前的状态，称为事务。

1.1.2事务四大特性

事务特性	含义
原子性	事务是工作的最小单元，整个工作单元要么全部执行成功，要么全部执行失败
一致性	事务执行前与执行后，数据库中数据应该保持相同的状态。如：转账前总金额与转账后总金额相同
隔离性	事务与事务之间不能互相影响，必须保持隔离性
持久性	如果事务执行成功，对数据库的操作是持久的

手动提交事务

MySQL中可以有两种方式进行事务的操作：
1.手动提交事务
2.自动提交事务，默认是自动提交事务

手动提交事务的SQL语句

功能	SQL语句
开启事务	Start transaction/begin
提交事务	commit
回滚事务	rollback

手动提交事务使用过程

1.4自动提交事务

MySQL默认每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务

1.41取消自动提交事务

查看MySQL是否开启自动提交事务:

select @@数据库名;

如果是1：表示自动提交，默认值
如果是0：表示关闭了自动提交

取消自动提交事务

set @@数据库名  = 0   **将事务设置为手动提交**
set @@数据库名  = 1   **将事务设置为自动提交**
select @@数据库名;

1.5事务原理

事务开启之后，所有操作都会临时保存到事务日志中，事务日志只有在得到commit命令才会同步到数据表中，执行完commit或rollback都会清空事务日志(rollback,断开连接)

1.5.1原理图

1.5.2事务的原理解释

1.如果没有开启事务，用户不适用日志文件，而是直接写到数据库
2.如果查询，数据从表中查询出来以后，经过日志文件加工以后返回
3.如果回滚，清除日志文件，不会写道数据库中

1.6回滚点

1.6.1什么是回滚点

可以在当前语句执行成功的位置设置一个回滚点。供后续操作失败后可以返回该位置，而不是返回所有操作，这个点称之为回滚点。

1.6.2回滚点的操作语句

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

二.事务的隔离级别

2.1并发访问的三个问题

当同时有多个用户在访问同一张表中的记录，每个用户在访问的时候都是一个单独的事务。
事务在操作时的理想状态是：事务之间不应该相互影响，实际应用的时候会引发下面三种问题，应尽量避免，可以通过数据库本身的功能去避免，设置不同的隔离级别。

• 脏读:一个事务(用户)读取到了另一个事务没有提交的数据
• 不可重复读 :一个事务多次读取同一天记录，出现读取数据不一致的情况，一般因为另一个事务更新了这条记录而引发的
• 幻读：在一次事务中，多次读取到的条数不一样

2.2设置隔离级别

2.2.1四种隔离级别：

2.2.2四种隔离级别起的作用：

• (1)Readuncommitted(读未提交):简称RU隔离级别，所有事务中的并发访问问题都会发生，可以读取到其他事务没有提交的数据
  (2)Readcommitted(读已提交):简称RC隔离级别，会引发不可重复读和幻读的问题，读取的永远是其他事务提交的数据
  (3)Repeatable read(可重复读):简称RR隔离级别，会引发幻读问题，一次事务读取到同一行数据，永远是一样
  (4)Serializable(串行化)：可以避免所有事务产生的并发访问的问题，但是效率非常低。

2.3安全和性能对比

隔离级别越高，安全性就越高，性能就越低。
隔离级别越低，安全性就越低，性能就越高。

2.4MySQL相关命令

查询全局事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql>

设置全局事务隔离级别

set global transaction isolation level 四种隔离; -- 服务器只要不关闭⼀直有效

**修改隔离级别后需要重启会话 **

2.5脏读

• 在并发情况下，一个事务读取到另一个事务没有提交的数据，这个数据称之为脏数据，此次读取也称为脏读。 只有read
  uncommitted(读未提交)的隔离级别才会引发脏读

2.5.1脏读演示

session-01	session-02
begin;
	begin;
select * from account where name =‘a’
	update account set money = money-200 where name=‘a’
select * from account where name =‘a’
	rollback;

2.5.2解决脏读

将全局的隔离级别进行提升
1.打开命令a，设置全局的隔离级别未read committed：

set global transaction isolation level read committed;

再次执行

session-01	session-02
begin;
	begin;
select * from account where name =‘a’
	update account set money = money-200 where name=‘a’
select * from account where name =‘a’
	rollback;

2.6不可重复读

概念：在同一个事务中的多次查询应该出现相同的结果，；两次读取不能出现不同的结果。

2.6.1和脏读的区别

• 脏读是读取前事务未提交的脏数据，不可重复读是重复读取了前一事务已提交的数据，但2次读取的结果不同。 应用场景：需要将查询的结果分别输出两个不同位置，结果在一个事务中针对输出目的地，两次的输出结果不一致，导致两个位置的结果不同，不知道以哪个为标准。

解决不可重复读

(1)设置隔离级别为repeatable read(可重复读)

set global transaction isolation level repeatable read;

设置完需要重启窗口
结论：为了保存多次查询数据一致，必须使用repeatable read隔离级别

2.7幻读

• 两次读取到的条数不一致，就是幻读 概念：一次事务多次读取到的条数不一致而引发的问题：
  在InnoDB中幻读在很多地方都得到了解决，但在一些特殊的情况下，还是会引发幻读问题；
  因为一次事务多次读取到的条数不一致会导致有很多情况发生

2.7.1解决幻读问题

设置隔离级别为repeatable read，随后重启客户端

set global transaction isolation level repeatable read;

2.8串行化

• 概念：要想彻底解决幻读，必须要把隔离级别调高，数据库中的最高隔离级别为串行化(serializable)
  串行化相当于锁表操作，即一个事务如果操作了某张表(增加丶删除丶修改)，那么就不允许任何事务操作此表，也不允许查询，等第一个事务提交或者回滚之后才可以操作，这样效率极其低下，因此一般不会采用serializable(串行化)隔离级别

2.8.1串行化演示

(1)开启一个银行窗口

-- 还原数据
truncate account;
INSERT INTO account (name, money) VALUES ('a', 1000), ('b', 1000);
set global transaction isolation level serializable; -- 设置隔离级别为串⾏化

(2)执行案例

session-01	session-02
begin;
	begin;
update account set money=money-500 where name=‘a’
	select * from account;

• 在串行化隔离级别中，相当于锁表的操作，在一个事务对进行任何的insert/update/delete等操作时，其他事务均不可对其进行操作，在读写上是串行的，并发能力极差