MySQL八股-全局锁，表级锁，表锁，元数据锁，意向锁，行级锁，行锁，间隙锁，临键

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语
句，已经更新操作的事务提交语句都将被阻塞

DML是Update，insert，delete语句，数据操纵语言
DDL是Alter，create，drop之类的语句，数据定义语言
DQL，是查询，数据查询语言

数据库中加全局锁，是一个比较重的操作，存在以下问题：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导
  致主从延迟。

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。
对于表级锁，主要分为以下三类：

1. 表锁
2. 元数据锁（meta data lock，MDL）
3. 意向锁

表锁(表级锁)

表锁作用是把整张表锁住
对于表锁，分为两类：

• 表共享读锁（read lock）
• 表独占写锁（write lock）

读锁

写锁

结论: 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁(MDL，Meta Data Lock，表级锁)

元数据说的是表结构，元数据锁是锁住了表结构的意思

MDL作用是防止DML和DDL语句冲突问题的

meta data lock , 元数据锁，简写MDL。
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

注意：这里Shared-Read和Shared-Write是兼容的！！只是Shared-Read/Shared-Write与Exclusive互斥！

元数据锁演示

元数据锁兼容的情况

当执行SELECT、INSERT、UPDATE、DELETE等语句时，添加的是元数据共享锁（SHARED_READ / SHARED_WRITE），之间是兼容的。

上图如何理解？左边的客户端开启事务，进行查询操作，开启Shared-Read锁，右边客户端进行查询和更新操作，开启了Shared-Read和Shared-Write锁，但是由于表级锁里面元数据读写锁是兼容的，因此不阻塞另外一个客户端的操作，是兼容的！！

元数据锁互相阻塞的情况

当执行SELECT语句时，添加的是元数据共享锁（SHARED_READ），会阻塞元数据排他锁（EXCLUSIVE），之间是互斥的。
左边客户端开启Shared-read元数据读锁，右边用到了Alter语句，Exclusive级别的排他锁，因此是互斥，会导致阻塞！！

下面这个语句可以查询看元数据锁的加锁情况。

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

刚开始只有performance-schema里面有元数据读锁

意向锁（Intention lock，表级锁）

规避行锁与表锁加锁时的冲突问题

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

意向锁应用场景是这样的：
假如我对表A的某一行执行更新，则需要对表A的这行加上行锁，然后假如我有一个线程来了，需要加表锁，那么我们还需要挨行的去检查每一行是否存在行锁，这十分憨批，如果我们设置一个类似于标志位flag，flag=0，无行锁，flag=1，有行锁，这就解决了！这个flag就是本章节提出的意向锁。每次来检查是否有意向锁，是否和表锁冲突就完事儿了！！

当客户端二，想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就会从第一行数据，检查到最后一行数据，效率较低。

有了意向锁之后 :
客户端一，在执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。

意向锁分类

• 意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。IS = Intention Select
• 意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。IX=Intention + X （X为Insert，Update，delete，…任意一个）

所以一旦Select 。。。locks in Shared mode，会存在IS和MDL的Shared-Read（元数据锁）

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
performance_schema. data_locks;

意向锁演示：

意向共享锁(IS)与表读锁是兼容的

表锁与IS有可能冲突，表锁的LOCK READ与IS兼容，表锁的写锁与IX不兼容，互斥

意向排他锁（IX）与表读锁、写锁都是互斥的

IX与表锁的lock read，lock write都互斥！！

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

1. 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
   
2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
   
3. 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持
   

行锁(Record-Lock)

行级锁有行锁，间隙锁，临键锁三种类型。现在介绍其中的行锁
InnoDB实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。（注意S是行锁里面的共享锁，IS是意向锁里面的共享锁，S是行，IS是表，都是读的级别）
• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。（IX是意向锁，表锁，X是行的排他锁，行级）
  

行锁的加锁情况

常见的SQL语句，在执行时，所加的行锁如下：

FOR UPDATE是在SQL语句中显式使用排他锁，避免其他事务干扰
LOCK IN SHARE MODE是显式使用共享锁

行锁优化

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。 next-key锁（间隙锁+行锁） $\to$ 行锁）

遍历的时候用索引扫描，又因为默认是使用next-key锁，因为你本身就是唯一的，还是等值查询，针对存在的记录查询，因此直接行锁就完事儿了

• InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。
  （next-key锁（间隙锁+行锁）$\to$表锁）

（比如通过非索引字段查询并且查询的时候带上了锁，比如是行锁里面的排他锁or行锁里面的共享锁，除非你只Select，不加任何的锁）

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

PS：
注意一下有区别：

下面这个语句可以查询看元数据锁的加锁情况。

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

可以通过以下SQL，查看意向锁及行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks ;

一个是metadata_locks一个是data_locks，情况不一样！！ ，而且Select出来的列名也是不一样！！

间隙锁（Gap-Lock）

间隙锁的作用防止多个事务并发时发生的幻读现象。

幻读
两次查询结果（两次读取结果）不一样就是幻读，为此，在可重复读，RR级别下，特此加入间隙锁，试图消除幻读现象，保证读1次和读2次，读99次结果都是一样的！！

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

如果这里是索引上的等值查询(唯一索引)，给存在的记录加锁时, 优化为行锁 。又是前面讨论过的情况

• 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
• 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

更详细的间隙锁参考另外一篇文章：
https://blog.csdn.net/weixin_46028606/article/details/144471986?fromshare=blogdetail&sharetype=blogdetail&sharerId=144471986&sharerefer=PC&sharesource=weixin_46028606&sharefrom=from_link

A.索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

数据准备：

DROP TABLE IF EXISTS `stu`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stu` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`age` int NOT NULL,PRIMARY KEY (`id`),KEY `idx_stu_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `stu`
--LOCK TABLES `stu` WRITE;
/*!40000 ALTER TABLE `stu` DISABLE KEYS */;
INSERT INTO `stu` VALUES (1,'tom',10),(3,'cat',30),(9,'rose',90),(11,'jetty',110),(19,'lily',190),(25,'luci',250);
/*!40000 ALTER TABLE `stu` ENABLE KEYS */;
UNLOCK TABLES;

X，GAP表示行锁+间隙锁，这俩加起来就是临键锁next-key锁。

B. 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。

数据准备

DROP TABLE IF EXISTS `hm1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `hm1` (`id` int NOT NULL AUTO_INCREMENT,`num` int NOT NULL,PRIMARY KEY (`id`),KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;--
-- Dumping data for table `hm1`
--LOCK TABLES `hm1` WRITE;
/*!40000 ALTER TABLE `hm1` DISABLE KEYS */;
INSERT INTO `hm1` VALUES (1,6),(2,12),(3,16),(40,18),(50,29);
/*!40000 ALTER TABLE `hm1` ENABLE KEYS */;
UNLOCK TABLES;

验证：

下图试图插入num=28的记录，被阻塞住了，还超时了

C. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。

图片水印挡住了+笔误了，字是锁住最后一个记录50和后面的(50,+∞)

学习资料：
通过网盘分享的文件：MySQL-进阶篇.pdf
链接: https://pan.baidu.com/s/1Wqryjq3JtB5FfP5_EULojw?pwd=e3mf 提取码: e3mf
–来自百度网盘超级会员v8的分享