MySQL 数据库服务事务知识

数据库存储事务机制概念

事务(Transaction)可以更通俗的理解为交易，所以事务会伴随着交易类的业务类型出现的概念（工作模式）；
现实生活中存在很多的交易行为，比如：物换物的等价交换、货币换物的等价交换、虚拟货币换物(虚拟物品)的等价交换；
因此就需要考虑如何保证现实生活中交易过程的和谐，一般会有法律、道德等方面规则进行约束；
而在数据库服务中为了保证线上交易的"和谐"，便加入了"事务"工作机制
数据库存储事务机制特性

在数据库服务中引入事务机制概念，主要是为了应用事务机制的相关特性处理安全一致性问题，其中事务机制主要包含的特性有：
特性一：原子性（Atomicity）
原子性表示一个事务生命周期中的DML语句，要么全成功要么全失败，不可以出现中间状态；
语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log

1. Begin：DML01 DML02 DML03 Commit;

复制代码

特性二：一致性（Consistency）
一致性表示一个事务发生前、中、后，数据都最终保持一致，即读和写都要保证一致性；
事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障；

1. CR + Double write

复制代码

特性三：隔离性（Isolation）
隔离性表示一个事务操作数据行的时候，不会受到其他事务的影响，主要利用锁机制来保证隔离性；
特性四：持久性（Durability）
持久性表示一旦事务进行了提交，即可永久生效（落盘）
保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log
事务ACID相关知识官方说明：https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html
数据库存储事务生命周期

在运用事务机制完成相关工作任务时，对于事务使用是存在生命周期概念的，标准显示的事务生命周期控制语句有：

1. -- 开启事务机制
2. begin;
3. start transaction;
5. -- 提交事务任务
6. commit;
8. -- 回滚事务操作
9. rollback;

复制代码

说明：事务生命周期中，只能使用DML语句，其中包括：select、update、delete、insert；DDL语句会隐式进行提交

事务的生命周期操作演示：

1. # 进行测试数据库查询数据
2. mysql> use world;
3. mysql> select * from city limit 10;
5. # 进行测试数据库数据撤销修改
6. mysql> begin;
7. mysql> update city set population=10 where id=1;
8. mysql> update city set population=10 where id=2;
9. -- 由于是采用事务进行的修改，所以只是在内存层面进行的修改，并没有对磁盘上的数据进行修改；
10. mysql> select * from city limit 10;
11. -- 由于是采用事务进行的修改，此时看到的数据信息只是内存层面的修改信息
12. mysql> rollback;
13. -- 由于是采用事务进行的撤销，会读取undo文件信息，将事务操作撤回到事务开始前的状态
14. mysql> select * from city limit 10;
15. -- 由于是采用事务进行的修改，当撤销操作执行完，看到数据信息还是原来的；
17. # 进行测试数据库数据永久修改
18. mysql> begin;
19. mysql> update city set population=10 where id=1;
20. mysql> update city set population=10 where id=2;
21. -- 由于是采用事务进行的修改，所以只是在内存层面进行的修改，并没有对磁盘上的数据进行修改；
22. mysql> select * from city limit 10;
23. -- 由于是采用事务进行的修改，此时看到的数据信息只是内存层面的修改信息
24. mysql> commit;
25. -- 由于是采用事务进行的提交，会加载redo文件信息，将事务内存层面的修改同步到磁盘中（完成了D特性）
26. mysql> select * from city limit 10;
27. -- 由于是采用事务进行的修改，当执行操作执行完，看到数据信息将永久保存下载；

复制代码

数据库存储事务提交方式

方式一：在事务生命周期管理过程中，事务的提交机制可以采用自动提交方式（auto_commit）
事务自动提交方式作用说明：
事务自动提交表示在没有显示的使用begin语句的时候，执行DML操作语句时，会在DML操作语句前自动添加begin；
并在DML操作语句执行后自动添加commit；
在生产环境中，若处于频繁事务业务场景中，建议关闭autocommit自动提交功能，或者每次事务执行的时候；
都进行显示的执行begin和commit
事务自动提交方式参数信息：

1. mysql> select @@autocommit;
2. +---------------------+
3. | @@autocommit |
4. +---------------------+
5. |                          1 |
6. +---------------------+
7. 1 row in set (0.00 sec)
8. -- 在事务自动提交功能设置修改时，设置为1表示开启自动提交，设置为0表示关闭自动提交

复制代码

事务自动提交方式参数修改：

1. # 临时关闭事务自动提交功能
2. mysql> set global autocommit=0;
3. -- 配置调整后，重新登录mysql数据库生效
5. # 永久关闭事务自动提交功能
6. [root@xiaoQ-01 ~]# vim /etc/my.cnf
7. [mysqld]
8. autocommit=0
9. -- 配置调整后，重新启动mysql数据库生效

复制代码

