MySQL锁、MVCC、以及解决什么问题

以下所有观点仅针对 InnoDB 引擎，主要参考MySQL官方文档5.7版本,本文内容主要参考MySQL官方5.7的文档，并融合的自身的理解。

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html

索引、IO即性能

MySQL的聚簇索引是基于B+树实现的，由于B+树的结构，叶子节点顺序存储了多行数据，因此，叶子节点所在的内存页不能存储太多的记录行，如果要移动一行数据就会将数据页的某行记录拷贝到另一页，主键如果是顺序的，则磁盘页的数据会按顺序填充，新增的主键大于之前的主键会自动追加到新的节点页或最右侧的节点页，而随机主键则可能由于记录需要插入到之前的叶子节点而移动产生很多IO。
IO消耗的时间和内存移动以及CPU计算相比实在是太大了，甚至可能是几个数量级的，因此，减少IO就是提升性能、响应时间。
即使是顺序递增主键，在生产环境中我们仍然偏向于使用SSD硬盘，这样记录log等操作也会更快。

同时MySQL基于行加锁（Row-level locking），而锁又是加在索引上的。索引又分为聚簇索引、二级索引，聚簇索引上面已经讲过，而二级索引的节点页不存储数据，只存储聚餐索引的ID。

除空间索引外，InnoDB 索引都是B-tree数据结构。空间索引使用 R-trees，这是用于索引多维数据的专用数据结构。索引记录存储在其 B 树或 R 树数据结构的叶页中。索引页的默认大小为 16KB。
当新记录插入InnoDB 聚集索引时， InnoDB尝试留出 1/16 的页面空闲以供将来插入和更新索引记录。如果索引记录按顺序（升序或降序）插入，则生成的索引页大约是 15/16 满。如果以随机顺序插入记录，则页面从 1/2 到 15/16 满。

锁分类

InnoDB的锁进行了多个维度的细分，从基础概念上来看有共享锁、排他锁、意向锁，在实现上来看有行锁、间隙锁、Next-Key lock、插入意向锁、自增锁

共享锁（S lock）

多个读操作可以共享这个锁，可以并发访问,常用于大量并发读并且和写操作互斥的场景
共享锁和排它锁和我们常用的 ReentrantReadWriteLock的读锁和写锁相似

共享锁允许多个transaction同时获取，但与排它锁互斥，因此共享锁需要等待排它锁所在的trasaction提交后才能获取，反之亦然

排他锁（X lock）

写锁都是排他的，一旦获得了某个锁，其他线程就无法获取对应行的写锁和读锁，排他锁既可读取也可变更
常用于delete / update等DML\DDL变更语句中

意向锁 Intention Locks

InnoDB 支持多粒度的锁共存，允许行锁和表锁，比如 LOCK TABLES ... WRITE 会持有表的X lock。InnoDB 使用意向锁来实现多粒度锁。
意向锁也分为意向共享锁（IS）和意向排他锁（IX）

因此意向锁主要是为了对整表加写锁时更加高效，当对整表加写锁后，理论上可以修改表中的任意一行
有的操作需要锁定整个表，如alter语句执行需要锁住整个表，而假设此时数据库针对表还运行了很多个事务，各个事务各锁定了很多行记录，很明显，锁定整个表相当于将表中的所有行锁定了，因此行锁存在时另一个事务无法立即获取整表的锁

以下是X\S\IX\IS的兼容情况

记录锁Record Locks

前面主要介绍了不同的锁概念，在InnoDB中的锁都是row-level锁，实现上都基于index记录，不同的隔离级别会稍有不同。
作用于index record,比如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;阻止其他事务插入、更新、删除t.c1=10的行
Record locks总是锁定index record,当表没有索引时，InnoDB创建一个隐藏的聚簇索引（clustered index）来实现。

间隙锁 Gap Locks

间隙锁主要用于REPEATABLE_READ隔离级别中解决幻读问题，RC隔离级别一般不产生间隙锁
间隙锁会锁住两个索引记录的中间值，或第一行索引记录前面的值或最后一行索引记录后的值。
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 会阻止其他事务insert t.c1=15的新记录
间隙锁可能跨越单个索引值、多个索引值，甚至是空的。
间隙锁会有额外的性能消耗，考虑到性能和并发能力的折中，仅在部分隔离级别中使用，如可重复读（RR）
间隙锁特例：

