研发人员必须了解的MySQL知识02

1. 事务实现原理

1.1 什么是事务

• 事务是并发控制的单元，它是一个操作序列，即一组操作，这些操作要么全部执行成功，要么全部执行失败，这些操作整体是一个不可分割的单位。在关系型数据库管理系统（RDBMS）中，一个事务可以是一条sql语句、一组sql语句、甚至可以是整个程序。mysql的innoDB存储引擎支持事务。

1.2 事务的使用步骤

1. 执行sql语句之前, 我们要开启手动事务, 使用命令: start transaction;

   mysql> start transaction; # transaction 可以省略 
   Query OK, 0 rows affected (0.00 sec)
   

2. 正常执行我们的sql语句.

3. 当一组sql语句执行完毕, 存在两种情况:

   ① 一组sql全部执行成功, 我们要将sql语句对数据库造成的影响提交到数据库中, 执行命令: commit;（提交）

   ② 一组sql中某些sql执行失败, 我们要将对数据库开启事务之后的操作选择性撤销, 执行命令: rollback;（回滚）

• 事务相当于单击游戏存档, 保存进度（开启事务）后玩游戏一段时间（执行一系列sql语句后）, 我们可以选择读取以前的游戏进度（rollback回滚）或继续游戏（commit提交）。 事务支持设置回滚点（相当于单击游戏保存了好几个进度），可以选择回滚到某个回滚点（读取某个进度）。

1.3 事务的分类

• 事务分为自动事务（默认）和手动事务。

1.3.1 自动事务

• mysql默认自动开启事务。但是mysql的自动事务是一条sql语句独占一个事务，即用户执行一条sql完毕后会立即同步到数据表中，默认操作完后自动commit提交。

• mysql通过autocommit变量控制事务的自动提交;

  # 查看自动提交是否被开启的命令：
  show variables like 'autocommit';
  # 设置自动提交开关的命令：
  set autocommit = off|0 / on|1;
  # 关闭自动事务的自动提交后，执行一条sql就必须手动执行一次commit或rollback；
  

1.3.2 手动事务

• 开启手动事务的语法: start transaction;

  mysql> start transaction; # transaction 可以省略
  Query OK, 0 rows affected (0.00 sec)
  

• 开启手动事务后，所有的写操作不会直接写入到数据表，而是先存放到事务日志。

• 开启手动事务后，执行一系列sql语句。执行完毕后，我们需要关闭手动事务，关闭手动事务本质就是选择性地将日志文件中的操作结果同步到到数据表中（提交），或者直接清空日志（回滚）。

  • 关闭事务（提交使用commit命令）： 将日志文件中的操作结果同步到到数据表（操作成功）;

  • 关闭事务（回滚使用rollback命令）： 直接清空日志文件（操作失败）;

1.4 事务的运行原理

开启事务之后进行的所有操作都会记录到日志文件中，并没有真正在数据表中执行操作，原表数据并没改动，可以选择将日志中的操作同步到真实数据表中，也可以将日志清空，数据表并未改动。

1.5 事务的操作实例

1.5.1 实例1-提交

-- 创建数据表. 账户表. 
mysql> create table t_account
	-> (
	-> id int,
	-> name char(20) comment"姓名",
	-> money double comment"存款"
	-> );
Query OK, 0 rows affected (0.01 sec)

