【MYSQL篇】mysql中相关锁和MVCC详解-Toy模板网

这篇具有很好参考价值的文章主要介绍了【MYSQL篇】mysql中相关锁和MVCC详解。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

前言

数据库的锁是在多线程高并发的情况下用来保证数据稳定性和一致性的一种机制。MySQL 根据底层存储引擎的不同，锁的支持粒度和实现机制也不同。MyISAM 只支持表锁，InnoDB 支持行锁和表锁。目前 MySQL 默认的存储引擎是 InnoDB，这里主要介绍 InnoDB 的锁。上一篇文章，我们对 MySQL 的事务进行了详细的阐述，也顺带提到了 MVCC，在介绍锁之前，我们先来聊聊 MVCC 。

MVCC

要让一个事务前后两次读取的数据保持一致，那么我们可以在修改数据的时候给它建立一个备份或者叫快照，后面再来读取这个快照就行了。

问题：这个快照什么时候创建？读取数据的时候，怎么保证能读取到这个快照而不是最新的数据？这个怎么实现呢？

MVCC 的核心思想是：我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

InnoDB 为每行记录都实现了两个隐藏字段：

DB_TRX_ID：插入或更新行的最后一个事务的事务 ID，事务编号是自动递增的（我们把它理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事务 ID）。

DB_ROLL_PTR：回滚指针（我们把它理解为删除版本号，数据被删除或记录为旧数据的时候，记录当前事务 ID）。

接下来我们就来详细说说通过这两个版本号的控制，保证一个事务两次读取的数据是一致的。

1、第一个事务

初始化数据。

Transaction 1
begin; 
insert into user values(NULL,'mayun') ; 
insert into user values(NULL,'tory') ; 
commit;

此时的数据，创建版本是当前事务 ID，删除版本为空：

id	name	创建版本	删除版本
1	mayun	1	undefined
2	tory	1	undefined

2、第二个事务

执行第 1 次查询，读取到两条原始数据，这个时候事务 ID 是 2：

Transaction 2 
begin; 
select * from user; -- (1) 第一次查询

3、第三个事务

插入数据：

Transaction 3 
begin; 
insert into user values(NULL,'tom') ; 
commit;

此时的数据，多了一条 tom，它的创建版本号是当前事务编号：3。

id	name	创建版本	删除版本
1	mayun	1	undefined
2	tory	1	undefined
3	tom	3	undefined

第二个事务，执行第 2 次查询：

Transaction 2 
select * from user; --(2)第二次查询

MVCC 的查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。

也就是不能查到在我的事务开始之后插入的数据，tom 的创建 ID 大于 2，所以还是只能查到两条数据。

4、第四个事务

删除数据，删除了 id=2 ，name = tory这条记录。

Transaction 4 
begin; 
delete from user where id=2;
commit;

此时的数据，tory 的删除版本被记录为当前事务 ID，4，其他数据不变。

id	name	创建版本	删除版本
1	mayun	1	undefined
2	tory	1	4
3	tom	3	undefined

在第二个事务中，执行第 3 次查询：

Transaction 2 
select * from user; --(3)第三次查询

查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。

也就是，在我事务开始之后删除的数据，所以 tory依然可以查出来。所以还是这两条数据。

5、第五个事务

执行更新操作，这个事务事务 ID 是 5。

Transaction 5 
begin; 
update user set name ='xiaoming' where id=1; 
commit;

此时的数据，更新数据的时候，旧数据的删除版本被记录为当前事务 ID 5（undo），

产生了一条新数据，创建 ID 为当前事务 ID 5。

id	name	创建版本	删除版本
1	mayun	1	5
2	tory	1	4
3	tom	3	undefined
1	xiaoming	5	undefined

第二个事务，执行第 4 次查询：

Transaction 2 
select * from user; --(4) 第四次查询

查找规则：只能查找创建时间小于等于当前事务 ID 的数据，和删除时间大于当前事务 ID 的行（或未删除）。

因为更新后的数据 xiaoming创建版本大于 2，代表是在事务之后增加的，查不出来。

而旧数据 mayun的删除版本大于 2，代表是在事务之后删除的，可以查出来。

