mysql事务学习笔记
2021-01-03 / highPhone啊

事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。在mysql中，事务是由存储引擎实现的，mysql有两种存储引擎，一种是InnoDB，另一种是Myisam，由于Myisam引擎不支持事务，所以这里讨论的事务都是基于InnoDB存储引擎的。

mysql事务四大原则(ACID)

  1. 原子性(Atomicity)
    事务是一个不可分割的工作单位，同一组事务的操作，要么都成功(持久化)，要么都失败(事务回滚)。例如我有600元，张三有300元，我给张三转账100元，这个过转账过程在同一组事务中，如果整个事务没有异常，那执行转账的所有操作，事务正常提交后，我的钱变成500，张三的钱变成400，这个结果就持久化到数据库了(都成功)。但是如果在我的钱已经转出(-100操作)，而张三的钱还没到账(+100操作)的时候，由于数据库宕机、其中一条sql语句执行不成功等不可预知的因素，造成这组转账事务还没来得及全部完成，在数据库恢复后，就会执行回滚操作，我的钱依然是600，张三的钱依然是300(都失败)。

  2. 一致性(Consistency)
    保证事务操作前和操作后的数据或者数据结构的一致性。例如，在上面的转账例子中，我和张三的钱总共是600+300=900元，在一组转账事务的前后，我们的总的钱这个数据是一致的。

  3. 隔离性(Isolation)
    隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  4. 持久性(Durability)
    事务一旦提交，就是不可逆的，事务中的操作会被持久化到数据库，不应该被数据库故障或者接下来的其他操作所影响。

mysql事务日志

在mysql中，与事务相关的日志有三种，分别是bin log(二进制日志),redo log(重做日志), undo log(回滚日志)，其中bin log是用mysql server层实现的，而redo log和undo log使用InnoDB存储引擎实现的。此处只是对事务日志做一个简述，没有深入展开。这里插个眼:mysql三种事务日志

  1. bin log

    • 可以使用 show variables like '%bin%'; 查看bin log相关配置
    • 可以使用sql_log_bin 设置bin log的开关，需重启mysql生效
    • 可以使用binlog_format设定bin log记录模式, 可选值有statement，row，mixed
    • bin log是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。
    • bin log在事务提交时写入

  2. redo log
    InnoDB的数据实际是存储在磁盘中的，但是如果每次读写都需要进行磁盘IO，效率会非常低，所以InnoDB有Buffer Pool(缓存池)机制，缓存池中存放了缓存表和索引数据，避免了读写都需要访问IO，提升了访问速度。当要访问的数据，在缓存池中没有找到，才会去读写IO。
    Buffer Pool的使用大大提高了数据库的读写效率，但也会造成另外一个问题：如果Mysql服务器宕机，此时，Buffer Pool中的数据还没有写到磁盘，这样数据的持久性无法保证。
    redo log被用来解决此问题，以保证数据持久性:在InnoDB中，未写入磁盘中页(Page)**叫做脏页**，redo log的作用就是记录脏页中的数据，在mysql宕机恢复后，一个事务是否持久化是根据redo log的刷盘情况决定的。如果一个事务的redo log已经全部刷入磁盘，那么这个事务就是有效的，也会被刷入磁盘(持久化)。
    为什么需要redo log:

    • 刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
    • 在InnoDB中，把脏页刷入磁盘叫做刷脏，刷脏的最小单位是页，即就算当前页中只有一行数据有修改，也需要更新整页。 而redo log中只记录真是需要写入磁盘的数据。

  3. undo log
    实现事务的原子性的关键，是事务发生回滚时，能够撤销回滚前已经执行的sql语句。InnoDB实现回滚，靠的是undo log:当事务对数据库进行修改时，InnoDB会生成对应的undo log，当事务发生回滚时，会根据undo log中的信息将数据回滚到事务修改前的样子。

事务隔离产生的三大问题: 脏读、不可重复读、幻读(虚读)

这里我们用下面这个表说明, 分别开了两个事务去对这个表进行操作：

1
2
3
4
5
6
7
8
9
mysql> select * from test;
+----+------------+----------+
| id | username   | password |
+----+------------+----------+
|  1 | highphone1 | 123456   |
|  2 | lisi       | 1234     |
|  3 | highphone1 | 1234     |
|  4 | highphone  | 123456   |
+----+------------+----------+

  1. 脏读
    一个事务读取到了另一个事务还没提交的修改

    事务A事务B
    begin;
    begin;
    update test set username = ‘test1’ where id = 1;
    select username from test where id =1;如果读取到的username为test1，则出现了脏读
    commit;

  2. 不可重复读
    事务A过程中，多次对同一个数据进行读取，事务B在事务A读取过程中，修改或者删除这一个数据，导致事务A在多次查询这个数据得到的结果不一样。

    事务A事务B
    begin;
    select username from test where id =1;第一次查询结果为’highphone1’begin;
    update test set username = ‘test1’ where id = 1;
    commit;
    select username from test where id =1;如果查询结果为’test1’, 则出现了不可重复读

  3. 幻读
    事务A按照一定条件条件查询出来一些数据，事务B又插入了一些满足事务A查询条件的数据，并提交了。事务A再次按同样条件查询会把事务B新插入的数据查询出来

    事务A事务B
    begin;
    select username from test where id >3;第一次查询结果只有一条，即id = 4那条记录begin;
    insert into test(id,username,password) values(5, ‘test2’,’123456’);
    commit;
    select username from test where id >3;第二次查询结果如果为两条，即出现了幻读

事务的隔离级别

  • 读未提交(Read Uncommitted)
    允许脏读, 也就是可能读取到其他事务未提交的修改。
  • 读已提交(Read Committed)
    只能读取到其他事务已提交的修改。Oracle等多数数据库默认隔离级别。
  • 可重复读(Repeatable Read)
    在同一个事务内的查询都是事务开始时刻一致的。mysql innodb默认的隔离级别。
  • 串行化(Serializable)
    完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

事务隔离级别以及在对应隔离级别下可能产生的问题如下表：

隔离级别脏读不可重复读幻读
读未提交(Read Uncommitted)可能可能可能
读已提交(Read Committed)不可能可能可能
可重复读(Repeatable Read)不可能不可能可能
串行化(Serializable)不可能不可能不可能

后记

在可重复读(RR)的事务隔离级别下，解决了脏读和不可重复读，但是没有解决幻读问题。但是在InnoDB的默认事务隔离级别RR中，是解决了幻读问题的，这里就涉及到了MVCC(多版本并发控制)的知识了，跟InnoDB快照读的实现有关。对于当前读、快照读，undo log的多版本实现，read view的可见性控制等知识，可以从下面几篇文献中继续学习，上面的总结也是基于这几篇文献的：

本文链接：https://highphone.xyz/fb1220e7.html