[大厂面试05期]说一说你对MySQL中锁的理解？

最新推荐文章于 2021-10-10 12:25:03 发布

Not_Found9

最新推荐文章于 2021-10-10 12:25:03 发布

阅读量31

点赞数

文章标签：面试 mysql 职场和发展数据库

本文链接：https://blog.csdn.net/ruiwen125/article/details/106953813

版权

PS:本文已收录到1.3 K+ Star 数的开源项目《大厂面试指北》，如果想要了解更多，可以看一看，项目地址：https://github.com/NotFound9/interviewGuide

这是我总结的一个表格，是本文中涉及到的锁（因为篇幅有限就没有包括自增锁）

1.数据库级别的锁

数据库级别的锁有以下两种：

1.1.全局读锁

对数据库执行 Flushtableswithreadlock命令让整个库处于只读状态。

1.2.让全局只读

执行 setglobalreadonly=true这个命令也可以让全库只能读，但是第一有些系统会使用readonly来做一个操作，例如根据readonly是否为true判断数据库是否是从库，第二是如果执行这个命令后，客户端断开连接后，数据库会一直处于只读状态，如果是FTWRL命令发送异常会释放全局锁。（如果是从库，设置read-only对super user权限无效）

使用场景：

最常用的场景是对数据库备份。对数据库加锁，让整个数据库处于只读状态，所有更新操作停止（如果是主库就不能执行更新语句，从库也不能执行同步过来的bin log），然后对整个数据库做逻辑备份（就是将所有数据生成SQL写入备份文件。）

补充资料：

更好的进行数据库备份的一种方法

就是通过官方自带的逻辑备份工具mysqldump来进行逻辑备份时，设置一个参数-single-transaction，这样导数据的时候就会开启一个事务，这样利用innodb的mvcc机制可以保证在事务执行过程中，读到的数据都跟事务开始时的一致，并且执行过程中，其他事务可以执行更新操作，不会对他造成影响（因为它就跟普通SELECT查询一样是读取的快照数据），这种方法必须要求数据库所有表的引擎都是innodb才行。

2.表级别的锁

表级别的锁有两种，一种是表锁，一种是元数据锁MDL。

2.1表锁 lock table

就是使用lock table user_table read/write命令来对表进行加读锁或者写锁。

加读锁（也就是表级别共享锁X锁）后，表对所有线程都是只能读，即便是当前线程也只能读表，不然会数据不一致。
加写锁后，表是对当前线程写，其他线程不能读，不能回数据不一致。

可以通过 unlock tables来解锁，客户端断开时也会自动释放锁，但是影响所有线程，影响面太大了。这种锁我们一般也不会主动去调用，但是我们去更新一些数据时，如果查询条件是根据一些没有索引的字段去查询的，那样更新时会主动申请表锁中的写锁，获取成功后才能修改数据，事务提交成功之后，才会释放锁。（这也是为什么我们一般强调对于常用的查询字段加索引，就是为了提高更新和读取效率。）

2.2元数据锁MDL(MetaData Lock)

分为读锁和写锁，加读锁时，所有的线程都可以读表，加写锁时，只能一个线程写，其他的不能读。锁不用显式使用，是访问一个表时，自动加上的。对表进行增删改查时，会加读锁。对表结构做修改时，会加写锁。

目的是为了在增删改查时不能修改表结构，修改表结构时不能去增删改查。

2.3 意向锁

意向锁的作用主要是表明当前表是否存在数据行加了行锁。这样事务可以根据当前表是否有意向锁来快速判断当前表是否存在数据行加了行锁，这样再加表级别的排斥锁X，共享锁S时，避免了去查询每一行数据，判断是否加了行锁，减小了性能开销。

意向共享锁（IS锁）

事务让一行数据只能读，需要申请对这行数据加行级别的共享锁S锁，在申请行级别的S锁之前会主动申请表级别的共享意向锁IS锁。

