当前位置: 首页 > news >正文

12 SQL进阶-锁(8.20)

一、锁的基本介绍

1、概念

2、分类

全局锁:索性数据库中的所有表

表级锁:每次操作锁住整张表

行级锁:每次操作锁住对应的行数据

二、全局锁

1、基本概念

场景:

现在正在备份这个数据,数据库中有三张表,在备份tb_stock时,有一个用户下单了,相应的要减少库存并生成订单,再备份tb_order,此时又需要插入订单日志,最后备份tb_orderlog,最后备份好数据。

在没有全局锁的情况下,很容易造成数据不一致。

加入全局锁,可查,不能增删改。

2、语法

3、实操

(1)开三个控制台,mysql -h192.168.200.130 -uroot -p

设置全局锁:flush tables with read lock;

(2)在第二个控制台执行查询操作成功,但执行增删改操作,会停滞。

(3)备份操作,在windows控制台执行,不要连接数据库,远程登录即可。

mysqldump -h192.168.200.130 -uroot -p12345 itcast > D:/itcast.sql

(4)释放全局锁

4、全局锁特点

在InnoDB引擎中,我们可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot -p123 itcast > itcast.sql

底层:通过快照读来实现。

三、表级锁

1、介绍

2、分类

(1)表锁

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

(3)意向锁

3、表锁

(1)分类

①表共享读锁(read lock)

演示:

开两个远程控制

a.第一个打开表锁:lock tables tb_user read;

b.在当前控制台更新数据,直接报错

c.另一个控制台更新数据,则会停滞

②表独占写锁(write lock)

其他客户端不可以增删改查

演示:

开两个远程控制

a.第一个打开表锁:lock tables tb_user write;

b.在当前控制台查询、更新数据,均成功

c.在第二个控制台查询和更新数据库表,俊辉停滞

(2)语法

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

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

(3)总结

4、元数据锁(meta data lock,MDL)

(1)基本概念

解释:元数据锁的本质是保护表结构的并发安全,而不是控制数据并发写入。这里的共享读写锁只是为了告诉系统这个时候我在用数据,别改表!

共享锁之间兼容,共享锁包含了共享读锁,共享写锁,它们都和排他锁不兼容的

在xshell中,开两个客户端,分别开启事务,在第一个事务中查询数据,成功,在第二个客户端更新数据也成功,是因为执行的update操作,他的锁类型为shared_write是与share_read共享,兼容的。看表格。

客户端一:

客户端二:

但是,当在第一个客户端查询student表,然后在第二个客户端改变表结构,此时第二个客户端会处于停滞状态。

客户端一:

客户端二:

在客户端一执行select语句,自动加上shared_read锁,他与改变表结构所自动加的exclusive锁不兼容,因此第二个客户端会处于停滞状态,直至第一个客户端提交事务。

(2)基本语法

查看元数据锁:

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

在没有执行事务前,没有加元数据锁:

在另一个客户端执行查询操作,再次查询,则会加入元数据锁:

5、意向锁

(1)引入

在客户端一中开启一个事务,此时要update id=3的行数据,会对这一行加一个行锁。客户端二此时执行lock tables xxx的操作,此时行锁和表锁会冲突,需要一行行检查是否有加锁的情况,效率较低。

为了解决这个问题,引入了意向锁

(2)概念

在客户端一中开启一个事务,此时要update id=3的行数据,会对这一行加一个行锁,此外,还会再加一个意向锁。客户端二此时执行lock tables xxx的操作,他在加表锁时,会检查这张表的意向锁,若意向锁与这个表锁兼容,直接加锁,不兼容则会处于停滞状态,直至客户端一提交事务,释放行锁与表锁。

(3)分类

(3)兼容性

可以通过一下SQL语句,查看意向锁及行锁的加锁情况:

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

(4)实际操作

IS:

客户端一:

客户端二:

is:意向共享锁

record:行锁

客户端二:

加入表锁共享锁,成功,因为is与表锁共享锁兼容。

加入表锁排他锁则会失败,因为不兼容。

IX:

客户端一:

此时会自动加入行锁和意向排他锁。

客户端二:

加入行锁和意向排他锁(IX)。

此时执行表锁共享锁,则会处于停滞状态,因为不兼容。

同理,加入表锁排他锁也会处于停滞状态。

