10月, 2018 | 滩之南

Java数据库访问的分片技术架构实现

最近和朋友同事在一起聊天，问起我正在做的项目-数据访问中间件，很多人都有一丝疑惑，这不是重复造轮子的事情么？业界里ORM框架有mybatis/hiberate，分库分表有sharding-jdbc/mycat等等，你们为什么还要新做一个框架？新做的框架有什么不一样？

对于这些问题，我希望通过本文能够帮助解决这些疑惑。本文主要介绍了数据库访问的技术架构，然后讨论了分片在不同架构层级的技术实现方案，对标业界各个数据库访问中间件，相互进行比较，分析各种架构的实现差别和优缺点。

1. 什么是数据库分片

传统关系型数据库集中存储数据到单一节点，单表可以存储达数亿行的数据记录，通过主从备份作为灾备方案，保证数据的安全性，这基本可以覆盖大多数的应用场景。但是，随着互联网技术的发展，海量数据和高并发访问的应用场景日益增多，单表数据记录在突破一定阈值之后，其性能和可用性大幅下降。为了解决这个问题，将单一节点的数据拆分存储到多个数据库或表，即分库分表，使得关系型数据库能够存储的数据量阈值上限扩大1-2个数量级，从而满足业务需求。

数据库的分片拆分有两种方式，

按照业务划分的垂直拆分，将不同业务、不同模块的数据拆分为不同表。
按照容量平衡的水平拆分，将同一表的数据按照一定平衡策略，存储到不同数据库和表中。

前者的垂直拆分一般是一次性的，可以通过静态拆分实现（即停机拆分），而后者的水平拆分则需要技术框架的运行时刻支持。本文主要讨论水平拆分的技术架构实现，分析和对比相关框架和中间件。

讨论数据库分片的技术架构前，需要了解如下二个问题，

数据库访问的整个技术调用栈是怎样的？
在整个调用回路，哪里可以执行分片操作？如何实现？

2. Java数据库访问技术栈

先看看第一个问题，一个典型的Java应用，其数据库访问的技术调用栈如下图所示，

从上而下分别为，

Application 应用程序
ORM对象关系映射框架
JDBC Client客户端
JDBC Server服务端
Database 物理数据库

这几个组件由上至下依次调用，相互之间进行输入输出。

下面一一介绍这几个技术组件的功能，为我们接下来讨论分片技术的实现和架构打下铺垫。

2.1 Application应用程序

在应用程序中有许多Java POJO对象，若希望将这些POJO对象存储到数据库中，首先要转换为ORM能够识别的、规范的Entity对象，也就是ORM框架中所定义的持久化对象（Persistent Entity），然后调用ORM提供的API对这些Entity对象进行增删改查操作。

2.2 ORM对象关系映射框架

ORM是Object Relation Mapping的简称，即对象关系映射。其定义了一个Java实体对象到数据库表的映射关系，使得我们对Java实体对象的各种增删改查操作可以进一步映射到对数据库表的SQL执行。业界中比较有名的Java ORM框架有Hibernate和MyBatis。

Java在JPA 2.2规范文档中针对ORM提供了语言上的实现规范。若ORM框架实现了JPA规范，则可称为JPA ORM，例如Hibernate、EclipseLink、OpenJPA、jOOQ（注1）。而有些ORM框架则实现了自定义的对象映射规范，比如MyBatis、Ctrip Dal等，在上图中被称之为Simple ORM。

ORM框架接受由上层应用程序所提供的Entity，并提供相应的增删改查API供应用程序调用，ORM将各种操作映射为Sql语句，并输出给下层的JDBC API执行。

2.3 JDBC Client

JDBC客户端主要实现了JDBC API规范文档中定义的接口，其主要包括如下几个接口组件，

DataSource 数据库连接配置信息
Connection 一个连接会话
Statement 一个sql执行语句
ResultSet 从数据库中获取的执行结果集

对于JDBC客户端来说，其接受ORM生成的sql语句，调用执行，然后返回结果集给ORM，然后ORM将结果集映射回规范化的Entity对象。

2.4 JDBC Server服务端

JDBC服务端一般指由物理数据库暴露给外部，实现了一定的通信协议，供外部组件通过网络远程调用访问后端的数据库。

业界中几个常见的数据库通信协议规范有，

