3.数据库事务的控制&特征

不念不忘少年蓝@ 2023-07-20 13:34 5阅读 0赞

参考：  
[一天学会 MySQL 数据库][MySQL]  
[MySQL学习笔记][MySQL 1]

### 一、事务 ###

在 MySQL 中，事务是一个最小的不可分割的工作单元,保证一个`业务的完整性`。

比如银行转账：

-- a -> -100
    UPDATE user set money = money - 100 WHERE name = 'a';
    -- b -> +100
    UPDATE user set money = money + 100 WHERE name = 'b';

实际项目中，若只有一条语句执行成功，而另一条失败，就会出现数据前后不一致。  
因此，在执行多条有关联 SQL 语句时，事务会要求这些语句执行要么都成功，要么都失败。

#### 控制事务 - COMMIT / ROLLBACK / BEGIN ####

在 MySQL 中，事务的自动提交状态默认是开启的。

查询事务的自动提交状态：
    SELECT @@AUTOCOMMIT;
    +--------------+
    | @@AUTOCOMMIT |
    +--------------+
    |            1 |
    +--------------+

`默认事务开启（自动提交）的作用`：当执行一条 SQL 语句时，其产生的效果就会立即体现出来，且不能回滚。  
`回滚`：撤销执行过的所有 SQL 语句，使其回滚到`最后一次提交`数据时的状态。

*  1.自动提交  
    查看自动提交状态：SELECT @@AUTOCOMMIT ；  
    设置自动提交状态：SET AUTOCOMMIT = 0 。
 *  2.手动提交  
    @@AUTOCOMMIT = 0 时，使用 `COMMIT` 命令提交事务。
 *  3.事务回滚  
    @@AUTOCOMMIT = 0 时，使用 `ROLLBACK` 命令回滚事务。

CREATE DATABASE bank;
    USE bank;
    CREATE TABLE user (
        id INT PRIMARY KEY,
        name VARCHAR(20),
        money INT
    );
    INSERT INTO user VALUES (1, 'a', 1000);
    //在执行插入语句后数据立刻生效，是因为 MySQL 中的事务自动将它提交到了数据库中。
    
    由于所有执行过的语句都已经被提交过了，所以数据不能发生回滚。
    让数据发生回滚（撤销sql语句执行效果）得先关闭自动提交（设置mysql自动提交为false）：
    	-- 关闭自动提交
    	SET AUTOCOMMIT = 0;
    	-- 查询自动提交状态
    	SELECT @@AUTOCOMMIT;
    	+--------------+
    	| @@AUTOCOMMIT |
    	+--------------+
    	|            0 |
    	+--------------+
    
    将自动提交关闭后，插入/测试数据回滚：
    	INSERT INTO user VALUES (2, 'b', 1000);
    	-- 关闭 AUTOCOMMIT 后，数据的变化是在一张虚拟的临时数据表中展示，
    	-- 发生变化的数据并没有真正插入到数据表中。
    	SELECT * FROM user;
    	+----+------+-------+
    	| id | name | money |
    	+----+------+-------+
    	|  1 | a    |  1000 |
    	|  2 | b    |  1000 |
    	+----+------+-------+
    	
    由于数据还没有真正提交，可以使用回滚
    	ROLLBACK;
    	SELECT * FROM user;	//此结果为表中真实的数据
    	+----+------+-------+
    	| id | name | money |
    	+----+------+-------+
    	|  1 | a    |  1000 |
    	+----+------+-------+
    
    使用 COMMIT 将虚拟的数据真正提交到数据库中 :
    	INSERT INTO user VALUES (2, 'b', 1000);
    	COMMIT;-- 手动提交数据到数据库中（持久性）
    	ROLLBACK;
    	SELECT * FROM user;	-- 再次查询（回滚无效，不可撤销）
    	+----+------+-------+
    	| id | name | money |
    	+----+------+-------+
    	|  1 | a    |  1000 |
    	|  2 | b    |  1000 |
    	+----+------+-------+

