Mysql数据库-锁(超详细，附图，进阶必看!!)

本文章将基于黑马程序员的 "Mysql数据库从入门到精通"教程 的视图部分进行归纳总结，如有侵权请联系删除!!
【MySQL锁机制高并发必杀技！彻底搞懂数据库“加锁”玄机】事务卡死、性能骤降？一文拆解行锁、表锁、间隙锁底层逻辑，揭秘共享锁与排他锁实战应用场景！从InnoDB锁优化到死锁检测，手把手教你规避库存超卖、数据错乱等高危问题。附赠锁监控命令与性能调优锦囊，助你在电商、金融等高并发场景中游刃有余。解锁数据库稳定运行的终极密码，速来领取你的加薪秘籍！🚀

(一)概述

一、锁的介绍

mysql数据库当中提供了各种各样的锁，用于应对不同场景

二、锁的分类

全局锁的范围是最大的，行级锁的范围与力度都是最小的

(二)全局锁

一、全局锁介绍

(1)为什么做全库地逻辑备份就需要加全局锁

假如现在数据库中存在三张表，此时我们需要去备份这三张表地数据，而由于在备份时业务系统还在不断地往数据库当中去操作、写入数据，那么此时可能会产生一种现象–当我们一边对表进行备份，一边执行业务逻辑，而业务之中又涉及对数据库中数据的更新或删除操作，那么我们最终拿到的数据备份的逻辑是正确的吗，能不能保证数据的一致性呢?答案是不能的。因为我们在执行备份的过程中，还有业务在往表结构当中去写入数据。像图中数据库中备份的stock表还是原来的表数据，但是在经历业务操作之后，order与orderLog表中数据都已更新且被备份，那么就说明三张表中的数据对不上，也就违背了数据的一致性，最终产生的问题就是数据不一致。所以我们在做全库数据备份时就需要加上这个全局锁，从而来保证我们所备份出来的数据是一致的、完整的。

二、对数据库加上全局锁并进行备份的步骤

1. flush tables with read lock指令代表为数据库加上全局锁。需要先给数据库加上全局锁，把整个数据库实例锁住。
2. 再通过使用mysqldump工具(mysqldump是mysql数据库当中提供的用于数据备份的工具)来执行备份操作。
   -u参数用于指定备份时访问数据库的用户名，-p参数用于指定密码，后面itcast指的是要备份哪个数据库，>尖角号用于指定要把备份的数据存到哪个sql文件当中。
3. 接下来一旦对数据库加上了全局锁，那么就意味着我们后续的ddl和dml语句都会被处于阻塞状态，不能再执行，但是是能执行dql查询操作的，也就是可以查询但是不能写入。
4. 在备份完成以后就能拿到备份之后的文件。
5. unlock tables指令代表释放数据库的全局锁，也就是在逻辑备份完成后再把全局锁释放。

• 所以要想进行全库的逻辑备份，就需要在逻辑备份之前手动加上全局锁，逻辑备份完成后再把全局锁释放

三、一致性数据备份

接下来将以命令行进行比较直观的演示，且模拟了三个不同会话来结合演示

1. 在客户端1为整个数据库加上全局锁
2. 到客户端2发现是可以成功执行select语句的，但是执行update语句对数据库中数据进行修改时会失败烁，也就说明此时处于阻塞状态，执行插入或删除操作也是同样不可以的
3. 此时在客户端3使用mysqldump工具进行数据备份，执行后发现报错了。原因是mysqldump是mysql提供的一种工具，它不是sql语句，所以不能在mysql的命令行当中去执行，而是要直接在windows的命令行种执行
   
4. 注意：在windows命令行当中执行数据备份的命令时还需要加上-h地址参数(需指定mysql服务器的主机地址)，因为我们现在访问的是远程Linux主机上的的数据库，不是在windows本机上的
5. 运行成功!下方警告是提示我们直接在命令行当中展示了数据库的密码，可能会有风险，但不会影响实际运行
6. 在sql文件中就能查看到表结构及表中数据
   
7. 释放锁以后就可以正常进行数据更新
   

四、全局锁的特点

1. 全局锁存在的弊端：全局锁的力度很大，一旦加了全局锁那么其他客户端在执行写入操作时都会阻塞。
2. 如果业务当中的数据库是主从结构而不是单机版，而且还做了读写分离，那这时做写入操作是不会阻塞的，因为写入主库是可以从从库当中进行备份，由此也会衍生出在从库上备份的问题。
3. 若加上–single-transaction参数来执行备份操作，那么在innoDB引擎中它实际上是通过快照读来实现的。

