MySQL锁—全局锁、表级锁、行级锁详解

2024-03-15 19:04
文章标签 mysql 全局 详解 database 行级 表级

本文主要是介绍MySQL锁—全局锁、表级锁、行级锁详解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL 锁

MySQL的锁按照锁的粒度可以分为全局锁、表级锁和行级锁。

一、全局锁

1. 概念

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

2. 应用场景

  数据的一致性备份

3. 语法示例

--加全局锁
flush tables with read lock; 
/*以
主机192.168.206.131
用户名root
密码123456
数据库test
为例*/
--注意，mysqldump是MySQL提供的一个数据备份工具，不是MySQL的命令，要在命令行窗口中执行
mysqldump -h192.168.206.131 -uroot -p123456 test > test.sql 
--释放锁
unlock tables;

4. 特点

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

  • 如果在主库上备份，那么在备份期间不能执行任何更新操作，业务基本上属于停摆状态。

  • 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志( binlog )，会导致主从延迟。

  在InnoDB引擎中，我们可以在备份时加上 --single-transaction 参数来完成不加锁的一致性数据备份。底层实现是：

  • 设置事务的隔离级别为可重复读，即REPEATABLE READ
  • start transaction with consistent snapshot
  • 完成备份操作
  • 提交事务

  在Repeatable Read隔离级别下，一致性视图是在执行start transaction with consistent snapshot时创建的。因此，即使在备份过程中有其他事务更新数据，也没有影响，从而达到了数据的一致性。

二、表级锁

  表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率高，并发度低，应用在MyISAM、InnoDB、BDB等存储引擎中。
主要分为以下三类：

  • 表锁
  • 元数据锁
  • 意向锁

1. 表锁

  表锁分为表共享读锁和表独占写锁两种。

-- 表名user 加锁
lock tables user read/write;
--释放锁
unlock tables;

  其特点为：加了读锁，当前客户端可以读，写会报错，其他客户端可以读，写会阻塞。加了写锁，当前客户端可以读也可以写，其他客户端的读和写都会被阻塞。

2. 元数据锁

  元数据锁( MDL )加锁过程是系统自动控制，无需显式使用。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
  这里的元数据，可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
  在MySQL 5.5中引入了MDL，当对一张表进行增删改查的时候，加元数据共享锁( SHARED_READ 或 SHARED_WRITE )；当对表结构进行变更操作的时候，加元数据排他锁( EXCLUSIVE )。其中元数据共享锁之间是兼容的，元数据排他锁和元数据共享锁之间是互斥的。也就是说，如果我们在当前客户端开启了事务，并对某张表进行了增删改查操作，但未提交，那么在另一个客户端中，我们同样可以进行增删改查，但一旦我们想修改表的结构，则会被阻塞。
  可以通过下面的SQL查看数据库中的元数据锁情况：

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

3. 意向锁

  试想这样一个场景，客户端A对某张表的某行数据进行UPDATE操作，对该行加了行锁，而客户端B想要对这张表加表锁，那么客户端B就需要遍历整张表来判定这张表有没有行锁，有哪种行锁，这就导致客户端B的效率很低。所以，为了避免DML在执行时加的行锁与表锁冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
  应用了意向锁之后，客户端A，在对某张表执行DML操作时，会对涉及的行加行锁，同时也会对该表加上意向锁。而客户端B，在对这张表加表锁的时候，会根据该表上所加的意向锁来判定是否可以成功加表锁，而不用逐行判断行锁情况了。一旦事务提交，意向共享锁和意向排他锁都会自动释放。

  • 意向共享锁( IS )：由语句select … lock in share mode添加。与表锁共享锁( read )兼容，与表锁排他锁( write )互斥。
  • 意向排他锁( IX )：由insert、update、delete、select…for update添加。与表锁共享锁( read )及排他锁( write )都互斥，意向锁之间不会互斥。

  可以通过下面的SQL查看数据库中的元数据锁情况：

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

三、行级锁

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

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

1. 行锁

  InnoDB实现了以下两种类型的行锁：

  • 共享锁( S )：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
  • 排他锁( X )：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
S(共享锁)X(排他锁)
S(共享锁)兼容互斥
X(排他锁)互斥互斥

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

SQL行锁类型说明
INSERT …排他锁自动加锁
UPDATE …排他锁自动加锁
DELETE …排他锁自动加锁
SELECT …不加锁
SELECT … LOCK IN SHARE MODE共享锁需要手动在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE排他锁需要手动在SELECT之后加FOR UPDATE

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

  • 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
  • InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。

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

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

2. 间隙锁&临键锁

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

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

下面我们模拟一下这三种场景：
  如果某张表以id为主键，只有id为3和10的数据，客户端A开启事务，对id为6的数据进行UPDATE操作，那么会在索引项3和10之间(不包含3和10)加入间隙锁，此时客户端B无法插入id在3和10之间的任何数据。

  如果某张表在age字段上建立了非唯一普通索引，只有age为21、23和28的数据，客户端A开启事务，对age为23的数据进行SELECT … LOCK IN SHARE MODE操作，那么会在对索引项23加入间隙锁，同时在索引项23和28之间加入间隙锁，此时客户端B无法插入age在21和28之间的任何数据，同时由于客户端A对索引项23加入了共享锁，客户端B也无法对索引项23再加入排他锁。

  如果某张表以id为主键，有id为3~10的数据，客户端A开启事务，对id>=6的数据进行SELECT … LOCK IN SHARE MODE操作，那么会对索引项6加入行锁，在索引项7、8、9、10加入临键锁，在supremum pseudo-record(可以理解为正无穷处)也加入临键锁。

  间隙锁的唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

注：行级锁的机制可以避免一部分幻读的产生，但无法完全避免。例如某张表中存在id为3和10的数据，在客户端A开启事务对id为6的数据进行SELECT …，只会对该表加入元数据共享锁，客户端B在不受影响的情况下插入了id为6的数据并提交了事务，客户端A想要插入id为6的数据则发现id为6的数据已经存在，还是出现了幻读现象。

这篇关于MySQL锁—全局锁、表级锁、行级锁详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/812967。

相关文章

Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
OpenHarmony鸿蒙开发（ Beta5.0）无感配网详解

OpenHarmony鸿蒙开发（ Beta5.0）无感配网详解

1、简介 无感配网是指在设备联网过程中无需输入热点相关账号信息，即可快速实现设备配网，是一种兼顾高效性、可靠性和安全性的配网方式。 2、配网原理 2.1 通信原理 手机和智能设备之间的信息传递，利用特有的NAN协议实现。利用手机和智能设备之间的WiFi 感知订阅、发布能力，实现了数字管家应用和设备之间的发现。在完成设备间的认证和响应后，即可发送相关配网数据。同时还支持与常规Sof
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
6.1.数据结构-c/c++堆详解下篇（堆排序，TopK问题）

6.1.数据结构-c/c++堆详解下篇（堆排序，TopK问题）

上篇：6.1.数据结构-c/c++模拟实现堆上篇（向下，上调整算法，建堆，增删数据）-CSDN博客 本章重点 1.使用堆来完成堆排序 2.使用堆解决TopK问题 目录 一.堆排序 1.1 思路 1.2 代码 1.3 简单测试 二.TopK问题 2.1 思路(求最小)： 2.2 C语言代码（手写堆） 2.3 C++代码（使用优先级队列 priority_queue）
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2