MySQL并发事务控制:锁、MVCC

事务的特性与隔离级别

 

要讲锁,必须要先讲事务的特性与隔离级别,因为锁机制的存在是为了保证事务对应隔离级别下的特性.事务具有以下几个特性 在MySQL中,存在以下几种隔离级别 RU 读未提交,顾名思义,在这种隔离级别下,当多个事务并行对同一数据进行操作时,会读取未提交的数据，也被称之为脏读.这种隔离级别因为会出现脏读现象,所以在实际场景中很少用.

RC 读提交,一个事务只能看见已经提交事务所做的改变.但这种隔离级别会出现 不可重复读现象，即在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致.

RR 可重复读,这是MySQL的默认事务隔离级别,在这种隔离级别下,解决了RC存在的不可重复读问题,确保在同一事务中,会看到同样的数据行.但可能会出现幻读,即当一个事务在执行读取操作，第一次查询数据总量后，另一个事务执行了新增数据的操作并提交后，这个时候第一个事务读取的数据总量和之前统计的不一样,就像产生幻觉一样.

SERIALIZABLE 串行化,是4种隔离级别中最高的级别，解决了脏读、可重复读、幻读的问题，但是性能最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，在并行事务执行过程中，后一个事务的执行必须等待前一个事务结束.

MySQL中的锁

在MySQL中,按锁类型划分,有以下种类 提到锁到种类,需要提一下MySQL到存储引擎,MySQL常用引擎有MyISAM和InnoDB，而InnoDB是mysql默认的引擎。MyISAM是不支持行锁的，而InnoDB支持行锁和表锁。
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁,读锁会阻塞对同一张表对写操作,而写锁既会阻塞对同一张表对写操作,也会阻塞读操作.

对于InnoDB来说,大家都知道InnoDB相对与MyISAM,支持了事务和行锁.而行锁顾名思义,就是针对具体某一行数据上的锁,更切确的说是针对索引加的锁(这个会在下文锁的实现中讲到).

排他锁,通常我们在InnoDB中执行一个更新操作,针对这一行数据会持有排他锁,持有排他锁时,不允许再在数据行上添加写锁与读锁,其他事务对此行数据的读、写操作都会被阻塞,只有当前事务提交了,锁释放了才允许其他事务进行读写,达到避免 脏读 的效果

共享锁,主要是为了支持并发的读取数据而出现的，当一个事务持有某一数据行的共享锁时,允许其它事务再获取共享锁,但不允许其它事务获取排他锁，也就是说,在持有共享锁时,多个事务可以读取当前数据,但不不允许任何事务对当前数据进行修改操作，从而避免 不可重复 的问题

意向锁,首先需要明白一点,意向锁的作用是在表上的,当一个事务需要获取共享锁或排他锁时,首先要获取对应的意向锁,为什么要这样做呢,举个例子,假设在事务A中,某一行数据持有共享锁,这一行只能读,不能写.此时事务B申请获得表的写锁,假如加锁成功,那么事务B将能够对整个表的数据进行读写,与事务A冲突.这种操作肯定是不允许的,所以MySQL会在申请共享锁或者排他锁的时候,先获取对应的意向锁,也就是说,你要操作表中的某一行锁数据,先要看看整个表能不能被操作.意向锁的申请是有数据库完成的,不需要人为申请.

行锁的3种实现

上文对几种锁类型进行了分析,其实平时开发中接触到最多的还是行锁,行锁的实现有以下几种

在InnoDB中,锁的实现是基于索引的
Record Lock(记录锁),会锁住索引记录,比如 update table where id = 1;,会是这种实现

Gap Lock(间隙锁),实质上是对索引前后的间隙上锁，不对索引本身上锁,目的是为了防止幻读.当使用范围条件而不是相等条件检索数据并请求排他锁、或共享锁时,对于该范围内不存在的记录,不允许其修改插入.举个例子,当表中只有一条id=101的记录,一个事务执行select * from user where user_id > 100 for update;,此时另一个事务执行插入一条id=102的数据是会阻塞的,必须等待第一个事务提交后才能完成.间隙锁是针对事务隔离级别为可重复读或以上级别

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。另外，在上面的例子中，我们选择的是一个普通（非唯一）索引字段来测试的，这不是随便选的，因为如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁
 

