mysql笔记八 事务

事务

事务的应用场景

什么是事务： 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 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都成功了转账才算成功。这个时候就需要用到事务。

手动提交事务

MySQL中可以有两种方式进行事务的操作：

• 手动提交事务
• 自动提交事务

手动提交事务的SQL语句

# 开启事务
start transaction;

# 提交事务
commit;

# 回滚事务
rollback;

手动提交事务使用过程

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

案例演示1 ：事务提交

# 模拟张三给李四转500元
# 此时张三和李四各有1000元

# 开启事务
start transaction;
# 张三账号-500
update account set balance = balance - 500 where name='张三';
# 李四账号+500
update account set balance = balance + 500 where name='李四';

# 使用SQLyog查看数据库，发现数据并没有改变
# 在控制台执行commit提交事务
# 使用SQLyog查看数据库，发现数据改变

案例演示2 ：事务回滚

# 模拟张三给李四转500元（失败）
# 目前张三和李四各1000元
# 开启事务
start transaction;
# 张三账号-500
update account set balance = balance - 500 where name='张三';

# 使用SQLyog查看数据库，发现数据并没有改变
# 在控制台执行rollback回滚事务
# 使用SQLyog查看数据库，发现数据没有改变

总结

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

自动提交事务

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

取消自动提交

# 查看MySQL是否开启自动提交事务
select @@autocommit;
# 1表示开启 0表示关闭

# 取消自动提交事务
select @@autocommit = 0;

事务原理

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

事务原理图

事务的步骤

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

回滚点

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

回滚点的操作语句

# 设置回滚点
savepoint 名字
# 回到回滚点
rollback to 名字

具体操作

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

总结

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

事务的隔离级别

事务的四大特性ACID

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

并发问题

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

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

四种隔离级别

级别	名字	隔离级别	脏读	幻读	数据库隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是
3	可重复读	repeatable	否	否	是
4	串行化	serializable	否	否	否

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

事务隔离级别相关命令

# 查询全局事务隔离级别
select @@tx_isolation;

# 设置事务隔离级别，需要退出MySQL再重新登录才能看到隔离级别的变化
set global transaction isolation level 级别字符串;

MySQL知识点列表

MySQL知识点列表