[大数据]mysql中锁的理解与LBCC详解

一.mysql中锁的介绍

本文默认是基于mysql的innoDB存储引擎描述，并且版本是mysql5.7 不同的版本可能存在一些差异

1. 按照模式划分

共享锁（S Lock）：可以分为共享锁和意向共享锁（IS）

排它锁（X Lock）：分为排它锁和意向排它锁（IX）

共享锁和排他锁与我们平时所了解读写锁是非常类似的

锁的兼容性如下表：

2. 验证及使用

我们来验证上述的表格的内容

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `number` INT(11) NOT NULL,
  `name` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  `age` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_number` (`number`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

（1） 共享锁与共享锁兼容

执行结果：

（2）共享锁与独占锁不兼容

执行结果：

其他的就不做验证了，因为这些都不是本文的重点内容

3. 意向锁的意义

mysql既然已经分为了共享锁和排他锁，为什么还要加入意向锁呢？

我们知道mysql即存在航锁，也存在表锁，那么假设存在如下情况

当mysql一个事务已经为一条记录加了行锁，现在又一个事务需要加表锁，显然第二次加表锁需要阻塞的。不然第二个事务和第一个事务都能够操作数据了。

那么mysql在增加表锁的时候，需要每一行记录都去判断有没有加行锁吗？这样效率太低了，msql可不会这么干的，所以就引入了意向锁。

那么mysql如何使用意向锁呢？

比如上面的实例，mysql在给一行记录添加行排它锁时，首先给这个表增加一个表排它意向锁，添加成功后才能添加行锁

然后第二个事务想添加表排它锁时首先只需要判断有没有意向锁，如果有就阻塞，这样只需要判断一次即可，大大提升性能

意向锁就不重点介绍了，也不是本文的重点。

二. LBCC

LBCC（Lock-Base Concurrency Control）基于锁的并发控制，这是本文的重点

在前面的文章中我们学习过MVCC，我们知道MVCC基于多版本控制可以提升mysql的并发读写能力，但是不能完全解决幻读，以及并发写的问题（出现更新丢失）；那么今天我们来看看LBCC是如何处理并发写，以及处理幻读。

1. 锁定读与快照读

锁定读：貌似又有人称呼它为当前读

select * from user for update;#排它锁

select * from user lock for share lock;#共享锁

快照读：貌似又有人称呼它为一致性读

select *from user;

2. 锁的范围

表锁：锁住整张表

行锁：record lock，只锁住一行记录

间隙锁：锁定一个范围，但是不包括记录本省

Next-Key Lock：行锁 + 间隙锁 左开右闭

Previous-Key Lock：行锁 + 间隙锁 左闭又开

mysql在REPEATABLE READ隔离级别下默认是使用Next-Key Lock，但是会存在锁升级或者降级的情况

（1）如果没有索引，或者索引失效，则升级为表锁

（2）主键索引和唯一索引直接降级成行锁

3. 验证

下面我们来做实验验证一下，上述的结论

建表语句如下：

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `number` INT(11) NOT NULL,
  `name` VARCHAR(255) COLLATE utf8_bin NOT NULL,
  `age` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_number` (`number`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

#向表中插入如下记录
INSERT INTO USER(id,number,`name`,age) VALUES (1,1,'1',1);

INSERT INTO USER(id,number,`name`,age) VALUES (11,11,'11',11);

INSERT INTO USER(id,number,`name`,age) VALUES (21,21,'21',21);

INSERT INTO USER(id,number,`name`,age) VALUES (31,31,'31',31);

INSERT INTO USER(id,number,`name`,age) VALUES (41,41,'41',41);

其中id是主键，number是唯一索引，name是普通索引，age没有索引

（1）锁定主键记录

操作步骤

执行结果

解析：

1.#（1）不能够更新id=11的数据，因为被加了行锁

2.#（2）可以插入，说明没有gap锁，下面解析gap范围

按照主键记录形成的区间如下：

（-∞,1] (1,11] (11,21] (21,31] (31,41] (41,+∞]

如果是使用的间隙锁，那么当我锁定主键11时，应该锁定的区间(1,11] (11,21]，如果是这样，那么我们是不能够插入id=5的数据的，但是事实上是插入成功的，所以证明此时不是使用的Next-Key Lock而是使用的行锁

（2）锁定唯一索引

锁定唯一索引我已经测试过了和上述的结果一样，大家可以自行测试

（3）表锁

上面的表中age是没有索引的，那么此时应该锁定一条记录时，会锁住整个表

操作步骤

执行结果：

解析：

我们从上面的图上可以看出，当我们锁定了age=11的记录时，此时右边的部分多个范围都不能更新，也不能插入，说明发生了表锁，为什么会发生表锁呢？

我是这么理解的：因为age上面没有索引约束，那么其实任何一条记录，都可能修改成age=11，所以mysql想用行锁或者想用gap锁，根本就锁不了。假如其他的行能够修改（或者新增）成age=11，那么与左边第一次查询的结果就有冲突了（左边查询的是一条，如果其他行也修改成age=11或者新增一条age=11的记录），这就有问题了，所以mysql为了保证不出现这样的问题，就锁住整个表了。

（4）普通索引

普通索引就会使用Next-Key Lock，我们来测试一下，上面的表中name是普通索引

操作步骤

执行结果：

解析：

上面的执行结果图上很明显的显示当锁住name='21’时，是无法更新name="21"这条记录，同时也不能插入name='15’和name='25’的记录，这就说明了此时使用了是 行锁 + Gap锁（间隙锁），其实此处不仅仅不能插入name为11-31之间的数据，同时name='155’和’255’也不能插入，这是因为name是varchar类型的，‘155’和’255’也在’11’he '31’这个范围。验证图如下：

本文就先到这里了，其实还有一些内容没有写完，就是区分Previous-Key Lock和Next-Key Lock.，这个表设计的不太方便测试这个，下次有时间再测试吧，大家也可以自行测试。先睡觉了熬夜干文章太累了。

最后再补充一个技巧吧，因为我在测试的时候，secureCrt断开与服务器链接，导致我的会话事务没有关闭，所以锁没有释放，可以通过下面的语句查询thread_id然后kill thread_id即可

select t.trx_mysql_thread_id from information_schema.innodb_trx t;
kill xxx;