[大数据] MySQL第四讲：MySQL的锁机制

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL第四讲：MySQL的锁机制 -> 正文阅读

[大数据]MySQL第四讲：MySQL的锁机制

我们学习了数据库的事务及事务的隔离级别，但是数据库是怎样隔离事务的呢？这时候就牵连到了数据库锁。当插入数据时，就锁定表，这叫做”锁表”；当更新数据时，就锁定行，这叫做”锁行”。当多个用户对数据库进行操作时，会带来数据不一致的情况，所以，锁主要是在多用户情况下保证数据库数据完整性和一致性。本文是MySQL第四讲，分析MySQL的锁机制

文章目录

1、MySQL 的锁机制？重难点 20201006 补充

1、锁的分类

锁模式分类	乐观锁、悲观锁
范围锁	行锁、表锁
算法锁(行锁的算法)	临间锁(Next-Key Locks)、间隙锁(gap lock)、记录锁(record lock)
属性锁	共享锁(Share locks，简称 S 锁)、排他锁(Exclusive locks，简称 X 锁)
状态锁	意向共享锁、意向排他锁

具体分类如下图所示：

1、从对数据库事务操作的粒度来划分；
锁的粒度主要有以下几种类型–》都是悲观锁

1、行锁：索引项加锁，粒度最小，并发性最高
- 更新数据行时发生
2、页锁：一次锁定一页。25个行锁可升级为一个页锁。
3、表锁：粒度大，并发性低
- 插入数据时发生
4、数据库锁：控制整个数据库操作
- 修改数据库字段会触发

2、从对数据操作的类型（读/写）来划分；

1、读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
2、写锁（排它锁）：当前写操作没完成前，它会阻断其他写锁和读锁

总结：MySQL中的锁机制基本上都是采用的悲观锁来实现的。

2、行锁实现

行锁就是锁一行或者多行记录，MySQL 的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引

如上图所示，数据库表中有一个主键索引和一个普通索引，Sql语句基于索引查询，命中两条记录。此时行锁就锁定两条记录，当其他事务访问数据库同一张表时，被锁定的记录不能被访问，其他的记录都可以访问到。
行锁的特征：
- 锁冲突概率低，并发性高，但是会有死锁的情况出现。

窗口A先修改了id为3的用户信息后，还没有提交事务，此时窗口B再更新同一条记录，然后就提示 Lock wait timeout exceeded; try restarting transaction ，由于窗口A迟迟没有提交事务，导致锁一直没有释放，就出现了锁冲突，而窗口B一直在等待锁，所以出现了超过锁定超时的警告了。

3、表锁实现

表锁就是锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。表锁响应的是非索引字段，即全表扫描，全表扫描时锁定整张表，可以通过执行计划看出扫描了多少条记录。

4、在MySQL中，行锁又衍生了其他几种算法锁，分别是记录锁、间隙锁、临键锁；我们依次来看看这三种锁，什么是记录锁呢？

4.1 记录锁
- 上面我们找到行锁是命中索引，一锁锁的是一张表的一条记录或者是多条记录，记录锁是在行锁上衍生的锁，记录锁的特征:
- 记录锁：锁的是表中的某一条记录，记录锁的出现条件必须是精准命中索引并且索引是唯一索引，如主键id。
4.2 间隙锁
- 间隙锁又称之为区间锁，每次锁定都是锁定一个区间，隶属行锁。既然间隙锁隶属行锁，那么，间隙锁的触发条件必然是命中索引的，当我们查询数据用范围查询而不是相等条件查询时，查询条件命中索引，并且没有查询到符合条件的记录，此时就会将查询条件中的范围数据进行锁定(即使是范围库中不存在的数据也会被锁定)，我们通过代码演示一下：
- 首先，我们打开两个窗口，在窗口A中我们根据id做一个范围更改操作，不提交事务，然后在范围B中插入一条记录，该记录的id值位于窗口A中的条件范围内，我们看看运行效果：
- 程序报错：Lock wait timeout exceeded; try restarting transaction 。这就是间隙锁的作用。间隙锁只会出现在可重复读的事务隔离级别中，mysql5.7默认就是可重复读。间隙锁锁的是一个区间范围，查询命中索引但是没有匹配到相关记录时，锁定的是查询的这个区间范围，上述代码中，所锁定的区间就是 (1,3]这个区间,不包含1，但是包含3，并且不包含4，也就是说这里是一个左开右闭的区间。如果将隔离级别改为读已提交，测试间隙锁，会发现间隙锁没有生效。

