MySQL进阶之锁(表级锁,元数据锁,意向锁)

2024-02-03 23:04

本文主要是介绍MySQL进阶之锁(表级锁,元数据锁,意向锁),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

表级锁

介绍

表级锁,每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高,并发度最低。应用在MyISAM、 InnoDB、BDB等存储引擎中。

对于表级锁,主要分为以下三类:

  • 表锁

  • 元数据锁(meta data lock,MDL)

  • 意向锁

表锁

对于表锁,分为两类:

  • 表共享读锁(read lock)

  • 表独占写锁(write lock)

语法:

  • 加锁:lock tables 表名... read/write。

  • 释放锁:unlock tables / 客户端断开连接 。

特点:

A.读锁

 

左侧为客户端一,对指定表加了读锁,不会影响右侧客户端二的读,但是会阻塞右侧客户端的写。

 

 B.写锁

 

 左侧为客户端一,对指定表加了写锁,会阻塞右侧客户端的读和写。

 

 

读锁不会阻塞其他客户端的读,但是会阻塞写。写锁既会阻塞其他客户端的读,又会阻塞 其他客户端的写。

元数据锁

MDL加锁过程是系统自动控制,无需显式使用,在访问一张表的时候会自动加上。MDL锁主要作用是维 护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作。为了避免DML与 DDL冲突,保证读写的正确性。

这里的元数据,大家可以简单理解为就是一张表的表结构。 也就是说,某一张表涉及到未提交的事务 时,是不能够修改这张表的表结构的。

在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候,加MDL读锁(共享);当对表结构进行变 更操作的时候,加MDL写锁(排他)。

SQL锁类型说明
lock tables xxx read / writeSHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select ... lock in share modeSHARED_READ与SHARED_READ、 SHARED_WRITE兼容,与 EXCLUSIVE互斥
insert 、update、 delete、select ... for updateSHARED_WRITE与SHARED_READ、 SHARED_WRITE兼容,与 EXCLUSIVE互斥
alter table ...EXCLUSIVE与其他的MDL都互斥

 

如上图,开启一个事务之后,可以对两个客户端进行查询,更新操作,因为共享锁之间是兼容的,所以可以进行操作。

 

 

在提交一个事务后,执行力select语句就会自动添加

SHARED_READ这个共享读锁,由于它与EXCLUSIVE互斥,所以在左侧提交事务之后才可以更新表结构。

我们可以通过下面的SQL,来查看数据库中的元数据锁的情况:

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

意向锁

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

通过示意图简单分析一 下:

首先客户端一,开启一个事务,然后执行DML操作,在执行DML语句时,会对涉及到的行加行锁。

 

 

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

有了意向锁之后 :

客户端一,在执行DML操作时,会对涉及的行加行锁,同时也会对该表加上意向锁。

 而其他客户端,在对这张表加表锁的时候,会根据该表上所加的意向锁来判定是否可以成功加表锁,而 不用逐行判断行锁情况了。

 

 

分类
  • 意向共享锁(IS): 由语句select ... lock in share mode添加 。 与 表锁共享锁 (read)兼容,与表锁排他锁(write)互斥。

  • 意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共 享锁(read)及排他锁(write)都互斥,意向锁之间不会互斥。

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

 

如上图所示,意向共享锁与表读锁是兼容的;

这篇关于MySQL进阶之锁(表级锁,元数据锁,意向锁)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/675693

相关文章

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

从入门到进阶讲解Python自动化Playwright实战指南

《从入门到进阶讲解Python自动化Playwright实战指南》Playwright是针对Python语言的纯自动化工具,它可以通过单个API自动执行Chromium,Firefox和WebKit... 目录Playwright 简介核心优势安装步骤观点与案例结合Playwright 核心功能从零开始学习

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

MySQL逻辑删除与唯一索引冲突解决方案

《MySQL逻辑删除与唯一索引冲突解决方案》本文探讨MySQL逻辑删除与唯一索引冲突问题,提出四种解决方案:复合索引+时间戳、修改唯一字段、历史表、业务层校验,推荐方案1和方案3,适用于不同场景,感兴... 目录问题背景问题复现解决方案解决方案1.复合唯一索引 + 时间戳删除字段解决方案2:删除后修改唯一字

Zabbix在MySQL性能监控方面的运用及最佳实践记录

《Zabbix在MySQL性能监控方面的运用及最佳实践记录》Zabbix通过自定义脚本和内置模板监控MySQL核心指标(连接、查询、资源、复制),支持自动发现多实例及告警通知,结合可视化仪表盘,可有效... 目录一、核心监控指标及配置1. 关键监控指标示例2. 配置方法二、自动发现与多实例管理1. 实践步骤

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种

MyBatis-Plus通用中等、大量数据分批查询和处理方法

《MyBatis-Plus通用中等、大量数据分批查询和处理方法》文章介绍MyBatis-Plus分页查询处理,通过函数式接口与Lambda表达式实现通用逻辑,方法抽象但功能强大,建议扩展分批处理及流式... 目录函数式接口获取分页数据接口数据处理接口通用逻辑工具类使用方法简单查询自定义查询方法总结函数式接口

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入(单行/多行及冲突处理),SELECT实现数据检索(列选择、条件过滤、排序分页),UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更