MySQL事务操作

1.事务的应用场景说明

什么是事务： 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条SQL语句出现异常，这条SQL就可能执行失败。
事务执行是一个整体，所有的SQL语句都必须执行成功。如果其中有1条SQL语句出现异常，则所有的SQL语句都要回滚，整个业务执行失败。
转账的操作：

-- 创建数据表
CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR ( 10 ), balance DOUBLE );
-- 添加数据
INSERT INTO account ( NAME, balance )
VALUES
	( '张三', 1000 ),
( '李四', 1000 );

模拟张三给李四转500元钱，一个转账的业务操作最少要执行下面的2条语句：
张三账号-500
李四账号+500

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';
select * from account;

假设当张三账号上-500元,服务器崩溃了。李四的账号并没有+500元，数据就出现问题了。我们需要保证其中一条SQL语句出现问题，整个转账就算失败。只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。

2.手动提交事务

MYSQL中可以有两种方式进行事务的操作：
1)手动提交事务
2)自动提交事务

2.1手动提交事务的SQL语句

功能	SQL语句
开启事务	start transaction;
提交事务	commit;
回滚事务	rollback;

2.2手动提交事务使用过程：

1. 执行成功的情况： 开启事务–>执行多条SQL语句 --> 成功提交事务
2. 执行失败的情况： 开启事务 --> 执行多条SQL语句 --> 事务的回滚
   

2.3案例演示1：事务提交

模拟张三给李四转500元钱（成功） 目前数据库数据如下：

1. 使用DOS控制台进入MySQL
2. 执行以下SQL语句： 1.开启事务， 2.张三账号-500， 3.李四账号+500
3. 使用navicat查看数据库：发现数据并没有改变
4. 在控制台执行commit提交事务：
5. 使用navicat查看数据库：发现数据改变

navicat查看数据库：发现数据并没有改变


数据改变

2.4案例演示2：事务回滚

模拟张三给李四转500元钱（失败） 目前数据库数据如下：

1. 在控制台执行以下SQL语句：1.开启事务， 2.张三账号-500
2. 使用navicat查看数据库：发现数据并没有改变
3. 在控制台执行rollback回滚事务：
4. 使用navicat查看数据库：发现数据没有改变

总结: 如果事务中SQL语句没有问题，commit提交事务，会对数据库数据的数据进行改变。 如果事务中SQL语句有问题，rollback回滚事务，会回退到开启事务时的状态。

3.自动提交事务

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

3.1案例演示3：自动提交事务

1. 将金额重置为1000
2. 更新其中某一个账户
3. 使用navicat查看数据库：发现数据已经改变
   
   

3.2取消自动提交

@@表示全局变量，1表示开启，0表示关闭

执行更新语句，使用navicat查看数据库，发现数据并没有改变

在控制台执行commit提交任务

4.事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

4.1 原理图：

4.2 事务的步骤：

1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
2. 开启事务以后，所有的操作都会先写入到临时日志文件中
3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回
4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件

5.回滚点

5.1 什么是回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

5.2 回滚点的操作语句

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

5.3 具体操作：

1. 将数据还原到1000
2. 开启事务
3. 让张三账号减3次钱，每次10块
4. 设置回滚点：savepoint three_times;
5. 让张三账号减4次钱，每次10块
6. 回到回滚点：rollback to three_times;
7. 分析执行过程

总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

6.事务的隔离级别

6.1事务的四大特性ACID

事务特性	含义
原子性（Atomicity）	每个事务都是一个整体，不可再拆分，事务中所有的SQL语句要么都执行成功，要么都失败。
一致性（Consistency）	事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是2000，转账后2个人总金额也是2000
隔离性（Isolation）	事务与事务之间不应该相互影响，执行时保持隔离的状态。
持久性（Durability）	一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

6.2事务的隔离级别

事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务update时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是insert或delete时引发的问题

6.3MySQL数据库有四种隔离级别

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

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

6.4MySQL事务隔离级别相关的命令

查询全局事务隔离级别

查询隔离级别
select @@tx_isolation;

设置事务隔离级别，需要退出MySQL再重新登录才能看到隔离级别的变化

设置隔离级别
set global transaction isolation level 级别字符串;

6.5脏读的演示

将数据进行恢复：UPDATE account SET balance = 1000;
1.打开A窗口登录MySQL，设置全局的隔离级别为最低

mysql -uroot -proot
set global transaction isolation level read uncommitted;

2.打开B窗口,AB窗口都开启事务

use day23;
start transaction;

3.A窗口更新2个人的账户数据，未提交

update account set balance=balance-500 where id=1;
update account set balance=balance+500 where id=2;

4.B窗口查询账户
select * from account;

5.A窗口回滚

rollback;

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入500块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

解决脏读的问题：将全局的隔离级别进行提升
将数据进行恢复：

UPDATE account SET balance = 1000;

1.在A窗口设置全局的隔离级别为read committed

set global transaction isolation level read committed;

B窗口退出MySQL，B窗口再进入MySQL

AB窗口同时开启事务

2.A更新2个人的账户，未提交

3.B窗口查询账户

A窗口commit提交事务

4.B窗口查看账户

结论：read committed的方式可以避免脏读的发生

6.6不可重复读的演示

将数据进行恢复：

UPDATE account SET balance = 1000;

1.开启A窗口

set global transaction isolation level read committed;

2.开启B窗口，在B窗口开启事务

start transaction;
select * from account;

3.在A窗口开启事务，并更新数据

start transaction;
update account set balance=balance+500 where id=1;
commit;

4.B窗口查询

select * from account;

两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑，当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

解决不可重复读的问题：
将全局的隔离级别进行提升为：repeatable read
将数据进行恢复：

UPDATE account SET balance = 1000;

1.A窗口设置隔离级别为：repeatable read

2.B窗口退出MySQL，B窗口再进入MySQL

start transaction;
select * from account;

3.A窗口更新数据

start transaction;
update account set balance=balance+500 where id=1;
commit;

4.B窗口查询

select * from account;

结论：同一个事务中为了保证多次查询数据一致，必须使用repeatable read隔离级别

6.7幻读的演示

在MySQL中无法看到幻读的效果。
可将事务隔离级别设置到最高，以挡住幻读的发生 将数据进行恢复：

UPDATE account SET balance = 1000;

1.开启A窗口

set global transaction isolation level serializable; – 设置隔离级别为最高

2.A窗口退出MySQL，A窗口重新登录MySQL

start transaction;
use 数据库名;
select count(*) from account;

3.再开启B窗口，登录MySQL

4.在B窗口中开启事务，添加一条记录

start transaction; – 开启事务
insert into account (name,balance) values (‘LaoWang’, 500);

5.在A窗口中commit提交事务，B窗口中insert语句会在A窗口事务提交后立马运行

6.在A窗口中接着查询，发现数据不变

select count(*) from account;

7.B窗口中commit提交当前事务

8.A窗口就能看到最新的数据

结论：使用serializable隔离级别，一个事务没有执行完，其他事务的SQL执行不了，可以挡住幻读