意向排斥锁(IX锁)

事务在更新某一行数据时，需要申请对这行数据加行级别的排斥锁X锁，在申请行级别的X锁之前会申请表级别的意向锁IX锁。

意向锁之间是兼容的，IS锁和IX是兼容，因为可能我们对第一行数据加S锁，那么会申请IS锁，对第二行数据加X锁，此时跟第一行的数据的S锁不冲突，所以也会先申请IX锁，由此可见，IS锁和IX之间不冲突，IS锁，IX锁与行级别的S，行级别的X之间也不冲突。

意向锁只是跟表级别的S，X锁可能会冲突。

	表级别的S锁	表级别的X锁
意向共享锁IS	兼容	不兼容
意向排斥锁IX	不兼容	不兼容

行级别的锁

行锁是innodb引擎特有的锁，也是分为共享锁（也就是通常说的读锁）和互斥锁（也就是通常说的写锁）

共享锁 S锁，就是读锁，允许事务读一行数据，不能被修改。所以读锁之间不排斥
互斥锁 X锁，就是写锁，就是让当前事务可以修改这行数据，其他事务不能修改这行数据

如果是从加锁的范围来区分，行锁主要分为记录锁(锁单个索引)，间隙锁（锁索引之间的间隙），下一键锁（等于记录锁+间隙锁）

记录锁 record lock

记录锁锁定的是单条索引记录。例如 SELECT c1 FROM t WHERE c1=10FOR UPDATE; ，如果c是主键或者是一个唯一性索引的字段，由于在表内唯一，所以只需要对c=10这个索引进行加锁，可以防止其他事务插入，更新或删除这个数据行。

间隙锁 gap lock

间隙锁就会对记录之间的间隙加锁，防止数据插入。

下一键锁 next-key lock

next-key lock是 record lock 和 gap lock的组合，就是会对索引记录加记录锁 + 索引记录前面间隙上的锁”，就是对要更新的数据的左右两个端点加间隙锁。

具体案例：

因为innodb默认的隔离级别是可重复读，我们在执行更新语句和使用当前读语句（SELECT…FOR UPDATE）时，都是需要加一些行锁的，来防止其他事务插入或者删除数据，导致在事务内多次读取到的数据行不同。针对行锁的加锁规则，极客时间中丁奇老师总结了以下四条规则：

原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为记录锁。
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

简单的来说，我认为就是next-key lock就是加锁的基本单位，只不过innodb做了很多优化，在不需要对那么大范围的数据行加锁时，会进行降级，降级为间隙锁，或者是记录锁。

下面就来看一个具体的例子

例如:

a是一个普通字段，对它建了索引，在数据库中对应a这个字段存在的值已经有1，5，10，20，30

那么根据next-key lock来划分区间，next-key lock是根据已有数据行来划分区间，并且是左开右闭区间，所以可以锁定的区间是

(负无穷,1]
(1,5]
(5,10]
(10,20]
(20,30]
(30,正无穷)

//更新操作
update table set b = '1' where a = 10;

在innodb中执行更新操作，

如果a是唯一性索引，根据原则3那么只需要对a为10的这条索引加记录锁就行了，因为不用担心其他事务再插入一条a为10的数据，因为插入时会有唯一性判断。
但是如果a是非唯一性索引，如果只是对a=10这个索引加锁，可能会有其他事务插入a=10的数据行，所以会对（5，10]和（10，20]这两个区间加锁，并且根据上面的原则4，会将（10，20]降级为间隙锁，也就是只对（10，20）加锁，因为a=20这个索引是否加锁都不影响当前的事务。
如果a没有索引，需要插入时会先申请表级别的互斥锁X锁，然后进行插入。

建了一个技术群，欢迎大家进群交流，一起学习进步！进群还领取《大厂面试指北》PDF版（如果二维码失效了，大家可以去"大厂面试"公众号菜单栏获取进群二维码）