2023-11-27 10:57 Java

关注

MySQL|隔离级别与事务

1 数据库三大范式？

（1）简单归纳：

第一范式（1NF）：字段不可分；
第二范式（2NF）：有主键，非主键字段依赖主键；
第三范式（3NF）：非主键字段不能相互依赖。

第一范式

原子性：强调的是列的原子性，即数据库中每一列的字段都是单一属性，不可再分的。并且这个单一属性必须是由基本的数据类型所构成的，如整数、字符串等。

如何这个特性，才能存入数据库。如果出现特殊情况，例如，一个人有多个电话号码，需要存储多行。

第二范式

依赖性：在满足1NF的基础上再满足依赖性的两个约束：一张表必须有一个主键；非主键类必须完全依赖于主键，而不能只依赖主键的一部分。

反例：学生表：学号，姓名，科目，成绩。主键：学号+科目。但是姓名依赖于学号，部分依赖于主键，不符合第二范式。进行拆分：成绩表：学号，科目，成绩。学生表：学号，姓名。

对不符合第二范式的表进行拆分，可以形成多个符合第二范式的表。

第三范式

在满足2NF的基础上，另外再满足一个条件：非主键列必须直接依赖于主键，不能存在传递依赖。也就是非主属性不能依赖于非主属性。

例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

简而言之，第三范式就是属性不依赖于其它非主属性。

2 数据库三大范式精要总结

（1）简单归纳：

第一范式（1NF）：字段不可分；　　
第二范式（2NF）：有主键，非主键字段依赖主键；
第三范式（3NF）：非主键字段不能相互依赖。

（2）解释：

1NF：原子性。字段不可再分,否则就不是关系数据库;；　　
2NF：唯一性。一个表只说明一个事物；　　- - 3NF：每列都与主键有直接关系，不存在传递依赖。

3 数据库隔离级别

未提交读，事务中发生了修改，即使没有提交，其他事务也是可见的，比如对于一个数A原来50修改为100，但是我还没有提交修改，另一个事务看到这个修改，而这个时候原事务发生了回滚，这时候A还是50，但是另一个事务看到的A是100.可能会导致脏读、幻读或不可重复读
提交读，对于一个事务从开始直到提交之前，所做的任何修改是其他事务不可见的，举例就是对于一个数A原来是50，然后提交修改成100，这个时候另一个事务在A提交修改之前，读取的A是50，刚读取完，A就被修改成100，这个时候另一个事务再进行读取发现A就突然变成100了；可以阻止脏读，但是幻读或不可重复读仍有可能发生
重复读，就是对一个记录读取多次的记录是相同的，比如对于一个数A读取的话一直是A，前后两次读取的A是一致的；可以阻止脏读和不可重复读，但幻读仍有可能发生
可串行化读，在并发情况下，和串行化的读取的结果是一致的，没有什么不同，比如不会发生脏读和幻读；该级别可以防止脏读、不可重复读以及幻读

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

4 什么是脏读？幻读？不可重复读？

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

5 事物的四大特性(ACID)介绍一下?

关系性数据库需要遵循ACID规则，具体内容如下：

原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

6 MySQL InnoDB 存储引擎的默认支持的隔离级别

这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化)隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

7 事务四大特性（ACID）原子性、一致性、隔离性、持久性？

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

第二种回答

原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

8 MySQL中为什么要有事务回滚机制？

而在 MySQL 中，恢复机制是通过回滚日志（undo log）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。当事务已经被提交之后，就无法再次回滚了。

回滚日志作用：

能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息
在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。

9 MySQL中为什么要有事务回滚机制？

回滚日志作用： 1)能够在发生错误或者用户执行 ROLLBACK 时提供回滚相关的信息 2) 在整个系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。

10 数据库并发事务会带来哪些问题？

数据库并发会带来脏读、幻读、丢弃更改、不可重复读这四个常见问题，其中：