(三)表级锁

一、表锁

(1)读锁

①逻辑分析

客户端1首先给表加上一个读锁，且可以去读取该表的数据，但是不能进行写入操作，而客户端2同样可以读取该表的数据但不能进行读写，在客户端1执行完逻辑以后释放表锁。那么可以总结出读锁的特点：加上读锁以后，表只能读取而不能写入，而且读锁不会阻塞其他客户端执行读取操作

②业务示例

使用两个会话来演示读锁：

1. 客户端1为表加上表锁后，客户端1与2都能执行查询操作，而在客户端1中执行更新操作会直接失败，并且提示表已被锁住，在客户端2中执行更新操作则会陷入阻塞状态，光标不断闪动
   
2. 在客户端1解锁表锁后，客户端2中就会继续执行先前的更新操作并成功
   

(2)写锁

①逻辑分析

客户端1为表加上写锁后就锁定了该表，此时使用客户端2会发现既不能对表执行查询操作也不能执行写入操作，也就是读写操作都会被禁止。但是对于客户端1来说，是可以对表执行读写操作的。最后需要把表的写锁释放。

二、元数据锁

1. 元数据可以简单理解为表结构，也就是说若某一张表存在未提交的事务，那么我们不能去修改这张表的表结构。
2. SHARED_READ指的就是共享读锁；SHARED_WRITE指的是共享写锁，实际上也都是MDL的读锁(共享锁)。也就是说增删改查这一类SQL操作的共享锁之间是兼容的，但是与在对表结构进行变更操作时所加的EXCLUSIVE排他锁是互斥的。
3. 列表中展示了对于常见的一类sql语句来说，它们都会加上的对应的元数据锁。
   而元数据锁中得读锁之间是可以互相兼容的(也就是SHARED_READ与SHARED_WRITE是兼容的)，但是写锁之间以及写锁与读锁之间是互斥的(也就是EXCLUSIVE之间不互相兼容且不与SHARED_READ或SHARED_WRITE兼容)。
4. 查询元数据所的sql语句实际上查询的是系统表当中的metadata_locks表，在这张表当中就记录了当前数据库实例当中的元数据锁。

(1)业务示例

1. 在两个客户端中分别打开事务，发现都能成功执行读写操作，因为刚才提到在事务当中执行增删改查操作时都会加上对应的元数据锁，而它们使用的都是共享锁，是能互相兼容的。
   
2. 两个客户端都提交事务，并在客户端1中重新开启事务(客户端2不开启)，在客户端2中执行修改表结构的操作，此时就会为表加上排他锁EXCLUSIVE，且发现会进入阻塞状态，因为在数据库中存在未提交的事务，而该事务中执行过select语句后就会自动地为该表加上元数据锁的SHARED_READ共享读锁。那么此时该共享读锁就会与排他锁发生排斥，客户端就会进入阻塞状态了。
   
3. 直到客户端1中的事务被提交，那么客户端2的阻塞状态就会结束了，且表结构的更新操作也成功执行。
   

(2)验证以上行为是元数据所的作用

1. 在客户端1中开启事务，并在客户端2中查看元数据锁，发现其中只有一条记录，且该元数据锁对应的也是metadata_locks表结构，而类型是SHARED_READ是一把共享读锁。也就说明开启事务是没有使用到元数据锁的，只有执行增删改查语句时才会产生对应的元数据锁
   
2. 执行select语句后再次进行查询就能看到查询语句生成的对应元数据锁了，且为SHARED_READ类型
   
3. 在客户端1的事务不关闭的情况下，在客户端2中执行更新操作，能够成功执行且元数据锁也成功添加记录，且为SHARED_WRITE类型，说明SHARED_WRITE与SHARED_READ类型的读锁可以共存。再去执行修改表结构操作，因为该操作将会往元数据锁表中添加EXCLUSIVE排他锁，会与前面的共享锁互斥，所以此时会被阻塞
   
4. 等到客户端1中的事务提交，那么客户端2中就能成功执行，且原先的共享锁都会消失

三、意向锁

意向锁的存在主要解决的问题就是在innoDB引擎当中加的行锁与表锁的锁冲突问题

(1)锁冲突的出现情况

假设在客户端A的线程A中开启了一个事务并且将要去更新表中的某一行数据，那么此时在默认的mysql隔离级别下，就会自动地为该行数据加上行锁。若此时客户端B在线程B中并发访问该表，且要去锁上该表(使用表锁)，那么就会发生锁冲突。因为表中已经存在行锁，不能直接对整张表加上表锁，所以此时线程B去执行表锁操作就需要先去判定该表中是否已经存在行锁及行锁的类型。
判定方法：要先从表中的第一条记录开始检查该行是否存在对应的行锁，是什么类型的行锁，并一直检查到最后一行，那这样检查的性能极低。为了解决这个问题，就需要使用mysql当中引入的意向表

