深入理解MySQL-事务隔离级别原理

MySQL JDBC装载

1. 加载驱动Class.forName(DRIVER);
2. 连接数据库Connection connect = DriverManager.getConnection(url,name,password);
3. 预编译sql语句PreparedStatement ps = connect.preparedStatement(sql);
4. 执行语句 ps.setString(1(/编号/),"");
5. 得到返回结果ResultSet rs = ps.execute();
6. 关闭连接

MySQL SQL执行顺序

from、on、join、where、group by、having+聚合函数、select、order by、limit

MySQL处理流程

传统sql执行流程：

客户端与服务器建立连接-》发送sql语句-》缓存查询，sql为key存在缓存直接返回（mysql8之后的版本移除）-》分析器-》-》优化器-》执行器

预处理sql执行：

在分析器里面sql解析过后不会执行，由客户端准备数据，服务器再执行数据和预处理sql

MySQL的binlog录入格式

statement，row和mixed。

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

MySQL存储引擎MyISAM、InnoDB

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎：不提供事务的支持，也不支持行级锁和外键。

	MyISAM	Innodb
存储结构	每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间	MyISAM可被压缩，存储空间较小	InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式	数据和索引是分别存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
记录存储顺序	按记录插入顺序保存	按主键大小有序插入
外键	不支持	支持
事务	不支持	支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）	表级锁定	行级锁定、表级锁定，锁定力度小并发能力高
SELECT	MyISAM更优
INSERT、UPDATE、DELETE		InnoDB更优
select count(*)	myisam更快，因为myisam内部维护了一个计数器，可以直接调取。
索引的实现方式	B+树索引，myisam 是堆表	B+树索引，Innodb 是索引组织表
哈希索引	不支持	支持
全文索引	支持	不支持

事务的四大特性

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

脏读、幻读和不可重复读

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

事务隔离级别

隔离级别	可能脏读	可能不可重读	可能幻读
Read uncommitt	✔	✔	✔
Read commit	❌	✔	✔
Repeatable read	❌	❌	✔
Serializable	❌	❌	❌

MySQL默认 Repeatable read

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

事务隔离主要依赖于MySQL锁以及MVCC版本控制

MVCC

MVCC全名多版本并发控制，主要目的为提高MySQL Innodb存储引擎的并发性能。
它能够在不加锁的情况下同时进行快照读和写操作，当前读和写操作同时进行会发生并发问题，需要借助锁机制。MySQL借助MVCC完成事务隔离级别读已提交和可重复读
先了解快照读和当前读
快照读
不加锁的select语句
不能读取到大于版本号事务提交的数据
当前读
select…for update 、select …lock in share mode、update、delete、replate into、insert
读取最新版本已提交的数据
版本链

在每一条修改数据行后隐藏三列数据
row_id:图中未标识，如果MySQL中没有索引默认使用该列创建索引
trx_id:事务id与修改该行数据的事务id相同
roll_pointer:指向上一个版本的修改数据行（用于回滚数据，插入语句没有指向）
Read View：一致性视图

trx_ids:表示活跃事务id列表
up_limit_id:活跃事务中的最小事务id
low_limit_id:已创建的最大事务id
creator_trx_id:当前的事务id
实现读已提交
在同一事务中每一次快照读时，都会生成一次一致性视图
判断
当版本链中trx_id<up_limit_id时，说明该数据在事务开启之前已提交可以访问；
当trx_id=creator_trx_id时，说明数据修改为当前事务修改可以访问；
当trx_id>low_limit_id时，说明当前修改在事务开启之后，不可访问；
当trx_id<=low_limit_id时，判断trx_id在不在trx_ids内，如果在说明未提交不可访问，相反可以访问
实现可重复读
可重复读判断方法与读已提交相似，只不过Read View在一个事务中只会生成一次。
遇到START TRANSACTION WITH consistent snapshot或者select语句时开启一致性视图
解决幻读
在可重复读隔离级别下快照读时，其它事务在次期间增加、删除数据，读取结果都不会有变化，但是进行数据修改时就可能产生错误。
解决方法可以是
1.开启串行读隔离级别
2.使用next-key：record lock + gap lock
record lock 为行锁加在索引上面，没有索引变为表锁
gap lock 给索引记录间隙加锁，确保索引间隙不被修改
不用gap lock场景
唯一索引where条件全部命中，除此之外都会使用record lock + gap lock如：
record lock + gap lock

• 只使用唯一索引查询，检索条件是范围检索，或唯一检索而检索结果不存在
• 使用普通索引检索，不管是何种查询，只要加锁，都会产生间隙锁。
• 使用唯一索引和普通索引（数据行优先根据普通索引排序，再根据唯一索引排序）