MySQL数据库事务

MySQL数据库事务学习与实践

一、什么是事务

1、事务概念

事务就是一组原子性的SQL查询，可以看做是一个独立的工作单元。

2、事务核心

事务的执行，数据库能够执行事务（一组sql语句的全部语句），那么就能够执行这一整组的查询。如果事务中有任何一条语句无法执行，则全部的语句都不会执行。

事务内的语句，要么全部执行成功，要么全部执行失败。

3、经典例子：银行转账

两个人转账A向B转账200元，三个步骤：

1、检查A账户余额大于等于200元

2、从A账户减去200元

3、B账户增加200元

三个步骤的操作，必须要打包在一个事务当中，如果有任何一个事务失败，那么就必须回滚所有步骤。

SQL中我们可以使用start transaction语句开启一个事务，使用commit语句提交事务将修改的数据持久化保存，如果失败则使用rollback语句撤销所有修改。

START TRANSACTION;
SELECT balance from accountA WHERE customer_id = 1;
UPDATE accountA set balance = balance - 200 where customer_id = 1;
UPDATE accountB set balance = balance + 200 where customer_id = 2;
COMMIT;

二、ACID

1、ACID概念

ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）。

原子性（atomicity）

一个事务必须被看做一个不可分割的最小的工作单元。整个事务中的所有操作，要么全部提交成功，要么全部失败，进行回滚。对于一个事务，不可能出现只执行其中一小部分的操作，就是事务的原子性。

一致性（consistency）

数据库总是从一个一致性的状态转移到另外一个一致性的状态。

隔离性（isolation）

一个事务开始时，只能"看见"已经提交事务所做的修改。

通常而言，一个事务所做的修改在最终提交前，对其他事务是不可见的。前面例子中执行完前三条语句，第四条语句未开始时，此时有另外一个A账户向B账户的转账程序开始，其看到的账户余额并没有被减去200。

后面我们会谈到**隔离级别(isolation level)**会发现我们所说的通常而言，与其他情况。

持久性（durability）

一旦事务提交，其所作的修改就会永久保存在数据库中。即使系统崩溃，修改的数据也不会丢失。

2、脏读、不可重复读、幻读

脏读（Dirty Read）

事务读取到了未提交的数据。A事务读取到B事务未提交的事物修改结果。

不可重复读（non-repeatable read）

一个事务从开始到提交之前，所做的修改，对其他事务都是不可见的。在一个事务内读取表中的某一行数据，多次读取结果不同。（不一定是错误，只是某些场合不对）

幻读（Phantom Read）

某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事物再次读取该范围内的记录时，会产生幻行。简单说，就是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

三、事务的隔离级别

事务的隔离性（isolation）其实在具体业务场景中会比想象复杂很多，在SQL中定义了四种隔离级别。每一种的隔离级别都规定了一个事务A中做的修改，在其他事务内与事务之间是可见的。哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也会降低。

1、四种隔离级别

READ UNCOMMITED （未提交读）

（1）概念：未提交读级别，事务的修改，及时是没有提交，对其他的事物也都是可见的。

未提交读就是脏读。

（2）与脏读关系：这种事务可以读取到未提交的事务的数据，就是脏读（Dirty Read）。

（3）问题：这个级别会有很多问题，性能上，READ UNCOMMITED （未提交读）并不会比其他级别好很多，但是却缺乏其他级别的好处，非必要一般不使用。

READ COMMIT（提交读）

（1）概念：提交读满足前面的事务隔离性的基本定义，一个事务开始时，只能"看见"已经提交事务所做的修改。也即，一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。但两次执行相同的查询，可能会得到不同的结果。

（2）与不可重复读关系：这个级别也可以叫做不可重复读。两次执行相同的查询，可能会得到不一样的结果。

REPEATABLE READ（可重复读）

（1）概念：保证在同一个事物中多次读取同样的记录，结果是一致的。(解决了脏读的问题)

（2）问题：存在幻读问题。当事务A在读取某个范围的记录，另外一个事务B在该范围内，插入了新的数据记录，之前事务A读取的事务读取该范围就会出现该幻行记录。

（3）问题解决：InnoDB和XtraDB存储的引擎，通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

（4）默认级别：MySQL默认级别就是可重复读。

SERIALIZABLE（可串行化）

（1）概念：最高的隔离级别。通过强制事务串行化执行，禁止事务并行，避免了前面的幻读问题。

（2）问题：可串行化会在读取的每一行数据都加上锁，可能会导致大量的超时和锁争用的问题。实际应用中，很少用到这个隔离级别，只有非常需要保证数据一致性的时候才会用到。

2、四种隔离级别表格小结

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITED	Yes	Yes	Yes	No
READ COMMIT	No	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
SERIALIZABLE	No	No	No	Yes

四、MySQL事务实践例子

1、基本语句

我们直接在MySQL进行实践。执行事务的基本语句：

-- mysql是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启（默认的）*/

-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始

INSERT xx
INSERT xx

-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK

-- 事务结束
SET autocommit = 1  -- 开启事务自动提交（默认设置）


-- 了解（一般不用）
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称  -- 撤销保存点

2、例子

以转账为例：

（1）数据准备

-- 转账
-- 创建数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

USE shop

CREATE TABLE `account` (
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY (`id`)    
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`, `money`)
VALUES('A', 1000.00), ('B', 1000.00)

（2）模拟转账：事务

一句句执行：

-- 模拟转账：事务
SET autocommmit = 0;  -- 1 关闭自动提交
START TRANSACTION -- 2 开启事务（一组事务）
UPDATE account SET money = money - 100 WHERE `name` = 'A'  -- 3 A减100
UPDATE account SET money = money + 100 WHERE `name` = 'B'  -- 4 B加100

COMMIT;  -- 5 提交事务,事务一旦提交就持久化了。
ROLLBACK;  -- 6 回滚

SET autocommit = 1; -- 开启事务自动提交（默认设置）

一句句执行。当还没有commit的时候。我们可以任意执行rollback进行回滚。但是一旦commit事务之后，作出的修改就发生了变化。此时再进行rollback就会无法回滚到1000 1000的状态。

依次执行句子1 2 3 此时可以看见当前事务的表的状态A-100 (此时B还是1000) 再执行4会发现B+100 （A 900 B 1000） 在3 4 步骤的时候都可以执行rollback 就会发现他们会回到最开始的数据。 但是我们重新执行 1 2 3 4 5。 此时进行rollback的时候回滚就是当前（A 900 B 1100的状态，因为commit后就进行了持久化。所以此时持久化后的数据就落磁盘了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LbBkVU3H-1615881198204)(事务/image-20210316154603325.png)]

要注意最后要恢复设置的默认值。

以后我们使用框架的时候，也会涉及到事务的相关操作，这里先开个坑，以后框架文章有机会和大家分享一下。

始的数据。 但是我们重新执行 1 2 3 4 5。 此时进行rollback的时候回滚就是当前（A 900 B 1100的状态，因为commit后就进行了持久化。所以此时持久化后的数据就落磁盘了。

要注意最后要恢复设置的默认值。

参考资料：
[1] Baron Scbwartz等 著，王小东等 译；高性能MySQL（High Performance MySQL）；电子工业出版社，2010

以后我们使用框架的时候，也会涉及到事务的相关操作，这里先开个坑，以后框架文章有机会和大家分享一下。
想和笔者交流可以关注微信公众号 大脸猫Coding