四、行锁

1、概念

2、分类

(1)行锁(read lock):锁定单个行记录的锁,防止其他事务对此进行update和delete。在RC、RR隔离级别下都支持。

(2)间隙锁(gap lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事物在这个间隙进行insert,产生欢度。在RR隔离级别下都支持。

(3)临键锁(next-key lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙gap。在RR隔离级别下都支持。

3、行锁

(1)分类

解释:S与S之间共享,S与X之间排斥。

X与S、X都排斥。

(2)不同操作下加的锁类型

(3)演示

可以通过一下SQL语句,查看意向锁及行锁的加锁情况:

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

场景一:

客户端一:

此时查询加锁情况,为empty。

此时加入lock in share mode,加入共享锁S。

客户端二:

第二行lock_mode的S,即为共享锁。

在客户端二执行相同操作。再次查询加锁情况。

加了两个共享锁,S与S之间相互兼容。

场景二:

客户端一:

客户端二:

此时是可以更新成功的,因为客户端一查询的是id=1的数据。

此时会失败。因为id=1。加的是排他锁。X与S互斥。

场景三:

客户端一:

客户端二:

此时在客户端二执行更新操作,则会停滞,因为客户端一中,name没有建立索引,因为客户端一升级为表锁。

对name建立索引即可。

4、间隙锁/临键锁

(1)规则

(2)演示

场景一:

student表格数据如下:

客户端一:

更新id=16的操作,但表格内没有id=16的数据。

客户端二:

查询锁的类型:X,GAP。加了间隙锁。在21前加了间隙锁,即15-21,看lock_data,不包含已经存在的id。

此时在15-21前的添加数据,则会处于停滞状态,因为加了间隙锁。

场景二:

现有数据:

客户端一:

客户端二:

查询锁类型:

对现有数据加行锁,S,REC_NOT_GAP,对id=4的数据加行锁。

S为临键锁,把id=4以及id=4之前的数据锁住。

S,GAP为间隙锁,把4和8之间的间隙也锁上。

场景三:

客户端一:

客户端二:

http://www.xdnf.cn/news/1351927.html

相关文章:

  • Python语法速成课程(二)
  • 科技赋能,宁夏农业绘就塞上新“丰”景
  • 进程的概念:进程调度算法
  • 【GPT入门】第57课 详解 LLamaFactory 与 XTuner 实现大模型多卡分布式训练的方案与实践
  • rust语言 (1.88) egui (0.32.1) 学习笔记(逐行注释)(七) 鼠标在控件上悬浮时的提示
  • linux中文本文件操作之grep命令
  • 【软件设计模式】策略模式
  • MySQL:事务管理
  • Intel RealSense D435 深度相机详解
  • Java 学习笔记(基础篇8)
  • Linux总线设备驱动模型深度理解
  • Vue3 学习教程,从入门到精通,基于 Vue 3 + Element Plus + ECharts + JavaScript的51购商城项目(45)
  • imx6ull-驱动开发篇37——Linux MISC 驱动实验
  • 大模型四种常见安全问题与攻击案例
  • MySQL数据库管理与索引优化全攻略
  • 力扣(全排列)
  • 使用 PSRP 通过 SSH 建立 WinRM 隧道
  • Linux-常用文件IO函数
  • jQuery 知识点复习总览
  • (nice!!!)(LeetCode 面试经典 150 题) 173. 二叉搜索树迭代器 (栈)
  • 55 C++ 现代C++编程艺术4-元编程
  • 数据结构与算法-字符串、数组和广义表(String Array List)
  • 【Tech Arch】Apache Flume海量日志采集的高速公路
  • 解码LLM量化:深入剖析最常见8位与4位核心算法
  • Mac相册重复照片终结指南:技术流清理方案
  • chromadb使用hugging face模型时利用镜像网站下载注意事项
  • Node.js特训专栏-实战进阶:23. CI/CD流程搭建
  • 通过官方文档详解Ultralytics YOLO 开源工程-熟练使用 YOLO11实现分割、分类、旋转框检测和姿势估计(附测试代码)
  • 优先使用 `delete` 关键字删除函数,而不是将函数声明为 `private` 但不实现 (Effective Modern C++ 条款11)
  • 2025年Java在中国开发语言排名分析报告