Next-Key Lock,是记录锁和间隙锁对结合,会同时锁住记录与间隙.在可重复读(Repeatable Read)隔离级别下，会以Next-Key Lock的方式对数据行进行加锁

MVCC

锁机制可以控制并发操作,来保证一致性,但是系统开销会很大.在RC、RR的隔离级别下,MySQL InnoDB通过MVCC (多版本并发控制)机制来解决幻读,使事务在并发过程中,SELECT 操作不用加锁，读写不冲突从而提高性能.其原理是通过保存数据在某个时间点的快照来实现的.通过在每行记录后面保存隐藏列来存放事务ID,这样每一个事务,都会对应一个递增的事务ID.假设三个事务同时更新来同一行数据,那么就会对应三个数据版本,但实际上版本1、版本2并不是物理存在的,而是通过关联记录在undo log中,这样就可以通过undo log找回数据的历史版本,比如回滚的操作,会使用上一个版本的数据覆盖数据页上的数据

下面举例一个RR隔离级别下快照读的例子1:开启事务A按条件A查询到两条数据,此时事务B再插入1条数据满足条件A的数据,并提交事务,此时事务A再按条件A进行查询,查询到的依然是两条数据,也就是说,事务A查询到的并不是当前最新的数据版本,而是通过MVCC实现的历史快照版本.这也是可重复读的实现.

上面的例子介绍了读操作,那么写操作呢,也是如此事务之间互不干扰吗.再举例一个RR隔离级别下更新操作的例子2:假设事务A执行一个更新语句,满足更新条件A的的数据是2条,更新成功后不提交事务,此时事务B插入一条新的满足条件A的数据,此时事务A再按条件A去更新数据,实验发现事务B新插入的数据也被更新了.出现了幻读,这就是当前读,即对数据修改的操作(update、insert、delete)都会读到已提交事务的最新数据.

那么当前读的幻读问题如何解决呢?MVCC不能解决的问题当然是交给锁来解决了.上文提到的Next-Key Lock正是解决这个问题的方法,还以上面的例子2为例,给条件A字段非唯一索引,事务B进行插入数据的时候就会被阻塞,原因是事务A持有了Gap Lock,只有事务A提交了,事务B才能成功插入数据.这就解决了当前读操作下的幻读问题.

所以MVCC机制可防止快照读引起的幻读，next-key锁可防止当前读引起的幻读.需要说明的是,MVCC只在RC和RR两个隔离级别下工作。其他两个隔离级别和MVCC不兼容, 因为 RU总是读取最新的数据行, 而不是符合当前事务版本的数据行.而SERIALIZABLE 则会对所有读取的行都加锁.

锁的触发和升级

以默认的InnoDB引擎RR级别说明,表锁可以理解为每一行记录都持有Record Lock,更新记录时,当更新字段没有走索引时,无法获取对应记录的Record Lock,行锁便会升级为表锁,这一点可以结合MySQL Explain去分析.需要注意的是当普通索引值区分度低时，此时观察Explain显示是走了索引的,但当另一个事务并发操作不同数据时,依然发现第二个事务会阻塞,这是因为MySQL的执行优化器认为给多行记录一次一次当加锁不如表锁来的高效,所以不会把这个普通索引当做索引,而当区分度高时,则认为是高效的,不会升级为表锁.所以,创建合适的索引很重要,区分度低的字段不建议创建索引.

什么时候会出现DeadLock

什么是死锁呢?死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。举个例子4: 事务A获取 id=20的锁，事务B获取id=30的锁,然后,事务A试图获取id=30的锁,而该锁已经被事务B持有,所以事务A等待事务 B释放该锁,然后事务B又试图获取id =20 的锁这个锁被事务 A 占有,于是两个事务之间相互等待,这就会导致死锁.死锁的场景还有许多,归根结底,都是因为多个事务想要获取的锁互斥且获取的顺序不一致所造成.如何避免死锁呢,通常Record Lock引起的死锁问题开发时都会比较小心,但Gap Lock可能导致死锁的问题通常会被忽略,所以这一点要多加注意,另外就是建立合适的索引,如果没有索引,那么在操作数据时会锁住每一行,会增大死锁的概率.

