【MySQL - 7】事务是什么？事务的四大特征、隔离级别及DCL操作大全（CRUD）

1. 事务

1.1 事务的概述

首先给你们介绍一下事务的概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败

是不是很抽象？来，我们说个简单的例子，现在我们来想一下“张三给李四转账500块”这件事情，与数据库相关的操作有什么？① 查询张三账户余额是否大于等于500 ② 把张三账户余额-500 ③ 把李四账户余额+500

想象一下，假如系统执行完了第二步，然后出现异常了，第三步不执行了，那就是张三的钱被扣掉了但是李四却没有收到，显然我们不可以让这种事情发生，那用什么解决这个问题呢？正是事务管理，下面介绍一下关于事务的三个操作

• 开启事务：START TRANSACTION;
• 回滚事务：ROLLBACK;
• 提交事务：COMMIT;

这是一张原始的账户表

我们先来看看没有开启事务之前的代码与效果

# 没有被事务管理
-- 张三账户-500
UPDATE account SET money = money -500 WHERE id = 1;
-- 这是人造异常
sdfaf
-- 李四账户+500
UPDATE account SET money = money +500 WHERE id = 2;

显然执行到异常情况时便不继续往下执行了，现在账户表的状况是这样的，张三的500块不翼而飞了，李四没有收到

我们先还原为原始的账户表状态，现在来看看开启事务管理后的代码与效果

# 被事务管理
-- 开启事务管理
START TRANSACTION;
-- 张三账户-500
UPDATE account SET money = money -500 WHERE id = 1;
-- 这是人造异常
sdfaf
-- 李四账户+500
UPDATE account SET money = money +500 WHERE id = 2;

显然异常情况后的代码也是不执行的，这时候我们来看看表的状况是怎么样的

嗯？跟刚刚是一样的呀？那有什么意义吗？其实不是的，现在你看到这个状态只是临时的显示状态，并没有真正写入到数据库当中，你可以试一下现在把SQLyog窗口关闭，注意这时候他会提示你有一个事务正在运行，退出将会回滚到之前状态，先直接点击是

再次打开查看一下刚刚的account表

看，是不是张三的钱没有真正被扣掉，其实刚刚在关闭窗口时候是SQLyog自动帮我们进行了回滚操作，我们也可以进行人工回滚，回滚到开启事务时的状态，下面再来一遍

# 被事务管理
-- 开启事务管理
START TRANSACTION;
-- 张三账户-500
UPDATE account SET money = money -500 WHERE id = 1;
-- 这是人造异常
sdfaf
-- 李四账户+500
UPDATE account SET money = money +500 WHERE id = 2;

大家都知道有异常，控制台的显示就不贴了，这时候我们希望回到原始状态，那么我们可以进行回滚操作

ROLLBACK;

看看回滚后的表

是不？已经还原到开启事务时的状态，好，那么现在我们已经把程序的异常处理好了，现在代码已经没有异常部分了

# 被事务管理
-- 开启事务管理
START TRANSACTION;
-- 张三账户-500
UPDATE account SET money = money -500 WHERE id = 1;
-- 李四账户+500
UPDATE account SET money = money +500 WHERE id = 2;

这时候查看一下账户表的状态，也显示正常地转账成功了

但要注意了，这时候的数据被写入到数据库当中了吗？其实还并没有，你需要人工提交一下

COMMIT;

这时候的数据才被真正写入到数据库当中

上面由于文字叙述可能会比较乱，下面给出一个大致的流程图辅助大家理解

1.2 手动提交与自动提交

看完上面的一小节，你可能会有疑问，为什么我们平时运行语句时候并不需要人工提交呢？这里就来说一下事务提交的两种方式

• 自动提交：MySQL是自动提交的数据库，每运行一条语句都会自动提交一次事务
• 手动提交：Oracle是手动提交的数据库，需要先开启事务再进行提交

有点意思是不是？你可以查看一下事务的默认提交方式，1代表自动提交，0代表手动提交

SELECT @@autocommit;