`实际应用`：银行转账项目，假设在转账时发生了意外，就可以使用 ROLLBACK 回到了发生意外之前的状态。假设数据正常，可用COMMIT 手动将数据真正提交到数据表中。

-- 转账
    UPDATE user set money = money - 100 WHERE name = 'a';
    -- 到账
    UPDATE user set money = money + 100 WHERE name = 'b';
    
    SELECT * FROM user;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |   900 |
    |  2 | b    |  1100 |
    +----+------+-------+
    
    -- 假设转账发生了意外，需要回滚。
    ROLLBACK;
    
    SELECT * FROM user;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |  1000 |
    |  2 | b    |  1000 |
    +----+------+-------+
    
    事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后，可手动开启一个事务处理事件，使其可以发生回滚：
    -- 使用 BEGIN； 或者 START TRANSACTION； 手动开启一个事务
    
    BEGIN;	-- START TRANSACTION; 可替换，等价
    UPDATE user set money = money - 100 WHERE name = 'a';
    UPDATE user set money = money + 100 WHERE name = 'b';
    
    -- 由于手动开启的事务没有开启自动提交，此时发生变化的数据仍然是被保存在一张临时表中。
    SELECT * FROM user;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |   900 |
    |  2 | b    |  1100 |
    +----+------+-------+
    
    ROLLBACK;
    SELECT * FROM user;
    +----+------+-------+
    | id | name | money |
    +----+------+-------+
    |  1 | a    |  1000 |
    |  2 | b    |  1000 |
    +----+------+-------+
    COMMIT;-- 提交数据
    
    事务开启之后，一旦commit提交，就不可回滚（当前事务在提交时就结束了，此时a1000，b1000）

总结：

*  事务开启：  
    1.修改默认提交 `set autocommit=0；`  
    2.默认提交开启后，手动修改`BEGIN；` /`START TRANSACTION；`
 *  事务手动提交：`commit；`
 *  事务手动回滚：`rollback；`

### 二、事务的 ACID 特征与使用 ###

事务的四大特征：

*  A 原子性：事务是最小的单位，不可以再分割；
 *  C 一致性：要求同一事务中的 SQL 语句，必须保证同时成功或者失败；
 *  I 隔离性：事务1 和 事务2 之间是具有隔离性的；
 *  D 持久性：事务一旦结束 ( COMMIT ) ，就不可以再返回了 ( ROLLBACK ) 。

#### 事务的隔离性 ####

事务的隔离性可分为四种 ( 性能从低到高 ) ：

*  1.READ UNCOMMITTED ( 读取未提交，脏读 )  
     若有多个事务，某事物对数据操作但未提交，其他事务可读取其未提交数据。
 *  2.READ COMMITTED ( 读取已提交，不可重复读 )  
     只能读取已提交数据，导致在读取同表的数据时，可能会发生前后不一致的情况。
 *  3.REPEATABLE READ ( 可被重复读，幻读 )  
     如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。  
     如：事务a和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到，就可能造成幻读。
 *  4.SERIALIZABLE ( 串行化 )  
     所有的事务按序执行，当user表被一个事务操作时，不可执行其他事务的写操作，执行完一个事务后方可执行下一个事务。

**性能比较：**  
隔离级别越高，性能越差，问题越少；  
READ UNCOMMITTED >READ COMMITTED>REPEATABLE-READ>SERIALIZABLE；  
mysql默认隔离级别是 REPEATABLE-READ。

查看当前数据库的默认隔离级别：
    	-- MySQL 8.x
    	SELECT @@GLOBAL.TRANSACTION_ISOLATION;	//GLOBAL 表示系统级别
    	SELECT @@TRANSACTION_ISOLATION;			//表示会话级别
    	
    	-- MySQL 5.x
    	SELECT @@GLOBAL.TX_ISOLATION;
    	SELECT @@TX_ISOLATION;
    	
    	+--------------------------------+
    	| @@GLOBAL.TRANSACTION_ISOLATION |
    	+--------------------------------+
    	| REPEATABLE-READ                | -- MySQL的默认隔离级别，可以重复读。
    	+--------------------------------+

###### 1）脏读 ######