事务自动提交方式设置方式优点缺点说明：
序号参数配置优劣势情况1autocommit=0
关闭事务自动提交优势：可以编写多个关联的DML，进行一次性提交操作，若出现异常可以回滚
符合原子特性劣势：可能出现多个关联的DML，只是完成了部分操作，这时就可能等待状态
基于隔离特性，操作的数据表或数据行就会进入锁定状态情况2autocommit=1
开启事务自动提交优势：可以出现多个关联的DML，逐行操作自动提交，就可以不用处于锁等待状态劣势：可能出现多个关联的DML，，每执行一条就进行提交，会造成多个语句执行不符合原子性方式二：在事务生命周期管理过程中，事务的提交机制可以采用隐式提交方式：
在进行事务操作时，需要注意操作语句必须都是DML语句，如果中间插入了DDL语句，也会造成之前的事务操作自动提交；

1. begin; DML1; DML2; DDL1; COMMIT; DML3; COMMIT;
2. -- 这种情况出现会破坏原本事务的原子性

复制代码

隐式自动提交方式语句：
在出现隐式自动提交时，可能导致提交的非事务语句有：
序号语句类型涉及命令01DDL语句类型alter、create、drop02DCL语句类型grant、revoke、set password03锁定语句类型lock tables、unlock tables04其他语句类型truncate table、load data infile、select for update

说明：在多个数据库会话窗口中，A窗口的所有事务性DML操作，不会受到B窗口的非事务语句影响，同一会话窗口会有影响；

隐式自动回滚情况分析：

• 情况一：在事务操作过程中，会话窗口自动关闭了，会进行隐式自动回滚；
  
• 情况二：在事务操作过程中，数据库服务被停止了，会进行隐式自动回滚；
  
• 情况三：在事务操作过程中，出现事务冲突死锁了，会进行隐式自动回滚；
  

数据库存储事务隔离级别

数据库事务隔离级别主要作用是实现事务工作期间，数据库操作读的隔离特性，所谓读的操作就是将数据页可以调取到内存；
然后可以读取数据页中相应数据行的能力，并且不同事务之间的数据页读操作相互隔离；
可以简单理解为：一个事务在对数据页中数据行做更新操作时，在没有更新提交前，另一个事务此时是不能读取数据页中数据行内容的；
对于数据库存储事务隔离级别包括4种，可以通过操作命令查看获取当前使用的隔离级别:

1. mysql> select @@transaction_isolation;
2. +---------------------------------+
3. | @@transaction_isolation  |
4. +---------------------------------+
5. | REPEATABLE-READ              |
6. +---------------------------------+
7. 1 row in set (0.00 sec)

复制代码

常用的事务隔离级别类型：
类型一：RU（READ-UNCOMMITTED 表示读未提交）
可以读取到事务未提交的数据，隔离性差，会出现脏读（当前内存读），不可重复读，幻读问题;
类型二：RC（READ-COMMITTED 表示读已提交）可用
可以读取到事务已提交的数据，隔离性一般，不会出现脏读问题，但是会出现不可重复读，幻读问题；
类型三：RR（REPEATABLE-READ 表示可重复读）默认
可以防止脏读（当前内存读），防止不可重复读问题，防止会出现的幻读问题，但是并发能力较差；
会使用next lock锁进制，来防止幻读问题，但是引入锁进制后，锁的代价会比较高，比较耗费CPU资源，占用系统性能；
类型四：SR（SERIALIZABLE 可串行化）
隔离性比较高，可以实现串行化读取数据，但是事务的并发度就没有了；
这是事务的最高级别，在每条读的数据上，加上锁，使之不可能相互冲突
事务隔离级别官方链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
常用的事务隔离级别名词：
在解释分析说明相应的隔离级别名词前，需要对数据库事务隔离级别进行调整，以及关闭自动提交功能：

1. # 设置事务隔离级别
2. mysql> set global transaction_isolation='READ-UNCOMMITTED';
3. mysql> set global transaction_isolation='READ-COMMITTED';
4. mysql> set global transaction_isolation='REPEATABLE-READ';
6. # 查看事务隔离级别
7. mysql> select @@transaction_isolation;
8. +---------------------------------+
9. | @@transaction_isolation |
10. +---------------------------------+
11. | READ-UNCOMMITTED         |
12. +---------------------------------+
13. mysql> select @@transaction_isolation;
14. +---------------------------------+
15. | @@transaction_isolation  |
16. +---------------------------------+
17. | READ-COMMITTED               |
18. +---------------------------------+
19. mysql> select @@transaction_isolation;
20. +---------------------------------+
21. | @@transaction_isolation |
22. +---------------------------------+
23. | REPEATABLE-READ             |
24. +---------------------------------+
26. # 临时关闭自动提交功能：
27. mysql> set global autocommit=0;
28. mysql> select @@autocommit;
29. +---------------------+
30. | @@autocommit |
31. +---------------------+
32. |                          0 |
33. +---------------------+

复制代码

创建隔离级别测试数据表：