协议	协议连接URL格式
MySql	jdbc:mysql://host:port/datasource?serverTimezone=UTC&useSSL=false&useLocalSessionState=true
PostgreSQL	jdbc:postgresql://host:port/datasource
SqlServer	jdbc:sqlserver://host:port;DatabaseName=datasource
Oracle	jdbc:oracle:thin:@host:port:datasource

各个物理数据库除了实现自己的通信协议，也可以采用已有流行的通信协议，比如分布式数据库的TiDB、AWS Aurora、OceanBase，以及数据访问中间件sharding-proxy、MyCat，都采用了兼容Mysql协议的接口服务。这使得用户和应用程序可以使用已有的Mysql客户端进行访问，方便用户和应用程序的接入。

作为JDBC服务端，其接受JDBC客户端远程调用时发送过来的Sql语句，执行后返回结果给JDBC客户端。

2.5 Database物理数据库

物理数据库本身的存储特性、容量性能、可扩展性，都对数据库技术分片技术的实现产生直接的影响。若物理数据库对分库分片提供原生支持，那么将大大简化分片技术的落地实现，比如阿里云的分布式关系型数据库产品DRDS。

3. 哪里分片、如何分片

在了解了数据库访问的技术调用栈之后，接下来的问题就是哪里分片、如何分片。

上文讲到分片是指将同一系列数据记录按照一定策略，存储到不同数据库和表中，使得数据的存储和读取达到最优解，这个策略就叫做分片策略。

常见的分片策略有，

简单的有按主键ID取模，平均分配记录到不同表中，平衡访问流量；
按地域分片，实现数据记录能够按最近IDC机房入库；
按时间分片，方便最新数据查询；
按数据关联度，比如和订单ID相关联的数据落入同一库，做到同库查询；

可以看到，分片策略依赖于分片字段的设计。

从分片技术实现的角度来说，分片字段的获取则是实现分片的第一步。在数据库访问的技术调用栈上，每层其实都可以获取到分片字段。下表描述了在各个技术调用栈层上，如何获取分片字段、如何实现分片策略，并列出了相应技术实现的分片框架、中间件或数据库，

数据库访问技术栈	如何获取分片字段	分片策略实现	技术实现
ORM	通过Entity解析	Sql生成执行时分片	MyBatis/Hibernate/Ctrip Dal
JDBC Client	通过Sql解析	对Sql改写	Sharding-JDBC
JDBC Server	通过Sql解析	对Sql改写	Sharding-Proxy, MyCat
物理数据库	通过存储数据字段	原生支持分片策略	阿里云的DRDS

上表中，分片字段的获取是区分不同分片架构实现的关键点之一，也影响着分片策略的技术实现。

4. Java数据库访问的分片技术架构实现

为了更加清晰地了解Java数据库访问的各个分片技术架构实现，下图以数据库访问技术调用栈层为基础，绘制了各个不同分片技术实现的架构，以便相互比较区分，

图中绘制了五种架构实现，分别为，

ORM：在对象映射框架中提供分片技术实现。其主要优势是ORM握有持久化对象（Persistent Entity），可以非常容易地获取到分片字段，同时ORM框架为开发者提供各种扩展手段实现分片技术，比如DAO的继承实现自定义API，ORM拦截器等。开发者可以针对ORM框架编写扩展代码，以灵活的方式实现所需分片逻辑。其缺点在于绑定了特定ORM框架，分片逻辑对应用不透明。
JDBC Client：对于JDBC客户端来说，其JDBC API接受上层传输过来的Sql语句，所以可以通过Sql解析，获取分片字段信息，然后对Sql进行改写。这个方式优点在于对应用和ORM框架透明，支持不同ORM框架，甚至更换ORM框架对分片逻辑无影响。缺点主要在于Sql解析，其对Sql语句有要求，不支持复杂的Sql语句，而且由于绑定JDBC Client，只支持单一开发语言。
客户端（ORM+JDBC Client）：这里的客户端是指同时提供ORM和JDBC Client的框架，携程的DAL就是这样一个数据库访问客户端。其主要优点是，由于同时涵盖了ORM和JDBC Client两大技术栈层，因而技术空间将大为丰富，除了能够提供代码生成、拥有丰富API接口的数据库访问DAO、对应用程序透明的分库分表功能之外，最大的特点便是提供动态灵活的Sql构建器，Sql构建器+Entity+分库分表的技术结合，大大方便了开发者的使用。缺点是单一语言，由于包括了JDBC Client技术栈，其支持的数据库将受到限制，比如携程的DAL只支持MySql和SqlServer两种数据库。
服务端（JDBC Protocol）：这个可以认为是第二种JDBC Client模式的服务化版本，在JDBC Server前部署一个中间代理服务，在代理服务里做sql解析和sql改写。其拥有JDBC Client模式几乎所有优缺点，不一样之处在于应用开发不再受到语言限制，可以支持多种应用开发语言，数据库连接数将得到高效复用。与此同时，这种服务化架构将方便实现sql的集中治理和限流监控功能，这也是服务化之后带来的一大好处。但是多一层网络跳转将对访问性能有一定的影响，服务的高可用性也需要特别留心设计。
自定义客户端和服务端：这个可以认为是上述第三种客户端模式的服务化版本。其拥有客户端模式的优缺点，也有服务化之后的优缺点。