(2)使用意向锁简化锁判定

线程A开启线程并执行更新操作时会为该行加上行锁，同时会为该表加上一个意向锁。此时线程B再想去为表加上表锁之前会先去检查该表当中意向锁的情况，通过意向锁的情况来决定是否能对这张表加锁成功。如果意向锁与当前准备加的表锁是兼容的，那么直接加锁；若它们不兼容，那么这块则会处于阻塞状态，阻塞到左侧线程A的事务提交，把行锁、意向锁释放以后就能解锁阻塞状态，拿到这张表的表锁。

也就是说，在引入了意向锁以后，再去添加表锁就不用逐行检查该表的行锁情况，只需要根据意向锁及它的类型来决定当前表锁是否可以添加成功。

(3)意向锁与表锁的兼容情况

1. 在查看意向锁及行锁的加锁情况的sql语句中，与刚才讲解元数据锁对应的表不是同一张表。元数据锁查询的是metadata_locks，而这一块操作的表是data_locaks。

①意向共享锁与表锁的兼容情况

1. 客户端1中在sql语句后面加上lock in share mode代表的是它会加上这一行行锁的共享锁，同时为该表加上意向共享锁
2. 在客户端2中查询意向锁及行锁加锁情况，来查看该行锁与意向锁是否已经成功添加上
3. 意向锁及行锁加锁情况的表格结构
   在执行结果中已经表明了object_chema操作的数据库，object_name涉及到的表，index_name涉及到的索引的名称， lock_type锁的类型(表锁还是行锁，RECORD代表行锁)，lock_mode锁的模式(意向共享锁IS、意向排他锁IX)，以及lock_data锁的数据值。
   也就意味着我们为id为1的该行数据加上了一个行锁(共享锁)，为score表加上了IS意向共享锁(表锁)
4. 接下来再为该表加上读锁，发现是可以成功加锁的，因为IS意向共享锁和读锁是兼容的；若加上写锁就会进入阻塞状态，因为IS意向共享锁和写锁是互斥的
   
5. 等到线程A中的事务提交，那么涉及到的意向共享锁就会释放，且线程B中的写锁也就能添加成功了。
   

②意向排他锁与表锁的兼容情况

1. 在客户端1中开启一个事务并执行更新数据操作，在我们执行update语句时就会自动为将要更新的该行数据加上行锁，同时也会为该表加上一个意向排他锁。执行完毕后再在客户端2中查看加锁情况，此时能看到id为1的该行数据拥有一个RECORD行锁(排他锁)，与一个IX意向排他锁。
2. 那么此时客户端2中要对该表加上一个表锁(读锁)，在加锁过程中innoDB引擎会去判定该表的意向锁情况，此时它会看到该表存在一个意向排他锁，所以这时两个锁就会互斥而导致进入阻塞状态。
3. 当客户端1中的事务提交以后，客户端2才能成功添加上表锁(读锁)。或同样的，在客户端2中去为表添加写锁也是不会成功的(会进入阻塞状态)。
   

(四)行级锁

一、行级锁介绍

(1)为什么说InnoDB的数据是基于索引组织的

索引的结构为B+树结构，而在InnoDB当中索引的分类分为聚集索引和二级索引，聚集索引的叶子节点下挂载的是这一行的行数据，二级索引叶子节点下挂载的是对应的主键值。所以行数据是基于聚集索引存储的，也就是InnoDB的数据是基于索引组织的。

(2)三类行级锁介绍

1. 行锁
   RC指的是read commit读已提交，RR指的是repeatable read可重复读。
2. 间隙锁
   间隙指的是在链表当中两个记录之间的范围，比如6到12、16到18之间的间隙。间隙锁指的就是锁住索引记录之间的间隙，但是不包含该记录。主要用于保证索引记录之间的间隙不变，防止其他事务对这个间隙进行insert或update操作，导致间隙结构发生改变，防止产生幻读现象。它主要是在RR隔离级别下支持。
3. 临键锁
   比如我们想要去加一个临键锁，那么这时就会把该行记录锁住，并且把该行记录前面的间隙也同时锁住。所以可以简单理解为该临键锁就是行锁与间隙锁的结合。同样也是在RR隔离级别下支持。

二、行锁

(1)行锁介绍

①共享锁与排他锁