锁问题的排查命令

show open tabbles; SHOW OPEN TABLES where In_use > 0;查看那些表被锁了
show status like 'table%';
table_locks_waited 出现表级锁定争用发生等待的次数,此值高说明存在验证的表记锁争用情况
table_locks_immediate 表示立即释放表锁的次数
show status like 'innodb_row_lock%'; Innodb_row_lock_current_waits 当前正在等待锁定的数量
Innodb_row_lock_time 系统启动到现在锁定总时间
Innodb_row_lock_time_avg 每次等待话费的平均时间 Innodb_row_lock_time_max 系统启动到现在等待最长一次所花时间 Innodb_row_lock_waits 系统启动后到现在共等待次数
information_schema
information_schema是MySQL专门记录性能信息的库,在5.7版本后默认打开
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 查看当前InnoDB的锁的信息,会显示是什么锁类型,属于那个事务ID
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 查看InnoDB事务ID,会显示是什么操作和一些常规信息,例如是否在运行running,还是等待锁. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;查看InnoDB锁的等待时间,和等待的是哪个事务ID的锁

题目

假设有表mvcccase，其中有两行记录，分别为(2,2)(3,3)

CREATE TABLE `mvcccase` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `value` bigint unsigned NOT NULL DEFAULT '0' COMMENT '值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

复制代码

起两个事务，执行如下操作：

SessionA	SessionB
start transaction;	start transaction;
select * from mvcccase; (1)	select * from mvcccase; (2)
	update mvcccase set id = 4 where id =2; (3)
	select * from mvcccase; (4)
select * from mvcccase; (5)
	commit;(6)
select * from mvcccase; (7)
update mvcccase set id=5 where id = 4; (8)
select * from mvcccase; (9)
commit;(10)
select * from mvcccase; (11)

共11个操作，每个操作的结果，如果都能答对，则说明掌握的很好。

基础知识

为了回答上面的问题，先回忆一些核心知识点。

Undo log

在InnoDB redo、undo、binlog，是如何合作的中曾提到过Undo Log。对每行数据进行操作之前都会记录Undo Log，目的是能将数据进行回滚。

同一个事务对数据进行多次修改或者多个事务对同一个数据进行修改，这些修改会按照时间顺序连成链，所以通过undo log可以发现数据修改的历史。

其中对于一行数据，有隐藏的DB_TRX_ID字段，记录这行数据由哪个事务更改。

快照读、当前读

通过Undo Log我们知道，一行数据，可能有多个版本。

当前读意味读取的是最新版本，select lock in share mode(共享锁), select for update ; update, insert ,delete都都是当前读。

快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本，select是快照读。

可见性规则

事务执行的快照读的那一刻，会生成数据库系统当前的一个快照。

生成快照的算法比较简单，在生成快照的时候，生成三个数据，活跃事务的ID数组、低水位、高水位，低水位由数组最小值推导而出，高水位由最大事务ID+1计算而出。

如活跃id为9，12，14，当前最大事务id为16，则低水位为8，高水位为17，活跃事务数组为{9,12,14}，如下图所示：

1. 如果落在绿色部分，表示这个版本是已提交的事务，这个数据是可见的；

2. 如果事务id是自己的值，表示数据是可见的;

3. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

4. 如果落在黄色部分，那就包括两种情况

   a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；

   b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

如果当前的版本不可见，则根据链表寻找上一个版本，再次判断可见性规则，直到找到可见的数据。

解题

假设sessionA和sessionB的事务id分别为9，12，(2,2)(3,3)数值由事务6执行。

SessionA-9	SessionB-12
start transaction;	start transaction;
select * from mvcccase; (1) (2,2)(3,3)	select * from mvcccase; (2) (2,2)(3,3)
	update mvcccase set id = 4 where id =2; (3) Rows matched: 1 Changed: 1 Warnings: 0
	select * from mvcccase; (4) (3,3)(4,2)
