小伙伴想精准查找自己想看的MySQL文章？喏 MySQL江湖路 | 专栏目录
信息云数据库负责人dhk表示：“在面试MySQL同事时，我们只考虑两点：索引和锁定。” 简而言之，MySQL锁定的重要性不言而喻。
本文通过同事“侨总”的面试，让你掌握通俗易懂的MySQL各种锁定机制，希望对你有帮助！ 近期将继续整理深入挖掘的锁定机制文章。 感兴趣的老铁，请关注，到时候叫我~
今天的主人公是我们公司的同事华侨总，是传说中拿着10个比特币的男人。 比特币暴涨以来，周末的戏养成了几个小爱好：后备箱、骑马滑雪道。
不，前几天侨民总是又来双叒叺体验面试。 晚上请我烧烤的时候，跟我说了这次有趣的面试经历。 我真的意思不够。 在他回味的时候，我又吃了十几串腰和羊肉。 啊，真香。
对不起，我脱轨了。 到中年，保温杯里放枸杞，总是这样。
来不及说明，快上车！
你好，我是pgny的同事“侨总”。 领队一般不叫我的名字，叫我小侨~
接下来是我的面试经验。 面试官是技术经理和HR。 大家一起吃好吃的吧~
侨总:马…端庄的小笼包好！
面试官你好，华侨，简历上说你熟悉MySQL摇滚，你认为精通是什么水平？
侨总马哥我是一只看不见的白羊。 精通，我想只要比面试官了解更多就完了。
面试官: 为什么有相似的感觉？ 《听我讲完redo log、binlog原理，面试官老脸一红》 )
面试官
侨总:数据库锁设计的初衷是处理并发问题。 作为多用户共享的资源，在发生并发访问时，数据库必须合理控制资源的访问规则，以确保数据完整性。 锁定是实现这些访问规则的重要机制。
简而言之，数据表就像公共厕所。 emmm…换个说法，数据表就像你开的酒店，每行的数据就像酒店的房间。 如果大家随意出入，可能会有多人抢同一个房间。 另外，锁上房间，申请钥匙的人可以入住锁住房间，其他人退房后可以再次使用。 这样可以确保房间的完整性，方便酒店的管理。
MySQL锁定机制的初衷就是这样的，当然，由于MySQL数据库自身体系结构的特点，存在着各种各样的数据存储引擎，每个存储引擎所针对的APP场景的特点都不同每个存储引擎的锁定机制也相差很大，因为为了满足每个特定APP应用场景的需要，每个存储引擎的锁定机制都是针对每个特定场景而优化设计的。
面试官:嗯，那就说说MySQL分成什么样的锁吧。
侨总
按锁定粒度从大到小分类，表锁定、页面锁定和行锁定特殊场景中使用的全局锁定
按锁定级别分类：共享(读取)锁定、排他(写入)、共享(读取)、排他(写入) )；
以及Innodb引擎为解决幻读等并发情况下事务中存在的数据问题而引入的Record Lock、Gap Lock、next-keylock、recordlockgaplock绑定)等
以及面向我们编程的两种锁定思想。 悲观锁定，乐观锁定。
面试官:你觉得幸福的车怎么样？
HR小姐姐。
面试官:小侨，那我们谈谈对表钥匙、钥匙的理解吧。
表锁定侨总:表级锁定是MySQL中每个存储引擎的最大粒度锁定机制。 该锁定机制的最大特点是实现逻辑非常简单，对系统的负面影响最小。 所以获取和解除锁定的速度很快。 表级锁定一次锁定整个表，因此可以很好地避免死锁问题。
当然，锁定粒度大带来的最大负面影响是，发生锁定资源冲突的概率也最高，并发性大幅降低。
使用表级锁定的主要是非事务存储引擎，如MyISAM、MEMORY和CSV。
行锁侨总:与表锁相反，行锁的最大特点是锁定对象的颗粒度小，目前各大数据库管理软件实现的锁定颗粒度最小。 由于锁定粒度小，因此发生锁定资源冲突的概率也最低，可以为APP应用程序提供尽可能大的并发处理能力，从而提高系统的整体性能。
在并发处理能力方面有很大的优势，但行级锁定会带来很多弊端。 由于锁定资源的粒度很小，因此每次获取锁定或解除锁定时都需要很多，消耗也很大。 另外，低电平的锁定也容易发生死锁。
使用行级锁定的主要是InnoDB存储引擎。
适用场景：从锁定的角度来看，表级锁定适用于以查询为中心的索引条件更新数据的APP应用程序
如Web应用；而行级锁则更适合于有大量按索引条件并发更新数据的情况，同时又有并发查询的应用场景。 页锁除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
使用页级锁定的主要是BerkeleyDB存储引擎。
面试官：那全局锁是什么时候用的呢？
全局锁侨总：首先全局锁，是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。
MySQL提供加全局读锁的命令：Flush tables with read lock (FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。
风险：
如果在主库备份，在备份期间不能更新，业务停摆如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟同步还有一种锁全局的方式：set global readonly=true ，相当于将整个库设置成只读状态，但这种修改global配置量级较重，和全局锁不同的是：如果执行Flush tables with read lock 命令后，如果客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。但将库设置为readonly后，客户端发生异常断开，数据库依旧会保持readonly状态，会导致整个库长时间处于不可写状态，试想一下微信只能看，不能打字~~
HR小姐姐：那微信不就完蛋了？
侨总：是啊，抓紧找隐形的白羊背锅！
面试官：不错，你把这几种锁的侧重点都表述清楚了。那你再说一下你对不同级别的那几种锁的使用场景和理解吧？
侨总：MySQL基于锁级别又分为：共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁
共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁侨总：对于共享（读）锁、排他（写）锁，比如咱们住酒店，入住前顾客都是有权看房的，只看不住想白嫖都是可以的，前台小姐姐会把门给你打开。当然，也允许不同的顾客一起看（共享 读），比如和这位杀马特小伙子。
看房时房间相当于公共场所，小姐姐嘱咐不能乱涂乱画，也不能偷喝免费的矿泉水。。如果你觉得不错，偷偷跑到前台要定这间房，交钱后会给你这个房间的钥匙并将房间状态改为已入住，不再允许其他人看房（排他 写）。
对了，当办理入住时前台小姐姐也会通知看房的杀马特小伙子说这间房已经有人定了！！等看房的杀马特qsdhc骂骂咧咧出门后，看到满头大汗的你，鄙夷着咽了一口口水，咳tui！然后你锁上门哼着歌儿，开始干那些见不得人的事儿~~直到你退房前，其他人无法在看你的房。
可见，读锁是可以并发获取的（共享的），而写锁只能给一个事务处理（排他的）。cbdgtx想获取写锁时，需要等待之前的读锁都释放后方可加写锁；而cbdgtx想获取读锁时，只要数据没有被写锁锁住，你都可以获取到读锁，然后去看房。
另外还有意向读写锁，严格来说他们并不是一种锁，而是存放表中所有行锁的信息。就像我们在酒店，当我们预定一个房间时，就对该行（房间）添加 意向写锁，但是同时会在酒店的前台对该行（房间）做一个信息登记（旅客姓名、男女、住多长时间、家里几头牛等）。大家可以把意向锁当成这个酒店前台，它并不是真正意义上的锁（钥匙），它维护表中每行的加锁信息，是共用的。后续的旅客通过酒店前台来看哪个房间是可选的，那么，如果没有意图锁，会出现什么情况呢？假设我要住房间，那么我每次都要到每一个房间看看这个房间有没有住人，显然这样做的效率是很低下的。杀马特qsdhc表示支持！
读写锁、意向锁的兼容性如下所示；
锁类型读锁写锁意向读锁意向写锁读锁兼容冲突兼容冲突写锁冲突冲突冲突冲突意向读锁兼容冲突兼容兼容意向写锁冲突冲突兼容兼容侨总：再回到MySQL原理上讲
1 共享（读）锁（Share Lock）共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。
如果事务A对数据B（1024房）加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。
SQL显示加锁写法:
SELECT … LOCK IN SHARE MODE;在查询语句后面增加LOCK IN SHARE MODE，MySQL就会对查询结果中的每行都加读锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请读锁，否则会被阻塞。其他线程也可以读取使用了读锁的表，而且这些线程读取的是同一个版本的数据。
2 排他（写）锁（Exclusive Lock）排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。
SQL显示加锁写法:
SELECT … FOR UPDATE;在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加写锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请写锁，否则会被阻塞。另外成功申请写锁后，也要先等待该事务前的读锁释放才能操作。
3 意向锁（Intention Lock）意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是 InnoDB 自动加的，不需要用户干预。
再强调一下，对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。
共享锁：SELECT … LOCK IN SHARE MODE;
排他锁：SELECT … FOR UPDATE;
面试官：（这小子有两下子）嗯，幸福的汽车你怎么看？
HR：通俗易懂，我听懂了~~
面试官：好，那最后一个问题，你上面提到了乐观锁和悲观锁，谈谈你对它的看法吧。
侨总：其实悲观锁和乐观锁，也并不是 MySQL 或者数据库中独有的概念，而是并发编程的基本概念。主要区别在于，操作共享数据时，“悲观锁”即认为数据出现冲突的可能性更大，而“乐观锁”则是认为大部分情况不会出现冲突，进而决定是否采取排他性措施。
反映到 MySQL 数据库应用开发中，悲观锁一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。乐观锁则与 Java 并发包中的 AtomicFieldUpdater 类似，也是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。
MySQL的多版本并发控制 （MVCC），其本质就可以看作是种乐观锁机制，而排他性的读写锁、两阶段锁等则是悲观锁的实现。
面试官：好，小侨我看你对MySQL锁这块儿确实研究得比较透彻，连HR都听懂了，还是让我比较满意的。
面试官：你平时有什么爱好么？
侨总：我除了周末听戏坐包厢，骑马酒吧滑雪场。就是喜欢炒比特币啦！
面试官：哦，不好意思，我们公司反对炒比特币的行为，回去等通知吧。
侨总：？？？
说着面试官ttdmy走出了会议室，HR小姐姐表示我哪壶不开提哪壶，马老板高点买的比特币，现在泡沫炸了，亏成马，老板都差点亏没了。