1. mysql> use mydb
2. mysql> create table t1 (
3.     id int not null primary key auto_increment,
4.     a int not null,
5.     b varchar(20) not null,
6.     c varchar(20) not null
7. ) charset=utf8mb4 engine=innodb;
9. mysql> begin;
10. mysql> insert into t1(a,b,c)
11. values
12. (5,'a','aa'),
13. (7,'c','ab'),
14. (10,'d','ae'),
15. (13,'g','ag'),
16. (14,'h','at'),
17. (16,'i','au'),
18. (20,'j','av'),
19. (22,'k','aw'),
20. (25,'l','ax'),
21. (27,'o','ay'),
22. (31,'p','az'),
23. (50,'x','aze'),
24. (60,'y','azb');
25. mysql> commit;
26. -- 确认两个SQL会话窗口，即不同的事务查看的数据是否一致的；

复制代码

• 名词解读分析一：脏读
  

脏读主要表示在一个事务窗口中，没有数据修改提交操作前，另一个事务就可以看到内存中数据页的修改；
简单理解：在一个事务窗口中，可以读取到别人没有提交的数据信息；
利用隔离级别RU解读：

1. # 设置事务隔离级别
2. mysql> set global transaction_isolation='READ-UNCOMMITTED';
3. mysql> select @@transaction_isolation;
4. +---------------------------------+
5. | @@transaction_isolation |
6. +---------------------------------+
7. | READ-UNCOMMITTED         |
8. +---------------------------------+
9. mysql> set global autocommit=0;
10. mysql> select @@autocommit;
11. -- 重新开启两个SQL会话窗口
13. # 数据库A会话窗口操作
14. mysql> begin;
15. mysql> update t1 set a=10 where id=1;
16. -- 只是在内存层面进行数据页中数据修改
17. mysql> rollback;
18. -- 进行事务回滚操作
20. # 数据库B会话窗口操作
21. mysql> begin;
22. mysql> select * from t1 where id=1;
23. +----+----+---+----+
24. | id   | a   | b  | c   |
25. +----+----+---+----+
26. |  1   | 10 | a  | aa |
27. +----+----+---+----+
28. 1 row in set (0.01 sec)
29. -- 在A会话窗口没提交的事务修改，被B会话窗口查询到了
30. mysql> select * from t1 where id=1;
31. +----+----+---+----+
32. | id   | a   | b  | c   |
33. +----+----+---+----+
34. |  1   | 5   | a  | aa |
35. +----+----+---+----+
36. 1 row in set (0.01 sec)
37. -- 在A会话窗口进行回滚后，在B窗口查询的数据又恢复了

复制代码

• 名词解读分析二：不可重复读
  

不可重复读表示在一个事务中，利用相同的语句多次查询，获取的数据信息是不同的；
利用隔离级别RU解读：

1. # 数据库B会话窗口操作
2. mysql> begin;
3. mysql> select * from t1 where id=1;
4. +----+----+---+----+
5. | id   | a   | b  | c   |
6. +----+----+---+----+
7. |  1   | 10 | a  | aa |
8. +----+----+---+----+
9. 1 row in set (0.01 sec)
10. -- 在B会话事务窗口进行数据第一次查询看到数据信息：a=10
11. mysql> select * from t1 where id=1;
12. +----+----+---+----+
13. | id   | a   | b  | c   |
14. +----+----+---+----+
15. |  1   | 5   | a  | aa |
16. +----+----+---+----+
17. 1 row in set (0.01 sec)
18. -- 在B会话事务窗口进行数据第二次查询看到数据信息：a=5

复制代码

利用隔离级别RC解读：

1. # 设置事务隔离级别
2. mysql> set global transaction_isolation='READ-COMMITTED';
3. mysql> select @@transaction_isolation;
4. +---------------------------------+
5. | @@transaction_isolation |
6. +---------------------------------+
7. | READ-COMMITTED              |
8. +---------------------------------+
9. mysql> set global autocommit=0;
10. mysql> select @@autocommit;
11. -- 重新开启两个SQL会话窗口
13. # 数据库A会话窗口操作
14. mysql> use mydb;
15. mysql> begin;
16. mysql> select * from t1 where id=1;
17. +----+---+---+----+
18. | id   | a  | b  | c  |
19. +----+---+---+----+
20. |  1   | 5  | a  | aa |
21. +----+---+---+----+
22. 1 row in set (0.00 sec)
23. -- A窗口事务查询信息 = B窗口事务查询信息
24. mysql> update t1 set a=10 where id=1;
25. -- A窗口事务进行修改
26. mysql> commit;
27. -- A窗口事务进行提交
29. # 数据库B会话窗口操作
30. mysql> use mydb;
31. mysql> begin;
32. mysql> select * from t1 where id=1;
33. +----+---+---+----+
34. | id   | a  | b  | c  |
35. +----+---+---+----+
36. |  1   | 5  | a  | aa |
37. +----+---+---+----+
38. 1 row in set (0.00 sec)
39. -- A窗口事务查询信息 = B窗口事务查询信息
40. mysql> select * from t1 where id=1;
41. +----+---+---+----+
42. | id   | a  | b  | c  |
43. +----+---+---+----+
44. |  1   | 5  | a  | aa |
45. +----+---+---+----+
46. 1 row in set (0.00 sec)
47. -- B窗口事务查询信息，不能看到A窗口事务未提交的数据变化，避免了脏数据问题；
48. mysql> select * from t1 where id=1;
49. +----+---+---+----+
50. | id   | a  | b  | c  |
51. +----+---+---+----+
52. |  1   | 10 | a  | aa |
53. +----+---+---+----+
54. 1 row in set (0.00 sec)
55. -- A窗口事务提交之后，B窗口事务查询信息和之前不同了