select * from mvcccase; (5) (2,2)(3,3)
	commit;(6)
select * from mvcccase; (7) (2,2)(3,3)
update mvcccase set id=5 where id = 4; (8) Rows matched: 1 Changed: 1 Warnings: 0
select * from mvcccase; (9) (2,2)(3,3)(5,2)
commit;(10)
select * from mvcccase; (11) (3,3)(5,2)

1、2

(1)(2)是快照读，此时DB中的数据为：

根据可见性规则，事务9和12能够看到事务6的数据，所以显示结果都为(2,2)(3,3)。

3

事务12的更新操作，做了两件事情

• 将ID=2这行置为deleted

• 创建新的ID=4

此时DB中的数据格式为：

4

执行select后，显示(3,3)(4,2)。根据可见性规则，ID= 2、3、4的行对事务12都可见，但ID=2已被删除，所以只显示(3,3)(4,2)。

5

select仍显示(2,2)(3,3)，虽然(3)执行完毕后，表中有三条数据，但根据可见性规则

• ID=2这条数据，最新的记录为事务12更改，对事务9不可见，所以进行回溯，找到事务6更改的版本，才可见

• ID=3这条数据，一直是事务6更改的版本，可见

• ID=4这条数据，只有事务12更改的版本，不可见

6

只有事务12提交后，其它事务才能更新ID=2、4，因为事务12对其加了写锁，其它事务想操作，会被阻塞。关于锁的部分，后面会找例子进行讲解。

7

仍显示(2,2)(3,3)，虽然事务12进行了提交，但表中的数据没有变更。分析流程和操作5一致。

8

虽然事务7快照中没有显示ID=4这条数据，但update可以成功，这是为什么呢？

• 因为SessionB提交了，在ID=4上面的写锁已经释放。

• 因为update操作是当前读，看的是最新版本，自然能够修改ID=4的值。

事务7的更新操作，做了两件事情：

• 将ID=4这行置为deleted

• 创建新的ID=5

更新后，DB中的数据格式为：

9

显示(2,2)(3,3)(5,2)，为什么会产生如此奇怪的结果？

现在表中有四条数据，根据可见性规则：

• ID=2这条数据，最新的记录为事务12更改，对事务9不可见，所以进行回溯，找到事务6更改的版本，才可见，值为(2,2)

• ID=3这条数据，一直是事务6更改的版本，可见

• ID=4这条数据，最新的是事务9更新的版本，虽然可见，但是因为已删除，所以不显示

• ID=5这条数据，最新的是事务9更新的版本，可见

10

sessionA提交之后，Undo Log也会被销毁，最终DB中的数据为：

11

此时进行的select，是一个新的事务，而且该事务ID比高水位还要高，所以表中的4行数据都可见，但因为有两行被删除，所以只显示(3,3)(5,2)。

作者：程序员麻辣烫
链接：https://juejin.cn/post/7048269238900883492
来源：稀土掘金
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

4.4 网络江湖传说，MVCC是否解决了幻读问题呢？

网络江湖有个传说，说MVCC的RR隔离级别，解决了幻读问题，我们来一起分析一下。

4.4.1 RR级别下，一个快照读的例子，不存在幻读问题

由图可得，步骤2和步骤6查询结果集没有变化，看起来RR级别是已经解决幻读问题啦~

4.4.2 RR级别下，一个当前读的例子

假设现在有个account表，表中有4条数据，RR级别。

• 开启事务A，执行当前读，查询id>2的所有记录。
• 再开启事务B，插入id=5的一条数据。

流程如下：

显然，事务B执行插入操作时，阻塞了~因为事务A在执行select ... lock in share mode（当前读）的时候，不仅在id = 3,4 这2条记录上加了锁，而且在id > 2 这个范围上也加了间隙锁。

因此，我们可以发现，RR隔离级别下，加锁的select, update, delete等语句，会使用间隙锁+ 临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，那就是说RR隔离级别解决了幻读问题？？