-- 插入数据. 张三有1000块, 李四有1000块
mysql> insert into t_account
	-> values
	-> (1,"张三",1000),
	-> (2,"李四",1000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | 张三 |  1000 |
|    2 | 李四 |  1000 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> -- 开启事务
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 李四给张三转账1000块

mysql> -- 李四账户-1000
mysql> update t_account
	-> set
	-> money = money-1000 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> -- 张三账户+1000
mysql> update t_account
	-> set
	-> money = money+1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | 张三 |  2000 |
|    2 | 李四 |     0 |
+------+------+-------+
2 rows in set (0.00 sec)

-- ---------------------------------
-- 用另一个用户登录mysql查看 t_account 表：
-- C:\Users\king>mysql -u user4 -p
-- Enter password: ***
-- Welcome to the MySQL monitor.

-- mysql> use db
-- Database changed

-- mysql> select * from t_account;
-- +------+------+-------+
-- | id   | name | money |
-- +------+------+-------+
-- |    1 | 张三 |  1000 |
-- |    2 | 李四 |  1000 |
-- +------+------+-------+
-- 2 rows in set (0.00 sec)
-- ---------------------------------

mysql> -- 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | 张三 |  2000 |
|    2 | 李四 |     0 |
+------+------+-------+
2 rows in set (0.00 sec)

-- ------------------------------------------
-- 用另一个用户登录mysql查看t_account表：
-- C:\Users\king>mysql -u user4 -p
-- Enter password: ***
-- Welcome to the MySQL monitor.

-- mysql> use db
-- Database changed

-- mysql> select * from t_account;
-- +------+------+-------+
-- | id   | name | money |
-- +------+------+-------+
-- |    1 | 张三 |  1000 |
-- |    2 | 李四 |     0 |
-- +------+------+-------+
-- 2 rows in set (0.00 sec)
-- --------------------------------------------

1.5.2 实例2-回滚

mysql> select * from t_account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | 张三 |  1000 |
|    2 | 李四 |  1000 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> -- 开启事务
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> -- 李四账户-1000
mysql> update t_account
	-> set
	-> money = money-1000 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> -- 张三账户+1000
mysql> update t_account
	-> set
	-> money = money+1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | 张三 |  2000 |
|    2 | 李四 |     0 |
+------+------+-------+
2 rows in set (0.00 sec)

-- ------------------------------------------
-- 用另一个用户登录mysql查看zhanghu表：
-- C:\Users\king>mysql -u user4 -p
-- Enter password: ***
-- Welcome to the MySQL monitor.

-- mysql> use db
-- Database changed
-- mysql> select * from t_account;
-- +------+------+-------+
-- | id   | name | money |
-- +------+------+-------+
-- |    1 | 张三 |  1000 |
-- |    2 | 李四 |  1000 |
-- +------+------+-------+
-- 2 rows in set (0.00 sec)
-- --------------------------------------------

mysql> -- 回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_account;
+------+------+-------+
| id   | name | money |
+------+------+-------+
|    1 | 张三 |  1000 |
|    2 | 李四 |  1000 |
+------+------+-------+
2 rows in set (0.00 sec)

-- ------------------------------------------
-- 用另一个用户登录mysql查看zhanghu表：
-- C:\Users\king>mysql -u user4 -p
-- Enter password: ***
-- Welcome to the MySQL monitor.

-- mysql> use db
-- Database changed
-- mysql> select * from t_account;
-- +------+------+-------+
-- | id   | name | money |
-- +------+------+-------+
-- |    1 | 张三 |  1000 |
-- |    2 | 李四 |  1000 |
-- +------+------+-------+
-- 2 rows in set (0.00 sec)
-- --------------------------------------------

1.5.3 实例3-给事务设置回滚点

• 在日志中的连续的成功操作的某处设置一个点，可以回滚到该点；
  就像在游戏进度中保存了多个游戏进度，可以读取到那些进度。
• 设置回滚点语法：savepoint 回滚点名字;
• 回到回滚点语法：rollback to 回滚点名字;

-- 开启事务
start transaction;

-- 张三减钱
update t_account 
set
money = money-1000 where id=1;

-- 设置回滚点
savepoint sp1;

-- 银行扣转账手续费 --> 这步操作失误，应该是id=1
update t_account 
set
money = money-1000*0.05 where id=2;

-- 回滚到回滚点
rollback to sp1;

-- 继续操作
update t_account 
set 
money =money-1000*0.05 where id=1;

1.6 事务四大特性

• A（atomic原子性）：被事务包裹的一系列sql语句被认为是一个整体。这个整体中的每一条sql要么全部执行成功, 要么全部执行失败。分布式事务主要就是搞这一块。
• I （isolation隔离性）：并行事务之间互不干扰。保证多线程并发环境下事务与事务之间互不干扰。多个用户并发访问同一个库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间要相互隔离。一个事务正确提交之前，它可能的结果不应该显示给其他事务。解决隔离性的方案就是给数据库设置事务的隔离级别。
• D（durability持久性）：事务一旦提交，不能再回滚，将永久的改变数据表。
• C（consistency一致性）：通过上面的AID保证一致性。数据在事务操作前后, 数据总量不能发生变化. 例如两人转账后,总钱数不变。

1.7 事务并发问题

• 事务并发问题实际上就是隔离性问题。隔离性问题的本质就是多线程并发操作同一个资源造成的多线程并发安全问题。加锁可以保证隔离性，但是会造成数据库性能大大下降。

• 若两个事务并发修改，必须隔离；若两个事务并发查询，完全不用隔离；若一个事务修改，另一个事务查询:

  • 如果mysql完全不隔离将会产生的问题

    • 脏读（Dirty Read）：一个事务读取到另一个事务未提交的数据。
      事务A读取到了事务B未提交的数据, 如果事务B将操作回滚了, 那事务A读取的数据不就不对了吗。

    • 不可重复读（Non-Repeatable Read）：一个事务读取到了另一个事务已经提交的数据. => 针对更新。
      一个事务中, 两次相同的查询却返回了不同的数据。
      在我两次读取某条数据的中间，有人对这条数据进行了修改并提交，导致我两次读取的结果不同。

    • 幻读（虚读）（Phantom Read）：一个事务读取到了另一个事务已经提交的数据. => 针对添加或删除。

      一个事务第一次读取到的数据条目比后来读取到的数据条目少或多。
      对整张表的操作当中，我删除了t_user表中的所有数据，在我删除的过程中，有人向t_user表中添加了一条数据并提交，导致我删除完毕之后查看表发现还有一条数据。

  • 如果mysql完全隔离将会产生的问题: 数据库性能大大降低。

  • 如果由数据库的使用者决定是否隔离：这样最好！mysql提供了四大隔离级别，使用者可以控制数据库工作在哪个隔离级别下，以便防止不同的隔离性问题。

1.8 隔离级别

• Read Uncommitted 读取未提交内容。最低隔离级别，会读到其他事务未提交的数据。会产生脏读。

• Read Committed 读取已经提交内容（RC）。事务过程中可以读取到其他事务已经提交的数据。会产生不可重复读问题。

• Repeatable Read 可重复读（RR）。每次读取相同的结果集，不管其他事务是否提交。会产生幻读问题。mysql默认。

  • mysql的RR隔离级别能解决两次当前读的幻读问题。那么我不做当前读就会有问题，所以mysql的RR没有完全解决幻读问题。

• Serializable 串行化。事务排队，没有并发，隔离级别最高，性能最低。串行化相当于让多个用户(线程)排队访问同一条数据, 相当于没有并发. 因此性能很低。

• 生产环境一般使用RC或者RR隔离级别。

  事务隔离级别	脏读	不可重复读	幻读
  读未提交(read-uncommitted)	是	是	是
  读已提交(read-committed)	否	是	是
  可重复读(repeatable-read)	否	否	是	mysql默认
  串行化(serializable)	否	否	否	性能非常低

  

---

• 关于隔离级别的语法

  -- 修改隔离级别
  set [session|global] transaction isolation level 隔离级别;
  -- 查询当前隔离级别
  select @@tx_isolation;
  

• 在上述的设置隔离级别的语法中，如果指定了session，那么只能修改一个会话窗口的事务的隔离级别，也就是一个黑窗口的隔离级别。而如果指定了global，那么就是修改数据库全局的隔离级别。

• 注意，在mysql8中，查询隔离级别的变量已经变成了transaction_isolation。可以通过以下的方式查询mysql8中的当前隔离级别：

  select @@transaction_isolation;
  show variables like 'transaction_isolation';
  

1.9 事务实现原理

1.9.1 概述

• innodb事务通过三个技术实现：MVCC（多版本并发控制）、undo log（做回滚）、redo log（做事务持久性）。

1.9.2 MVCC

• MVCC即多版本并发控制，作用是解决读写冲突问题。使用隐藏列来管理多版本。
  

• mysql的表中有两个隐藏列：事务编号和回滚指针。事务编号单调递增，事务编号越大，代表该记录越新，事务编号表示这条记录是哪个事务写入的。回滚指针指向该条记录的上一个版本，类似git版本控制。

• select for update 是当前读，即读取该条记录的最新版本；普通的select是快照读，即读取到我应该读到的版本；
  

  无论用户B提交几次事务无论出现几个版本，用户A读取到的依然是历史版本001，所以RR使用快照读的方式解决了不可重复读。（MVCC是通过undo log实现的快照读。下文讲解）

• 那么mysql怎么知道应该读取001快照版本响应给A用户呢？这就是MVCC中一个很重要的技术点，叫做可见性判断。

  • 可见性判断就是判断哪些记录是我们可以读取到的
    • 创建快照这一刻（就是select这一刻），还没有提交的事务（指的是其他用户的事务）。不能读到！
    • 创建快照之后创建的事务（指的是其他用户创建的事务）。不能读到！

• 如何实现可见性判断？Innodb通过Read View实现可见性判断。

  • 做快照时获取所有活跃事务的id。就是A用户开启事务后select时，mysql获取所有还没提交的事务的id。例如A用户select某记录的时候，还有其他3个用户修改该记录，但是这三个用户还没有提交事务，那么这三个事务就是活跃事务。活跃事务列表中肯定有一个事务的id是最小的，肯定有一个事务的id是最大的，因为事务id单调递增。
    

1.9.3 undo log

• undo log 是回滚日志。undo 直译为取消、撤销、回滚数据。也就是 win 中 ctrl+z 的作用。

• undo log 作用：保证事务原子性、实现数据多版本（每条记录的历史版本全部存储在undo log中）。

• MVCC 通过 undo log 实现多版本。读最新版本叫当前读，根据可见性判断的策略读取到历史版本叫快照读。

• delete undo log：只用于回滚。事务提交之后就不能回滚了，那么事务提交之后 undo log 就可以删除了。

• update undo log：不仅用于回滚，同时实现快照读，不能随便删除。

  • 只用于回滚的话，事务提交 undo log 就可以删除了。但是还要实现快照读（读历史版本），那么就不能随便删除 undo log。
  • 但是 undo log 总要删除，删除时机是什么？undo log 中数据不能全删，删除系统最小活跃事务id之前的记录即可。

  

1.9.4 redo log

• redo log 相对比较简单，和多版本、可见性都没有关系。

• redo 直译为重做。

• redo log 作用：实现事务持久性。实现事务持久性的意思就是，在数据库发生异常崩溃的时候，有些事务没提交完，或者数据刷盘出错了，用来恢复数据。所以说redo log作用就是 ①记录数据修改，用来做异常恢复。②redo log 还能实现顺序写磁盘，降低数据写入磁盘的代价，提高刷盘效率。

• redo log 实现原理：循环写文件策略

  • redo log 可以理解为是4个文件拼成一个大文件

  • Write Pos：写入位置。有事务需要写就从Write Pos开始写，之后新的写事务就写到 [Write Pos，Check Point] 之间。

  • Check Point：刷盘位置。事务提交成功之后，之前的redo log就没用了，就可以刷掉（刷掉指的是redo log中数据刷到磁盘，然后删除redo log中该数据）。 [Check Point，Write Point] 之间的数据将被刷到磁盘，然后在redo log中删掉。

  • Check Point => Write Pos：待落盘数据。

    

    上图理解为张三在Write Pos处写事务、提交事务，李四在张三屁股后面将提交的事务刷盘。

• 一条数据的写入流程
  

  • mysql客户端发起update请求，server层进行解析，server层将请求解析成innodb api操作innodb。
  • 第5步记录的是数据的修改信息，而不是记录数据。
  • 第7步如果事务是手动的，就客户端手动发起事务提交，如果事务是自动的，mysql自动提交事务。

• redo log的刷盘时机。涉及到会不会丢数据。下面是刷盘策略。
  

  • 写数据后只有进行刷盘操作，数据才会被写入磁盘。
  • 上图Log Buffer代表将redo log写入内存。
  • 这里涉及到一个参数innodb_flush_log_at_trx_commit。取值可以是0、1、2，对应上图的三行。
    • 如果刷盘策略值=0，数据从预加载内存提交事务之后，数据进入内存中的redo log，然后每秒刷一次盘写文件，由于是每秒写一次磁盘，所以可能丢失一秒的redo log数据。性能好，会丢数据。
    • 如果刷盘策略值=1，那么只有要事务提交，就直接将数据从预加载内存刷到磁盘，这样一点不会丢数据。性能差，不会丢数据。
    • 如果刷盘策略值=2（使用较多），每次提交事务就将数据放到操作系统内存，每秒从操作系统内存中刷盘。这样即使数据库挂了，只要操作系统不挂就不会丢数据。性能和丢数据适中，常用。

• redo log 能实现顺序写磁盘，降低数据写入磁盘的代价，提高刷盘效率