sql transaction | 滩之南

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

标签：sql transaction

sql事务特性简介

1. Sql事务特性

2. Sql事务特性：原子性

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

4. Sql隔离级别和问题

4.1 脏读

4.2 不可重复读

4.3 幻读

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

5. 演示

5.1 准备工作

5.2 事务的原子性

5.3 事务的脏读

5.4 事务的不可重复读

5.5 幻读

6. 小结

7. 参考资料