修改隔离级别READ UNCOMMITTED：
    	-- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
    	SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    	
    	-- 查询系统隔离级别，发现已经被修改。
    	SELECT @@GLOBAL.TRANSACTION_ISOLATION;
    	+--------------------------------+
    	| @@GLOBAL.TRANSACTION_ISOLATION |
    	+--------------------------------+
    	| READ-UNCOMMITTED               |
    	+--------------------------------+
    	
    INSERT INTO user VALUES (3, '小明', 1000);
    INSERT INTO user VALUES (4, '淘宝店', 1000);
    
    SELECT * FROM user;
    +----+-----------+-------+
    | id | name      | money |
    +----+-----------+-------+
    |  1 | a         |   900 |
    |  2 | b         |  1100 |
    |  3 | 小明      |  1000 |
    |  4 | 淘宝店    |  1000 |
    +----+-----------+-------+
    
    -- 开启一个事务操作数据
    -- 假设小明在淘宝店买了一双800块钱的鞋子：
    START TRANSACTION;
    UPDATE user SET money = money - 800 WHERE name = '小明';
    UPDATE user SET money = money + 800 WHERE name = '淘宝店';
    
    -- 然后淘宝店在另一方查询结果，发现钱已到账。
    SELECT * FROM user;
    +----+-----------+-------+
    | id | name      | money |
    +----+-----------+-------+
    |  1 | a         |   900 |
    |  2 | b         |  1100 |
    |  3 | 小明      |   200 |
    |  4 | 淘宝店    |  1800 |
    +----+-----------+-------+
    
    由于小明的转账是在新开启的事务上进行操作的，而该操作的结果可被其淘宝店看见，因此淘宝店的查询结果是正确的，淘宝店确认到账。
    
    	-- 若此时小明在它所处的事务上又执行了 ROLLBACK 命令：
    	ROLLBACK;
    	-- 此时无论对方是谁，如果再去查询结果就会发现：
    	SELECT * FROM user;
    	+----+-----------+-------+
    	| id | name      | money |
    	+----+-----------+-------+
    	|  1 | a         |   900 |
    	|  2 | b         |  1100 |
    	|  3 | 小明      |  1000 |
    	|  4 | 淘宝店    |  1000 |
    	+----+-----------+-------+

这就是所谓的脏读，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

###### 2）不可重复读 ######

把隔离级别设置为 READ COMMITTED 
    	SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    	SELECT @@GLOBAL.TRANSACTION_ISOLATION;
    	+--------------------------------+
    	| @@GLOBAL.TRANSACTION_ISOLATION |
    	+--------------------------------+
    	| READ-COMMITTED                 |
    	+--------------------------------+
    这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。
    
    但是对于当前事务来说，它们看到的还是未提交的数据，例如：
    	-- 正在操作数据事务（当前事务）
    	START TRANSACTION;
    	UPDATE user SET money = money - 800 WHERE name = '小明';
    	UPDATE user SET money = money + 800 WHERE name = '淘宝店';
    	
    	-- 虽然隔离级别被设置为了READ COMMITTED，但在当前事务中仍然是数据表中临时数据。
    	SELECT * FROM user;
    	+----+-----------+-------+
    	| id | name      | money |
    	+----+-----------+-------+
    	|  1 | a         |   900 |
    	|  2 | b         |  1100 |
    	|  3 | 小明      |   200 |
    	|  4 | 淘宝店    |  1800 |
    	+----+-----------+-------+
    	
    	-- 假设此时在远程开启了一个新事务，连接到数据库。
    	$ mysql -u root -p12345612
    	-- 此时远程连接查询到的数据只能是已经提交过的
    	SELECT * FROM user;
    	+----+-----------+-------+
    	| id | name      | money |
    	+----+-----------+-------+
    	|  1 | a         |   900 |
    	|  2 | b         |  1100 |
    	|  3 | 小明      |  1000 |
    	|  4 | 淘宝店    |  1000 |
    	+----+-----------+-------+
    
    假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：
    	-- 小张此时查询数据：
    	SELECT * FROM user;
    	+----+-----------+-------+
    	| id | name      | money |
    	+----+-----------+-------+
    	|  1 | a         |   900 |
    	|  2 | b         |  1100 |
    	|  3 | 小明      |   200 |
    	|  4 | 淘宝店    |  1800 |
    	+----+-----------+-------+
    	
    	-- 在小张求表的 money 平均值之前，小王做了一个操作：
    	START TRANSACTION;
    	INSERT INTO user VALUES (5, 'c', 100);
    	COMMIT;
    	
    	-- 此时表的真实数据是：
    	SELECT * FROM user;
    	+----+-----------+-------+
    	| id | name      | money |
    	+----+-----------+-------+
    	|  1 | a         |   900 |
    	|  2 | b         |  1100 |
    	|  3 | 小明      |  1000 |
    	|  4 | 淘宝店    |  1000 |
    	|  5 | c         |   100 |
    	+----+-----------+-------+
    	
    	-- 这时小张再求平均值的时候，就会出现计算不相符合的情况：
    	SELECT AVG(money) FROM user;
    	+------------+
    	| AVG(money) |
    	+------------+
    	|  820.0000  |
    	+------------+

