面试官：谈谈MySQL如何保证事务并发安全？我：......

2020-11-10 本文已影响0人极光少年

前言

前一段时间我在找工作的时候，经常会被问到事务以及事务的隔离级别等问题，正好最近在公司对事务隔离级别的原理做了一次技术分享，今天把它整理出来分享给大家。如果大家觉得有不对的地方呢，可以在评论区指出。

事务

一、什么是事务

事务，即一组数据库操作的集合，这组操作要么全部执行成功，任意一个操作失败那么所有操作全部回滚。

二、事务的特性

事务有四大特性，即原子性，一致性，隔离性，持久性。这四个特性通常称为ACID特性。

原子性(Atomicity)：一个事务是一个不可分割的单位。一个事务中的操作要么全部执行，要么全不执行。

例如一个账户A向另一个账户B转账，A账户的余额减少，B账户的余额就要增加，两个操作一定同时成功或者同时失败。
一致性(Consistency)：事务使数据由一个状态变为另一个状态，数据的完整性保持稳定。

例如转账操作中，A账户转账给B账户，A账户减少的金额与B账户增加的金额必须是相同的。
隔离性(Isolation)：一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

事务互相干扰就会造成数据不一致，隔离性的最终目的是为了保证一致性。
持久性(Durability)：一个事务一旦提交，它对数据库中的数据的改变应是永久的。

只要事务提交成功，对数据库的修改就保存下来了，不会因为任何原因再回到修改前的状态。

三、事务的状态

事务共有五种状态：

活动状态
事务在执行时所处的状态称为活动状态。
部分提交状态
事务中的最后一个操作被执行后的状态叫做部分提交状态。此时变更还未刷新到磁盘上。
失败状态
事务不能正常执行的状态叫做失败状态。
提交状态
事务在经过部分提交状态后，将所有的变更数据写入磁盘，写完后进入提交状态，标志着事务成功执行完成。
中止状态
事务执行失败，数据回滚至事务执行前，此时事务所处的状态叫做中止状态。

四、事务的隔离级别

事务具有隔离性，隔离性的最终目的是为了保证数据一致性。而实现事务隔离性最简单的办法就是让事务串行执行，类似线程的串行执行，但这种方式效率低下，性能较差。所以在保证隔离性的前提下，为了不牺牲性能，事务能够支持多种隔离级别。
我们先来看一下当多个事务同时处理同一批数据时，如果没有采取有效的隔离机制，会发生哪些问题。

丢失修改
事务1和事务2对同一数据进行修改，其中事务2提交的数据结果破坏了事务1提交的结果，导致事务1的修改失效。例如火车售票系统，售票点1(事务1)查询车票余票有200张，售票点2(事务2)查询车片也有200张；售票点1卖出1张车票，剩余车票199张，写回数据库；售票点2同样卖出1张车票，剩余车票199张，写回数据库；此时共卖出2张车票，应剩余198张车票，但库存仍有199张，售票点1的修改被破坏了。
脏读
事务1修改某一数据时，事务2读取该数据，此时事务1因某些原因需要撤销修改，将数据回滚，恢复为修改前，而事务2读取的是修改后的数据，与此时数据库中的该值不一致，事务2读取到的数据即为脏读。
幻读
事务1进行两次读操作，第一次读取了n条数据，此时事务2对这n条数据进行了删除或插入m条数据，事务1进行第二次读操作，此时读到的数据为n-m或n+m条，仿佛出现了幻觉，即为幻读。
不可重复读
事务1进行两次读操作，第一次读取了某一数据，此时事务2对这条数据进行了修改，事务1进行第二次读操作，此时读到的数据与第一次读到数据结果不一致，即为不可重复读。

注：幻读与不可重复读有些相似，但幻读强调的是数据记录的增加或删减，不可重复读强调的是数据记录的修改。

产生以上四类情况的主要原因是并发操作破坏了事务的隔离性，所以要对事务进行并发控制，使得一个事务的执行不受其他事务的干扰，避免造成数据不一致。根据以上造成数据不一致的情况不同，数据库也具有不同的处理方式，即事务隔离级别。事务的隔离级别越高，能解决的数据不一致问题越多，但性能损耗也越大。四种事务的隔离级别如下。