复制代码

利用隔离级别RR解读：

1. # 设置事务隔离级别
2. mysql> set global transaction_isolation='REPEATABLE-READ';
3. mysql> select @@transaction_isolation;
4. +---------------------------------+
5. | @@transaction_isolation |
6. +---------------------------------+
7. | REPEATABLE-READ              |
8. +---------------------------------+
9. mysql> set global autocommit=0;
10. mysql> select @@autocommit;
11. -- 重新开启两个SQL会话窗口
13. # 数据库A会话窗口操作
14. mysql> use mydb;
15. mysql> begin;
16. mysql> select * from t1;
17. -- 确认初始数据信息
18. mysql> update t1 set a=10 where id=1;
19. -- A窗口事务进行修改
20. mysql> commit;
21. -- A窗口事务进行提交
23. # 数据库B会话窗口操作
24. mysql> use mydb;
25. mysql> begin;
26. mysql> select * from t1;
27. -- 确认初始数据信息
28. mysql> select * from t1 where id=1;
29. +----+---+---+----+
30. | id   | a  | b  | c  |
31. +----+---+---+----+
32. |  1   | 5  | a  | aa |
33. +----+---+---+----+
34. 1 row in set (0.00 sec)
35. -- B窗口事务查询信息，不能看到A窗口事务未提交的数据变化，避免了脏数据问题；
36. mysql> select * from t1 where id=1;
37. +----+---+---+----+
38. | id   | a  | b  | c  |
39. +----+---+---+----+
40. |  1   | 5  | a  | aa |
41. +----+---+---+----+
42. 1 row in set (0.00 sec)
43. -- A窗口事务提交之后，B窗口事务查询信息和之前是相同的；
44. -- 在RR级别状态下，同一窗口的事务生命周期下，每次读取相同数据信息是一样，避免了不可重复读问题
45. mysql> commit;
46. mysql> select * from t1 where id=1;
47. -- 在RR级别状态下，同一窗口的事务生命周期结束后，看到的数据信息就是修改的了

复制代码

• 名词解读分析三：幻读
  

利用隔离级别RC解读：

1. # 设置事务隔离级别
2. mysql> set global transaction_isolation='READ-COMMITTED';
3. mysql> select @@transaction_isolation;
4. +---------------------------------+
5. | @@transaction_isolation |
6. +---------------------------------+
7. | READ-COMMITTED              |
8. +---------------------------------+
9. mysql> set global autocommit=0;
10. mysql> select @@autocommit;
11. -- 重新开启两个SQL会话窗口
13. # 数据库A会话窗口操作（重新进入）
14. mysql> use mydb;
15. mysql> select * from t1;
16. +----+----+---+-----+
17. | id | a  | b | c   |
18. +----+----+---+-----+
19. |  1 | 10 | a | aa  |
20. |  2 |  7 | c | ab  |
21. |  3 | 10 | d | ae  |
22. |  4 | 13 | g | ag  |
23. |  5 | 14 | h | at  |
24. |  6 | 16 | i | au  |
25. |  7 | 20 | j | av  |
26. |  8 | 22 | k | aw  |
27. |  9 | 25 | l | ax  |
28. | 10 | 27 | o | ay  |
29. | 11 | 31 | p | az  |
30. | 12 | 50 | x | aze |
31. | 13 | 60 | y | azb |
32. +----+----+---+-----+
33. 13 rows in set (0.00 sec)
34. -- 查看获取A窗口表中数据
35. mysql> alter table t1 add index idx(a);
36. -- 在A窗口中，添加t1表的a列为索引信息
37. mysql> begin;
38. -- 在A窗口和B窗口中，同时做开始事务操作；
39. mysql> update t1 set a=20 where a<20;
40. -- 在A窗口中，将a<20的信息均调整为20
41. mysql> commit;
42. -- 在A窗口中，进行事务提交操作，是在B窗口事务没有提交前
43. mysql> mysql> select * from t1;
44. -- 在A窗口中，查看数据信息，希望看到的a是没有小于20的，但是结果看到了a存在等于10的（即出现了幻读）
46. # 数据库B会话窗口操作（重新进入）
47. mysql> use mydb;
48. mysql> select * from t1;
49. +----+----+---+-----+
50. | id | a  | b | c   |
51. +----+----+---+-----+
52. |  1 | 10 | a | aa  |
53. |  2 |  7 | c | ab  |
54. |  3 | 10 | d | ae  |
55. |  4 | 13 | g | ag  |
56. |  5 | 14 | h | at  |
57. |  6 | 16 | i | au  |
58. |  7 | 20 | j | av  |
59. |  8 | 22 | k | aw  |
60. |  9 | 25 | l | ax  |
61. | 10 | 27 | o | ay  |
62. | 11 | 31 | p | az  |
63. | 12 | 50 | x | aze |
64. | 13 | 60 | y | azb |
65. +----+----+---+-----+
66. 13 rows in set (0.00 sec)
67. -- 查看获取B窗口表中数据
68. mysql> begin;
69. mysql> insert into t1(a,b,c) values(10,'A','B')
70. -- 在B窗口中，插入一条新的数据信息 a=10
71. mysql> commit;
72. -- 在B窗口中，进行事务提交操作

