搞懂MySQL对事务的破坏与补救措施，扫清对事务的误区

数据库事务是对数据库中数据一系列操作的工作单元，这个单元应该保证四大特性：A(原子性）、C（一致性）、I（隔离性）、D（持久性）。这个工作单元就是我们平时说的事务，而其四大特性是为了解决现实问题而提出的规约，不同的数据库实现事务时都应该依照该规约。因为后续内容都是对这规约的实现，所以需要先明确这四个特性：

• 原子性（Atomicity）：事务中的一系列操作是一个不可分割的最小单元，对数据操作表现为：这系列操作要么全部成功，要么全部失败。
• 一致性（Consistency）：事务的的结果应该是符合事务中数据操作的预测结果的，对数据库表现为：这系列操作的最终结果就是想要的结果。
• 隔离性（Isolation）：存在多个事务时，各个事务之间的系列操作是互不影响的，相互隔离的。
• 持久性（Durability）：事务一旦完成，事务的结果应该是持久保存下来的，不会因为任何原因导致该结果丢失。

关系型数据库是依照该规约来实现事务的，其中原子性、一致性、持久性一般是通过文件记录方式来实现，而隔离性则是通过锁机制实现的。

好了，到这里就基本了解事务了,先低头沉思一个问题：事务带来了脏读、幻读、不可重复读的问题？然后再继续往下看，学技术不要随大流，要有自己的见解和思考。

这篇文章主要说隔离性，在读一遍隔离性特点：存在多个事务时，各个事务之间的系列操作是互不影响的，相互隔离的。实际情况中隔离性是通过锁来实现的。如果将数据库比较为图书馆，馆中每个书柜上都是一张表，为了实现隔离特性，可以在图书馆大门上加一把锁，钥匙只有一把，这样同时图书馆中只会有一个人，各个期间就实现了隔离特性（对于其他特性的文件记录可以理解为图书馆监控把每个人的行为都记录到文档中）。这是对事务的隔离特性的完美实现，也是事务隔离特性的要求，这种实现就是串行化。读到这里再思考下那个问题。

一个图书馆同时只提供一个人进入，太浪费资源了。是啊，太浪费资源了，但是如果提供并发能力（破坏隔离性）往往会破坏数据的一致性，因此需要在并发量和一致性之间找到一个平衡。最后大佬们根据对数据一致性破坏的程度将事务的隔离性区分了4个级别，一致性破坏造成的影响区分为:脏读、幻读、不可重复读，这就是ANSI SQL-92标准。

到这里，就可以回复上面那个问题了：脏读、幻读、不可重复读是为了并发能力而破坏了事务的隔离性而产生的，而不是事务造成的，是一种异常现象。

并发能力的提高

隔离性破坏

破坏隔离性，意味着多个事务可以相互影响，而事务本身的原子性还在。那么影响程度可以区分为：1.读取到了其他事务成功的结果（已提交）2.读取到了其他事务失败的结果（未提交）。这样以来隔离特性暂时可以区分为两种：读已提交、读未提交，加上最基本的串行化，目前有三种隔离级别。

一致性破坏

上面说破坏隔离性往往会导致破坏一致性，先说下不同破坏程度造成的影响程度：脏读、幻读、不可重复读

脏读

在一个事务中的多次读取中，该事务某次读取到了其他事务中未提交的数据，造成了该次读取的中出现了任何和上次读取的结果不一致的现象。

白话解释：脏读是读取到了此刻不应该存在的脏数据（此时此刻该数据还在酝酿中，还没有呱呱落地）。

不可重复读

在一个事务中的多次读取中，该事务某次读取读取到了其他事务中update或者delete提交的数据，造成了该次读取的结果与上次读取的结果中同行的数据某列值不相同或者数据行变少的现象。

白话解释：不可重复读是读取到了本应该已存在却被修改的数据（数据篡改为不可重复读）

幻读

在一个事务中的多次读取中，该事务某次读取读取到了其他事务中insert提交的数据,造成了该次读取的结果与上次读取的结果中行数变多的现象

白话解释：幻读是读取到了除了本应该存在的数据还有凭空造出来的数据（无中生有为幻读）

 

读未提交会产生一个数据不一致现象就是不可重复读，而现实意义中不可重复读基本等价于数据没有价值（大家去图书馆去查看参考资料，结果每个人看到的都不一样），基于现实意义，在读已提交和串行化之间增加了一个可重复读的隔离级别。至此隔离性破坏程度区分为四种，基本满足数据的现实需求了

 

隔离性的破坏程度对一致性的影响程度关系
 

事务隔离级别	脏读	不可重复读	幻读
ISOLATION_READ_UNCOMMITTED（读未提交）	可能	可能	可能
ISOLATION_READ_COMMITTED（读已提交）	不可能	可能	可能
ISOLATION_REPEATABLE_READ（可重复读）	不可能	不可能	可能
ISOLATION_SERIALIZABLE（串行化）	不可能	不可能	不可能

这里注意可重复读不能解决幻读问题

隔离性破坏方案MVCC

隔离性破坏主要解决的是并发能力，隔离性体现为多个事务互不影响，一旦破坏则存在以下情况

• A读-B读：无影响
• A读-B写：A事务可能读取到B事务修改、新增、删除的数据，造成数据不一致
• A写-B写：同行数据同一时刻只允许A事务或者B事务一个写，目前只能通过基于锁的并发控制技术（LBCC）来解决。

以上情况中，只有A读-B写这种情况能被无锁化来提高并发能力。优化方案就是多版本并发控制（MVCC）技术

MVCC

MVCC基本意思是不同事务对数据库的修改操作，不会覆盖掉以前的旧数据（不会篡改数据了，也就不会有不可重复读），而是产生一个新数据，实现旧版本数据和新版本数据共同存在，事务读取时只读取与自己相关版本的数据行。这样多个版本存在的数据行就不会受到其他事务的影响，从而不需要锁就能产生隔离性。想想图书馆中的每个人都拿的是印刷版本，而不是孤本，这样大家读书就互不影响了。由于版本数据的隔离性，故可以处理不可重复读问题。

 

注意：MVCC针对支持事务隔离性破坏而提出的解决方案，也就是说MVCC只在支持事务的存储引擎，如InnoDB才存在，

而且MVCC只适用于RC和RR级别（其他级别没有意义）。

 

不同数据对MVCC的实现不同。MySQL中主要是对数据行添加2个隐藏列来体现多版本。这两个列分别为事务ID（db_trx_id）、回滚指针（db_roll_pointer），也就是一个表中行数据存储格式如下,

db_row_id是保证表聚簇索引时才存在的（主键不存在且非null唯一索引也不存在时）

db_trx_id是存储数据行所属的事务ID（事务ID是一个自增的ID）

db_roll_pointer是执行undo日志中上个版本的数据行的指针

最终形成一个版本链，由于undo日志记录的数据也可以看做一个版本，这里简化后的版本链如下

ReadView

版本链中数据行这么多，一个事务怎么知道自己能看到哪些数据行呢？MySQL提出了ReadView概念来解决这个问题。

ReadView: 可读视图？一致性视图，ReadView在RR级别事务中第一个select操作就开始构建，RC级别每次select都会重新构建。ReadView在5.7版本中构造器如下

ReadView::ReadView()
	:
	m_low_limit_id(),
	m_up_limit_id(),
	m_creator_trx_id(),
	m_ids(),
	m_low_limit_no()
{
	ut_d(::memset(&m_view_list, 0x0, sizeof(m_view_list)));
}

看不懂没关系，下面介绍下主要的构造参数

• m_ids：存活的事务ID列表（ReadView创建时的存活事务ID列表，这是一个逆序列表，最底层是最近开始的事务，ID最大）
• low_limit_id： 当前事务可见低水位事务ID（其实是最大事务ID，要分配给下一个事务的ID）
• up_limit_id：当前事务可见高水位事务ID（其实是最小事务ID，如果m_ids为空，则最大与最小一致）

版本数据行的可见性与事务ID密切相关。因此需要了解到事务具有时间维度后具有的以下特点

• 事务创建有先后，事务ID是自增的，因此事务ID整体有序；
• 事务结束有先后，导致提交时事务的ID是无序的；

版本行的可见性区分边界为ReadView创建时间点，根据其他事务结束时间点与ReadView时间点先后顺序区分如下

因此按照ReadView时间点就可以给事务划分种类

 

• 将在ReadView创建点之前已提交的划分为一种
• 将在ReadView创建点之后还未已提交的（之前已开启）划分为一种，将这类事务作为存活事务作为m_ids，排除自身事务ID后逆序排序，其中事务ID最小的作为up_limit_id，如果m_ids为空，up_limit_id=low_limit_id。
• 将在ReadView创建点之后新创建的事务划分为一种，将这类事务中ID最小的作为low_limit_id

获取版本链中可见数据行方式

所有数据行中其事务ID如果在存活的事务ID列表中则表示不可见，逆序查询版本链，直至触发终止条件，版本链逆序结束。（删除的数据行对任何事务都是不可见的）。

终止条件是根据以上参数条件得到的，事务结束时的事务ID只与事务创建时间正相关，而与ReadView的落点没有太大关系，优化之后存在以下关系

• 满足小于up_limit_id的即可终止（创建点之前已提交数据行都可见）
• 满足大于等于low_limit_id的即可终止（创建点之后数据行都不可见）
• 在m_ids中的数据行都不可见

ReadView的最终效果：创建ReadView时，已经提交的最新版本数据行就是可见数据行，最终查询到的结果也是混这个数据行。

查询结果

RR级别时：在首次select操作时，只有前面已提交的事务结果才会被当前事务查询到。

RC级别时：在每次select操作时，只要是本次select之前别提交的事务结果才会被当前事务查询到。

解决的问题

RR级别：解决脏读、不可重复读

RC级别：解决脏读

特殊：如果读取的数据都是版本数据，则能解决幻读（版本的完美隔离性），也就是说能解决快照读的幻读。如果当前读则不能解决（当前读有可能产生新的快照数据）。

快照读与当前读

快照读

前面提到Read View,它读取的数据都是版本数据，这些版本数据就是快照数据。快照数据在创建Read View时创建。普通（无锁的）的select都是快照读。

当前读

前面说MVCC中旧版本数据和新版本数据共存，如果其他事务也想创建自己的旧版本数据，那么旧版本数据从哪里来？是从已提交数据中最新版本中拿来的，这种读取就是当前读。当前读取就是读取已提交的持久数据。带锁的select（select xxx from table for update 、select xx from table lock in share mode）和insert、update、delete（都要先读取到才能继续操作）都是当前读。

被当前读读取到的数据会被加锁：lock in share mode使用S锁（共享锁），其他都是X锁（排他锁）。

这里说明下不能解决幻读的原因：当前读能读取到后续且已提交其他事务中的执行结果。RR举例

例1：影响到其他事务的结果

mysql> SELECT * from read_view where id < 4;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  3 | 王五 |
+----+------+
2 rows in set (0.03 sec)

开启事务A，select开启快照

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

mysql> SELECT * from read_view where id < 4;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  3 | 王五 |
+----+------+
2 rows in set (0.03 sec)

事务B执行且提交

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

mysql> update read_view set name='王六' where id <4;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

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

mysql> select * from read_view where id < 4;
+----+------+
| id | name |
+----+------+
|  1 | 王六 |
|  3 | 王六 |
+----+------+
2 rows in set (0.03 sec)

 

事务A继续执行，先快照读印证可重复读，在当前读

mysql> select * from read_view where id <4;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  3 | 王五 |
+----+------+
2 rows in set (0.07 sec)

mysql> update read_view set name='王麻子' where id < 4;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from read_view where id <4;
+----+--------+
| id | name   |
+----+--------+
|  1 | 王麻子 |
|  3 | 王麻子 |
+----+--------+
2 rows in set (0.08 sec)

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

mysql> select * from read_view where id <4;
+----+--------+
| id | name   |
+----+--------+
|  1 | 王麻子 |
|  3 | 王麻子 |
+----+--------+
2 rows in set (0.07 sec)

事务B在快照读

mysql> select * from read_view where id < 4;
+----+--------+
| id | name   |
+----+--------+
|  1 | 王麻子 |
|  3 | 王麻子 |
+----+--------+
2 rows in set (0.04 sec)

 

代码虽然有点多余，但是都在做验证，已经能看到后续事务B的结果被事务A修改了，也就是说事务A影响到了后续已提交事务的结果。

 

例2：幻读示例，基础数据不动，操作流程不动，将例1中事务B的update操作改为insert操作

事务B操作

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

mysql> insert read_view(id,name) values(2,'李四');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from read_view where id < 4;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
|  3 | 王五 |
+----+------+
3 rows in set (0.03 sec)

事务A继续操作

mysql> select * from read_view where id <4;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  3 | 王麻子 |
+----+--------+
2 rows in set (0.03 sec)

mysql> update read_view set name='王麻子' where id<4;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> select * from read_view where id <4;
+----+--------+
| id | name   |
+----+--------+
|  1 | 王麻子 |
|  2 | 王麻子 |
|  3 | 王麻子 |
+----+--------+
3 rows in set (0.03 sec)

事务A还没提交就已经发现问题了，凭空多出来一行，无中生有即为幻读。

出现幻读的原因是：update的当前读能读取到其他事务的执行结果，顺便给当前事务新增了原本不属于当前事务的快照数据（已有的还在，没有的创建快照）。

解决幻读的方式就是：创建ReadView的select使用当前读，给相关数据行加锁，防止其他事务修改。这里使用间隙锁就可以了，因为已存在的快照数据不会被其他事务影响，只有不存在的快照数据才会出现新增快照数据的情况。间隙锁就能防止新增快照数据。如果不想使用锁，就要保证select之后出现的任何当前读操作只涉及到select时的结果，不能超出其范围即可，这样能同样防止新增快照数据。

LBCC

前面提到提高并发能力一个手段就是LBCC（基于锁的并发控制），这里就一块说下锁机制，也能了解间隙锁为什么能防止幻读。针对多事务之间涉及到写-写操作都会用的锁，锁分为表锁和行锁

表锁

表锁是锁的数据库的某一整张表，区分为读锁和写锁，用法如下

LOCK tables lock_demo READ;   -- 表加锁，读锁
UNLOCK tables  ;   -- 表解锁

LOCK tables lock_demo WRITE;   -- 表加锁，写锁：其他事务不能读、不能写
UNLOCK tables  ;   -- 表解锁

 

行锁

行锁是锁的数据库表中的某一行数据，区分为共享锁（S锁）和排他锁（X锁）。行锁对应的索引数据行不存在则会升级为表锁

共享锁

共享锁也称为读锁、S锁，锁定的数据行可以被多个事务同时读取，不能修改

上锁

select xxx from table where xxx lock in share mode;

解锁

事务结束（commit、rollback）、进程结束

排他锁

排他锁也称为写锁、X锁，锁定的数据行不能被其他事务读取，当然也不会被修改了。排他锁包含记录锁、间隙锁、临键锁

 

上锁

select xxx  from  table where xxx  for update;
update table set xxx=xxx where xxx
delete from table where xxx
insert table (xxx) values (xxx)

解锁

事务结束（commit、rollback）、进程结束

对比

表级锁: 开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
行级锁: 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高

MyISAM：只支持表锁

InnoDB：支持表锁和行锁

锁细节

存在表lock_demo，其中id为主键，age为普通索引，数据如下

id	age
1	15
2	56
3	67

记录锁：对聚簇索引值进行精确匹配锁定，锁定的整行数据

---

示例

BEGIN ;
select * from lock_demo where id=1 for update;

记录锁

BEGIN;
UPDATE lock_demo set age=16 WHERE age =15; -- 15 对应的id=1,在聚簇引树中已产生记录锁，因此阻塞

 

间隙锁：会锁定匹配范围内索引列对应值的内部间隙和首尾间隙

---

• 对非聚簇索引树中的索引列的进行加锁语句，不管数据是否存在，都会产生间隙锁

示例：

BEGIN;
select * from  lock_demo where age=16 for update;

 

间隙锁

BEGIN;
INSERT into lock_demo VALUES(null,10); -- 10 在（-∞,15）区间内，外部非相邻间隙，因此可以执行

BEGIN;
INSERT into lock_demo VALUES(null,55); -- 55 在（15,56）区间内，因此阻塞    -- 内部间隙

BEGIN;
INSERT into lock_demo VALUES(null,77); -- 77 在（77,+∞）区间内，外部非相邻间隙，因此可以执行

 

 

临键锁：会锁定匹配范围内索引列对应值的内部所有空间和首尾间隙（间隙锁+记录锁）

---

• 间隙锁的范围内如果存在记录，则升级为临键锁
• 对聚簇索引树中的索引列进行不存在的单条记录加锁语句，会产生间隙锁和记录锁
• 对聚簇索引树中的索引列进行范围加锁语句，不管数据是否存在，会产生间隙锁和记录锁

示例

BEGIN;
select * from  lock_demo where age BETWEEN 1 and 57 for update;

• 间隙锁锁定的是范围值且不包含索引值的前后2两个间隙，示例中索引值=15，则间隙锁锁定的是（-∞，15）和（15,56）和（56,67）三个间隙

间隙锁：针对的是间隙，对间隙操作只能是插入语句

BEGIN;
INSERT into lock_demo VALUES(null,10); -- 10 在（-∞,15）区间内，因此阻塞    -- 前间隙

BEGIN;
INSERT into lock_demo VALUES(null,55); -- 55 在（15,56）区间内，因此阻塞    -- 内部间隙

BEGIN;
INSERT into lock_demo VALUES(null,66); -- 66 在（56,67）区间内，因此阻塞    -- 后间隙

BEGIN;
INSERT into lock_demo VALUES(null,77); -- 77 在（77,+∞）区间内，因此可以执行

 

记录锁：针对的是行记录，对记录操作只能是更新和删除

BEGIN;
UPDATE lock_demo set age=16 WHERE age =15; -- 15 对应的id=1,在聚簇引树中已产生记录锁，因此阻塞

BEGIN;
DELETE from lock_demo where id=1;  -- 对应的id=1,在聚簇引树中已产生记录锁，因此阻塞

 

索引区间划分如下

 

 

表锁和行数其实也可以共存的，例如事务A使用共享锁住了整张表，而事务B也使用共享锁住了整张表，根据锁的机制，那么这两个事务难道只能串行化才能处理吗？这两个事务明明不冲突，为什么不能同时存在？于是意向锁便出现了

如果事务想要给表中几行数据加上行级共享锁，那么需要先在表级别加上意向共享锁（IS）；

如果事务想要给表中几行数据加上行级排他锁，那么需要先在表级别加上意向排他锁（IX）；

通过意向锁检测可以优化加锁操作

意向锁加锁检测

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

 

意向间隙锁

间隙空间很大，如果多个事务使用的间隙互不影响，也应该能同时进行。于是通过意向间隙锁来预定间隙位置，通过意向间隙锁就可以检查多个间隙插入是否冲突，如果不冲突就可以并发执行。

 

自增锁

专用于自增列的锁，这是一个表锁，一张表的自增ID是串行化的。当然这种自增锁行为是可以被优化的

配置参数：innodb_autoinc_lock_mode值0 ，1，2

• 0：表锁，一直持有自增锁，直到执行完插入语SQL后才释放自增锁。效率最低，很有可能阻塞
• 1：MySQL5.7默认值,表锁，对于已知插入行数的简单插入语句，批量锁获取足够的自增ID后，释放自增锁给其他事务使用，然后执行插入SQL。对于未知插入行数的批量插入语句，效果等同于0。具有伸缩性，效率相对居中，但也会出现阻塞
• 2：MySQL8.0默认值，获取一批自增ID（部分）后，把自增锁释放（交给其他事务去执行插入），后续再获取自增锁来获取一批自增ID（部分），再释放，这样交错获取ID，虽然最终ID是自增的，但是很有可能不是连续的。当然这种效率也是最高的。

简单插入：在预处理SQL时就能知道插入的行数。例如 insert into xxx values()、insert into xxx values()()、replace into xxx values()、replace into xxx values()()、

批量插入：在预处理SQL时不能知道插入的行数，只有真正执行时才知道。例如： insert xxx select、replace xxx select 、load data

原文地址：程序猿微录 搞懂MySQL对事务的破坏与补救措施，扫清对事务的误区