前言
前不久碰到了个mysql隔离级别相关的问题，后来就打算写一篇文章，记录一下mysql相关知识，以后复习也不用辛辛苦苦去找资料了，本文主要从以下几个方面来记录：
存储引擎
存储引擎是MySQL有别于其他数据库管理系统的最大特色，我们知道关系型数据库的数据是存在表里的，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式，每个表格就是一个数据，表是在存储数据的同时，还要组织数据的存储结构，而这些数据的组织结构就是由存储引擎决定的，即存储引擎的作用就是规定了数据存储时的存储结构。
存储引擎的优略势
在同个数据库中，我们可以为任何表指定存储引擎。例如，一个应用程序可能主要使用 InnoDB表，其中会有一个CSV 表用于将数据导出到电子表格，会有几个 MEMORY表用于临时工作区，这就需要了解各个存储引擎的优点和缺点，如下表格，详细的列出了常用搜索引擎支持与不支持的项（mysql最常用的存储引擎是InnoDB，毕竟只有它支持事务）：
| 特征 | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| B树索引 | 支持 | 支持 | 支持 | 不支持 | 不支持 |
| 事务安全 | 不支持 | 不支持 | 支持 | 不支持 | 不支持 |
| 备份/时间点恢复 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 集群数据库支持 | 不支持 | 不支持 | 不支持 | 不支持 | 支持 |
| 聚集索引 | 不支持 | 不支持 | 支持 | 不支持 | 不支持 |
| 压缩数据 | 支持 | 不支持 | 支持 | 支持 | 不支持 |
| 资料快取 | 不支持 | 不支持 | 支持 | 不支持 | 支持 |
| 加密数据 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 外键支持 | 不支持 | 不支持 | 支持 | 不支持 | 支持 |
| 全文搜索索引 | 支持 | 不支持 | 支持 | 不支持 | 不支持 |
| 地理空间数据类型支持 | 支持 | 不支持 | 支持 | 支持 | 支持 |
| 地理空间索引支持 | 支持 | 不支持 | 支持 | 不支持 | 不支持 |
| 哈希索引 | 不支持 | 支持 | 不支持 | 不支持 | 支持 |
| 索引缓存 | 支持 | 不支持 | 支持 | 不支持 | 支持 |
| 锁定粒度 | 表 | 表 | 行 | 行 | 行 |
| MVCC(多版本并发控制) | 不支持 | 不支持 | 支持 | 不支持 | 不支持 |
| 复制支持 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 储存限制 | 256TB | 内存 | 64TB | 无限制 | 384EB |
| T树索引 | 不支持 | 不支持 | 不支持 | 不支持 | 支持 |
| 交易次数 | 不支持 | 不支持 | 支持 | 不支持 | 支持 |
| 更新数据字典的统计信息 | 支持 | 支持 | 支持 | 支持 | 支持 |
MySQL 8.0支持的存储引擎
InnoDB
MySQL 8.0中的默认存储引擎。InnoDB是用于MySQL的事务安全（兼容ACID）的存储引擎，具有提交，回滚和崩溃恢复功能来保护用户数据。 InnoDB行级锁定（无需升级为更粗粒度的锁定）和Oracle风格的一致非锁定读取可提高多用户并发性和性能。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I / O。为了保持数据完整性， InnoDB还支持FOREIGN KEY引用完整性约束。
MyISAM
这些表占用的空间很小。 表级锁定限制了读/写工作负载中的性能，因此它通常用于Web和数据仓库配置中的只读或只读工作负载中。
Memory
将所有数据存储在RAM中，以便在需要快速查找非关键数据的环境中进行快速访问。该发动机以前称为HEAP发动机。它的用例正在减少；InnoDB借助其缓冲池存储区，它提供了一种通用且持久的方式来将大多数或所有数据保留在内存中，并 NDBCLUSTER为大型分布式数据集提供了快速的键值查找。
CSV
其表实际上是带有逗号分隔值的文本文件。CSV表允许您以CSV格式导入或转储数据，以与读取和写入相同格式的脚本和应用程序交换数据。由于CSV表未建立索引，因此通常InnoDB在正常操作期间将数据保留在表中，并且仅在导入或导出阶段使用CSV表。
Archive
这些紧凑的，未索引的表旨在用于存储和检索大量很少参考的历史，存档或安全审核信息。
Blackhole
Blackhole存储引擎可以接受但不存储数据，类似于Unix/dev/null设备。查询总是返回一个空集。这些表可用于将DML语句发送到从属服务器的复制配置中，但是主服务器不会保留其自己的数据副本。
NDB（也称为 NDBCLUSTER）
此集群数据库引擎特别适合于需要尽可能高的正常运行时间和可用性的应用程序。
Merge
使MySQL DBA或开发人员可以在逻辑上对一系列相同的MyISAM表进行分组并将它们作为一个对象引用。适用于VLDB环境，例如数据仓库。
Federated
提供了链接单独的MySQL服务器以从许多物理服务器创建一个逻辑数据库的能力。非常适合于分布式或数据集市环境。
Example
此引擎作为MySQL源代码中的示例，说明了如何开始编写新的存储引擎。它主要是开发人员感兴趣的。存储引擎是什么都不做的 “ 存根 ”。您可以使用此引擎创建表，但是不能将数据存储在表中或从表中检索数据。
存储引擎的设置与转换
新建表指定存储引擎
创建新表时，可以通过ENGINE在CREATE TABLE语句中添加表选项来 指定要使用的存储引擎（下述sql为新建一个存储引擎为MEMORY的用户表）：
CREATE TABLE `user` (
`id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
`into_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`into_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`upd_user` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`upd_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`status` int(2) DEFAULT '1' COMMENT '软删除状态：默认：1 （-1：已删除 1：正常）',
`version` int(2) DEFAULT '1' COMMENT '版本号',
`remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_name` (`user_name`)
) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户表';
修改默认存储引擎
省略该ENGINE选项时，将使用默认存储引擎。在MySQL 8.0中默认引擎为InnoDB，可通过设置default_storage_engine变量来设置当前会话的默认存储引擎：
SET default_storage_engine = NDBCLUSTER;
存储引擎间转换
要将表从一个存储引擎转换为另一个存储引擎，请使用ALTER TABLE指示新引擎的语句(user为表名)：
ALTER TABLE `user` ENGINE = InnoDB;
Innodb 锁机制
锁是数据库系统区别于文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问。不同数据库和不同搜索引擎都可能有不同的锁机制，MyISAM 引擎的锁是表锁设计，并发读没有问题，但是并发写入可能就存在一定的问题。InnoDB 存储引擎锁的实现和 Oracle 数据库非常类似，提供一致性的非锁定读和行级锁支持。行级锁没有相关额外的开销，可以同时得到并发性和一致性，InnoDB支持多种粒度锁定，允许行锁和表锁并存。
行锁
InnoDB实现标准的行级锁定，其中有两种类型的锁： 共享（S）锁和排他（X）锁。
共享锁(S Lock) 允许事务读取一行数据
排他锁(X Lock) 允许事务删除或者更新一行数据
如果一个事务T1已经获得了记录r上的共享锁，另一个事务T2也可以获得记录r上的共享锁，这种情况称为锁兼容。但如果T1获取的是记录r上的排他锁，则T2获取不了记录r上的共享锁，这种情况称为锁不兼容。
注意: 共享锁和排他锁都是行锁，兼容指的是同一记录上的锁的兼容情况。
表锁
InnoDB 额外支持的一种表级锁类型，意向锁(Intention Locks)，意向锁可以分为意向共享锁(Intention Shared Lock, IS)和意向排他锁(Intention eXclusive Lock, IX)。但它的锁定方式和共享锁和排他锁并不相同，意向锁上锁只是表示一种“意向”,并不会真的将对象锁住，让其他事物无法修改或访问，比如上面建的user表，假设我们的程序中有个事务T1想要修改表user中的行r1，它会上两个锁:
1、给user表上意向排他锁
2、给修改的行r1上排他锁
在此期间，其他的事务也还是可以访问user表，它上的锁只是表明一种意向，它只会将user表中的某几行记录添加一个排他锁。
意向锁定协议如下：
1、在事务可以获取表中某行的共享锁之前，它必须首先获取表中的意向共享锁或更高级别的锁。
2、在事务可以获取表中某行的排它锁之前，它必须首先获取意向排他锁该表中的锁。
如果一个锁与现有锁兼容，则将其授予请求的事务，但如果与现有锁冲突，则不授予该锁。事务等待直到冲突的现有锁被释放。如果锁定请求与现有锁定发生冲突，并且由于可能导致死锁而无法被授予许可 ，则会发生错误。
其他锁
InnoDB的锁机制还有
1、记录锁：记录锁定是对索引记录的锁定。例如， SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 可以防止从插入，更新或删除行，其中的值的任何其它交易t.c1是 10。
2、间隙锁：间隙锁定是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前的间隙的锁定。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;防止其他事务将value 15插入column中t.c1，无论该列 中是否已有这样的值，因为该范围中所有现有值之间的间隙都是锁定的。
3、下一键锁：下一键锁定是索引记录上的记录锁定和索引记录之前的间隙上的间隙锁定的组合。InnoDB执行行级锁定，以使其在搜索或扫描表索引时对遇到的索引记录设置共享或排他锁。因此，行级锁实际上是索引记录锁。索引记录上的下一键锁定也会影响该索引记录之前的“ 间隙 ”。即，下一键锁定是索引记录锁定加上索引记录之前的间隙上的间隙锁定。如果一个会话R在索引中的记录上具有共享或排他锁 ，则另一会话不能R在索引顺序之前的间隙中插入新的索引记录 。
4、插入意图锁：插入意图锁定是一种通过INSERT行插入之前的操作设置的间隙锁定 。此锁发出插入意图的信号是，如果多个事务未插入间隙中的相同位置，则无需等待插入到同一索引间隙中的多个事务。假设有索引记录，其值分别为4和7。单独的事务分别尝试插入值5和6，在获得插入行的排他锁之前，每个事务都使用插入意图锁来锁定4和7之间的间隙，但不要互相阻塞，因为行是无冲突的。
5、自动上锁：一个AUTO-INC锁是通过交易将与表中取得一个特殊的表级锁 AUTO_INCREMENT列。在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务都必须等待自己在该表中进行插入，以便第一个事务插入的行接收连续的主键值。该innodb_autoinc_lock_mode 配置选项控制用于自动增加锁定的算法。它允许您选择如何在可预测的自动增量值序列与插入操作的最大并发性之间进行权衡。
在mysql官网有详细的资料，可以点击这里查看。
事务相关问题
事务是什么
事务，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。
如：张三想转100块钱给李四，那么就需要从张三的钱包扣除100，然后给李四的钱包加100，在这个过程中，如果张三扣钱没有出现异常，在李四加钱时出现异常，那么就要回滚操作，把扣了的钱还给张三，如果张三减钱和李四加钱都没有出现异常，那么才执行成功，而在这其中负责监视这些行为的，就是事务！
事务的特性
事务的特性为：ACID
原子性(Atomicity)： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部都执行，要么都不执行。
一致性(Consistency)： 指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
隔离性(Isolation)： 多个事务并发访问时，事务之间是相互隔离的，一个事务不应该被其他事务干扰，多个并发事务之间要相互隔离。
持久性(Durability)： 表示事务完成提交后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。
事务并发产生的影响
我们先在数据库建个表，插入一条记录，然后通过测试来看可能会产生哪些影响。
-- 新建用户表
CREATE TABLE `user_info` (
`id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
`user_money_amount` decimal(15,0) DEFAULT '0' COMMENT '用户金额汇总（分）',
PRIMARY KEY (`id`),
KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='用户信息表';
-- 插入一条数据
INSERT INTO user_info ( user_name, user_money_amount )
VALUES
( 'wx', 2000 );
脏读
事务A读到了事务B还没有提交的内容，就会产生脏读数据，现在右边修改了但是还没有提交事务，左边就已经读到了，如果右边后续发生了异常，这个修改是要回滚的，而左边读到的数据就是错的。
不可重复读
事务B又干扰到了事务A，虽然这次事务B还没提交的内容事务A读不到，但是事务A在同一事务中读了三次，并且三次的结果还不一致，这就是不可重复读。
幻读
事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。
隔离级别
什么是隔离级别
事务指定一个隔离级别,该隔离级别定义一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。隔离级别从允许的并发副作用(例如,脏读或幻读)的角度进行描述。
查看系统级隔离级别和会话级隔离级别
想要查看本地系统和会话隔离级别的话，可以通过以下SQL查询：
select @@global.transaction_isolation,@@transaction_isolation;
修改SQL如下：
-- 修改系统级别
set global transaction_isolation='read-uncommitted';
-- 修改会话级别
set transaction_isolation='read-uncommitted';
隔离级别的类型
读未提交（Read Uncommitted）
修改mysql会话级隔离级别为读未提交：
测试读未提交隔离级别会有什么问题：
脏读：
不可重复读：
幻读：
由上面的图可以看出，读未提交隔离级别会出现脏读、不可重复读、幻读问题，读未提交隔离级别很少用于实际应用。
读已提交（Read Committed）
修改mysql会话级隔离级别为读未提交：
测试读已提交隔离级别会有什么问题：
脏读：
不可重复读：
幻读：
由上面的图可以看出，读已提交隔离级别会不出现脏读问题，但是会出现不可重复读、幻读问题。
可重复读（Repeatable Read）
修改mysql会话级隔离级别为可重复读：
测试可重复读隔离级别会有什么问题：
脏读：
不可重复读：
幻读：
由上面的图可以看出，读已提交隔离级别会不出现脏读和不可重复读问题，但是会出现幻读问题。读已提交隔离级别是mysql默认隔离级别，虽然会出现幻读情况，但实际开发中用的最多的隔离级别就是可重复读，具体原因你看了下面就知道了。
串行化（Serializable）
修改mysql会话级隔离级别为串行化：
测试串行化隔离级别会有什么问题：
由上面的图可以看出，当隔离级别设置为serializable的时候，不出现脏读、不可重复读和幻读问题，事务B对表的写操作，在等事务A的读操作。其实，这是隔离级别中最严格的，读写都不允许并发。它保证了最好的安全性，但是性能不是很好，这也是为什么读已提交是默认隔离级别并且实际使用最多的原因了。
隔离级别的实现原理
读未提交（Read Uncommitted）
读未提交隔离级别是最不常用的隔离级别，在读未提交隔离级别下：SELECT语句以非锁定方式执行，但是可能会使用行的早期版本。因此，使用此隔离级别，此类读取不一致。这也称为 脏读。否则，此隔离级别的工作方式类似于 读已提交（Read Committed）
读已提交（Read Committed）
使用READ COMMITTED隔离级别，事务中的每个一致读取都会设置并读取其自己的新快照。使用时FOR SHARE，将发生锁定读取，SELECT阻塞直到包含最新行的事务结束。
快照读：就是select
select * from table ....;
当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update ;
delete;
因此，在读已提交的级别下，都会通过MVCC获取当前数据的最新快照，不加任何锁，也无视任何锁(因为历史数据是构造出来的，身上不可能有锁)。
但是，该级别下还是遗留了不可重复读和幻读问题： MVCC版本的生成时机: 是每次select时。这就意味着，如果我们在事务A中执行多次的select，在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读，即：重复读时，会出现数据不一致问题。
MVCC（ Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程。可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。 READ COMMITTD、 REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同， READCOMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。
Read View：
1、Read View就是事务执行快照读时，产生的读视图。
2、事务执行快照读时，会生成数据库系统当前的一个快照，记录当前系统中还有哪些活跃的读写事务，把它们放到一个列表里。
3、Read View主要是用来做可见性判断的，即判断当前事务可见哪个版本的数据
为了下面方便讨论Read View可见性规则，先定义几个变量：
- m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
- min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小
值。 - max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
- creator_trx_id：表示生成该ReadView的事务的事务id。
- trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
1）如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自
己修改过的记录，所以该版本可以被当前事务访问。
2）如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事
务生成ReadView前已经提交，所以该版本可以被当前事务访问。
3）如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事
务生成ReadView后才开启，所以该版本不可以被当前事务访问。
4）如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下
trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃
的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版
本可以被访问
注意，可重复读跟读已提交隔离级别，最大的区别就是：读已提交每次读取数据前都生成一个ReadView，而可重复读只在第一次读取数据时生成一个ReadView。
如上图，在1操作我们将左边事务id定为1，右边事务id定为2，然后结合底下我画的图和上面的字，你看看，看不懂就多看几遍，就知道为啥读已提交会产生不可重复读问题了，如果各位觉得有问题请留言，我在看下。
很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。
如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。
MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。
可重复读（Repeatable Read）
可重复读和读已提交最大的区别，就是生成ReadView的策略不一样，可重复读只在第一次读取数据时生成一个ReadView，而读已提交每次读取数据前都生成一个ReadView。
第一次select即创建ReadView，之后不会再发生变化，所以读到的还是原来的数据，即避免了不可重复读问题。
串行化（Serializable）
官方说法：
InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)
所有SELECT语句会隐式转化为SELECT … FOR SHARE，即加共享锁。
读加共享锁，写加排他锁，读写互斥。如果有未提交的事务正在修改某些行，所有select这些行的语句都会阻塞。
简单来说，在该隔离级别下，会自动将所有普通select转化为select … lock in share mode执行，即针对同一数据的所有读写都变成互斥的了，可靠性大大提高，并发性大大降低。
这里要注意一个概念，不要认为select不会加锁，在Serializable隔离级别下，会自动将快照读（select * from table ....）转换为当前读（select * from table where ? lock in share mode），即自动加锁。
参考与感谢
结尾
这个图是我看上面几篇文章学习画的，我也不确定有没有问题，如果各位觉得有问题，可以提出来讨论一下，然后我在改正，谢谢各位！（既然看到这里了，点个赞不过分吧！）
如果有需要的话可以关注一下我的公众号，会即时更新Java相关技术文章，公众号内还有一些实用资料，如Java秒杀系统视频教程、黑马2019的教学资料(IDEA版)、BAT面试题汇总(分类齐全)、MAC电脑常用安装包(有一些是淘宝买的，已PJ的)。
有缘下篇文章再见！
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