复制代码

利用隔离级别RR解读：

1. # 设置事务隔离级别
2. mysql> set global transaction_isolation='REPEATABLE-READ';
3. mysql> select @@transaction_isolation;
4. +---------------------------------+
5. | @@transaction_isolation |
6. +---------------------------------+
7. | REPEATABLE-READ              |
8. +---------------------------------+
9. mysql> set global autocommit=0;
10. mysql> select @@autocommit;
11. -- 重新开启两个SQL会话窗口
13. # 数据库A会话窗口操作
14. mysql> use mydb;
15. mysql> select * from t1;
16. -- 查看获取A窗口表中数据
17. mysql> alter table t1 add index idx(a);
18. -- 在A窗口中，添加t1表的a列为索引信息
19. mysql> begin;
20. mysql> update t1 set a=20 where a>20;
21. -- 在A窗口中，将a>20的信息均调整为20
23. # 数据库B会话窗口操作
24. mysql> use mydb;
25. mysql> select * from t1;
26. -- 查看获取B窗口表中数据
27. mysql> begin;
28. mysql> insert into t1(a,b,c) values(30,'sss','bbb');
29. -- 在B窗口中，插入一条新的数据信息 a=30，但是语句执行时会被阻塞，没有反应；
30. mysql> show processlist;
31. -- 在C窗口中，查看数据库连接会话信息，insert语句在执行，等待语句超时（默认超时时间是50s）
32. -- 因为此时在RR机制下，创建了行级锁(阻塞修改)+间隙锁(阻塞区域间信息插入)=next lock
33. -- 区域间隙锁 < 左闭右开(可用临界值)  ;  区域间隙锁 > 左开右闭（不可用临界值）

复制代码

事务隔离机制知识点补充：
提到事务肯定不陌生，和数据库打交道的时候，总是会用到事务。
最经典的例子就是转账，你要给朋友小王转 100 块钱，而此时你的银行卡只有 100 块钱。
转账过程具体到程序里会有一系列的操作，比如查询余额、做加减法、更新余额等，这些操作必须保证是一体的；
不然等程序查完之后，还没做减法之前，你这100块钱，完全可以借着这个时间差再查一次，然后再给另外一个朋友转账，
如果银行这么整，不就乱了么？这时就要用到“事务”这个概念了。
简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
在 MySQL 中，事务支持是在引擎层实现的。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。
比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。
下面将会以 InnoDB 为例，剖析 MySQL 在事务支持方面的特定实现，并基于原理给出相应的实践建议，希望这些案例能加深你对 MySQL 事务原理的理解。
隔离性与隔离级别
提到事务，肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性），
我们就来说说其中 I，也就是“隔离性”。
当数据库上有多个事务同时执行的时候，就可能出现以下问题：

• 脏读（dirty read）
  
• 不可重复读（non-repeatable read）
  
• 幻读（phantom read）
  

为了解决这些问题，就有了“隔离级别”的概念。在谈隔离级别之前，首先要知道，隔离得越严实，效率就会越低。
因此很多时候，都要在二者之间寻找一个平衡点。SQL 标准的事务隔离级别包括：
隔离级别英文描述解释说明读未提交RU-read uncommitted一个事务还没提交时，它做的变更就能被别的事务看到。读提交RC-read committed一个事务提交之后，它做的变更才会被其他事务看到。可重复读RR-repeatable read一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。串行化serializable顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。
当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。其中“读提交”和“可重复读”比较难理解，所以我用一个例子说明这几种隔离级别。
假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

1. mysql> create table T(c int) engine=InnoDB;
2. mysql> insert into T(c) values(1);

复制代码

两个事务操作行为：
事务行为顺序事务A事务B01启动事务；查询得到值1启动事务02查询得到值103将1改为204查询得到值v105提交事务B06查询得到值v207提交事务A08查询得到值v3在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、V3 的返回值分别是什么。

• 若隔离级别是“读未提交”：
  

则 V1 的值就是 2，事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。