## 设置事务隔离级别为不可重复读
set session transaction isolation level read committed;
## 查看当前事务级别
SELECT @@tx_isolation

4.3 临键锁
- MySQL 的行锁默认就是使用的临键锁，临键锁是由记录锁和间隙锁共同实现的，上面我们学习间隙锁时，间隙锁的触发条件是命中索引，范围查询没有匹配到相关记录。而临键锁恰好相反，临键锁的触发条件也是查询条件命中索引，不过，临键锁有匹配到数据库记录；
  间隙锁所锁定的区间是一个左开右闭的集合，而临键锁锁定是当前记录的区间和下一个记录的区间。
- 从上图我们可以看到，数据库中只有三条数据1、5、7，当修改范围为1~8时，则锁定的区间为(1,+∞)，锁定额不单是查询范围，并且还锁定了当前范围的下一个范围区间，此时，查询的区间8，在数据库中是一个不存在的记录值，并且，如果此时的查询条件是小于或等于8，也是一样的锁定8到后面的区间。
- 如果查询的结尾是一个存在的值，此时又会怎样呢？现在数据库有三条数据id分别是1、5、7，我们查询条件改为大于1小于7再看看。
- 我们可以看到，由于7在数据库中是已知的记录，所以此时的锁定后，只锁定了(1,7]，7之后的数据都没有被锁定。我们还是可以正常插入id为8的数据及其后面的数据。所以，临键锁锁定区间和查询范围后匹配值很重要，如果后匹配值存在，则只锁定查询区间，否则锁定查询区间和后匹配值与它的下一个值的区间
4.4 为什么会出现这种情况呢？为什么临键锁后匹配会这样呢？
- 在这里，我们不妨看看mysql的索引是怎么实现的，前面文章中有提到树结构，mysql的索引是基于B+树实现的，每个树节点上都有多个元素，即关键字数，当我们的索引树上只有1、5、7时，我们查询1~8，这个时候由于树节点关键字中并没有8，所以就把8到正无穷的区间范围都给锁定了。
- 那么，如果我们数据库中id有1、5、7、10，此时我们再模糊匹配id为1~8的时候，由于关键字中并没有8，所以找比8大的，也就找到了10，根据左开右闭原则，此时10也是被锁定的，但是id为11的记录还是可以正常进行插入的。

5、怎么判断是行锁还是表锁？ 20181016 有赞

6、乐观锁，悲观锁

乐观锁和悲观锁的区别？

不是mysql或数据库中独有的概念，而是并发编程的基本概念
悲观锁（synchronized）：每次拿数据的时候都觉得数据会被人更改，所以拿数据的时候就把这条记录锁掉，这样别人就没法改这条数据了，一直到你的锁释放

关系型数据库，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select…for update时会获取被select中的数据行的行锁，因此其他并发执行的select…for update如果试图选中同一行则会发生排斥(需要等待行锁被释放)，因此达到锁的效果

问题是: select… for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题

乐观锁：MVCC

查询数据的时候总觉得不会有人更改数据，等到更新的时候再判断这个数据有没有被人更改，有人更改了则本次更新失败
实现了乐观锁的方式：记录数据版本或者时间戳

