MySQL 进阶 锁 -- MySQL锁概述、MySQL锁的分类:全局锁(数据备份)、表级锁(表共享读锁、表独占写锁、元数据锁、意向锁)、行级锁(行锁、间隙锁、临键锁)

本文主要是介绍MySQL 进阶 锁 -- MySQL锁概述、MySQL锁的分类:全局锁(数据备份)、表级锁(表共享读锁、表独占写锁、元数据锁、意向锁)、行级锁(行锁、间隙锁、临键锁),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

  • 1. MySQL锁概述
  • 2. 全局锁
    • 2.1 全局锁介绍
    • 2.2 全局锁语法
      • 2.2.1 加全局锁
      • 2.2.2 释放全局锁
      • 2.2.3 数据备份
    • 2.3 全局锁特点
  • 3. 表级锁
    • 3.1 表级锁介绍
    • 3.2 表锁
      • 3.2.1 表共享读锁(read lock)
      • 3.2.2 表独占写锁(write lock)
      • 3.2.3 小结
    • 3.3 元数据锁(自动添加,一旦事务提交了元数据锁会自动释放)
    • 3.4 意向锁(自动添加,一旦事务提交了意向锁会自动释放)
      • 3.4.1 意向锁介绍
      • 3.4.2 意向锁分类
  • 4. 行级锁(自动添加,一旦事务提交了自动释放行级锁)
    • 4.1 行锁
      • 4.1.1 行锁介绍
      • 4.1.2 行锁演示
    • 4.2 间隙锁 & 临键锁(next-key 锁)
  • 5. 小结

1. MySQL锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,除传统的计算资源(CPU、RAM、I/O)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。

MySQL中的锁,按照锁的粒度分,分为以下三类:

  • 全局锁:锁定数据库中的所有表。
  • 表级锁:每次操作锁住整张表。
  • 行级锁:每次操作锁住对应的行数据。

2. 全局锁


2.1 全局锁介绍

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

其典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。

为什么全库逻辑备份,就需要加全就锁呢?

我们一起先来分析一下不加全局锁,可能存在的问题。

假设在数据库中存在这样三张表:tb_stock 库存表,tb_order 订单表,tb_orderlog 订单日志表。
在这里插入图片描述

  • 在进行数据备份时,先备份了tb_stock库存表。
  • 然后接下来,在业务系统中,执行了下单操作,扣减库存,生成订单(更新tb_stock表,插入tb_order表)。
  • 然后再执行备份 tb_order 表的逻辑。
  • 业务中执行插入订单日志操作。
  • 最后,又备份了tb_orderlog表。

此时备份出来的数据,是存在问题的。因为备份出来的数据,tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。

那如何来规避这种问题呢? 此时就可以借助于MySQL的全局锁来解决。

再来分析一下加了全局锁后的情况
在这里插入图片描述
对数据库进行进行逻辑备份之前,先对整个数据库加上全局锁,一旦加了全局锁之后,其他客户端的DDLDML全部都处于阻塞状态,但是可以执行DQL语句,也就是处于只读状态,而数据备份就是查询操作。那么数据在进行逻辑备份的过程中,数据库中的数据就是不会发生变化的,这样就保证了数据的一致性和完整性


2.2 全局锁语法


2.2.1 加全局锁

flush tables with read lock;

2.2.2 释放全局锁

unlock tables;

2.2.3 数据备份

首先连接上MySQL:

mysql -h 192.168.135.130 -P 3306 -u root -p

在这里插入图片描述
开启全局锁:

flush tables with read lock;

在这里插入图片描述
加了锁之后你会发现执行除了DQL以外的语句会阻塞住:
在这里插入图片描述

我们现在来备份codejiao数据库:
在这里插入图片描述

# mysqldump 是MySQL 提供的工具, 这个命令不是sql语句, 不可以在MySQL的命令行当中执行, 直接在windows的命令行执行即可
# 这里192.168.135.130是服务器IP 3306 是MySQL服务端口 
# root是表示root用户  317525是用户密码 codejiao 是数据库名称
# D:\codejiao.sql 是备份到本地的文件名称 这个文件可以不存在
mysqldump -h 192.168.135.130 -P 3306 -u root -p317525 codejiao > D:\codejiao.sql

在这里插入图片描述这里给出的警告是表示直接把密码暴露在命令中不安全,但是并不影响备份的操作

在这里插入图片描述

备份完成的样子:
在这里插入图片描述

接下来就可以释放全局锁了

# 释放全局锁
unlock tables;

2.3 全局锁特点

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

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

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

# 例如这样即可
mysqldump --single-transaction -h 192.168.135.130 -P 3306 -u root -p317525 codejiao > D:\codejiao2.sql

在这里插入图片描述在这里插入图片描述


3. 表级锁


