首页 > 数据库 >MYSQL-->锁

MYSQL-->锁

时间:2022-10-09 21:56:33浏览次数:47  
标签:加锁 间隙 -- lock 索引 意向锁 MYSQL 共享

介绍

锁是计算机协调多个进程或者线程并发访问某一个资源的机制。

在数据库中,除了传统的计算资源(CPU,RAM,I/O)的争用之外,数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性,有效性是所有数据库必须解决的问题。

锁冲突也是影响数据库并发访问性能的一个重要因素,故数据库中锁很重要也很复杂。.

全局锁

介绍

全局锁就是对整个数据库实例加锁。

枷锁后整个实例就处于只读状态,后续的增删改操作,事务提交操作,修改表操作等会处于阻塞状态。

全局锁一般用在数据库的逻辑备份,对所有表进行锁定,从而获取一致性视图,确保数据的完整性。

语法

加全局锁

flush tables with read lock;

解锁

unlock tables;

数据库备份(mysqldump)

mysqldump -u账号 -p密码 -h主机地址 要备份的数据库 > 备份的路径/xxx.sql

mysqldump是mysql提供的一个备份工具,直接在命令行执行即可不需要进入mysql

xxx.sql文件存储的是SQL语句!

表级锁

介绍

表级锁,每次操作锁住整张表。

锁定的力度很大,发生锁冲突的概率最高,并发度最低。

应用在MyISAM,InnoDB,BDB等存储引擎中

分类

对于表级锁主要分为

  1. 表锁
  2. 元数据锁(meta data lock ,简称MDL)
  3. 意向锁

表锁

表锁又可分为

  1. 表共享读锁(read lock)又称为读锁
  2. 表独占写锁(write lock)又称为写锁

语法

加锁(写锁或读锁)

lock tables 表名列表 read/write;

read加的是读锁

write加的是写锁

解锁

unlock tables;

或者客户端断开连接也可以解锁。

写锁和读锁的特点

  1. 读锁

    假定客户端1给表1加了读锁

    此时,客户端1可读取表1的数据,但不能对表1进行写入操作。

    客户端2可以读取表1的数据,也不能对表1进行写入数据操作。此时会进入阻塞状态等待解锁

    即,一旦加了读锁,那么读锁不会阻止其他客户端进行读取操作,只会阻止所有客户端的写入操作和修改表等操。

  2. 写锁

    假定客户端A给表1加了写锁

    此时客户端A可以对表1进行增删改查,读取等所有操作

    客户端B无法对表1进行任何的增删改查,修改表等操作,会进入阻塞状态等待解锁

    即,一旦加了写锁,那么写锁不会阻止加写锁的客户端的任何操作,只会阻止其他客户端的所有操作

写锁就相当于把表变成你的所有物了其他人不能看不能碰!

读锁就相当于你把表捐给了博物馆,所有人包括你自己都不能碰,只能看

元数据锁

元数据锁加锁过程是系统自动控制的,不需要手动枷锁。

在访问一张表的时候会自动加上,

MDL锁主要作用是维护表元数据一致性,在表上有活动的事务的时候不可以对这个表结构进行修改!

元数据锁是为了避免DML与DDL冲突,保证读写的正确性。

元数据可以简单的理解为表结构

在MYSQL5.5版本中引入了MDL

当对一张表进行增删改查的时候,自动加上MDL读锁(共享)

当对表结构进行变更操作的时候会自动加上MDL写锁(排他)

比如我在客户端A中先开启事务!!然后再对表等进行操作才会打开元数据锁!!

先开启事务后!!再触发对应SQL才会加锁!!

当锁之间冲突的时候,后执行的一方会进入阻塞状!

对应SQL 锁类型 说明
lock tables 表名 read/write 表锁中的 读锁/写锁
select、select...lock in share mode 元数据锁中的共享读锁(shared_read) 与shared_read,shared_write兼容,与Exclusive排斥
insert、update、delete、select...for update 元数据锁中的共享读锁(shared_write) 与shared_read,shared_write兼容,与Exclusive排斥
alter table ... 元数据锁中的排他写锁(Exclusive) 与其他MDL都排斥

查看元数据锁

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

意向锁

意向锁也是在事务中出现的!

意向锁是添加给表的

为了解决DML在执行的时候,加的行锁和表示之间冲突,在InnoDB中加入了意向锁

让表锁不需要检查每行数据是否加锁,使用意向锁来减少表示的检查。

假定客户端A对表中某行记录加了表锁和与意向锁

当客户端B要对表加一个表锁的时候,会对表中意向锁查看是否与自己加的表锁兼容

如果兼容就加锁成功,如果不兼容就加锁失败。

分类

  1. 意向共享锁(IS):由select....lock in share mode添加
  2. 意向排他锁(IX):由insert、update、delete、select...for update添加

加上意向锁的同时也会添加上行锁的共享锁,行锁的排他锁!

兼容情况

  1. 意向共享锁(IS):与表锁共享锁(read)兼容,与表锁排他锁(read)互斥
  2. 意向排他随(IX):与表锁共享锁(read)和排他锁(write)都互斥。意向锁之间不会互斥。

查看意向锁和行锁的加锁情况

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(read commit)和RR(Repeatable Read)隔离级别下支持

行锁和意向锁,元数据锁是一起触发的!

行锁分为

  1. 共享锁(S)

    事务A开启共享锁,事务B如果也是共享锁那么就兼容不会进入阻塞状态

  2. 排他锁(X)

    事务A开启排他锁,只有事务A可以操作,其他事务都会进入阻塞状态

排斥情况

当前锁类型\请求锁类型 S(共享锁) X(排他锁)
S(共享锁) 兼容 冲突
X(排他锁) 冲突 冲突