SELECT data AS old_data, version AS old_version FROM …;
// 根据获取的数据进行业务操作，得到new_data和new_version
UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功，操作完成
} else {
    // 乐观锁获取失败，回滚并重试}

底层机制是这样：
- 在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改。
- 如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

悲观锁与乐观锁的应用场景

1、读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能
2、火车余票查询和购票系统：同时查询的人可能很多，虽然具体座位票只能是卖给一个人，但余票可能很多，而且也并不能预测哪个查询者会购票，这个时候就更适合用乐观锁。

数据库衍生的职位：

数据库应用工程师，很多业务开发者就是这种定位，综合利用数据库和其他编程语言等技能，开发业务应用 java相关
数据库工程师，更加侧重于开发数据库、数据库中间件等基础软件 java相关
数据库管理员（DBA），这是一个单独的专业领域
java与数据库交互的技术：JDBC JPA/Hibernate MyBatis、SpringJDBC Template

2、MySQL遇到的死锁问题？面试必备发生在innobd引擎中（高并发时，发生在同一个事务中先delete（获取间隙锁）再insert的情况，把多行数据锁定了，同时获取了数据段的共享锁）

间隙锁的问题：为了解决innodb引擎的幻读问题
什么是间隙锁？
- innodb中行锁的一种，但是这种锁锁住的却不止一行数据，它锁住的是多行，是一个数据范围，主要作用是为了防止出现幻读，但是它会把锁定范围扩大；
- 隶属行锁；
- 查询条件命中索引，并且没有查询到符合条件的记录；
怎么解决？
- 修改代码逻辑，数据存在才删除，尽量不去删除不存在的记录。

Demo：一次生产环境的死锁事故
背景：某日线上产生了多封报警邮件, 邮件内容均如下, 由于生产环境这里简化了表格结构如下

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '名称',
  `age` int(3) NOT NULL COMMENT '年龄',
  `school` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '学校',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name_age`(`name`, age) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Compact;

报警邮件内容如下

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error occurred while setting parameters
### SQL: update student SET school = "清华" WHERE ( name = '小明' )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

代码还原
根据报警邮件中的日志信息, 我们针对代码位置进行定位, 代码逻辑如下, 这里依旧简化了代码
业务1

开启事务

.... 业务普通查询

for ( ... 循环条件 ) {

    .... Student对象构建

    .... 将构建好的对象 Student 进行insert入库

    .... 将构建好的对象 Student 发送mq进行异步处理

    .... 业务剩余条件

}

.... 业务剩余条件

提交事务

针对异步处理的代码逻辑如下
业务2

.... 接受到消息Student

.... 计算需要变更的参数

.... 将计算好的参数 赋值给Student `school`字段

.... 将Student 在表中的数据进行更新(这里产生了死锁)

问题分析

初步猜测, 由于业务1中是在开启事务后循环插入数据, 最后在提交事务的, 那么异步发送出的消息也就是业务2在执行更新的时候，都会由于业务1的事务未提交而一直出于blocked, 可能在这blocked期间产生了死锁, 但是死锁产生的原理还是没有整明白, 且不是100%必现, 我们针对问题进行深层次的分析.

追踪MYSQL innodb 状态
首先我们进去mysql, 执行

show engine innodb status;

获取信息非常多, 提取LATEST DETECTED DEADLOCK信息, 也就是最后一次死锁的信息如下

------------------------
LATEST DETECTED DEADLOCK
------------------------
191028 13:33:14
*** (1) TRANSACTION:
TRANSACTION 2656E7, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 879805, OS thread handle 0x7f8d26308700, query id 3761780 XXXXXXXXXXXXX root Updating
update student SET school = "清华" WHERE ( name = '小明' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1362 n bits 376 index `name_age` of table `数据库1`.`student` trx id 2656E7 lock_mode X waiting
XXXXXX

*** (2) TRANSACTION:
TRANSACTION 2656E2, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 3
MySQL thread id 879796, OS thread handle 0x7f8d261c3700, query id 3761781 XXXXXXXXXXXXX root update
insert into student (XXXXXXXXXXXXXXXXXXXXXXXX)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1362 n bits 376 index `name_age` of table `数据库1`.`student` trx id 2656E2 lock_mode X locks rec but not gap
XXXXXX

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1362 n bits 376 index `name_age` of table `数据库1`.`student` trx id 2656E2 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 292 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
XXXXXX

*** WE ROLL BACK TRANSACTION (1)
------------

问题定位
这里我们事先在数据库中先插入两条现有数据

insert student(name, age, school) values("test1", 10, "value1");
insert student(name, age, school) values("test10", 10, "value2");

模拟线上数据库执行操作
这里分别列举了2种情况, 去描述数据中的执行操作
在这里插入图片描述

为什么明明一样的执行顺序, 在name字段值不一样的情况下结果不一致呢？

核心概念1: 临间锁(Next-key Lock)优化机制, 当查询的索引含有唯一属性时，Next-Key Lock进行优化，将其降级为Record Lock
核心概念2: Next-key Lock加锁顺序分为两步, 第一步加间隙锁, 第二步加行锁
核心概念3: 插入意向锁（Insert Intention Locks）是一种特殊的间隙锁, 在插入时判断是否有和插入意向锁冲突的锁, 如果有, 加插入意向锁, 进入锁等待；如果没有, 直接写数据, 不加任何锁

当建表student时, 默认数据都如下：
在这里插入图片描述
情况1:

事务1: 执行SQL插入第一条 name = test15时，判断是否和插入意向锁{(test10, 10) ~ (+∞)}存在冲突, 没有那么直接插入数据, 获得(test15, 10)这一行的写锁；
事务2: 执行SQL更新，修改name = test15的记录shcool为’XX’，尝试获取Next-key, 此时由于where条件中只有name并不满足唯一索引条件不进行优化, 先尝试获取间隙锁{(test10, 10) ~ (test15, 10)}获取成功, 获取(test15, 10)这行的行锁时发现被事务1占住那么blocked住；
事务1: 执行SQL插入第二条 name = test16时，判断是否和插入意向锁{(test15, 10) ~ (+∞)}存在冲突, 没有那么直接插入数据, 获得(test16, 10)这一行的写锁；
事务3执行SQL更新，修改name = test16的记录，等同与事务2 获得{(test15, 10) ~ ((+∞))}间隙锁, 被(test16, 10)行锁blocked住；
事务1: 提交释放行锁
事务2 3: 获得行锁执行成功

情况2

事务1: 执行SQL插入第一条 name = test15时判断是否和插入意向锁{(test10, 10) ~ (+∞)}存在冲突, 没有那么直接插入数据, 获得(test15, 10)这一行的写锁；
事务2: 执行SQL更新修改name = test15的记录shcool为’XX’, 尝试获取Next-key, 此时由于where条件中只有name并不满足唯一索引条件不进行优化, 先尝试获取间隙锁{(test10, 10) ~ (test15, 10)}获取成功, 获取(test15, 10)这行的行锁时发现被事务1占住那么blocked住；
事务1: 执行SQL插入第二条 name = test14时，判断是否和插入意向锁{(test10, 10) ~ (test15, 10)}存在冲突, 此时发现事务2占有间隙锁, 那么需要加插入意向锁, 此时进入锁等待, Innodb发现事务1与事务2存在死锁关系, 由于事务2权重小直接回滚释放间隙锁, 事务1加插入意向锁成功, 插入数据(test14, 10)；
事务3: 执行SQL更新，修改name = test14记录, 尝试获取Next-key, 此时由于where条件中只有name并不满足唯一索引条件不进行优化, 先尝试获取间隙锁{(test10, 10) ~ (test14, 10)}获取成功, 获取(test14, 10)这行的行锁时发现被事务1占住那么blocked住；
事务1: 提交释放行锁
事务3: 获得行锁执行成功

结论

在使用where条件时, 由于没有使用联合唯一索引, 导致了Next-key Lock没有进行优化降级为Record Lock, Next-key Lock的加锁顺序分为两步, 第一步加间隙锁, 第二步加行锁, 在成功执行完第一步后, bolcked在第二步, 导致了与之后的插入意向锁产生了冲突, 从而造成两个事务相互等待产生了死锁。