MySQL锁的浅析到深入

站长

2023年09月16日 23:22 · 阅读数 6

MySQL锁是什么？锁还有什么类别？

MySQL锁是指同一时间同一资源只能够被一个线程访问。在数据库里，除了传统的计算资源（像CPU、RAM、I/O等）的应用之外，数据也是一种供许多用户共享的资源。那如何来保证数据访问的一致性与有效性是所有的数据库都必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

MySQL锁的浅析到深入

锁的分类

从对数据操作的类型进行区分（读写）

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响。

写锁（排它锁）：当前写的操作没有完成前，它会阻断掉其他的写锁与读锁。

从对数据操作的粒度区分

表级锁：表级锁是MySQL里锁定粒度最大的一种锁，表示会对当前的操作的整一张表来进行加锁。

行级锁：行级锁是MySQL里锁定粒度最细的一种锁，表示是只对当前的操作的行来进行加锁。

页级锁：页级锁是MySQL里锁定粒度在行级锁与表级锁的中间，一次锁定相邻的一组记录。

从并发的角度来区分（乐观锁与悲观锁其实就是一种思想）

悲观锁：对数据被外界（包括了本系统当前的其他事务与来自外部系统的事务处理）修改持保守态度（悲观），所以，在整个数据的处理过程中，将数据处于锁定的状态。可以把悲观锁可以理解为假设是最坏的情况，每次的话去拿数据的时候都会被修改，所以每次拿数据都会上锁。

乐观锁：乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式的对数据冲突与否进行检测，如果发现了冲突了，就返回错误信息再进行业务重试。可以把乐观锁理解为假设是最好的情况，每次拿数据的时候都不会被别人修改，所以不会上锁。

间隙锁与意向锁

间隙锁：在条件查询中，比如：where id>100，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙”，“间隙”主要是为了防止幻读。

意向锁：意向锁分为intention shared lock（IS）和intention exclusive lock（IX），意向锁的目的就是表明有事务正在或者是将要锁住某个表里的行。

行锁与表锁的比较

表级锁是MySQL里锁定粒度最大的一种锁，表示对当前操作的整张表来进行加锁，它实现简单。最常用的MYISAM与INNODB都支持表级的锁定。特点是开销小，加锁快。不会出现死锁；锁定粒度大，发生锁的冲突概率是很高的，并发度较低。

行级锁是MySQL中锁定粒度最细的一种锁，表示的话只对当前操作行进行加锁。行级锁能够大大的减少数据库操作的冲突。并且加锁粒度最小，但是加锁的开销也是最大的。特点是开销大，加锁慢，甚至会出现死锁；锁定粒度最小，发生锁的概率也是最低的，并发度也最高。使用方式是InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件来检索数据，InnoDB才使用行级锁，否则的话使用的是InnoDB将使用表锁。

InnoDB死锁

当两个或者是两个以上的事务相互持有和请求锁，并形成一个循环的依赖关系，就会产生死锁。多个事务同时锁定同一个资源的时候，也会产生死锁。在一个事务系统中，死锁是真实存在并且是不能完全避免的。那如何解决呢？InnoDB会自动检测事务死锁，立即回滚其中的某一个事务，并且会返回一个错误。它根据某一种机制来选择那个最简单的（也就是代价最小）的事务来进行回滚。

如何避免死锁

MySQL锁的浅析到深入

锁的本质是资源的相互竞争，相互等待，往往是两个或者是两个以上的Session加锁的顺序不一致导致的。

那应该怎么样有效的避免呢？

1、在程序中，操作多装表的时候，尽量以相同的顺序来访问（避免形成等待环路）

2、在批量操作单张表数据的时候，先对数据进行排序（避免形成等待环路）A线程id：1,10,20按顺序加锁，B线程id：20,10,1

3、如果可以，可以把大事务化成小事务，甚至是不开启事务select for update==>insert==>update = insert into update on duplicate key。

4、尽量使用索引访问数据，避免没有where条件的操作，避免间隙锁对并发产生影响。1,10,20等值于where id in(1，10,20)范围查询id>1and id<20

5、避免在同一个时间点运行多个对同一表进行读写的脚本，要特别注意加锁操作数据流比较大的语句；我们经常会有一些定时脚本，避免它们在同一时间点运行。