通过以上演示我们能看到，通过版本号的控制，无论其他事务是插入、修改、删除，第二个事务查询到的数据都没有变化。

InnoDB 常见的几种锁机制

共享锁

Shared Locks （共享锁）：我们获取了一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁。而且多个事务可以共享一把读锁。

那怎么给一行数据加上读锁呢？

我们可以用 select …… lock in share mode; 的方式手工加上一把读锁。

释放锁有两种方式，只要事务结束，锁就会自动事务，包括提交事务和结束事务。

验证一下，看看共享锁是不是可以重复获取。

Transaction 1
begin;
select * from user where id = 1 lock in share mode;

Transaction 2
begin;
select * from user where id = 1 lock in share mode;  --ok

排它锁

Exclusive Locks（排它锁）：它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

排它锁的加锁方式有两种，第一种是自动加排他锁，可能是同学们没有注意到的：我们在操作数据的时候，包括增删改，都会默认加上一个排它锁。

还有一种是手工加锁，我们用一个 FOR UPDATE 给一行数据加上一个排它锁，这个无论是在我们的代码里面还是操作数据的工具里面，都比较常用。

释放锁的方式跟前面是一样的。

排他锁的验证：

Transaction 1
begin;
update user set name = 'xiaoming' where id=1;

Transaction 2
begin;
select * from user where id = 1 lock in share mode; -- BLOCKED
select * from user where id=1 FOR UPDATE; -- BLOCKED
delete  from user where id=1 ; -- BLOCKED

意向锁

当我们给一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，数据库会自动在这张表上面加一个意向排他锁。

反过来说：如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。如果一张表上面至少有一个意向排他锁，说明有其他的事务给其中的某些数据行加上了排他锁。

意向锁不会锁住任何东西，除非有进行全表请求的操作，否则不会锁住任何数据。存在的意义只是用来表示有事务正在锁某一行的数据，或者将要锁某一行的数据。

记录锁

记录锁（record Locks）：锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引加锁。所以我们在进行查询的时候尽量采用索引进行查询，这样可以降低锁的冲突。

当我们对于唯一性的索引（包括唯一索引和主键索引）使用等值查询，精准匹配到一条记录的时候，这个时候使用的就是记录锁。

比如 where id = 1 4 7 10 。

我们使用不同的 key 去加锁，不会冲突，它只锁住这个 record。

间隙锁

间隙锁（Gap Locks）：间隙锁是一种记录行与记录行之间存在空隙或在第一行记录之前或最后一行记录之后产生的锁。间隙锁可能占据的单行，多行或者是空记录。

通常的情况是我们采用范围查找的时候，比如在学生成绩管理系统中，如果此时有学生成绩 60，72，80，95，一个老师要查下成绩大于 72 的所有同学的信息，采用的语句是 select * from student where grade > 72 for update，这个时候 InnoDB 锁住的不仅是 80，95，而是所有在 72-80，80-95，以及 95 以上的所有记录。为什么会这样呢？

实际上是因为如果不锁住这些行，那么如果另一个事务在此时插入了一条分数大于 72 的记录，那会导致第一次的事务两次查询的结果不一样，出现了幻读。所以为了在满足事务隔离级别的情况下需要锁住所有满足条件的行。

临键锁

Next-Key Locks（临键锁）：NK 是一种记录锁和间隙锁的组合锁。既锁住行也锁住间隙。并且采用的左开右闭的原则。InnoDB 对于查询都是采用这种锁的。

举个例子说明：

现在有一个表a，如下：

【MYSQL篇】mysql中相关锁和MVCC详解

我们执行下面的操作：

# T1
START TRANSACTION WITH CONSISTENT SNAPSHOT; //1

SELECT * FROM a WHERE uid = 6 for UPDATE; //2

COMMIT;  //5


# T2
START TRANSACTION WITH CONSISTENT SNAPSHOT;  //3

INSERT INto a(uid) VALUES(11);
INSERT INto a(uid) VALUES(5);  //4
INSERT INto a(uid) VALUES(7);
INSERT INto a(uid) VALUES(8);
INSERT INto a(uid) VALUES(9);

SELECT * FROM a WHERE uid = 6 for UPDATE;

COMMIT;

ROLLBACK;