• 若隔离级别是“读提交”：
  

则 V1 是 1，V2 的值是 2，事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。

• 若隔离级别是“可重复读”：
  

则 V1、V2 是 1，V3 是 2，之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。

• 若隔离级别是“串行化”：
  

则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。
所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2。
数据库存储事务工作流程

根据存储事务的工作流程原理，来了解如何保证事务的ACID特性，利用了MySQL数据库的哪些工作机制；
事务工作流程名字解释：

• 名词解释一：redo log-Disk
  

表示重做日志，当出现异常情况，内存中数据直接写入磁盘失败时，可以通过重启数据库服务，读取此文件修复数据信息；
文件存储表项为：ib_logfile0~N    默认48M，轮询使用

• 名词解释二：redo log buffer-mem
  

表示重做日志生成缓冲区，相当于redo log的内存区域。redo log文件与redo log buffer是有IO关系的；
事务修改提交后：redo log buffer -> redo log，表示写入数据到redo log；
事务操作恢复时：redo log -> redo log buffer，表示读取数据从redo log；

• 名词解释三：tablespace file-disk
  

表示存储表数据行和索引等信息的文件，含有表空间所有数据文件；ibd

• 名词解释四：Innodb buffer pool-mem
  

表示数据缓冲区，主要用于缓冲事务要处理的数据和索引信息，tablespace文件与buffer pool是有IO关系的；

• 名词解释五：LSN
  

表示日志序列号，在buffer pool中有数据页信息的变化就会记录到redo log buffer中，主要记录变化了多少字节量；
利用LSN记录相应数据页的变化量(LSN+变化字节量)，也可以理解为记录的是日志量的变化；
MySQL每次数据库启动，都会比较磁盘数据页和redolog的LSN，必须要求两者一致，数据库才能正常启动；

• 名词解释六：WAL（Write Ahead Log）
  

表示redo日志生成记录优先于数据页写入到磁盘的过程，并且是支持预写入机制（group commit）的；

• 名词解释七：Dirty page
  

表示在内存进行修改的数据页，在redo buffer中会记录数据页的数据量的变化，此时在数据页还未最终写入到磁盘中时；
就称之为脏页，所以一般所谓的脏读就是读取脏页的数据页信息；

• 名词解释八：CheckPoint
  

表示为检查点，就是将脏页刷写到磁盘的动作；

• 名词解释九：DB_TRX_ID（6字节）
  

表示为事务ID号，InnoDB会为每一个事务生成一个事务号（由事务管理器管理TM），伴随着整个事务生命周期
其中事务ID号码信息，在redo和undo日志文件中都会有相应的标识；

• 名词解释十：DB_ROLL_PTR（7字节）
  

表示回滚指针，在rollback时会使用undo日志回滚已修改的数据，DB_ROLL_PTR会指向此次事务的回滚业务点；
从而找到undo上的相应的日志信息；
数据库名词解释官方参考：https://dev.mysql.com/doc/refman/8.0/en/glossary.html
事务工作流程具体解读：
简单事务情况举例：

1. mysql> begin;
2. mysql> update t1 set A=2 where A=1;
3. mysql> commit;

复制代码

事务工作流程一：redo log 重做日志如何应用

• 用户发起update操作事务语句，将磁盘数据页（page100，A=1，LSN=1000）加载到内存（buffer_pool）缓冲区；
  
• 将在内存中发生数据页修改操作（A=1改为A=2），形成数据页脏页，更改中数据页的变化会记录到redo buffer中；
  加入1000个字节日志信息，LSN=1000+1000=2000;
  
• 当执行事务提交操作的时候，基于WAL机制，等到redo buffer中的日志完全落盘到ib_logfileN-redo log中，即commit正式完成；
  
• 此时ib_logfileN中记录了一条日志，内容为：page100数据页变化+LSN=2000
  

简单理解：记录内存数据页变化日志+undo（DB_TRX_ID，DB_ROLL_PTR），通过LSN和数据页建立关系
特殊情景分析：当此时，redo落盘了，数据页没有落盘，出现宕机情况了；

• MySQL CR（自动故障恢复）工作模式，启动数据库时，自动检查redo的LSN和数据页LSN；
  
• 如果发现redo LSN > 数据页的LSN，加载原始数据页+变化redo指定内存，使用redo重构脏页（前滚）；
  
• 如果确认此次事务已经提交（commit标签），立即触发CKPT（checkpoint）动作，将脏页刷写到磁盘上；
  

知识点补充：
MySQL有一种机制，批量刷写redo的机制：会在A事务commit时，顺便将redo buffer中的未提交的redo日志也一并刷到磁盘；
为了区分不同状态的redo，日志记录时会标记是否commit；
redo保证了ACID哪些特性：
主要保证了D的特性，另外A C也有间接关联；
Redo Log日志文件生成流程：

Redo Log日志文件应用流程：

说明：利用redo Log重做日志功能可以保证事务的D特性，基于可以丢内存数据，但是不可以丢操作事务日志的原则；