显然MySQL的默认提交方式是自动提交的，那我能不能修改呢？肯定也是可以的

SET @@autocommit = 0;

再来看看

1.3 事务的四大特征

经过两个小节的事务介绍，你有没有感受到事务有什么特征呢？事务有四大特征，是工作面试高频问题，要好好记住

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
• 持久性：当事务提交或者回滚后，数据库会持久化地保存数据
• 隔离性：多个事务之间相互独立
• 一致性：事务操作前后，数据总量不变（就像前面例子中张三和李四的账户总额）

四个特性中的第一、二、四都很好理解，那么第三个特性隔离性是什么意思呢？来看下一个小节

1.4 事务的隔离级别

首先来介绍一下事务隔离的概念：多个事务之间隔离的、相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题

会引发什么问题呢？一共有三个

• 脏读：一个事务读取到另一个事务中没有提交的数据
• 不可重复读（虚读）：在同一事务中，两次读取到的数据不一样
• 幻读：一个事务操作数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

那有什么不同的隔离级别呢？一共有四个

• READ UNCOMMITTED：读未提交，产生的问题：脏读、不可重复读、幻读
• READ COMMITTED：读已提交，产生的问题：不可重复读、幻读（这是Oracle的默认级别）
• REPEATABLE READ：可重复读，产生的问题：幻读（这是MySQL的默认级别）
• SERIALIZABLE：串行化，可以解决所有的问题

这时候有聪明的小朋友就想，既然串行化可以解决所有的问题，那我全部都设置为这个不就好了，道理是可以这样，但是隔离级别从小到大安全性越来越高其效率却会越来越低，因此我们需要合理地设置隔离级别，使得在保证安全性的前提下提高效率

下面介绍两个有关隔离级别的操作：

• 查询隔离级别：SELECT @@tx_isolation;，在MySQL8.0中该语句已经改成了SELECT @@transaction_isolation;
• 设置隔离级别：SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

这两个语句都很简单，这里就不演示他的效果了，但要注意的是更改隔离级别后要关闭重新打开cmd才生效，下面的重头戏是演示一下几个不同的隔离级别及其产生的问题，为方便演示，这次使用我们熟悉的小黑框，而所使用的例子还是前面所介绍的转账例子，初试状态还是一样

好啦，下面来开始，由于幻读无法很准确演示，这里只演示脏读与不可重复读

READ UNCOMMITTED

先来操作一波

注意这时候还没COMMIT，接下来我们再开多一个小黑框，开启事务查看一下数据

显然一个事务可以查看到另一个事务所没有提交的数据，发生脏读，这时候我们在旧窗口中回滚一下事务

显然在新窗口中两次读取到的数据不一样，发生了不可重复读（虚读）

READ COMMITTED

下面继续来更改一下事务隔离等级后操作一波

注意这时候仍然是还没COMMIT，接下来我们搞个新的小黑框，开启事务，查看一下数据

这时候新窗口查看不到旧窗口中没有提交的数据，该隔离级别中不存在脏读的问题，那接下来我们把旧窗口中的事务提交一下，然后在新窗口中再查看一下数据

显然该隔离级别同样出现了 不可重复读（虚读） 的问题

REPEATABLE READ

下面继续来更改一下事务隔离等级后操作一波

注意这时候仍然是还没COMMIT，接下来我们搞个新的小黑框，开启事务，查看一下数据

这时候新窗口查看不到旧窗口中没有提交的数据，该隔离级别中不存在脏读的问题，那接下来我们把旧窗口中的事务提交一下，然后在新窗口中再查看一下数据

显然该隔离级别不存在不可重复读（虚读） 的问题

基本演示到这里结束，有兴趣的同学可以自己研究一下如何可以演示幻读的效果

2. DCL操作用户

还记得之前的文章中介绍说到在MySQL中主要介绍四种操作吗？分别是DDL、DML、DQL和DCL，前面三种之前已经做过详细的介绍了，现在我们来学习一下DCL