读未提交(Read uncommitted)
一个事务可以读取另一个事务未提交的修改。是最低的隔离级别。
读已提交(Read committed)
一个事务只能读取另一个事务已提交的修改。该级别可以解决脏读问题。
可重复读(Repeatable read)
事务开始读取数据时不允许其他事务对这些数据进行修改。保证了同一事务多次读取同样的记录结果一致。该级别可以解决不可重复读的问题，但不能完全解决幻读问题。
可串行化(Serializable)
最该级别的事务隔离级别。强制事务串行执行，可以解决脏读、不可重复读、幻读问题，但效率低下，通常不使用这种方式。

注：可重复读是InnoDB的默认事务隔离级别，且已能够达到了SQL标准的可串行化。

MVCC与锁

一、什么是MVCC

MVCC(Multi Version Concurrency Control)，即多版本并发控制。通过维护数据的历史版本，解决并发访问下的数据不一致性。

二、MVCC的原理

1.undo log
undo log是InnoDB的事务日志。undo log是回滚日志，记录的是行数据的修改记录，即哪些行被修改成怎样，提供回滚操作。事务的操作记录会被记录到undo log中，用于事务进行回滚操作。
2.版本链
在InnoDB中，每个行记录都隐藏着两个字段：
1）trx_id：事务id。该字段用于记录修改当前行记录的事务的id。
2）roll_pointer：回滚指针。该字段用于记录修改当前行记录的undo log地址。
假设一张学生分数表t_student_score有id,name,class,score四个字段，此时只有一条记录，当前执行插入操作的事务id为1，则有如下示例图：

假设此时有两个事务t1、t2，id分别为2、3的事务对这条记录进行修改，执行如下操作：

由于每次数据的修改都会在undo log中产生日志记录下来，且roll_pointer会指向undo log的地址。所以，两次修改后的日志通过roll_pointer串联起来，形成的版本链如下图所示：

如图示，版本链的头结点是最新的行记录，而历史行记录由roll_pointer记录的undo log地址串联起来。如果数据库隔离级别为读未提交，那么读取版本链中最新的数据即可；如果数据库隔离级别为可串行化，事务之间是串行执行的，不会发生数据不一致的情况，直接执行读操作即可；如果数据库隔离级别为读已提交或可重复读，那么就需要遍历整条版本链，找到trx_id与当前事务相同的记录，即需要判断版本链中哪个版本是当前事务可见的。InnoDB通过ReadView实现了这个功能。
ReadView主要包括四个部分：

m_ids:表示在生成ReadView时当前系统中活跃的读写事务的id。
min_trx_id:表示在生成ReadView时当前系统中活跃的读写事务的最小id。
max_trx_id:表示在生成ReadView时系统应该分配给下一个事务的id值。
creator_trx_id:表示生成该ReadView的事务的id。

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

如果被访问版本的trx_id的值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
如果被访问版本的trx_id的值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
如果被访问版本的trx_id的值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id的值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

在MySQL中，读已提交和可重复读两种隔离级别的一个非常大的区别就是它们生成ReadView的时机不同。读已提交在每次读数据前都会生成一个ReadView，这样可以保证每次都能读到其他事务已提交的数据。可重复读只在第一次读取数据时生成一个ReadView，这样就能保证后续读取的结果一致。

三、锁

我们在开发过程中，常常遇到多个线程同时访问一个共享资源，往往需要并发控制来确保执行结果正确，在数据库事务中同样如此。事务也存在并发访问，即多个事务同时访问数据。事务的并发访问一般有三种情况：

1. 读-读操作：并发事务同时访问同一行或同一段数据记录。由于事务都是进行读操作，不会对数据造成影响，因此并发读操作完全允许。  
2. 写-写操作：并发事务同时修改同一行或同一段数据记录。由于事务都是进行写操作，极易发生丢失修改问题，因此要通过加锁解决，即当一个事务要对某行修改时，首先会给该行加锁，如果加锁成功才可以进行修改；如果加锁失败，就要排队等待，待加锁事务提交或回滚后将锁释放。  
3. 读-写操作：一个事务进行读操作，另一个事务进行写操作。这种情况容易产生脏读、幻读、不可重复读问题。最好的解决方案是读操作进行多版本并发控制(MVCC)，写操作加锁。