脏读：在第一个修改事务和读取事务进行的时候，读取事务读到的数据为100，这是修改之后的数据，但是之后该事务满足一致性等特性而做了回滚操作，那么读取事务得到的结果就是脏数据了。

幻读：一般是T1在某个范围内进行修改操作（增加或者删除），而T2读取该范围导致读到的数据是修改之间的了，强调范围。

丢弃修改：两个写事务T1 T2同时对A=0进行递增操作，结果T2覆盖T1，导致最终结果是1 而不是2，事务被覆盖

不可重复读：T2 读取一个数据，然后T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

11 不可重复读和幻读区别是什么？

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。
例2（同样的条件, 第1次和第2次读出来的记录数不一样）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

12 关系型数据库的四大特性在得不到保障的情况下会怎样？

ACID，原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)

我们以从A账户转账50元到B账户为例进行说明一下ACID这四大特性。

原子性

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。即要么转账成功，要么转账失败，是不存在中间的状态！

如果无法保证原子性会怎么样？

OK，就会出现数据不一致的情形，A账户减去50元，而B账户增加50元操作失败。系统将无故丢失50元~

一致性

一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。那什么是合法的数据状态呢？这个状态是满足预定的约束就叫做合法的状态，再通俗一点，这状态是由你自己来定义的。满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！

如果无法保证一致性会怎么样？

例一:A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须大于0。例二:A账户200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的余额必须不变。

隔离性

隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

如果无法保证隔离性会怎么样？

假设A账户有200元，B账户0元。A账户往B账户转账两次，金额为50元，分别在两个事务中执行。如果无法保证隔离性，A可能就会出现扣款两次的情形，而B只加款一次，凭空消失了50元，依然出现了数据不一致的情形！

持久性

根据定义，持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

如果无法保证持久性会怎么样？

在MySQL中，为了解决CPU和磁盘速度不一致问题，MySQL是将磁盘上的数据加载到内存，对内存进行操作，然后再回写磁盘。好，假设此时宕机了，在内存中修改的数据全部丢失了，持久性就无法保证。

设想一下，系统提示你转账成功。但是你发现金额没有发生任何改变，此时数据出现了不合法的数据状态，我们将这种状态认为是数据不一致的情形。

13 数据库如何保证一致性？

分为两个层面来说。

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。
从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

14 数据库如何保证原子性？

主要是利用 Innodb 的undo log。 undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的 SQL语句，他需要记录你要回滚的相应日志信息。例如

当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

15 数据库如何保证持久性？

主要是利用Innodb的redo log。重写日志，正如之前说的，MySQL是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再写回到磁盘上。如果此时突然宕机，内存中的数据就会丢失。怎么解决这个问题？简单啊，事务提交前直接把数据写入磁盘就行啊。这么做有什么问题？

只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。

于是，决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

采用redo log的好处？

其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下：

redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

16 什么是事务的隔离级别？MySQL的默认隔离级别是什么？

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别（使用间隙锁解决了幻读问题）， Oracle 默认采用的 READ_COMMITTED隔离级别

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

InnoDB 存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

17、读可重复读和读提交有什么区别

对于可重复读来说，就是在 a 进入这个事务以后，那个他的这个数据在他的视图来说就是已经是固定了，如果说在 a 这个事务提交之前 B 的这个事务修改了那个数据，在 A 是看不到的于读提交的情况来说，就是说还是 a b 两个事务，b 事务修改一个数据然后并且提交以后，但 a a 还没有提交，然后 a 这个时候去读那个数据，就会读到 b 已经修改的数据。

补充

读提交，指一个事务提交之后，它做的变更才能被其他事务看到。可重复读，指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别。

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 MVCC 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。

「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

18、说下你了解的MVCC机制？包括其中的原理？