单列唯一索引查询某个值时不会产生间隙锁
多列唯一索引中条件表达式中仅包括唯一索引的部分列
不同事务可以在建旋上持有冲突的锁，如A在建旋上持有一个共享间隙锁，B在同一个间隙上持有排他间隙锁，原因：如果从索引清楚记录，则必须合并不同事务在记录上持有的间隙锁

InnoDB 中的间隙锁是纯粹的抑制性，这意味着它们的唯一目的是防止其他事务插入到间隙中。间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。共享和独占间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

间隙锁可以显示的关闭，比如将事务隔离级别切换为READ COMMITTED(RC)后只有外键约束和duplicate-key检查才会使用到间隙锁。

使用 READ COMMITTED 隔离级别或启用 innodb_locks_unsafe_for_binlog 还有其他影响。READ COMMITTED在 MySQL 执行 WHERE 条件后，不匹配行的记录锁将被释放。对于 UPDATE 语句，InnoDB 执行半一致性读取，这样它会将最新提交的版本返回给 MySQL，以便 MySQL 可以确定该行是否匹配 UPDATE 的 WHERE 条件。
常用于RR隔离级别解决幻读幽灵行的问题。

Next-Key Locks

结合了index Record Lock 和 Gap Lock
InnoDB默认在REPEATABLE_READ隔离级别中使用Next-Key locks 防止幻读

插入意向锁

插入意向锁是一种在插入行之前由 INSERT 操作设置的间隙锁。此锁表示插入的意图，即如果插入到同一索引间隙中的多个事务未插入到间隙内的同一位置，则它们无需相互等待。假设有值为 4 和 7 的索引记录。分别尝试插入值 5 和 6 的单独事务，在获得插入行的排他锁之前，每个使用插入意向锁锁定 4 和 7 之间的间隙，但不要相互阻塞，因为行是不冲突的。

AUTO-INC Locks

自增列锁是一个特殊的表级别锁，主要用于事务插入时对AUTO_INCREMENT的处理
自增列对应的索引不一定要求是主键获取唯一索引，为了避免重复值，建议使用主键或唯一索引
innodb_autoinc_lock_mode 变量有三种可能的设置。“传统”、“连续”或 “交错”锁定模式的设置分别为 0、1 或 2 。

事务中的两种“读”

在提及MVCC之前先明确InnoDB的两种读模式

可能有读者会疑惑，事务的隔离级别其实都是对于读数据的定义，但到了这里，就被拆成了读和写两个模块来讲解。这主要是因为MySQL中的读，和事务隔离级别中的读，是不一样的。
对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

快照读（即一致性读，无锁）：就是select

	select * from table ....;

当前读（即锁定读，有锁）：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。

	select * from table where ? lock in share mode;
	select * from table where ? for update;
	insert;
	update ;
	delete;

事务的隔离级别实际上都是定义了当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”，就需要另外的模块来解决了。

一致性读 Consistent Nonlocking Reads

一致读取意味着使用InnoDB多版本控制向查询呈现数据库在某个时间点的快照。
不同的隔离级别一致性读的行为机制不同：

可重复读中，同一个事务的所有一致性读取都会读取该事务中的第一次读建立的快照
读已提交中，事务中的每个一致读取都会设置并读取自己的新快照。

一致读取是 InnoDB 处理 SELECT语句 READ COMMITTED和 REPEATABLE READ隔离级别的默认模式。一致读取不会对其访问的表设置任何锁定，因此其他会话可以在对表执行一致读取的同时自由修改这些表。
假设您在默认 REPEATABLE READ 隔离级别下运行。当您发出一致读取（即普通 SELECT语句）时， InnoDB为您的事务提供一个时间点，根据该时间点您的查询看到数据库。如果另一个事务在分配您的时间点后删除了一行并提交，您不会看到该行已被删除。插入和更新的处理方式类似。

锁定读 Locking Reads

如果您查询数据，然后在同一事务中插入或更新相关数据，则常规SELECT 语句无法提供足够的保护。其他事务可以更新或删除您刚刚查询的相同行。 InnoDB支持两种类型的锁定读取，可提供额外的安全性：

SELECT ... LOCK IN SHARE MODE 在当前事务提交之前保证共享锁定的记录不会被修改。如果其中任何行被另一个尚未提交的事务更改，您的查询将等待该事务结束，然后使用最新值。
SELECT ... FOR UPDATE 对于搜索遇到的索引记录，锁定行和任何关联的索引条目，就像您为这些行发出 UPDATE 语句一样

MVCC