虽然 READ COMMITTED 只能读取到其他事务已经提交的数据，但在读取同一个表的数据时，可能会发生前后不一致的情况。

###### 3）幻读 ######

将隔离级别设置为 REPEATABLE READ ( 可重复读 ) 
    	SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    	SELECT @@GLOBAL.TRANSACTION_ISOLATION;
    	+--------------------------------+
    	| @@GLOBAL.TRANSACTION_ISOLATION |
    	+--------------------------------+
    	| REPEATABLE-READ                |
    	+--------------------------------+
    	
    测试 REPEATABLE READ ，假设在两个不同的连接上分别执行 START TRANSACTION :
    	-- 小张 - 成都
    	START TRANSACTION;
    	INSERT INTO user VALUES (6, 'd', 1000);
    	
    	-- 小王 - 北京
    	START TRANSACTION;
    	
    	-- 小张 - 成都
    	COMMIT;
    
    
    一般说来，当前事务开启没提交之前，查询不到，提交后可以被查询。
    若提交之前其他事务开启，那么在这条事务线上不会查询到当前有操作事务的连接。
    
    因为小王在小张 COMMIT 之前开启了一个新的事务，小王在他自己事务线上，跟其他事务无联系，故只能查询到自己所处事务的记录：
    	SELECT * FROM user;
    	+----+-----------+-------+
    	| id | name      | money |
    	+----+-----------+-------+
    	|  1 | a         |   900 |
    	|  2 | b         |  1100 |
    	|  3 | 小明      |  1000 |
    	|  4 | 淘宝店    |  1000 |
    	|  5 | c         |   100 |
    	+----+-----------+-------+
    	
    若小王此时插入了同一条数据：
    	INSERT INTO user VALUES (6, 'd', 1000);
    	-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
    	-- 报错了，操作被告知已存在主键为 6 的字段。
    	
    这种现象被称为幻读，一个事务提交的数据，不能被其他事务读取到。

###### 4）串行化，所有事务的写入操作全都是串行化的 ######

把隔离级别修改成 SERIALIZABLE :
    	SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    	SELECT @@GLOBAL.TRANSACTION_ISOLATION;
    	+--------------------------------+
    	| @@GLOBAL.TRANSACTION_ISOLATION |
    	+--------------------------------+
    	| SERIALIZABLE                   |
    	+--------------------------------+
    
    在两个地方同时对表进行操作：
    	-- 小张 - 成都
    	START TRANSACTION;
    	-- 小王 - 北京
    	START TRANSACTION;
    	
    小王插入一条数据：
    	INSERT INTO user VALUES (7, '王小花', 1000);
    
    出现等待状态，直到小张执行 COMMIT 结束它所处的事务，或者出现等待超时。

[MySQL]: https://www.bilibili.com/video/BV1Vt411z7wy?p=58
[MySQL 1]: https://github.com/hjzCy/sql_node/blob/master/mysql/MySQL%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0.md