mysql事务_个人文章

来源：恒创科技编辑：恒创科技编辑部

2023-12-22 17:57:59

事务

数据准备

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

概述

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

事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。

代码演示

/* 模拟转账的操作 */

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

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

分类

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

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

MySQL默认事务是自动提交的，所以想要开启事务，就需要执行对应的命令。相关的命令如下：

开启事务

start transaction;

提交事务

commit;

回滚事务

rollback;

代码演示

-- 重置表数据
update account set balance = 1000;

-- 开启事务
start transaction;

-- 张三账户-500
update account set balance = balance - 500 where name = '张三';

-- 李四账号+500
update account set balance = balance + 500 where name = '李四';

-- 提交
commit;

-- 查看表数据,数据已发现改变
select * from account;

-- 重置表数据
update account set balance = 1000;

-- 开启事务
start transaction;

-- 张三账户-500
update account set balance = balance - 500 where name = '张三';

-- 李四账号+500
update account set balance = balance + 500 where name = '李四';

-- 回滚
rollback;

-- 查看表数据,数据未发现改变
select * from account;

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

自动提交

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

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

SELECT @@AUTOCOMMIT;-- @@表示全局变量，1 表示开启，0 表示关闭

取消自动提交事务

SET @@AUTOCOMMIT = 0;

开启自动提交事务

SET @@AUTOCOMMIT = 1;

代码演示

-- 重置表数据
update account set balance = 1000;

-- 查看是否开启了自动提交事务
SELECT @@AUTOCOMMIT;

-- 取消自动提交事务
SET @@AUTOCOMMIT = 0;

-- 张三账户-500
update account set balance = balance - 500 where name = '张三';

-- 李四账号+500
update account set balance = balance + 500 where name = '李四';

-- 回滚
rollback;

-- 查询表数据，数据未发生改变（自动提交事务已关闭，回滚，会导致前面的SQL失效）
select * from account;

-- 重置表数据
update account set balance = 1000;

-- 查看是否开启了自动提交事务
SELECT @@AUTOCOMMIT;

-- 张三账户-500
update account set balance = balance - 500 where name = '张三';

-- 李四账号+500
update account set balance = balance + 500 where name = '李四';

-- 提交
commit;

-- 查询表数据，数据已发生改变（自动提交事务已关闭，只有commit，才能提交数据）
select * from account;

-- 重置表数据
update account set balance = 1000;

-- 查看是否开启了自动提交事务
SELECT @@AUTOCOMMIT;

-- 开启自动提交事务
SET @@AUTOCOMMIT = 1;

-- 张三账户-500
update account set balance = balance - 500 where name = '张三';

-- 李四账号+500
update account set balance = balance + 500 where name = '李四';

-- 回滚
rollback;

-- 查询表数据，数据已发生改变(自动提交事务已开启，回滚无效)
select * from account;

事务原理

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

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

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

设置回滚点

SAVEPOINT 回滚点名字;

返回回滚点

ROLLBACK TO 回滚点名字;

代码演示

-- 重置表数据
update account set balance = 1000;

-- 开启事务
start transaction;

-- 李四账号-100
update account set balance = balance - 100 where name = '李四';

-- 设置回滚点
savepoint one_time;

-- 李四账号再-100
update account set balance = balance - 100 where name = '李四';

-- 返回回滚点
rollback to one_time;

-- 提交
commit;

-- 查询表数据，发现李四账号只减少了100
select * from account;

事务特征

原子性(Atomicity)

每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。

一致性(Consistency)

事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是2000，转账后2 个人总金额也是2000

隔离性(Isolation)

事务与事务之间不应该相互影响，执行时保持隔离的状态。

持久性(Durability)

一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

事务隔离级别

事务在操作时的理想状态是：所有的事务之间保持隔离，互不影响。

但是因为并发操作，多个用户同时访问同一个数据。就可能引发并发访问的问题，如下：

脏读

一个事务读取到了另一个事务中尚未提交的数据。

不可重复读

一个事务中两次读取的数据内容不一致（update时引发的问题）

幻读

一个事务中两次读取的数据的数量不一致（insert或delete引发的问题）

下表为MySQL数据库中的四种隔离级别，每种级别可能又不同的访问问题：

级别名字隔离级别脏读不可重复读幻读默认采用此级别的数据库1读未提交read uncommitted是是是 2读已提交read committed否是是Oracle 和 SQL Server3可重复读repeatable read否否是MySQL4串行化serializable否否否

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

相关命令

查询全局事务隔离级别

SELECT @@TX_ISOLATION;

设置事务隔离级别（需重登MySQL，才能看出隔离级别变化）

SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

代码演示

/* 脏读演示 */

-- 打开 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为最低(read uncommitted——读未提交)
set global transaction isolation level read uncommitted;

-- 再打开 B 窗口登录MySQL,进入指定数据库，然后 A B 窗口都开启事务
start transaction;

-- A 窗口更新 2 个人的账户数据，不提交
update account set balance = balance - 500 where name = '张三';
update account set balance = balance + 500 where name = '李四';