根据锁的作用范围分类，可以将锁分为表级锁和行级锁。表级锁作用于数据库表上，粒度较大；行级锁作用于数据行上，粒度较小。
为了实现读-读操作不受影响，写-写操作、读-写操作能够互相阻塞，MySQL使用了读写锁的思想，实现了共享锁与排他锁：

1. 共享锁(S锁)：用于不更改或不更新数据的操作，如SELECT语句。共享锁可以在同一时刻被多个事务持有。获得共享锁的事务只能读取数据，不能更改数据。我们可以通过SELECT ... LOCK IN SHARE MODE手工加共享锁。需要注意的是如果一个事务对数据加上了共享锁，其他事务只能对这部分数据再加共享锁，不能加排它锁。  
2. 排他锁(X锁)：用于修改数据操作，如INSERT、UPDATE、DELETE。确保事务不会同时对同一部分数据进行多重修改，在同一时刻只能被一个事务持有。排他锁的加锁方式有两种，第一种是自动加锁，在对数据进行增删改时都会默认加上一个排他锁。另一种是手工加锁，使用SELECT ... FOR UPDATE可以实现手工加排他锁。

考虑一个场景，事务t1给某行数据加行级共享锁，让该行数据只能读不能写，之后事务t2申请表级排他锁，让整张表的数据只能写不能读。如果事务t2的锁申请成功，那么它可以修改表里的任意一行数据，这与t1持有的行锁是冲突的。那么数据库要如何判断这个冲突呢？
首先需要判断表是否已被其他事务加了表锁；然后需要判断表中每一行是否加了行锁。这种判断方式需要遍历表中的每一行，效率低下。于是有了意向锁(I锁)。
意向锁可以认为是S锁和X锁在数据表上的标识，通过意向锁可以快速判断表中是否有记录被上锁，从而避免通过遍历的方式来查看表中有没有记录被上锁，提升加锁效率。意向锁是由数据库自己维护的。当我们给一行数据加上共享锁之前，数据库会自动先申请表的意向共享锁(IS锁)；当我们给一行数据加上排他锁之前，数据库会自动先申请表的意向排他锁(IX锁)。例如，我们要加表级别的X锁，首先判断表上是否有被其他事务加了表锁，如果没有，再检查是否有意向锁，此时直接根据意向锁就能知道这张表是否有行级别的X锁或者S锁，这时候数据表里面如果存在行级别的X锁或者S锁的，加锁就会失败。

四、InnoDB中的锁

1.表级锁
InnoDB中的表级锁主要包括表级别的意向共享锁(IS锁)，意向排他锁(IX锁)以及自增锁(AUTO-INC锁)。IX锁和IS锁已经介绍过了，下面介绍一下自增锁。
大家都知道，如果我们给某列字段加了AUTO_INCREMENT自增属性，插入的时候不需要为该字段指定值，系统会自动保证递增。系统实现这种自动给AUTO_INCREMENT修饰的列递增赋值的原理主要是两个：
1)AUTO-INC锁：在执行插入语句的时先加上表级别的AUTO-INC锁，插入执行完成后立即释放锁。如果我们的插入语句在执行前无法确定具体要插入多少条记录，比如INSERT ... SELECT这种插入语句，一般采用AUTO-INC锁的方式。
2)轻量级锁：在插入语句生成AUTO_INCREMENT值时先才获取这个轻量级锁，然后在AUTO_INCREMENT值生成之后就释放轻量级锁。如果我们的插入语句在执行前就可以确定具体要插入多少条记录，那么一般采用轻量级锁的方式对AUTO_INCREMENT修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。

2.行级锁
在了解InnoDB的行级锁之前，我们先简单了解一下当前读和快照读。

1)当前读：即加锁读。读取记录的最新版本，会加锁保证其他并发事务不能修改当前记录，直至获取锁的事务释放锁。使用当前读的操作主要包括：显示加锁的读操作与插入、更新、删除等写操作。  
2)快照读：即不加锁读。读取记录的快照版本而非最新版本，通过MVCC实现。InooDB在可重复读隔离级别下，如果不显示的加LOCK IN SHARE MODE、FOR UPDATE的SELECT操作都属于快照读，保证事务执行过程中只有第一次读之前提交的修改和自己的修改可见，其他的均不可见。