还有一种是物理数据库原生支持分片策略，比如阿里的DRDS，这个架构非常简单，分片策略对应用、ORM、JDBC都透明，都由数据库中配置和实现，本文就不列出讨论。

5. 技术架构实现对比

下表将各个分片技术方案的相关信息列出，作为对上述讨论的一个总结，

6. 是不是重复造轮子

区分是不是重复造轮子，一要判断架构是否有相似性，二要判断实现方式和提供的功能是否一样。即使是同一架构，同一实现功能，相互竞品之间也是有一定的性能特性区分。正所谓青出蓝而胜于蓝，长江后浪推前浪，在前人基础上，如何造出更好的轮子才是正道。

Hibernate、Sharding-JDBC、Ctrip DAL、MyCat四者分别以不同架构方式实现了数据库分片功能，通过上表可以知道，四者之间优缺点各有千秋，选择哪一种还是要取决于应用场景。

7. 分库分表的代价

我们在讨论分片的技术架构实现时，更需要了解的是分库分表所带来的成本代价。

分库分表操作不是一本万利的账，会带来新的风险和问题挑战，比如，

引入分库分表框架或中间件，其本身的技术掌握有一定成本，分库分表的配置管理和运行调试也是难点
数据记录插入容易，查询和Join变难
跨库的分布式事务，全局性约束失效（比如唯一主键）
自动化运维（灾备切换、数据库迁移等）实现
再扩容、更新分片算法

这些都是进行分库分表后面临的不小的成本代价。正确评估业务量和数据库能够承受的容量阈值，对症下药，是对分库分表的最佳建议。

以下是一些做分库分表前需要注意的考虑点，

若数据库的容量阈值足够满足业务量的数据记录需求，能不做分库分表则不做，避免不必要的成本代价。若不确定是否满足，可以先不做，待后续运营一段时间后再评估。
若业务量的数据记录需求超过数据库的容量阈值1-2个数量级，以审慎的态度考虑分库分片方案，并且对分库分表后的运营成本进行一定的考察。建议考虑下做定时数据备份清理工作，看能否把数据记录减少到数据库可支撑的范围之内，避免分库分表。只有在数据量超过容量阈值，数据库读写速度满足不了业务需求时，分库分片才是一个可选解决方案。
若业务量的数据记录需求超过数据库的容量阈值3-N个数量级，这个时候，数据量也超过了分库分片所能承受的应用场景。有这么大数量级的业务数据，分布式关系数据库是一个方向，比如谷歌的Spanner，亚马逊云的Aurora，PingCAP的TiDB、阿里的OceanBase，其可以支撑的数据量都可达万亿行以上。

8. 参考资料

注1：jOOQ可以自动生成JPA实体代码，并通过JPA native query API来实现对实体的操作，一个使用方法如下所示，

EntityManager.createNativeQuery(org.jooq.Query.getSQL(), resultSetMapping)

JPA 2.2规范文档
JDBC 4.3 规范文档
PingCAP的TiDB：https://www.pingcap.com/docs-cn/
Sharding-Sphere：http://shardingsphere.io/
Ctrip Dal开源项目：https://github.com/ctripcorp/dal
阿里云的分布式关系型数据库产品：DRDS

sql事务特性简介

Sql事务有原子性、一致性、隔离性、持久性四个基本特性，要实现完全的ACID事务，是以牺牲事务的吞吐性能作为代价的。在有些应用场景中，通过分析业务数据读写，使得可以降低事务的隔离性，容忍相应出现的数据一致性问题，实现事务的高并发、高吞吐、低时延性，这是sql事务优化的最佳实践。本文对sql标准中隔离性级别定义和可能会出现的问题进行一一介绍，最后通过Mysql数据库进行相应的演示。