1. 共享锁： 若某事务成功获取到了某一行数据的共享锁，其他事务也可以同样获取到该行数据的共享锁，也就是都可以对同一行数据执行读取操作，并且组织其他事务获取该行数据的排他锁。
2. 排他锁：如果某事务成功获取到了某一行数据的排他锁，那么此时它可以对该行数据进行更新数据。假如第一个事务获取到了某行数据的排他锁，那么其他的事务就不能再获取该行数据的共享锁及排他锁。

②由这两点也就引出在InnoDB存储引擎当中两种类型的行锁之间的兼容情况

1. 共享锁与共享锁之间是兼容的：若事务A获取了一行数据的共享锁，那么事务B也可以继续去获取该行数据的共享锁。
   共享锁与排他锁之间是冲突的：也就是说如果事务A获取到了一行数据的共享锁，那么事务B就不能再去获取该行数据的排他锁，它们之间是互斥冲突的。
2. 排他锁与共享锁和排他锁都是冲突的

(2)常见的增删改查SQL在执行时所加的行锁类型

1. 执行增删改操作的SQL语句时所加的都是排他锁，对于这一类排他锁我们不需要去手动控制，数据库存储引擎会自动加锁
2. 若是正常执行一条select语句是不会去加任何行锁的(包括共享锁与排他锁)
3. 在select执行查询操作时手动地在SQL语句后面加上lock in share mode参数就会加上一把共享锁
4. 在select执行查询操作时手动地在SQL语句后面加上for update参数就会加上一把排他锁

(3)行锁演示

1. 对唯一索引进行检索时，对已存在地记录进行等值匹配时，就会自动地将next-key临键锁优化为行锁
2. 查询的data_locks表与先前使用过的表相同

• 接下来将会以该stu表为示例来演示行锁的使用，通过索引可以看到该表的主键为id，拥有一个主键索引，其他的两个字段name与age都是没有索引的
  

①演示共享锁与排他锁的兼容情况

1. 在会话A中开启事务，并执行一条select语句，根据刚才描述的行锁类型，普通的select并不会为该表加上行锁。此时去查询表结构的加锁情况，发现data_locks表为空，也就意味着当前并没有加锁。在会话B中开启事务并使用普通的select语句也可以正常执行出结果。
2. 此时在会话A中执行select … lock in share mode语句，就会为该表加上一把共享锁。再在会话B中查询加锁情况，看到存在两个锁，除了先前介绍过的IS意向共享锁以外就是现在讲解的S共享锁(行级锁)，REC_NOT_GAP参数指的是没有间隙。
3. 在会话B中再去执行select … lock in share mode语句，发现可以正常执行，且再次查看加锁情况表中，可以看到stu表中id为1的这条记录被加上了两个共享锁，说明共享锁与共享锁是兼容的。最后把会话B的事务提交。
   
4. 在会话B中重新开启一个事务，并查询加锁情况表，可以看到先前事务中加的行锁被释放掉了，现在这个锁是会话A所持有的共享锁。再去执行update更新操作，发现可以成功执行。因为该update操作更新的是id为3的数据，而先前select查询的是id为1的数据，也就是说先前的共享锁锁的是id为1的行数据，而此时update操作只会为id为3的行数据加上一个排他锁，这两行数据并不是同一条，所以并不冲突。若此时再去为id为1的数据执行更新操作，那么就会进入阻塞状态，因为此时的共享锁与排他锁发生互斥。
   

②验证排他锁与排他锁的兼容情况

1. 在会话A与会话B中同时开启事务，并执行update更新操作，因为两侧同时为同一行数据加排他锁就会出发排他锁与排他锁之间的互斥现象，所以会进入阻塞状态
   
2. 只有当会话A当中的事务先提交，会话B中的事务才可以获取到这一行的排他锁并进行更新操作。
   

③在InnoDB中若不通过索引条件检索数据就会将所加的行锁升级为表锁

1. 在会话A中开启事务并执行update语句更新name为lily的行数据，而现在加的是什么锁?如果加的是行锁，那么应该锁的是id为19的这一行数据。那么此时再到会话B中开启事务，并执行update语句更新id为3的数据。该条sql本应只会给id为3的数据加上排他锁(行锁)，与会话A中为id为19的数据所加的行锁不冲突，那么本条sql应该是可以执行成功的，因为它们是不同的数据行。但是此时会发现会话B中进入阻塞状态，就说明发生了锁冲突。原因是在会话A中执行update操作时指定的name字段是没有索引的，那么此时InnoDB引擎会对表中所有记录加锁，就会升级为表锁，也就发生了表锁与行锁的锁冲突。
   
