[SQL必知必会学习] 10 事务处理

 

事务的特性

事务的 4 个特性说起， 这 4 个特性英文字母来表达就是 ACID。

1. A， 也就是原子性（Atomicity） 。 原子的概念就是不可分割， 你可以把它理解为组成物质的基本单位， 也是我们进行数据处理操作的基本单位

2. C， 就是一致性（Consistency） 。一致性指的就是数据库在进行事务操作后， 会由原来的一致状态， 变成另一种一致的状态。 也就是说当事务提交后， 或者当事务发生回滚后， 数据库的完整性约束不能被破坏。

3. I， 就是隔离性（Isolation） 。 它指的是每个事务都是彼此独立的， 不会受到其他事务的执行影响。 也就是说一个事务在提交之前， 对其他事务都是不可见的。

4. 最后一个 D， 指的是持久性（Durability） 。 事务提交之后对数据的修改是持久性的， 即使在系统出故障的情况下， 比如系统崩溃或者存储介质发生故障， 数据的修改依然是有效的。

ACID 可以说是事务的四大特性， 在这四个特性中， 原子性是基础， 隔离性是手段， 一致性是约束条件，持久性是我们的目的。

事务的控制

Oracle 是支持事务的， 在MySQL 中， 则需要选择适合的存储引擎才可以支持事务。可以通过SHOW ENGINES 命令来查看当前 MySQL 支持的存储引擎都有哪些， 以及这些存储引擎是否支持事务。

MySQL 支持的存储引擎.png

常用控制语句

1. START TRANSACTION 或者 BEGIN， 作用是显式开启一个事务。
2. COMMIT：提交事务。 当提交事务后， 对数据库的修改是永久性的。
3. ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]， 意为回滚事务。 意思是撤销正在进行的所有没有提交的修改， 或者将事务回滚到某个保存点。
4. SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。 一个事务中可以存在多个保存点。
5. RELEASE SAVEPOINT：删除某个保存点。
6. SET TRANSACTION， 设置事务的隔离级别。

使用事务有两种方式， 分别为隐式事务和显式事务。 隐式事务实际上就是自动提交， Oracle 默认不自动提交， 需要手写 COMMIT 命令， 而 MySQL 默认自动提交， 当然我们可以配置 MySQL 的参数：

mysql> set autocommit =0;  // 关闭自动提交
mysql> set autocommit =1;  // 开启自动提交

在 MySQL 的默认状态下， 下面这个事务最后的处理结果

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
BEGIN;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

常用控制语句01.png

一共执行了 2 个事务， 第一个是插入“关羽”， 提交后执行成功， 第二个是插入两次“张飞”， 需要注意的是， 我们将 name 设置为了主键， 也就是说主键的值是唯一的， 那么第二次插入“张飞”时就会产生错误， 然后执行ROLLBACK 相当于对事务进行了回滚， 所以我们看到最终结果只有一行数据， 也就是第一个事务执行之后的结果。

下面的操作又会怎样呢？注意插入“张飞”前并没有BEGIN;

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

常用控制语句02.png

上次操作我把两次插入“张飞”放到一个事务中， 这次操作它们不在同一个事务里，那么对于 MySQL 来说， 默认情况下这实际上就是两个事务， 因为在 autocommit=1 的情况下， MySQL 会进行隐式事务， 也就是自动提交， 因此在进行第一次插入“张飞”后， 数据表中就存在了两个数据， 而第二次插入“张飞”就会报错，最后我们在执行ROLLBACK 的时候， 实际上事务已经自动提交了， 就没法进行回滚了。

再来看下这段代码：

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

常用控制语句03.png

事务开始之前设置了 SET @@completion_type = 1; ， 结果就和我们第一次处理的一样， 只有一个“关羽”。

MySQL 中 completion_type 参数的作用

1. completion=0， 这是默认情况。 也就是说当我们执行COMMIT 的时候会提交事务， 在执行下一个事务时， 还需要我们使用 STARTTRANSACTION 或者 BEGIN 来开启。

2. completion=1， 这种情况下， 当我们提交事务后， 相当于执行了 COMMIT AND CHAIN，也就是开启一个链式事务， 即当我们提交事务之后会开启一个相同隔离级别的事务。

3. completion=2， 这种情况下 COMMIT=COMMIT AND RELEASE， 也就是当我们提交后， 会自动与服务器断开连接。

使用了 completion=1， 也就是说当我提交之后， 相当于在下一行写了一个START TRANSACTION 或 BEGIN。 这时两次插入“张飞”会被认为是在同一个事务之内的操作， 那么第二次插入“张飞”就会导致事务失败， 而回滚也将这次事务进行了撤销， 所以你能看到的结果就只有一个“关羽”。

当我们设置 autocommit=0 时， 不论是否采用START TRANSACTION 或者 BEGIN 的方式来开启事务， 都需要用 COMMIT 进行提交， 让事务生效， 使用 ROLLBACK 对事务进行回滚。MySQL中，如果是连续BEGIN，开启了第一个事务，还没有进行COMMIT提交，而直接进行第二个事务的BEGIN，数据库会隐式的帮助COMMIT第一个事务，然后进入到第二个事务

当我们设置 autocommit=1 时， 每条 SQL 语句都会自动进行提交。不过这时， 如果你采用 START TRANSACTION 或者 BEGIN 的方式来显式地开启事务， 那么这个事务只有在 COMMIT 时才会生效， 在 ROLLBACK 时才会回滚。

事务处理与事务的隔离级别.png

参考资料：

练习数据库使用 SQL必知必会专栏（极客时间）中的作者提供的 王者荣耀数据库以及NBA数据库
练习系统 MySQL Server version: 5.7.26-0ubuntu0.16.04.1 (Ubuntu)

SQL必知必会专栏（极客时间）链接：
http://gk.link/a/103Sm

《MySQL必知必会》学习笔记（24-29）:

https://www.jianshu.com/p/ee15e71ab1b8

此篇内容:使用游标、使用触发器、管理事务处理、全球化和本地化、安全管理、数据库维护

---

GitHub链接：
https://github.com/lichangke/LeetCode

知乎个人首页：
https://www.zhihu.com/people/lichangke/

简书个人首页：
https://www.jianshu.com/u/3e95c7555dc7

个人Blog:
https://lichangke.github.io/

欢迎大家来一起交流学习