1. Sql事务特性

业界常用字母缩写ACID描述Sql事务的四个基本特性，这四个字母分别代表为，

Atomicity 原子性
Consistency 一致性
Isolation 隔离性
Durability 持久性

下面对这四个特性进行介绍，

原子性：在一个事务中sql的操作，要不成功提交，要不回滚。当一个事务对数据库有做多项改动，这些改动要不全部一起提交进入数据库，要不就全部回滚，数据库无变化。
一致性：在事务执行的过程中，数据库一直保持一致性的状态，无论事务是否成功提交或者回滚，或者事务在执行中。当一个事务改动了多个表的记录，查询时要不看到所有变化后的新记录，要不就看到变化前的老记录，不会出现查询出新老记录混合出现的场景。
隔离性：各个事务之间相互隔离，互不影响，隔离性是通过数据库锁机制实现。要说明的是，隔离性是相对的，在数据库使用过程中，为了实现高并发，会降低事务之间的隔离性，并牺牲一定的数据一致性。更详细的讨论见下文。
持久性：事务的执行结果具有持久性，一旦事务提交后，其结果将被安全持久化到存储设备上，无论遇到电力中断、系统崩溃、关机、或者其它潜在威胁。

这四个特性中，原子性是事务最基本的特性，现代数据库都支持完整的原子性事务，而对于一致性、隔离性、持久性，在面对高可用性、高并发、高吞吐时会进行相应的取舍。

2. Sql事务特性：原子性

原子性是事务最基本的特性，根据其定义可以知道事务的执行分为三个阶段，

uncommitted 未提交，当前事务在执行中。
commited 已提交，当前事务做的改动被数据库接受，已安全持久化到数据库存储中。
rollback 已回滚，当前事务所做的操作被撤销，对数据库无改动。

一个执行中的事务只能以commited/rollback两者状态之一作为结束。

3. Sql事务特性：一致性、并发性和隔离性

数据库事务中，保持数据一致性是需要代价的，若要保证绝对一致性，则相关联的事务只能以串行执行（serializability），这是一种严格的隔离方式。在这种隔离方式下，有数据关联性的几个事务操作，只能一个一个按顺序执行，事务的并发被完全限制，数据库的事务吞吐将大为降低，一个写入操作甚至会被一个只读查询操作阻塞，等待读操作完成之后才可以进行下一步写操作。

在有些通用场景中，对读数据的准确性和时效性要求没有那么高，但希望有高吞吐量，能快速获取查询结果，在数据库操作高并发的同时，实现低时延性、快速的响应。为了实现这个目的，数据库专家提出了不同的数据隔离性级别，通过降低事务的隔离性，从而使得数据库的并发吞吐能够获得最佳的效率。

在sql-1992标准中，对数据库实现的隔离级别和隔离性提出了相关的规范定义，其中隔离级别包括四种，隔离性按低往高排序分别为，

READ UNCOMMITTED 读未提交：可以允许读未提交的事务数据
READ COMMITTED 读提交：只允许读已提交的事务数据
REPEATABLE READ 可重复读：保证读取的数据不会出现不一致的情况
SERIALIZABLE 串行：保证数据读取和写入的绝对一致性

现代数据库基本都实现了上述四个级别的事务隔离配置，供不同场景下使用。

4. Sql隔离级别和问题

鱼和熊掌不可兼得，面对隔离性和数据一致性，便是这样的选择题。追求高并发吞吐，必然低隔离性，数据一致性问题则愈严重。了解sql不同隔离级别定义和相应会出现的一致性问题，是进行隔离性级别优化选择的前提。

下表对Sql隔离级别和问题进行简要说明（依据sql-1992标准），

隔离级别	脏读 dirty read	不可重复读 non-repeatable read	幻读 phantom	并发吞吐性
读未提交	可能	可能	可能	高
读提交	不会	可能	可能	中等
可重复读	不会	不会	可能	低
串行	不会	不会	不会	串行

上表中，有列出三种数据不一致的问题，

脏读
不可重复读
幻读

下面对这三个问题一一进行讲解，然后给出mysql数据库中的三种问题的演示。

4.1 脏读

在一个事务T1中对某个数据记录进行了修改。若在事务T1提交之前，T2中此刻读取这个数据记录，随后T1进行了回滚操作，则T2将读取到一个未提交的无效数据。这个问题就叫做脏读。