-- B 窗口查询账户, 结果读取了 A 窗口尚未提交的事务，这就叫作脏读
select * from account;

-- A 窗口回滚
rollback;

-- B 窗口查询，表数据又变为原来的样子
select * from account;

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
解决脏读的办法：将全局的隔离级别进行提升为read committed

/* 解决脏读演示 */

-- 打开 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为read committed-读已提交
set global transaction isolation level read committed;

-- B 窗口重登MySQL，A B窗口都开启事务
start transaction;

-- A 窗口更新2人账号，不提交
update account set balance = balance - 500 where name = '张三';
update account set balance = balance + 500 where name = '李四';

-- B 窗口查询账户，并没有读取到 A 窗口尚未提交的数据(脏读已被解决)
select * from account;

-- A 窗口提交事务
commit;

-- B 窗口查看账户，才读取到 A 窗口已经提交的数据(脏读已被解决)
select * from account;

隔离级别为read committed可以避免脏读的发生

/* 不可重复读演示 */

-- 打开 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为read committed-读已提交
set global transaction isolation level read committed;

-- B 窗口重登MySQL，并开启事务
start transaction;

-- B 窗口查询表数据
select * from account;

-- A 窗口也开启事务
start transaction;

-- A 窗口更新2人账号，并提交
update account set balance = balance - 500 where name = '张三';
update account set balance = balance + 500 where name = '李四';
commit;

-- B 窗口查询，发现查询到的数据与前一次不同
select * from account;

两次查询输出的结果不同，这也是一种危险情况！我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，这样就可能会导致账务错误。
解决不可重复读的办法：将全局的隔离级别提升为repeatable read

/* 解决不可重复读演示 */

-- 打开 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为repeatable read-可重复读
set global transaction isolation level repeatable read;

-- B 窗口重登MySQL，并开启事务
start transaction;

-- B 窗口都查询表数据
select * from account;

-- A 窗口也开启事务

-- A 窗口更新2人账号，并提交
update account set balance = balance - 500 where name = '张三';
update account set balance = balance + 500 where name = '李四';
commit;

-- B 窗口查询，发现查询到的数据与前一次一致
select * from account;

隔离级别为repeatable read可以避免不可重复读的发生
首先需要声明的是：在MySQL中，我们是无法看到幻读的效果的。但是我们可以将事务隔离级别设置到最高，以挡住幻读的发生，将数据进行恢复。

/* 幻读演示 */

-- 打开 A 窗口登录 MySQL，重置表格
update account set balance = 1000;

-- A 窗口设置全局的隔离级别为最高serializable-串行化
set global transaction isolation level serializable;

-- A 窗口重登MySQL, 并开启事务
start transaction;

-- A 窗口查询表记录总数
select count(*) from account;

-- B 窗口登录 MySQL，并开启事务
start transaction;

-- B 窗口添加一条记录(此时回车确认时，会发现操作没有继续进行，光标仍在闪烁)
insert into account (name, balance) values ('Jason', 500);

-- A 窗口中执行commit提交事务后，B 窗口中的insert语句就立即执行完毕
commit;

-- A 窗口中查询表记录总数，发现总数未改变，B 窗口的insert没起作用
select count(*) from account;

-- B 窗口中执行commit提交事务
commit;

-- A 窗口查询表记录总数，发现总数已经改变，B 窗口的insert起作用了
select count(*) from account;

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

总结默认情况下autocommit（自动提交）=1时：
执行sql修改语句（insert into，update…set，delete from）后，是立即生效的；

若手动修改autocommit（自动提交）=0，即手动提交时：
执行sql修改语句（insert into，update，delete from）后，不会生效；

在当前终端查看到的数据是存在缓存里的数据；

重新打开一个终端查看，会发现数据没有生效；

需要输入commit命令，提交数据到数据库，这样才会生效；

不管是autocommit=1或=0时；开启事务使用了begin命令，执行了sql修改语句后，都必须要使用commit命令提交数据
给数据库，不然不会生效；
如果使用了begin，没有使用commit,而是用begin另外开启了一个事务，之前的修改会被提交，隐式提交；
即：使用begin开启事务->后面必须使用commit/begin(其他命令不行)，sql修改语句才能生效；以上要使用到commit的，在commit之前，使用命令rollback，都可以使数据回滚事务开启到结束：begin+commit/rollback

对于set autocommit=0的设置的有效范围（）：

只在当前操作的mysql数据库有效，临时有效退出当前mysql数据库（未退出终端），失效退出当前终端，再重新使用mysql，失效事务的开启，与是否选择了特定的数据库无关。即：在执行use database_name命令前，就可以通过begin/start transaction来开启事务，然后在选择你需要使用的数据库，进行数据操作。begin和 start transaction 等价，都是显式开启一个事务；commit 和 commit work 等价，都是对事物进行提交；rollback 和 rollback work 等价，都是对事物进行回滚；

本文地址：https://www.henghost.com/jishu/12289/