3.1 表级锁介绍

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

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

  • 表锁
  • 元数据锁(meta data lock, MDL
  • 意向锁

3.2 表锁

对于表锁,分为两类:

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

语法:

  • 加锁:lock tables 表名... read / write
  • 释放锁:
    • 第一种方式unlock tables
    • 第二种方式:客户端断开连接 。

3.2.1 表共享读锁(read lock)

在这里插入图片描述
左侧为客户端一,对指定表加了读锁,不会影响右侧客户端二的读,但是会阻塞右侧客户端的写。(任何客户端对该表都只有写的权限)

测试:

在这里插入图片描述


3.2.2 表独占写锁(write lock)

在这里插入图片描述

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

测试:

在这里插入图片描述


3.2.3 小结

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


3.3 元数据锁(自动添加,一旦事务提交了元数据锁会自动释放)

meta data lock元数据锁,简写MDL

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

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

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

常见的SQL操作时,所添加的元数据锁:

对应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都互斥

总的来说就是SHARED_READSHARED_WRITE兼容,并且都不兼容EXCLUSIVE

演示:

当执行SELECT、INSERT、UPDATE、DELETE等语句时,添加的是元数据共享锁(SHARED_READ / SHARED_WRITE),之间是兼容的。
在这里插入图片描述
当执行SELECT语句时,添加的是元数据共享锁(SHARED_READ),会阻塞元数据排他锁(EXCLUSIVE),之间是互斥的。
在这里插入图片描述
我们可以通过下面的SQL,来查看数据库中的元数据锁的情况(其实查询的是系统表performance_schema.metadata_locks里面的数据):

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

在这里插入图片描述


3.4 意向锁(自动添加,一旦事务提交了意向锁会自动释放)


3.4.1 意向锁介绍

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

假如没有意向锁,客户端一对表加了行锁后,客户端二如何给表加表锁呢,来通过示意图简单分析一下:

  1. 首先客户端一,开启一个事务,然后执行DML操作,在执行DML语句时,会对涉及到的行加行锁。
    在这里插入图片描述
  2. 当客户端二,想对这张表加表锁时,会检查当前表是否有对应的行锁,如果没有,则添加表锁,此时就会从第一行数据,检查到最后一行数据,效率较低(全表扫描)。
    在这里插入图片描述

有了意向锁之后 :

客户端一,在执行DML操作时,会对涉及的行加行锁,同时也会对该表加上意向锁。
在这里插入图片描述

  1. 而其他客户端,在对这张表加表锁的时候,会根据该表上所加的意向锁来判定是否可以成功加表锁,而不用逐行判断行锁情况了(有意向锁就不可以加表锁)。
    在这里插入图片描述

3.4.2 意向锁分类

  • 意向共享锁(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;

演示:

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述


4. 行级锁(自动添加,一旦事务提交了自动释放行级锁)

行级锁,每次操作锁住对应的行数据。锁定粒度最小,发生锁冲突的概率最低,并发度最高。应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁。对于行级锁,主要分为以下三类:

  • 行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行 update 和 delete。在RC(读已提交)、RR(可重复读)隔离级别下都支持。
    在这里插入图片描述

  • 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR(可重复读)隔离级别下都支持。
    在这里插入图片描述

  • 临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。 在RR(可重复读)隔离级别下支持。
    在这里插入图片描述


4.1 行锁


4.1.1 行锁介绍

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

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

两种行锁的兼容情况如下:

在这里插入图片描述

常见的SQL语句,在执行时,所加的行锁如下:

在这里插入图片描述


4.1.2 行锁演示

默认情况下,InnoDBREPEATABLE 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;

演示数据准备: 就id有一个主键索引,其余2个字段没有索引

CREATE TABLE `stu`
(`id`   int NOT NULL PRIMARY KEY AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,`age`  int NOT NULL
) ENGINE = InnoDBCHARACTER SET = utf8mb4;INSERT INTO `stu`
VALUES (1, 'tom', 1);
INSERT INTO `stu`
VALUES (3, 'cat', 3);
INSERT INTO `stu`
VALUES (8, 'rose', 8);
INSERT INTO `stu`
VALUES (11, 'jetty', 11);
INSERT INTO `stu`
VALUES (19, 'lily', 19);
INSERT INTO `stu`
VALUES (25, 'luci', 25);

普通的select语句,执行时,不会加锁。

在这里插入图片描述

select…lock in share mode,加共享锁

共享锁与共享锁之间兼容。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

共享锁与排他锁之间互斥。

客户端一获取的是id1这行的共享锁,客户端二是可以获取id3这行的排它锁的,因为不是同一行数据。 而如果客户端二想获取id1这行的排他锁,会处于阻塞状态,以为共享锁与排他锁之间互斥。
在这里插入图片描述

说明:我这里写的写锁指的是排他锁。

排它锁与排他锁之间互斥

在这里插入图片描述
当客户端一,执行update语句,会为id1的记录加排他锁; 客户端二,如果也执行update语句更新id1的数据,也要为id1的数据加排他锁,但是客户端二会处于阻塞状态,因为排他锁之间是互斥的。 直到客户端一,把事务提交了,才会把这一行的行锁释放,此时客户端二,解除阻塞。

无索引行锁升级为表锁:

stu表中数据如下:
在这里插入图片描述

我们在两个客户端中执行如下操作:

在这里插入图片描述

在客户端一中,开启事务,并执行update语句,更新nameLily的数据,也就是id19的记录 。然后在客户端二中更新id3的记录,却不能直接执行,会处于阻塞状态,为什么呢?原因就是因为此时,客户端一,根据name字段进行更新时,name字段是没有索引的,如果没有索引,此时行锁会升级为表锁(因为行锁是对索引项加的锁,而name没有索引)。

接下来,我们再针对name字段建立索引,索引建立之后,再次做一个测试:

 create index idx_stu_name on stu(name);

在这里插入图片描述

在这里插入图片描述

此时我们可以看到,客户端一,开启事务,然后依然是根据name进行更新。而客户端二,在更新id3的数据时,更新成功,并未进入阻塞状态。 这样就说明,我们根据索引字段进行更新操作,就可以避免行锁升级为表锁的情况。


4.2 间隙锁 & 临键锁(next-key 锁)

默认情况下,InnoDBREPEATABLE READ 事务隔离级别运行,InnoDB使用 next-key 锁(临键锁)进行搜索和索引扫描,以防止幻读。

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

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

示例演示:

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

在这里插入图片描述
只有客户端一,提交了事务(释放间隙锁),才可以插入id7的数据
在这里插入图片描述

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

介绍分析一下:

我们知道InnoDB的 B+树索引结构,叶子节点是有序的双向链表。 假如,我们要根据这个二级索引查询值为18的数据,并加上共享锁,我们是只锁定18这一行就可以了吗? 并不是,因为是非唯一索引,这个结构中可能有多个18的存在,所以,在加锁时会继续往后找,找到一个不满足条件的值(当前案例中也就是29)。此时会对18加临键锁,并对29之前的间隙加锁。

在这里插入图片描述

在这里插入图片描述

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

在这里插入图片描述
查询的条件为id>=19,并添加共享锁。 此时我们可以根据数据库表中现有的数据,将数据分为三个部分:

  • [19]
  • (19,25]
  • (25,+∞]

所以数据库数据在加锁是,就是将19加了行锁,25的临键锁(包含25及25之前的间隙),正无穷的临键锁(正无穷及之前的间隙)。


5. 小结

在这里插入图片描述



这篇关于MySQL 进阶 锁 -- MySQL锁概述、MySQL锁的分类:全局锁(数据备份)、表级锁(表共享读锁、表独占写锁、元数据锁、意向锁)、行级锁(行锁、间隙锁、临键锁)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/564645

相关文章

Spring Security 从入门到进阶系列教程

Spring Security 入门系列 《保护 Web 应用的安全》 《Spring-Security-入门(一):登录与退出》 《Spring-Security-入门(二):基于数据库验证》 《Spring-Security-入门(三):密码加密》 《Spring-Security-入门(四):自定义-Filter》 《Spring-Security-入门(五):在 Sprin

大模型研发全揭秘:客服工单数据标注的完整攻略

在人工智能(AI)领域,数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者,掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中,工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注,不仅能够帮助提升客服自动化系统的智能化水平,还能优化客户服务流程,提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

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

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

关于数据埋点,你需要了解这些基本知识

产品汪每天都在和数据打交道,你知道数据来自哪里吗? 移动app端内的用户行为数据大多来自埋点,了解一些埋点知识,能和数据分析师、技术侃大山,参与到前期的数据采集,更重要是让最终的埋点数据能为我所用,否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式,可以区分为: 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理:自动程度高的,能解决通用统计,便于统一化管理,但个性化定

如何去写一手好SQL

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

基于人工智能的图像分类系统

目录 引言项目背景环境准备 硬件要求软件安装与配置系统设计 系统架构关键技术代码示例 数据预处理模型训练模型预测应用场景结论 1. 引言 图像分类是计算机视觉中的一个重要任务,目标是自动识别图像中的对象类别。通过卷积神经网络(CNN)等深度学习技术,我们可以构建高效的图像分类系统,广泛应用于自动驾驶、医疗影像诊断、监控分析等领域。本文将介绍如何构建一个基于人工智能的图像分类系统,包括环境

水位雨量在线监测系统概述及应用介绍

在当今社会,随着科技的飞速发展,各种智能监测系统已成为保障公共安全、促进资源管理和环境保护的重要工具。其中,水位雨量在线监测系统作为自然灾害预警、水资源管理及水利工程运行的关键技术,其重要性不言而喻。 一、水位雨量在线监测系统的基本原理 水位雨量在线监测系统主要由数据采集单元、数据传输网络、数据处理中心及用户终端四大部分构成,形成了一个完整的闭环系统。 数据采集单元:这是系统的“眼睛”,

使用SecondaryNameNode恢复NameNode的数据

1)需求: NameNode进程挂了并且存储的数据也丢失了,如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2)故障模拟 (1)kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 (2)删除NameNode存储的数据(/opt/module/hadoop-3.1.4/data/tmp/dfs/na

异构存储(冷热数据分离)

异构存储主要解决不同的数据,存储在不同类型的硬盘中,达到最佳性能的问题。 异构存储Shell操作 (1)查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies (2)为指定路径(数据存储目录)设置指定的存储策略 hdfs storagepolicies -setStoragePo