脏读的问题在于，读取到错误的、无效的数据。

4.2 不可重复读

在一个事务T1中读取了某个数据记录，若此时事务T2对这个数据记录进行了修改和删除并提交，随后T1再尝试重复读取同一数据记录，这个时候T1发现数据有变化（或者发现已经不存在）。这种在一个事务中，重复读取数据却获取到不一致的查询结果，就叫做不可重复读的问题。

不可重复读主要问题在于，在一个事务中同一数据记录多次读取，会有前后不一致的问题（尽管前后读取的数据都是准确的）。

4.3 幻读

在一个事务T1中读取了一系列满足指定查询条件的数据记录，若此时事务T2执行一些操作，若T2操作会更新某些数据记录，而这些数据记录刚好落入T1事务中的查询条件，则当T1再次读取同一查询条件的数据记录，发现数据记录有不一样。

幻读的主要问题在于，在一个事务中数据记录读取的准确性依赖查询条件，其数据集合是当前事务所涉及的数据记录的超集。

4.4 不可重复读和幻读的区别

一个常见的疑问是，不可重复读和幻读的区别。从事务的控制角度，不可重复读针对的是当前事务所操作的数据记录，幻读针对的是符合当前事务查询条件的所有数据记录，后者是前者的超集。从解决方案来说，对于不可重复读的问题，只要锁住当前事务操作的数据记录即可，或者读取快照，两种方法都可以有效地避免前后读取不一致的问题；而对于幻读，则需要锁住所有符合查询条件的记录，其范围是无限扩大的，有时候甚至需要锁住整张表。

举个例子来说，下面的sql语句，将状态为NEW的记录进行更新，若表中符合NEW状态的记录有5个，

update `order` set `status`='PAID' where `status`='NEW';

则，

要解决不可重复读的问题，只要锁住当前表中那5条记录即可，或者留存快照，当前事务不受其它事务影响即可。
要解决幻读的问题，则要锁住所有可能出现NEW状态的其它事务操作，包括插入和更新操作。解决幻读问题，本质问题是需要了解其它事务对数据库的更新变化，一旦发现对当前事务有影响，则对外部其它事务进行阻塞，保证当前事务的优先执行权。

5. 演示

下面通过Mysql演示Sql的不同隔离级别和出现的问题，演示中使用的Mysql版本为5.7.16。

5.1 准备工作

在数据库中，执行如下语句，创建测试数据库和表order。

CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8mb4;
DROP TABLE IF EXISTS `test`.`order` ;