前面讲解锁知识的时候已经聊到使用Next-Key来解决幻读问题，防止幽灵行产生，事务处理过程中有时可能重复执行某一条语句，在某些场景下我们希望他们的返回结果是一样的。笔者能想到的场景就是代码分层情况下不同层级代码多次执行想要拿到相同的效果或事务内部插入或修改数据后查询验证。
MySQL InnoDB使用MVCC机制来解决不可重复读问题，这是实现可重复读隔离级别的关键。
InnoDB是一个多版本的存储引擎。它保留有关已更改行的旧版本的信息（Undo Logs），以支持并发和回滚等事务功能。
InnodB内部为每一行隐藏增加了3个fields

6-byte DB_TRX_ID 表示最后插入或更新的事务ID
7-byte DB_ROLL_PTR 指向undo log方便回滚
6-byte DB_ROW_ID 单调递增的行id，如果InnoDB自动生成了隐藏的聚簇索引，会包含ID列的值

Undo logs分为insert和update两种。
Insert undo logs只在事务回滚时才需要，并且在事务提交后可以被立即丢弃
Update undo logs同时也在一致性读中被使用，但只有在不存在已为其分配快照的事务存在后才能丢弃它们 InnoDB，在一致读取中可能需要更新撤消日志中的信息来构建数据库的早期版本排

InnoDB多版本并发控制 (MVCC) 对二级索引的处理方式与对聚集索引的处理方式不同。聚集索引中的记录在原地更新，它们的隐藏系统列指向Undo logs，可以从中重建早期版本的记录。与聚集索引记录不同，二级索引记录不包含隐藏的系统列，也不会就地更新。
当二级索引记录标记为删除或被新的事务更新时，InnoDB会扫描聚簇索引里面的记录，如果记录的DB_TRX_ID会被检查，使其可以从Undo Log中获取争取的版本。

MySQL的写操作分为

修改数据区
Redo日志

如果事务A要写一个数据，需要找到对应的数据页，load到内存中，成为cache页，然后修改cache中的数据区，再记一条日志，等待日志落盘后，就返回给客户端，而非数据落盘，而此时事务可能还未提交。
当其他事务B中有请求要查询该内存页时，而A还未提交，此时显然不能直接读脏数据（cache中的），那么该如何处理呢？
此时事务A会开辟一块Undo的数据区，作用是将数据回放到上一个事务的完结状态，事务B的查询操作就会访问到Undo数据区，

简单流程

事务1：将一行数据a=1 改成 a=2，那么它会对这行数据先加排他锁，再将这行数据上一个已提交的事务verison数据copy到undo数据区
事务2：拿到一个全局的已提交的version去读这一行，发现被事务1修改的那一行version比查询的那一行大，则去Undo区里面去查

不可重复读问题

一个事务中，同样的查询，两次结果不相同，就叫不可重复读。
主要说的如某个事务执行过程中，前一次查询和后一次查询，数据不一样,比如某一列的值被修改了，这种情况通常为两次查询过程中，另一个事务操作了这一行数据。

解决不可重复读

通过Mvcc版本比较，解决不可重复读的问题，事务会访问到另一个事务开辟Undo区域，保证后一次读和前一次读的结果相同；
再加上写锁的排他特性，保证同一时刻，只会有一个事务可以操作某一行数据
当然，RR级别才有可重复读能力，RC级别是会不可重复读的

幻读

幻读主要指的是两此select(count)之类的查询结果不一样，出现了幽灵行数据。
事务A第一次执行查询后，事务B通过insert插入了一行数据，事务A再次执行查询时，发现多了一行数据。
当然，RR解绑才有解决幻读的能力，RC级别是会幻读的，也就是说RC级别一般情况不会产生间隙锁，这对性能是有一定提升的。

不可重复读和幻读的区别
很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert，阻塞间隙间的插入就自动解决了幽灵行的问题。

解决幻读

解决幻读：通过行锁 + 间隙gap锁,这里说的行锁是通过mvcc实现的乐观锁，比较cache页中数据行的version和当前事务的version，如果当前version比较小，则去undo区域，在普通select读这种快照读情况下，实际上是不会有锁的，而在dml中，涉及到数据变更，会锁住被修改的行，同时，加gap锁，防止其他事务插入数据导致的幻读

唯一索引的唯一搜索（非范围查询，如t_key = 1）不用加gap锁，因为其他事务在尝试获取index-record lock时会失败