间隙锁

间隙锁定义：间隙锁是一个在索引记录之间的间隙上的锁

间隙锁的作用：RR隔离级别保证对读取到的记录加锁（记录锁），同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入（间隙锁），不存在幻读现象。

MySQL锁的浅析到深入

加锁规则：

1、加锁的基本单位是next-key lock，next-key lock是前开后闭区间

2、查找的过程中访问到的对象才会加到锁

3、索引上的等值查询时，给唯一索引加锁的时候，next-key lock退化为行为锁

4、索引上的等值查询时，向右遍历的时候且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁

等值查询间隙锁

建表test

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(6,6,6),
(10,10,10),(15,15,15),(20,20,20),(30,30,30);

SessionA	SessionB	SessionC
begin;update test set b=b+1 where id=7;
	insert into test values(8,8,8);(blocked)
		update test set b=b+1 where id=10;(query OK)

由于表test中没有id=7的记录的话，根据上述的规则1，加锁单位是next-key lock，sessionA加锁的范围就是（5,10]。根据规则规则3，这是一个等值查询，而且的话id=10不满足查询条件，next-key lock退化成为了间隙锁，最终加锁范围就是（5,10），所以SessionB想要在间隙里插入id=8的记录就会被锁住，但是SessionC是可行的。

非唯一索引等值锁

SessionA	SessionB	SessionC
begin；select id from test where a=6 lock in share mode；
	update test set b=b+1 where id=6;(query OK)
		insert into values(7,7,7)；（blocked）

根据规则1，加锁单位next-key lock，因此会给(0,6]加上next-key lock。因为a是普通索引，所以的话访问a=5这一条记录是不能够马上停下来的，需要向右进行遍历，查到a=10才放弃。根据规则2，访问到的都要加上锁，因此要给(5,10]加next-key lock。根据规则4，等值判断向右进行遍历，最后一个值不满足a=6这一个等值条件，所以的话退化成为间隙锁（5,10）。根据规则2，只有访问到的对象才会加上锁，但这个查询使用的覆盖索引，并不用访问到主键索引，所以在主键索引上没有任何的锁，这就是为什么SessionB能够成功。锁是加在索引上的，所以在这个例子里，lock in share mode只锁覆盖索引，如果是for update系统就会认为你接下来要更新数据，这样就会顺便给主键索引上满足条件的行加上了行锁，这样的话SessionB就会被阻塞住。如果要用lock in share mode来给行加读锁避免数据被更新，就得绕过覆盖索引的优化，在查询字段里加入索引不存在的字段。

高级面试题之MySQL千万级数据如何做分页？

在后端开发中为了防止一次性加载太多数据导致内存、磁盘IO都开销过大，经常需要分页展示，这个时间就需要用MySQL的LIMIT关键字。但你以为LIMIT分页就万事大吉了么，LIMIT在数据量大的时候极可能造成深度分页问题。

MySQL锁的浅析到深入

通过explain分析深度分页查询问题 explain select * from user where age>10 and age<90000000 order by age desc limit 8000000,10000;

执行计划Extra列可能出现的值以及含义

1、Using where：表示优化器需要通过索引回表查询数据

2、Using index：即覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表，通常是通过将查询字段建立联合索引的实现

3、Using index condition：在5.6版本后加入的新特性，就是索引下推，是MySQL关于减少回表次数的重大优化。

4、Using filesort：文件排序，这个一般是在Order by的时候，数据量过大，MySQL会将所有数据召回内存中排序，比较消耗资源。

解决方案

通过主键索引优化，在查询条件中带上主键索引explain select * from user where id>{maxId} age>10 and age<90000000 order by age desc limit 8000000,10000;。

通过Elastic Search搜索引擎优化（倒排索引）实际上类似于淘宝这样的电商基本上都是把所有商品放进ES搜索引擎里的（那么海量的数据，放进MySQL是不可能的，放进Redis也不现实）。但即使用了ES搜索引擎，也还是有可能发生深度分页的问题的，这时怎么办呢？答案是通过游标scroll

🤞作者小三是刚刚毕业不久全栈工程师，写的技术文章基本上是学习过程中笔记整理而来，大家看了之后如果喜欢可以给小弟点点赞哦。🎁 粉丝福利：学习资料、简历模板统统都有点击领取