[实践]事务一致性0-MySQL事务验证与原理分析

目标

本次笔记会简单介绍mysql的事务，并使用命令行简单操作事务。演示内容包含:

1. 事务的提交与回滚。
2. 事务的隔离级别。
3. 事务的影响。

介绍

1. 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
2. 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
3. 事务用来管理 insert,update,delete 语句

事务的特性ACID

1. 原子性(Atomicity)，事务中的所有操作被看做是一个整体，是不可分割的，要么一起成功要么一起失败。
2. 一致性(Consistency)，相同条件下的执行具有相同结果，结果与预期保持是一致。
3. 隔离性(Isolation)，事务与事务之间相互隔离。
4. 持久性(Durability)，事务最终的结果会落地，被持久化。

事务的隔离级别

事务的并发问题^[1]

1. 脏读(Dirty Read)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
2. 不可重复读(Non-Repeatable Read)：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3. 幻读(Phantom)：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
   小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

事务隔离级别^[2]

mysql事务操作语句^[3]

1. BEGIN或START TRANSACTION：显式地开启一个事务；
2. COMMIT：也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
3. ROLLBACK：有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
4. SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
5. RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
6. ROLLBACK TO identifier：把事务回滚到标记点；
7. SET TRANSACTION：用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

实战

原子性,一致性,持久性

查询是否自动提交事务

mysql> show global variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+

设置为非自动提交

mysql> set global autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

创建示例表

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '名字',
  `birthday` datetime(3) DEFAULT NULL COMMENT '生日',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户' 

新建示例数据

insert user values(null, '张三', '2020-11-11 11:11:11');

查询数据

mysql> select * from user;
+----+--------+-------------------------+
| id | name   | birthday                |
+----+--------+-------------------------+
|  1 | 张三   | 2020-11-11 11:11:11.000 |
+----+--------+-------------------------+

开启事务，删除数据并回滚

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+--------+-------------------------+
| id | name   | birthday                |
+----+--------+-------------------------+
|  1 | 张三   | 2020-11-11 11:11:11.000 |
+----+--------+-------------------------+
1 row in set (0.00 sec)

mysql> delete from user;
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+--------+-------------------------+
| id | name   | birthday                |
+----+--------+-------------------------+
|  1 | 张三   | 2020-11-11 11:11:11.000 |
+----+--------+-------------------------+
1 row in set (0.00 sec)

开启事务，新增数据并提交

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert user values(null, '李四', '2020-11-11 11:11:11');
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+--------+-------------------------+
| id | name   | birthday                |
+----+--------+-------------------------+
|  1 | 张三   | 2020-11-11 11:11:11.000 |
|  2 | 李四   | 2020-11-11 11:11:11.000 |
+----+--------+-------------------------+
2 rows in set (0.00 sec)

隔离性 隔离级别

查询并修改事务的隔离级别

mysql8以下版本讲transaction_isolation修改为tx_isolation

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

修改事务的隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

脏读

|时间线|session1|session2|
待续....