综上可知，通过MVCC可以解决脏读、不可重复读、幻读这些读一致性问题，但是这只是解决了普通SELECTD的数据读取问题，即快照读的读取问题。在当前读，即加锁读的情况下依然要解决脏读、不可重复读、幻读问题。这个时候需要在读取的记录上加锁，由于都是在行记录上加锁，这些锁都称为行级锁。
InnoDB的行锁是通过锁住索引来实现的，如果加锁查询时没有使用索引，会将整个表的聚簇索引锁住，相当于锁住整个表。根据锁定范围不同，行锁可分为：

记录锁(Record Lock)：单个行记录上的锁。
间隙锁(Gap Lock)：锁定一个范围，但不包括记录本身。
临键锁(Next-Key Lock)：是记录锁和间隙锁的结合。锁定一个范围，包括记录本身。是MySQL的默认行锁。
间隙锁和临键锁都是用来解决幻读的。

我们来测试一下在什么情况下会产生锁。

测试准备

环境:数据库MySQL，数据库引擎InnoDB，默认的事务隔离级别(RR)。

库表：

主键索引测试表

CREATE TABLE `t_num_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `num` int DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据初始化

INSERT INTO `t_num_test` VALUES ('1', '200');
INSERT INTO `t_num_test` VALUES ('5', '300');
INSERT INTO `t_num_test` VALUES ('9', '400');
INSERT INTO `t_num_test` VALUES ('13', '500');

普通索引测试表

CREATE TABLE `t_num_normal_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `num` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_num` (`num`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据初始化

INSERT INTO `t_num_normal_test` VALUES ('1', '1');
INSERT INTO `t_num_normal_test` VALUES ('5', '5');
INSERT INTO `t_num_normal_test` VALUES ('9', '9');
INSERT INTO `t_num_normal_test` VALUES ('13', '13');

关闭事务自动提交

#查看事务自动提交是否开启
SHOW VARIABLES LIKE '%autocommit%'; 
#关闭事务自动提交
SET autocommit = 0;

为了测试方便，我们让两个数据表的id相同，普通索引测试表的普通索引列的值与id列的值相同。

开始测试之前，我们来看一下两个数据表存在的区间有哪些。

(-∞,1]
(1,5]
(5,9]
(9,13]
(13,+∞)

因为两个表的数据值基本一致，所以对于主键索引测试表来说，上述区间根据t_num_test.id列的值生成；对于普通索引测试表来说，上述区间根据t_num_normal_test.num列的值生成。

1.记录锁测试

1)主键索引

#开启事务1
BEGIN;

#事务1，查询id = 5的数据并加锁
SELECT * FROM t_num_test WHERE id = 5 FOR UPDATE;


#开启事务2，插入区间(1,5]
INSERT INTO t_num_test VALUES(2,201);
#正常执行

#开启事务3，插入区间(5,9]
INSERT INTO t_num_test VALUES(6,600);
#正常执行

#开启事务4，更新锁定行
UPDATE t_num_test SET num = 10000 WHERE id = 5;
#阻塞

#提交事务
COMMIT;

根据上述案例可知，在使用主键索引id进行精准查询，只锁定一条记录时，MySQL加的是记录锁，不会产生间隙锁。

注：唯一索引在此场景下作用效果与主键索引相同。

2)普通索引

#开启事务1
BEGIN;
#事务1，查询id = 5的数据并加锁
SELECT * FROM t_num_normal_test WHERE num = 5 FOR UPDATE;

#开启事务2，插入区间(1，5]
INSERT INTO t_num_normal_test VALUES(2,2);
#阻塞

#开启事务3，插入区间(5,9]
INSERT INTO t_num_normal_test VALUES(6,6);
#阻塞

#开启事务4，插入区间(9,13]
INSERT INTO t_num_normal_test VALUES(10,10);
#正常执行

#开启事务4，更新锁定行
UPDATE t_num_normal_test SET num = 10000 WHERE id = 5;
#阻塞

#提交事务
COMMIT;

不难发现，当插入数据至区间(1,5]和区间(5,9]时事务发生阻塞，插入数据至区间(9,13]时事务正常执行了，也就是说在区间(1,5]和区间(5,9]产生了间隙锁；在更新锁定行时事务发生阻塞，在该行产生了记录锁。

根据上述案例可知，在使用普通索引指定num的值查询时，MySQL在行上加记录锁，在该行相邻区间加间隙锁，而记录锁与间隙锁的组合组成了临键锁，即使用了临键锁。

2.间隙锁测试

1)主键索引

#开启事务1
BEGIN;
#事务1，锁定区间(5,9]
SELECT * FROM t_num_test WHERE id BETWEEN 5 AND 9 FOR UPDATE;

#开启事务2，插入区间(1，5]
INSERT INTO t_num_test VALUES(2,201);
#正常执行

#开启事务3，插入区间(5,9]
INSERT INTO t_num_test VALUES(6,600);
#阻塞

#开启事务4，插入区间(9,13]
INSERT INTO t_num_test VALUES(10,1000);  
#正常执行

#开启事务5，更新id = 5的记录
UPDATE t_num_test SET num = 10000 WHERE id = 5;
#阻塞

#开启事务6，更新id = 9的记录
UPDATE t_num_test SET num = 10000 WHERE id = 9;
#阻塞

#提交事务
COMMIT;

测试结果：当给区间(5,9]加锁时，向区间(5,9]插入数据的事务被阻塞，向区间(1,5],(9,13]插入数据的事务正常执行了，所以MySQL在区间(5,9]上产生了间隙锁。在向id为5和9的两条记录执行更新操作时，事务被阻塞了，说明在两条数据记录上添加了记录锁。

由上可知，根据主键索引锁定一个区间时，MySQL会在该区间添加间隙锁，在区间边界处添加记录锁。

注：唯一索引在此场景下作用效果与主键索引相同。

如果锁定不存在的行会发生什么情况？

#开启事务1
BEGIN;
#事务1，锁定不存在的数据
SELECT * FROM t_num_test WHERE id = 7 FOR UPDATE;

#开启事务2，插入区间(1，5]
INSERT INTO t_num_test VALUES(2,201);
#正常执行

#开启事务3，插入区间(5,9]
INSERT INTO t_num_test VALUES(6,600);
#阻塞

#开启事务4，插入区间(9,13]
INSERT INTO t_num_test VALUES(10,1000);  
#正常执行

#开启事务5，更新id = 5的记录
UPDATE t_num_test SET num = 10000 WHERE id = 5;
#正常执行

#开启事务6，更新id = 9的记录
UPDATE t_num_test SET num = 10000 WHERE id = 9;
#正常执行

#提交事务
COMMIT;

测试结果：在区间(5,9]产生间隙锁。

当锁定不存在的数据时，会在该数据所在区间产生间隙锁。

2)普通索引

同记录锁普通索引

3.无索引列测试

#开启事务1
BEGIN;
#事务1，无索引列加锁
SELECT * FROM t_num_test WHERE num = 300 FOR UPDATE;

#开启事务2，插入区间(1，5]
INSERT INTO t_num_test VALUES(2,201);
#阻塞

#开启事务3，插入区间(5,9]
INSERT INTO t_num_test VALUES(6,600);
#阻塞

#开启事务4，插入区间(9,13]
INSERT INTO t_num_test VALUES(10,1000);  
#阻塞

#开启事务5，插入区间(13,+∞)
INSERT INTO t_num_test VALUES(14,2000,'kkk');
#阻塞

#提交事务
COMMIT;

表锁。

总结

对主键索引或唯一索引来说，当锁定一条记录时，会产生记录锁；当锁定一个区间时，会产生间隙锁和记录锁，即临键锁。

对普通索引来说，会产生临键锁。

对无索引列来说，会锁住整张数据表。

面试官：谈谈MySQL如何保证事务并发安全？我：......

前言

事务

一、什么是事务

二、事务的特性

三、事务的状态

四、事务的隔离级别

MVCC与锁

一、什么是MVCC

二、MVCC的原理

三、锁

四、InnoDB中的锁

测试准备

环境:数据库MySQL，数据库引擎InnoDB，默认的事务隔离级别(RR)。

库表：

1.记录锁测试

1)主键索引

2)普通索引

2.间隙锁测试

1)主键索引

2)普通索引

3.无索引列测试

总结

猜你喜欢

热点阅读