DCL是与用户有关的操作，首先我们要弄清楚用户的信息是储存在哪个地方呢？在数据库中有一个mysql数据库（名字就叫mysql），里面有一张USER表专门用于存放用户的相关信息，因此，我们接下来的操作都是在mysql数据库中对USER表进行的

2.1 用户的增加、删除和查询

• 添加：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
• 删除：DROP USER '用户名'@'主机名';
• 查询：SELECT * FROM USER;

注意，在主机名中若使用通配符%则表示可以在任意主机使用该用户登陆数据库

下面我们就来演示一下以上的相关操作，现在先来查看一下现有的用户

SELECT * FROM USER;

接下来新增一个用户

CREATE USER 'xing'@'%' IDENTIFIED BY 'ting';
SELECT * FROM USER;

看一下能不能成功登陆

接下来把这个用户给删除了

DROP USER 'xing'@'%';
SELECT * FROM USER;

退出原有账户重新登陆看看能否成功

2.2 用户的密码修改

修改密码可以有两种不同的语句：

• UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
• SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

但是要注意了，在MySQL8.0后这两个语句已经被舍弃了，需要用以下语句才能成功修改

• ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';
• ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

下面我们继续来操作一把

CREATE USER 'xing'@'%' IDENTIFIED BY 'ting';
ALTER USER 'xing'@'%' IDENTIFIED BY '123';

我们再换一种方式试试

ALTER USER 'xing'@'%' IDENTIFIED WITH mysql_native_password BY 'abc'; 

2.3 用户的权限管理

现在想一个问题，是不是我们添加一个账户后这个账户就可以正常使用呢？我们用管理员账户来看一下现有的数据库

我们再来使用刚刚新创建的用户来查看一下数据库

显然所查询到的内容是不一样的，为什么呢？没有权限，创建新用户后在不给予权限时默认是没有权限的，那怎么查看有没有哪些权限呢？可以使用我们前面所介绍到的查询用户表的操作SELECT * FROM USER;

当然，关于管理权限我们也有专属的几种操作：

• 查询权限：SHOW GRANTS FOR '用户名'@'主机名';
• 授予权限：GRANT 权限1, 权限2, ... , 权限n ON 数据库名.表名 TO '用户名'@'主机名';
• 撤销权限：REVOKE 权限1, 权限2, ... , 权限n ON 数据库名.表名 FROM '用户名'@'主机名';

注意：

• 权限列表中的权限关键字是我们在日常操作中的SELECT、INSERT、DELETE等等，若想要授予或撤销该用户全部权限，可直接用关键字ALL来代替权限列表
• 若想要给全部数据库的某表或某数据库的全部表或全部数据库的全部表授予或撤销权限，可用*来代替“全部”所指代的内容，即可使用如下格式*.表名、数据库名.*、*.*

那么接下来我们就来操作一下相关内容

首先来查看一下刚刚所创建的用户到底有啥权限

SHOW GRANTS FOR 'xing'@'%';

可以看到这个用户只有一个USAGE权限，我们不知道是啥，没关系，下面来新增一些我们知道是啥的权限

-- 为该用户授予study4数据库所有表的查看权限
GRANT SELECT ON study4.* TO 'xing'@'%';
SHOW GRANTS FOR 'xing'@'%';

再来新增一个

-- 为该用户授予所有数据库所有表的查看和新增权限
GRANT SELECT,INSERT ON *.* TO 'xing'@'%';
SHOW GRANTS FOR 'xing'@'%';

是不是还不够过瘾，我们来添加一下全部权限看看

-- 为该用户授予所有数据库所有表的所有权限
GRANT ALL ON *.* TO 'xing'@'%';
SHOW GRANTS FOR 'xing'@'%';

是不是瞬间就多了很多权限呢？现在我后悔了，我不想给删除权限这个用户了，我们来撤销一下

-- 撤销该用户所有数据库所有表的删除权限
REVOKE DELETE ON *.* FROM 'xing'@'%';
SHOW GRANTS FOR 'xing'@'%';

至此，事务与DCL的相关常用操作与内容也已经全部介绍完啦，下一篇文章将会介绍一下JDBC的相关内容