CREATE TABLE IF NOT EXISTS `test`.`order` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL DEFAULT '未知',
  `quantity` INT NOT NULL DEFAULT '0',
  `price` DOUBLE NOT NULL DEFAULT '0.0',
  `status` VARCHAR(64) NOT NULL DEFAULT 'NEW' COMMENT '订单状态：NEW-新订单，PAID-订单已付，CLOSE-订单结束',
  `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单';

INSERT INTO `test`.`order` (`id`, `name`, `quantity`, `price`, `status`) VALUES ('1', 'apple', '1', '5.0', 'NEW');
select * from `test`.`order`;

下面是一些基本的sql事务查询语句，

查询事务：SELECT * FROM information_schema.INNODB_TRX\G;
查询当前隔离级别：select @@tx_isolation;
设置当前隔离级别：set session transaction isolation level read uncommitted;

演示中会启动两个sql连接，分别为session1和session2，方便演示两个session之间的相互影响。

5.2 事务的原子性

事务的提交，

start transaction;
update `test`.`order` set `price`='7.0' where `id`='1';
commit;

事务的回滚，回滚后数据的修改被撤销，

start transaction;
update `test`.`order` set `price`='8.0' where `id`='1';
rollback;

5.3 事务的脏读

请按照下表执行相应的演示步骤，

step	session 1	session 2
1	use test;	use test;
2	set session transaction isolation level read uncommitted;
3	start transaction;
4	select * from `order`;
5		start transaction;
6		update `order` set `price`='10.0' where `id`='1';
7	select * from `order`;
8		rollback;
9	select * from `order`;
10	commit;	;

其中，

session-1 中在第2步设置了隔离级别为：读未提交。
session-1 中在第7步读取到的数据为session-2中未提交的数据，之后session-2在第8步进行了回滚，使得该数据失效。

请见session-1的输出，

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `order`;
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |     7 | NEW    | 2018-09-13 22:44:29 |
+----+-------+----------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> select * from `order`;
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |    10 | NEW    | 2018-09-13 22:46:49 |
+----+-------+----------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> select * from `order`;
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |     7 | NEW    | 2018-09-13 22:44:29 |
+----+-------+----------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

可以看到在session-1中第7步第二次查询时，获得了无效的数据，这就是脏读。解决脏读，可以提高隔离级别到：读已提交。

set session transaction isolation level read committed;

请见接下来的演示。

5.4 事务的不可重复读

请按照下表执行相应的演示步骤，

step	session 1	session 2
1	use test;	use test;
2	set session transaction isolation level read committed;
3	start transaction;
4	select * from `order`;
5		start transaction;
6		update `order` set `price`='11.0' where `id`='1';
7	select * from `order`;
8		commit;
9	select * from `order`;	;
10	commit;	;

其中，

session-1 中在第2步设置了隔离级别为：读已提交。
session-1 中在第9步读取到的数据为session-2中已提交的数据，该数据和前两次查询的数据不一致。

请见session-1的输出，

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `order`;
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |     7 | NEW    | 2018-09-13 22:44:29 |
+----+-------+----------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> select * from `order`;
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |     7 | NEW    | 2018-09-13 22:44:29 |
+----+-------+----------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> select * from `order`;
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |    11 | NEW    | 2018-09-13 22:52:45 |
+----+-------+----------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

可以看到在session-1中第9步第三次查询时，获得了不一致的数据，这就是不可重复读的问题。解决不可重复读，可以提高隔离级别到：可重复读。

set session transaction isolation level repeatable read;

请见接下来的演示。

5.5 幻读

请按照下表执行相应的演示步骤，

step	session 1	session 2
1	use test;	use test;
2	set session transaction isolation level repeatable read;
3	start transaction;
4	select * from `order` where `status`='new';
5		insert into `order` (`name`, `status`) VALUES ('apple', 'NEW');
6	select * from `order` where `status`='new';
7	update `order` set `status`='PAID' where `status`='NEW';
8	select * from `order` where `status`='PAID';
9	commit;	;

其中，

session-1 中在第2步设置了隔离级别为：可重复读。
session-1 中在第6步读取到NEW状态的数据记录为一条。
session-1 中在第7步更新了NEW状态的数据记录，状态设置为PAID。
session-1 中在第8步查询更新结果，发现实际上更新操作影响了两条数据记录。

请见session-1的输出，

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from `order` where `status`='new';
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |     5 | NEW    | 2018-09-14 17:20:24 |
+----+-------+----------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> select * from `order` where `status`='new';
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |     5 | NEW    | 2018-09-14 17:20:24 |
+----+-------+----------+-------+--------+---------------------+
1 row in set (0.00 sec)

mysql> update `order` set `status`='PAID' where `status`='NEW';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from `order` where `status`='PAID';
+----+-------+----------+-------+--------+---------------------+
| id | name  | quantity | price | status | date                |
+----+-------+----------+-------+--------+---------------------+
|  1 | apple |        1 |     5 | PAID   | 2018-09-14 17:22:18 |
|  2 | apple |        0 |     0 | PAID   | 2018-09-14 17:22:18 |
+----+-------+----------+-------+--------+---------------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

可以看到在session-1中第7步进行更新操作时，更新了当前事务并未看见的另外一条数据记录，这就是幻读所面临的问题。解决幻读问题，可以提高隔离级别到：串行。

set session transaction isolation level serializable;

若在上述演示中，在session-1中的第2步设置隔离级别为串行，则session-2中的第5步insert操作会被阻塞，直到session-1完成事务。

6. 小结

保证绝对的数据一致性，是以并发吞吐的下降为代价的。在很多时候，牺牲一定的隔离性，在有些应用场景下可以容忍一定的数据不一致问题，从而保障高并发的需求。了解sql隔离级别定义和相应会出现的问题，是进行隔离性级别优化选择的前提，根据不同的应用场景，选择合适的隔离级别，是数据库性能调优的重要手段。

7. 参考资料

sql1992规范
sql2011规范
Mysql 8.0用户手册 - ACID
Mysql 8.0用户手册 - 15.5.2.1 Transaction Isolation Levels
SqlServer 2017用户手册 - Transaction Isolation Levels
Oracle Database - Data Concurrency and Consistency
wiki 文档 - sql standard