网站干什么的,合肥公司网站搭建服务商,租号网站开发,宣武门网站建设文章目录 全局锁表级锁表锁(表级锁)元数据锁(MDL#xff0c;Meta Data Lock#xff0c;表级锁)元数据锁演示元数据锁兼容的情况元数据锁互相阻塞的情况 意向锁#xff08;Intention lock#xff0c;表级锁#xff09;意向锁分类意向锁演示#xff1a;意向共享锁(**IS**)与… 文章目录 全局锁表级锁表锁(表级锁)元数据锁(MDLMeta Data Lock表级锁)元数据锁演示元数据锁兼容的情况元数据锁互相阻塞的情况 意向锁Intention lock表级锁意向锁分类意向锁演示意向共享锁(**IS**)与表读锁是兼容的意向排他锁**IX**与表读锁、写锁都是互斥的 行级锁行锁(Record-Lock)行锁的加锁情况行锁优化 间隙锁Gap-LockA.索引上的等值查询(唯一索引)给不存在的记录加锁时, 优化为间隙锁 。数据准备 B. 索引上的等值查询(非唯一普通索引)向右遍历时最后一个值不满足查询需求时next-keylock 退化为间隙锁。数据准备 C. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。 全局锁
全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语 句已经更新操作的事务提交语句都将被阻塞 DML是Updateinsertdelete语句数据操纵语言 DDL是Altercreatedrop之类的语句数据定义语言 DQL是查询数据查询语言 数据库中加全局锁是一个比较重的操作存在以下问题
如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆。如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志binlog会导 致主从延迟。
表级锁
表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。 对于表级锁主要分为以下三类
表锁元数据锁meta data lockMDL意向锁
表锁(表级锁)
表锁作用是把整张表锁住 对于表锁分为两类
表共享读锁read lock表独占写锁write lock
读锁 写锁 结论: 读锁不会阻塞其他客户端的读但是会阻塞写。写锁既会阻塞其他客户端的读又会阻塞其他客户端的写。 元数据锁(MDLMeta 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的这行加上行锁然后假如我有一个线程来了需要加表锁那么我们还需要挨行的去检查每一行是否存在行锁这十分憨批如果我们设置一个类似于标志位flagflag0无行锁flag1有行锁这就解决了这个flag就是本章节提出的意向锁。每次来检查是否有意向锁是否和表锁冲突就完事儿了 当客户端二想对这张表加表锁时会检查当前表是否有对应的行锁如果没有则添加表锁此时就会从第一行数据检查到最后一行数据效率较低。 有了意向锁之后 : 客户端一在执行DML操作时会对涉及的行加行锁同时也会对该表加上意向锁。
意向锁分类
意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁(read)兼容与表锁排他锁(write)互斥。IS Intention Select意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥意向锁之间不会互斥。IXIntention X X为InsertUpdatedelete…任意一个
所以一旦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 readlock write都互斥
行级锁
行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中 InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类
行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持 间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持 临键锁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?fromshareblogdetailsharetypeblogdetailsharerId144471986sharereferPCsharesourceweixin_46028606sharefromfrom_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)
) ENGINEInnoDB AUTO_INCREMENT100 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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;XGAP表示行锁间隙锁这俩加起来就是临键锁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)
) ENGINEInnoDB AUTO_INCREMENT51 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_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;验证 下图试图插入num28的记录被阻塞住了还超时了
C. 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。
图片水印挡住了笔误了字是锁住最后一个记录50和后面的(50,∞)
学习资料 通过网盘分享的文件MySQL-进阶篇.pdf 链接: https://pan.baidu.com/s/1Wqryjq3JtB5FfP5_EULojw?pwde3mf 提取码: e3mf –来自百度网盘超级会员v8的分享