行锁的触发情况

SQL 行锁类型 说明
insert... 排他锁 自动加锁
update... 排他锁 自动加锁
delete... 排他锁 自动加锁
select...
select ...lock in share mode 共享锁 select后加上lock in share mode
select...for update 排他锁 select后加上for update

默认情况下 ,InnoDB在RR事务隔离级别上运行,InnoDB允许使用next-key锁进行搜索和索引扫描,防止幻读。

  1. 针对唯一索引进行检索,对已存在的记录进行等值匹配,会自动优化为行锁
  2. InnoDB的行锁是针对于索引加的锁,不通过索引条件检索数据,那么会从行锁升级为表锁。

间隙锁(Gap Lock)和临键锁(Next-key Lock)

间隙锁

间隙是俩个记录之间的间隙

间隙锁唯一目的就是防止其他事务插入间隙。

间隙锁可以共存,一个事务采用的间隙锁不会阻止另外一个事务在同一个间隙采用间隙锁

锁定索引记录的间隙(不含该记录),确保所有记录间隙不变,防止其他事务在这个间隙进行insert从而产生幻读。

在RR(Repeatable Read)隔离级别下支持

image

临间锁

行锁和间隙锁的组合,同时锁住数据,并锁住数据前面的间隙Gap。

在RR(Repeatable Read)隔离级别下支持

俩个锁的出现情况

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

    begin;
    update stu set name='wd' where id=5; #假定id=5不存在表中只存在id为1,2,3,4,6,8
    

    这样就在4和6的间隙加了一个间隙锁了

  2. 索引上的等值查询(普通索引),向右遍历时最后一个值不满足查询要求时,临间锁退化为间隙锁

    假定B+树叶子节点为

    6|12 16|18 29|34 |35|35| 35|40 44|48

    begin;
    select * from stu where age=35;
    

    假定age字段为普通索引

    此时innodb会加上临间锁

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

会加上临间锁

几种锁的情况总结

  1. 对于元数据锁来说,关注一下在多个事务开启的时候,如果其中一个事务进行了修改表操作,其他事务就会进入阻塞状态。

修改表结构的叫排他锁,其他的增删改查叫共享锁.

  1. 对于意向锁,这玩意就是拿来解决表级锁和行级锁的冲突用的,当表级锁是共享且意向锁是共享那么就兼容。当意向锁为排他锁那么只有对排他锁兼容。有且仅有这俩情况兼容。其中lock in share mode是共享,for update是排他
  2. 对于行级锁,他是跟表级锁的意向锁,元数据锁一起随着innodb引擎自动添加的。
  3. 行级锁只有共享锁之间是兼容的其他都不兼容。
  4. 只有lock in share mode是共享锁,其他的增删改查 for update都是排他锁
  5. 间隙锁是在唯一索引的情况下,条件为不存在的记录时会出现
  6. 间隙锁也在普通索引下的等值查询,会出现,因为普通索引会出现多个重复值
  7. 临间锁是在唯一索引的范围匹配下进行等值查询会出现。锁住范围的同时锁住范围最后一个数前面的间隙

标签:加锁,间隙,--,lock,索引,意向锁,MYSQL,共享
From: https://www.cnblogs.com/wdadwa/p/MYSQL_Learning_12.html

相关文章

  • Typora的使用基础
    Markdown学习标题三级标题#+空格+回车变标题几个井号代表几个标题最多6个字体Hello,world!俩边俩个星号变粗体Hello,world!俩边一个星号变斜体Hello,world!俩边三个星号变......
  • 单例模式
    publicclassSingleton{privatestaticclassSingletonHolder{privatestaticfinalSingletonINSTANCE=newSingleton();}privateS......
  • (编程语言界的丐帮 C#).NET Framework 读取Excel到DataTable
    (编程语言界的丐帮C#).NETFramework读取Excel到DataTable生成DataTable到Excel,支持2007.xlsx,2003 .xls。 nuget引用 NPOI。ExcelHelper:usingNPOI.HPSF;usi......
  • 二叉树
    P4715淘汰赛查看代码/*本题一眼就是二叉树,不过是满二叉树,把左右节点做比较,大的就做父节点,类似线段树的打法不过这题也可以用队列+结构体实现,比对时大的继续留在队......
  • "你帮我助"软件开发PSP数据统计
    计划在疫情期间,各个小区居民发挥互助精神,进行物品交换,互通有无。亟需一个物品交换软件该程序允许添加物品的信息,删除物品的信息,显示物品列表,也允许查找物品的信息为方......
  • 文件的操作(读写、方法、光标、内容修改)
    文件(读写、方法、光标、内容修改)文件的基本操作​ 文件的概念 应用程序运行过程中产生的数据最先都存放在内存中,要是想永久保存下来,必须保存于硬盘中,应用程序要是想操作......
  • 如何隐藏系统的磁盘
    装系统的时候创建的盘,大小比较小的那些,我们在资源管理器中是看不到的,不分配盘符即可,已有盘符的,删除就可以     ......
  • 学习python-Day69
    今日学习内容一、权限类的使用使用步骤:写一个类,继承BasePermission重写has_permission方法在方法中校验用户是否有权限(request.user)就是当前登录用户有权......
  • Day10函数基础学习以及计算机硬盘修改数据的原理(了解)
    今日内容概要文件内光标的移动实战演练计算机硬盘存取数据的原理文件内容修改函数简介函数的语法结构函数的定义与调用内容详细文件内光标移动案例(了解)im......
  • 基本数据类型的sizeof()
    基本数据类型的sizeof基本类型包括:bool,char,short,int,long,longlong,float,double32位编译器指针都是4位数据类型大小bool1char1short2int4long......