存储引擎读写磁盘数据页IO信息：

1. mysql> select @@innodb_read_io_threads;
2. +------------------------------------+
3. | @@innodb_read_io_threads |
4. +------------------------------------+
5. |                                                4 |
6. +------------------------------------+
7. 1 row in set (0.00 sec)
8. -- 接收SQL层处理信息传达到存储引擎层的读IO配置信息；
10. mysql> select @@innodb_write_io_threads;
11. +------------------------------------+
12. | @@innodb_write_io_threads |
13. +------------------------------------+
14. |                                                 4 |
15. +------------------------------------+
16. 1 row in set (0.00 sec)
17. -- 接收SQL层处理信息传导到存储引擎层的写IO配置信息

复制代码

存储引擎序号号码信息查看：

1. mysql> show engine innodb status\G
2. Log sequence number                   105377511
3. -- redo buffer中的SN号码信息
4. Log flushed up to                           105377511
5. -- redo buffer刷新到磁盘上的SN号码信息
6. Last checkpoint at                         105377511
7. -- 磁盘数据页的SN号码信息

复制代码

存储引擎redo buffer落盘的机制策略：****

1. mysql> select @@innodb_flush_log_at_trx_commit;
2. +-----------------------------------------------+
3. | @@innodb_flush_log_at_trx_commit |
4. +-----------------------------------------------+
5. |                                                               1 |
6. +-----------------------------------------------+
7. 1 row in set (0.00 sec)
8. -- 表示数据库配置与安全有关的两个双一配置
9. -- 当数值为1：表示每次事务提交就立刻进行redo buffer刷新落盘，若落盘不成功，则commit命令操作也不会成功；默认
10. -- 当数值为0：表示日志缓存信息写入磁盘是按照每秒种进行一次操作，未刷新日志的事务可能会在崩溃中丢失；不安全
11. -- 当数值为2：表示在事务提交后先生成日志缓存信息，然后再按照每秒钟进行一次写入磁盘操作；不安全
12. -- 参考官方链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html

复制代码

事务工作流程二：undo log 回滚日志如何应用

• 事务发生数据页修改之前，会申请一个undo事务操作，保存了事务回滚日志（逆向操作的逻辑日志）
  
• undo写完之后，事务修改数据页头部（会记录DB_TRX_ID+DB+ROLL_PTR）,这个信息也会被记录在redo Log中
  

简单理解：记录数据修改的前镜像（逆向操作）,数据页和undo通过DB_TRX_ID，DB_ROLL_PTR建立关系
特殊情景分析01：
当执行rollback命令时，根据数据页的DB_TRX_ID+DB+ROLL_PTR信息，找到undo日志并进行回滚；
特殊情景分析02：

1. mysql> begin;
2. mysql> update t1 set A=2 where A=1;
3. -- 此时宕机了

复制代码

假设：undo 有；redo 没有

• 启动数据库时，检查redo和数据页的LSN号码，发现是一致的；
  
• 所以不需要进行redo的前滚，此时也不需要回滚。undo信息直接被标记为可覆盖状态；
  

假设：undo 有；redo 也有（没有commit标签）

• MySQL CR（自动故障恢复）工作模式，启动数据库时，自动检查redo的LSN和数据页LSN;
  
• 如果发现redo LSN>数据页的LSN。随即加载原始数据页+变化redo Log日志信息到相应内存位置，使用redo重构脏页（前滚）；
  
• 如果确认此次事务没有commit标记，立即触发回滚操作，根据DB_TRX_ID+DB_ROLL_PTR信息，找到undo回滚日志，实现回滚；
  

以上流程被称之为InnoDB的核心特性：自动故障恢复（CR），会先前滚再回滚，先应用redo再应用undo；
undo保证了ACID哪些特性：
主要保证事务的A的特性，同时C和I的特性也有关系；
undo Log日志文件生成流程：

undo Log日志文件应用流程：

说明：利用undo Log重做日志功能可以保证事务的A特性，基于先进行数据页前滚操作恢复脏页，在进行回滚操作恢复操作前事务；

事务工作流程三：事务中的C特性如何保证
InnoDB crash recovery：数据库意外宕机时刻，通过redo前滚+undo回滚保证数据的最终一致；
InnoDB doubewrite buffer：默认存储在ibdataN中，解决数据页写入不完整；DWB一共2M，分两次。每次1M写入；
redo日志只能恢复好的数据页的内容，但是不能恢复已经有异常的数据页内容；
可以参考官方资料：https://dev.mysql.com/doc/refman/8.0/en/mysql-acid.html
DWB文件信息生成流程：

DWB文件信息应用流程：

事务工作流程四：事务中的I特性如何保证
主要对数据库服务并发访问资源的保护，在并发事务工作期间，防止事务与事务之间的资源争抢（相互影响）；

• 保证读隔离性
  