2. 在会话A中将事务提交，会话B中的update操作就能执行成功了
   
3. 接下来可以为name字段建立索引，再去执行先前操作(在会话A中开启事务并执行update语句更新name为Lei的行数据)，再在会话B中开启事务并执行update语句更新id为3的数据，发现更新成功!也就意味着在会话A中没有使用表锁将所有记录锁上。
   

三、间隙锁与临键锁

(1)索引上的等值查询(唯一索引)

首先查看stu表中存在的数据

1. 在会话A中开启事务并执行update语句更新id为5的数据，但是在stu表中id为5的该条数据，那么此时当我们执行这条update语句并不会把该条数据使用行锁锁住，而是会对id为3至8之间的间隙去加上一个间隙锁。通过查询data_locks表来查看加锁情况，发现存在一个IX意向排他锁(表锁，不是关注的核心)与一个X排他锁(行锁)，而在该条排他锁记录中还存在一些参数：GAP指的是间隙，说明这是一个间隙锁；PRIMARY说明这里使用到了主键索引(也就是唯一索引)；锁的类型RECORD说明这是一个行级锁；lock_data中的8代表的是它锁的是8之前的这一块的间隙，也就是3~8之间的间隙(但不包含3与8)。
2. 再在会话B中开启事务并执行insert新增操作，指定要向stu表中插入一个id为7的数据，执行后发现会进入阻塞状态，因为前面我们已经对3至8之间的整个间隙加了锁，此时不能再向该间隙内插入数据。
   
3. 当会话A中的事务提交后，间隙锁就会释放掉，并且会话B中的插入语句也能成功执行。
   
4. 将会话B中的事务提交后，数据就成功插入进表格了，这也就验证了间隙锁的存在。
   

(2)索引上的等值查询(普通索引)

1. 为什么要使用临键锁来防止幻读
   因为mysql在加行锁时它是针对于索引加的锁，而mysql中索引是基于B+树的数据结构，在树的叶子节点上又形成了一个有序的双向链表。而对于普通的非唯一索引来说，假如现有的链表当中有一个字段值为18，那么将来其他事务也可能会再次插入一个字段值为18的记录，此时就会容易出现幻读。所以当我们进行等值查询时根据二级索引的值18进行查询，那么此时它就会通过加上next-key lock临键锁的方式，不仅仅为18对应的这条记录加上了一个行级共享锁进行锁住，还会同时将它前后的间隙全部锁住(16_18、18、1829)，以此来避免其他事务未来对该链表的结构进行修改，防止出现幻读。
2. 为什么next-key lock临键锁会退化为间隙锁
   而当我们要为字段值18前后的间隙进行加锁，那么就需要去向右或向左遍历来寻找间隙，找到最后一个不满足查询条件的字段值29与16的数据，并为18_{29之间的间隙以及16}18之间的间隙进行加锁。

①next-key lock临键锁会退化为间隙锁的逻辑演示

1. 在会话A中为stu表的age字段建立普通(非唯一)索引，开启事务后就根据该字段进行等值查询，并且加上lock in share mode参数来为该表加上一把意向共享锁。
2. 在会话B中查看加锁情况：
   在第三条记录中可以看到此时为stu表中的第3条记录加上一把S行级共享锁，同时还是一个REC_NOT_GAP没有间隙的锁；在第二条记录中可以看出此时还会为 3,3 也就是第3条记录以及它之前的间隙进行整体锁住；在第四条记录中可以看出此时加的是一个GAP间隙锁，也就是说它是把3~7之间的间隙进行锁住。这么操作是为了防止其他事务往这个间隙当中再去插入记录，出现幻读现象，所以会把字段值为3以及它前后的间隙都进行锁住。

(3)索引上的范围查询(唯一索引)

1. 在会话A中开启事务，根据主键id进行范围查询，并加上lock in share mode参数。
2. 此时在会话B中查看该表的加锁情况：首先第二条记录说明此时为id值为19的记录加上了一个S,REC_NOT_GAP航机共享锁；在第四条记录说明此时为id值为25的记录及之前的间隙(19~25)加上了一个S临键锁；第三条记录还说明此时存在一个supremum pseu正无穷临键锁，指的是为id值为25的记录以及它之后一直到正无穷之间的记录加上一个临键锁。
3. 也就意味着在执行这条主键范围查询的sql语句时会把id值为19的这行记录用行级共享锁锁住，并为19~25的记录加上一个临键锁，最后还为25~+∞的记录加上一个临键锁。
4. 会话A提交事务后，所有的锁就会被释放掉了。