mysql 设计两个主键都不可重复_每天一道面试题：mysql（三）

面试必遇到的数据库事务隔离级别

直接进入主题，开淦

了解事务隔离级别之前，首先回忆一下第一期数据库锁

https://mp.weixin.qq.com/s/qx-gE3LH__LTVDTFGIPQUg

1、什么是事务？

事务是一系列的操作,他们要符合ACID特性.最常见的理解就是:事务中的操作要么全部成功,要么全部失败.但是只是这样还不够的。

2、ACID是什么？

A=Atomicity

原子性,就是上面说的,要么全部成功,要么全部失败.不可能只执行一部分操作.

C=Consistency

系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态.

I=Isolation

隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的。

D=Durability

持久性,一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果.

3、同时有多个事务在进行会怎么样呢？

• 脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.

• 不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.

• 幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉"

mysql的事务4种隔离级别:

主要用来限定事务内哪些改动是可见的，哪些是不可见的。低级别的隔离一般支持更高的并发处理，并拥有更低的系统开销。

查看事务隔离级别方法

show variables like '%tx_isolation%'

• 未提交读(READ UNCOMMITTED)

这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).

这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.

• 已提交读(READ COMMITTED)

其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改.

• REPEATABLE READ(可重复读)

可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是 幻读,当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.

• SERIALIZABLE(可串行化)

这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

4、InnoDB使用了哪种事务隔离级别？

可重复读

5、解决问题---mvcc模型

MVCC 全称是多版本并发控制系统，InnoDB 和 Falcon 存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决幻读问题。

MVCC工作原理是啥？

InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间(删除时间)。当然存储的并不是真实的时间而是(system version number)。

每开始一个新的事务，系统版本号都会自动新增，事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行记录的版本号进行比较。

6、REPEATABLE READ(可重读)隔离级别下 MVCC 如何工作？

SELECT：InnoDB 会根据以下条件检查每一行记录：

第一，InnoDB 只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行要么是在开始事务之前已经存在要么是事务自身插入或者修改过的。

第二，行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。
INSERT：InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。
DELETE：InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE：InnoDB 为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识保存这两个版本号，使大多数操作都不用加锁。它不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

7、MySQL 事务实现原理是什么？

事务的实现是基于数据库的存储引擎，不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有InnoDB 和 NDB。
InnoDB 是高版本 MySQL 的默认的存储引擎，因此就以 InnoDB 的事务实现为例，InnoDB 是通过多版本并发控制(MVCC，Multiversion Concurrency Control )解决不可重复读问题，加上间隙锁(也就是并发控制)解决幻读问题。
因此 InnoDB 的 RR 隔离级别其实实现了串行化级别的效果，而且保留了比较好的并发性能。事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。8、如何设置 MySQL 的事务隔离级别？

MySQL 事务隔离级别 MySQL.cnf 文件里设置的(默认目录 /etc/my.cnf)，在文件的文末添加配置：

transaction-isolation = REPEATABLE-READ

READ-UNCOMMITTED/READ-COMMITTED/REPEATABLE-READ/SERIALIZABLE
或：

set global | session tx_isolation=‘READ-COMMITTED’;

命令修改隔离级别。

// 设置全局事务隔离级别为 read committed

MySQL> set global transaction isolation level read committed;

// 设置当前会话事务隔离级别为 read committed

MySQL> set session transaction isolation level read committed;

9、说一说三个范式

• 第一范式: 每个列都不可以再拆分. 

• 第二范式: 非主键列完全依赖于主键,而不能是依赖于主键的一部分. 

• 第三范式: 非主键列只依赖于主键,不依赖于其他非主键.

在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由.比如性能. 事实上我们经常会为了性能而妥协数据库的设计.

放松放松