设table_a 有非唯一索引t_key,上一个已提交最新版本的全局version为10000

	CREATE TABLE `table_a` (
	  `id` bigint(11) NOT NULL AUTO_INCREMENT,
	  `t_key` int(11) NOT NULL,
	  PRIMARY KEY (`id`),
	  KEY `idx_t_key` (`t_key`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8

事务1：
事务id：10001

	select * from table_a where t_key = '1';   //load page into cache/memory
	update table_a set t_key = '2' where  t_key = '1'; //对t_key = 1 的数据行加锁，为了防止其他事务insert t_key(1),加间隙锁，并将t_key='1'的上一个版本数据行copy到undo区域，undo区域中t_key还是1,cache页中数据行版本变为10001，此时事务还没有提交，因此cache页是一个脏数据页

（事务2此时介入，查询sql select * from table_a where t_key = '1';）
....

end

事务2：
事务id：10002

	select * from table_a where t_key = '1'; //查询cache页，发现上一个最新已提交version为10000，小于10001，去undo区域获取数据行
	insert(t_key) table_a values(1) //事务1对table_a加了间隙锁，如索引节点 [-无穷,1]和[1,2]中间被锁住（假设有多条记录，t_key分别为1,2,3），不包括2，获取锁时都需要等待，如果table_a的t_key字段没有索引，在innodb会锁定整个表，锁住整个表的方式，通过实验，确定为锁住primary索引，包括不存在的记录

（等待事务1 end）
（没有间隙锁释放，可以插入）

间隙锁的定位方式为最近锁定行的左右区间，避免锁定没有必要的行，假如有索引数据(1，1)(3，4)，(5,5)(8,5)(9,7)，(主键,t_key)
update where t_key = 4时，会锁住(1)_{(4)之间，(4)}(5)之间，而不是锁住(1) ~ (5),所以才叫间隙锁，通过分析，间隙锁应该是发生了B+树的叶子节点上，并且已经对应到行记录上了
事务1锁定了where t_key = 4 时，间隙锁t_key(1，1)_{(3，4)之间，(3，4)}(5,5)之间,此时还需要关注主键的位置
则insert(2,3)时，它在(1，1)(3，4)之间，会阻塞
insert(4,5)时，它在(3，4)~(5,5)之间,会阻塞
insert(6,5)时，成功，因为它没在(1，1)_{(3，4)之间，(3，4)}(5,5)之间，同样t_key都是5，一个可以成功一个不可以

如果一个事务通过update 主键id锁定了行(3,4),则另一个事务此时也无法通过t_key索引更新(3,4)，因为都对应到了主键id为3的数据行

假如table_a还有一个name字段
则

select * from table_a where t_key=5 and name =‘5’ for update,将会锁住t_key的索引;
select * from table_a where name ='5' for update,会锁住primary索引

事务1：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

	mysql> select * from table_a where name='5';
	Empty set (0.00 sec)

	mysql> select * from table_a where name='6';
	+----+-------+------+
	| id | t_key | name |
	+----+-------+------+
	|  5 |     5 | 6    |
	|  8 |     5 | 6    |
	+----+-------+------+
	2 rows in set (0.00 sec)

	mysql> update table_a set name='5' where t_key=5;
	Query OK, 2 rows affected (0.00 sec)
	Rows matched: 2  Changed: 2  Warnings: 0

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

事务1 update操作执行，但没有commit时，执行事务2

事务2：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

	mysql> select * from table_a where t_key =5;
	+----+-------+------+
	| id | t_key | name |
	+----+-------+------+
	|  5 |     5 | 6    |
	|  8 |     5 | 6    |
	+----+-------+------+
	2 rows in set (0.00 sec)

	mysql> update table_a set name='8' where t_key=5 and name='6';
	Query OK, 0 rows affected (4.64 sec)
	Rows matched: 0  Changed: 0  Warnings: 0

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

	最终name的值为
	mysql> select * from table_a where t_key =5;
	+----+-------+------+
	| id | t_key | name |
	+----+-------+------+
	|  5 |     5 | 5    |
	|  8 |     5 | 5    |
	+----+-------+------+
	2 rows in set (0.00 sec)

参考链接

https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
https://www.zhihu.com/question/266011062/answer/310929189
https://juejin.cn/post/7002180864008257543 alter table 到底锁不锁表
https://cloud.tencent.com/developer/article/1730923

posted on 2018-07-11 10:33 j.liu windliu 阅读(1750) 评论(0) 编辑收藏举报