方式一：利用隔离级别保证
序号隔离级别简单回顾01RU有可能会出现脏读、不可重复读、幻读02RC有可能会出现不可重复读，幻读03RR有可能会出现幻读（99.9%的读异常问题配合锁机制都可以处理）04SR(SE)采用事务串行工作机制方式二：利用MVCC机制隔离（只能保证读的隔离）
MVCC(multi-version-concurrent-control)即多版本并发控制，是一种并发控制的方法；
可以类别成Git进行并发处理的机制，其实就是每个事务在发生更新的过程中，维护发生更新事务的各个版本；
各个事务版本通过undo的日志（前镜像）实现快照的技术（read view），从而可以保存多个事务版本；
对于隔离级别而言，只有RC和RR级别可以使用到MVCC机制的，实现一种快照读机制，而RU和SR级别是不会使用到MVCC机制的；

• RC：应用MVCC的快照读机制，是基于语句级别的；（不可重复读 ture）
  

在事务期间，执行每个查询语句的时候，都会检查MVCC版本（快照列表），获取最新的已提交事务的快照；

• RR：应用MVCC的快照读机制，是基于事务级别的；（不可重复读 false）
  

在事务期间，执行首条查询语句的时候，就会生成MVCC版本（相应快照），将会一直读取此快照数据信息，直到事务生命周期结束；
以上的RR隔离级别利用MVCC的快照读机制，又称为一致性快照读；
==================================================================================================
MVCC进行多版本控制时，会应用的两种锁机制：乐观锁/悲观锁
每个事务操作都要经历两个阶段：

• 读阶段--应用乐观锁：
  

MVCC利用乐观锁机制，实现非锁定读取，借助快照技术（read view）

1. # 进行操作事务处理过程（trx1）
2. > begin;
3. > DML01 语句
4. -- 在做第一次事务操作的时候，当前事务获取系统最新的 rv1 版本快照
5. > DML02 语句
6. -- 在做下一次事务操作的时候，生成新的事务系统查询的 rv2 版本快照
7. > select
8. -- 此时查询的是 rv2快照数据信息
9. > commit
10. -- rv2 快照数据被提交，成为系统最新的快照

复制代码

RC隔离级别快照应用：

1. trx-01：rv1  -> rv2  -> commit;
2. trx-02：rv1  -> rv1  -> rv2

复制代码

RR隔离级别快照应用：

1. trx-01：第一个查询时，生成global consitence snapshot RV-CS1（10:00），一直伴随着事务生命周期结束
2. trx-02：第一个查询时，生成global consitence snapshot RV-CS2（10:01），一直伴随着事务生命周期结束

复制代码

• 写阶段--应用悲观锁：
  

即对于写操作，是不能进行并发操作的；
MVCC技术总结：
01 mvcc采用乐观锁机制，实现非锁定读取；
02 在RC级别下，事务中可以立即读取到其它事务提交过的readview数据快照信息；
03 在RR级别下，事务中从第一次查询开始，生成一个一致性readview，直到事务结束
==================================================================================================

• 保证写隔离性
  

方式一：利用隔离级别保证
在应用不同隔离级别时也会有不同的锁机制

• RC：具有记录锁机制；
  
• RR：具有间隙锁机制+下一键锁机制（next lock）  表锁
  

方式二：利用锁进制隔离（保护并发访问资源）
类型锁机制简述说明内存资源锁latch（闩锁）主要是保护内存资源；rwlock（读写锁）、mutex（只读锁）
避免不同程序争用相同地址区域内存资源）元数据锁MDL主要是保护元数据资源，限制DDL操作；metadata lock表级别锁table_lock主要是保护整个数据表资源；命令方式锁表lock table t1 read；工具方式锁表利用mysqldump、XBK(PBK)进行备份非InnoDB数据时，将触发FTWRL全局锁表；行锁升级为表锁比如做数据更新操作时，没有设置索引条件信息，就会出现全表扫描，出现表锁；行级别锁row_lockInnoDB默认锁粒度，加锁方式都是在索引上加锁的；record lock记录锁，在聚簇索引锁定，在RC级别只有record lockgap lock间隙锁，在辅助索引间隙加锁，在RR级别存在，防止幻读；next look下一键锁，即GAP+Record，在RR级别存在，防止幻读；从功能应用方面进行锁分类：了解

• IS：表示意向读锁或查询锁，可以在表上进行加锁做提示（select * from t1 lock in shared mode）;
  
• S：表示读锁或查询锁，现在基本上没有自动设置了，除非手工进行设置锁定（lock table t1 read);
  
• IX：表示意向写锁或排他锁，可以在表上进行加锁做提示（select * from t1 for update）
  
• X：表示写锁或排他锁，限制其他人的指定操作行为；
  

官方参考资料链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

-->**********   如果您认为这篇文章还不错或者有所收获，请点击右下角的【推荐】/【赞助】按钮，因为您的支持是我继续创作分享的最大动力！   **********
            作者：讲文张字
            出处：https://www.cnblogs.com/zhangwencheng
            版权：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出             原文链接
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！