一文精通MySQL事务规则

引言：每个程序员学习数据库离不开MySQL,而学习MySQL就必学事务,今天我们就来一起学习一下MySQL的事务;

• 事务的概念

在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。由多条SQL语句组成一个功能(事务).需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。

• 事务应用场景

最通俗易懂的就是银行转账的案例,例如张三给李四转钱,张三账号减去相应数额资金,李四账号添加相应数额资金;

案例演示:

-- 创建account数据表
CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);

-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

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

• 张三账号-500

-- 1. 张三账号-500
UPDATE account SET balance = balance - 500 WHERE id=1;

• 李四账号+500

-- 2. 李四账号+500
UPDATE account SET balance = balance + 500 WHERE id=2;

结果如下

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

• 事务的四大特性(A,C,I,D)

• 手动提交事务

在MySQL数据库中有两种提交事务的规则,一种是手动提交事务,一种是自动提交事务,其中自动提交事务是默认的,下面先看一下手动提交事务的案例,MySQL中有专门用于事务的SQL,如下

• 手动提交事务步骤

手动提交事务分别有两种情况,如下图

• 第1种情况：开启事务 -> 执行SQL语句 -> 成功 -> 提交事务

目前数据库内容如下

• 使用DOS控制台进入MySQL
• 执行以下SQL语句：1.开启事务， 2.张三账号-500， 3.李四账号+500

START TRANSACTION;
UPDATE account SET balance = balance - 500 WHERE id=1;
UPDATE account SET balance = balance + 500 WHERE id=2;

• 使用Navicat查看数据库数据,发现数据库并没有变

• 在控制台执行commit提交任务：

• 使用Navicat查看数据库：发现数据改变

• 第2种情况：开启事务 -> 执行SQL语句 -> 失败 -> 回滚事务
• 模拟张三给李四转500元钱（失败）目前数据库数据如下：

• 在控制台执行以下SQL语句：1.开启事务， 2.张三账号-500

START TRANSACTION;
UPDATE account SET balance = balance - 500 WHERE id=1;

• 使用Navicat查看数据库：发现数据并没有改变

• 在控制台执行rollback回滚事务：

• 使用Navicat查看数据库：发现数据没有改变

• 自动提交事务

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

• 将张三和李四金额重置为1000

• 执行以下SQL语句

UPDATE account SET balance = balance - 500 WHERE id=1;

• 使用Navicat查看数据库：发现数据已经改变

• 我们可以使用SQL语句查看MySQL是否开启自动提交事务

show variables like '%commit%';
-- 或
SELECT @@autocommit;

• 通过修改MySQL全局变量"autocommit"，取消自动提交事务

• 0:OFF（关闭自动提交）
• 1:ON（开启自动提交）

取消自动提交事务，设置自动提交的参数为OFF，执行SQL语句：set autocommit = 0;

• 在控制台执行以下SQL语句：张三-500

UPDATE account SET balance = balance - 500 WHERE id=1;

• 使用Navicat查看数据库，发现数据并没有改变

• 在控制台执行commit提交任务

• 使用Navicat查看数据库，发现数据改变

• MySQL事务的原理

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

• 事务的隔离级别

事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题。

• 脏读演示

• 不可重复读演示

• 幻读演示

MySQL数据库有四种隔离级别：上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。