MVCC(Multi-Version Concurrency Control)是一种多版本并发控制机制，它可以在数据库的读写操作中，将数据按照时间版本进行保存，并且在读取时只读取已提交的版本，避免数据的并发访问产生的问题。

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值，而是系统版本号(system version number)。每开始一个新的事务，系统版本号都会自动递增。

补充

MVCC就是多版本并发控制，实现了读写的并发控制，在mysql通过readview 隐藏字段和undolog实现了，比如在可重复读里面，比如开启了一个事务，就生成了一个readview，然后记录现在active的事务，判断查询的数据在这个事务可不可读。

19、谈一下你了解的redolog(redo_log)？

redolog（重做日志）

记录所有对MySQL数据库的修改操作，但是只记录了物理操作，比如页的修改。redolog主要用于MySQL的崩溃恢复，即在MySQL崩溃后，通过重做日志，将数据库恢复到最近一次提交的状态。

可以使用 Forcing InnoDB Recovery 来进行崩溃恢复。

20、谈一下你了解的undolog(undo_log)日志？

undolog（回滚日志）用于记录事务的回滚操作，即在事务执行过程中，如果发生了回滚，会将回滚操作记录到undolog中。undolog主要用于 MySQL 的回滚操作，比如使用ROLLBACK语句回滚事务。undolog是InnoDB存储引擎的特有日志，不同于其他存储引擎。

21、binlog和redolog做数据恢复的区别?

redolog有大小限制，数据可能被覆盖，用来处理紧急数据库故障；binlog是全量操作日志，可以进行做全量的数据恢复。

补充

binlog和redolog都是用于MySQL数据库的日志。它们都可以用于数据恢复，但是它们的使用场景和恢复方法有所不同。

binlog是MySQL的二进制日志，它记录了所有对MySQL数据库的修改操作，包括插入、更新和删除等。binlog可以用于恢复MySQL数据库到指定的时间点或者指定的事务。具体来说，可以使用mysqlbinlog命令将binlog文件解析成SQL语句，然后再执行这些SQL语句，从而恢复MySQL数据库的状态。

redolog是MySQL的重做日志，它记录了所有对MySQL数据库的修改操作，但是只记录了物理操作，比如页的修改。redolog可以用于恢复MySQL数据库的崩溃恢复，即在MySQL崩溃后，通过重做日志，将数据库恢复到最近一次提交的状态。具体来说，可以使用innodb_recovery命令来进行崩溃恢复，该命令会根据重做日志来恢复数据库。

因此，binlog和redolog都可以用于数据恢复，但是它们的使用场景和恢复方法有所不同。binlog主要用于数据恢复到指定时间点或者指定事务，而redolog主要用于MySQL的崩溃恢复。

22、MySQL日志文件有哪几种？

redo log 重做日志，确保事务的持久性
undo log 回滚日志，确保事务的原子性，用于回滚事务，同时提供mvcc下的非锁定读
bin log 二进制日志，用于主从复制场景下，记录master做过的操作
relay log 中继日志，用于主从复制场景下，slave通过io线程拷贝master的bin log后本地生成的日志慢查询日志，用于记录执行时间过长的sql，需要设置阈值后手动开启

23、谈一下你理解的binlog？

binlog是二进制日志文件。他主要用来做主从同步。他有statement格式和row格式。

statement记录了执行的SQL语句，Row 格式保存哪条记录被修改。

binlog事务提交的时候才写入的。也可以用来做归档。

补充 binlog日志是MySQL数据库的一种日志记录机制，用于记录数据库的修改操作（如插入、更新、删除等），以便在需要时进行数据恢复、数据复制和数据同步等操作。

binlog日志的实现以下功能：

数据恢复：binlog日志可以用于回滚到之前的某个时间点，从而恢复数据。
数据复制：binlog日志可以用于在主从数据库之间复制数据，从而实现数据的高可用和负载均衡等功能。 MySQL的binlog日志有三种格式，分别是Statement格式、Row格式和Mixed格式。它们之间的区别如下：
STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；