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='李四';

假设当张三账号上-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. 使用 SQLYog 查看数据库：发现数据并没有改变
4. 在控制台执行 commit 提交事务：
5. 使用 SQLYog 查看数据库：发现数据改变

2.4 案例演示 2：事务回滚

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

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

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

3 自动提交事务

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

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

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

3.2 取消自动提交

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



• @@表示全局变量，1表示开启，0表示关闭
• 取消自动提交事务



• 执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变
• 在控制台执行 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）	事务在执行前数据库的状态和执行后数据库的状态保存一致。如：转账前两个人总金额是2000，转账之后两个人总金额也是2000 。
隔离性（Isolation）	事务与事务之前不应该相互影响，执行是保持隔离的状态。
持久性（Durability）	一致事务执行成功，对数据库的修改是持久的。

6.2 事务的隔离级别

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

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

6.3 MySQL数据库的始终隔离级别

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

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是	NA
2	读已提交	read committed	否	是	是	Oracle 和 SQL Server
3	可重复度	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否	NA

• 注意： 隔离级别越高，性能越差，安全性越高。

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

• 查询全局事务隔离级别

select @@tx_isolation;

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

set global transaction isolation level 级别字符串;