程序员经验分享-hwhale

MySQL行锁、表锁&间隙锁

2023-05-16

事务隔离级别的实现原理：锁

表级锁&行级锁

表级锁：对整张表加锁。开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率高，并发度低。
行级锁：对某行记录加锁。开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高。
注:

  1. 对于InnoDB引擎，绝大部分情况应该使用行锁
  2. 使用表锁中，表比较大，事务需要更新全部或大部分数据
  3. 事务涉及到多个表，比较复杂，可能引起死锁，造成大量的事务回滚

排它锁和共享锁

共享锁（Shared），又称为S锁，读锁
共享锁锁定的资源可以被其他用户读取，但不能修改

在进行SELECT的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。

排它锁（Exclusive），又称为X锁，写锁
排它锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或修改

X锁和S锁之间有以下的关系：SS(读-读)可以兼容的，SX(读-写)、XX(写-写)之间是互斥的

  • 一个事务对数据对象O加了S锁，可以对O进行读取操作，但不能进行更新操作。加锁期间其他事务能对O加S锁但不能加X锁

  • 一个事务对数据对象O加了X锁，就可以对O进行读取和更新。加速期间其他事务不能对O加任何锁。

//对某一行加上共享锁
select uid from student where uid=1 lock in share mode; 
//对某个数据行上添加排它锁
select uid from student where uid=1 for update;

InnoDB行级锁

InnoDB存储引擎支持事务处理，表支持行级锁定，并发能力更好

行级锁

  1. InnoDB的行锁是通过给在索引上的索引项加锁来实现的，是给索引在加锁，并不是给单纯表的行记录在加锁；索引若过滤条件没有索引的话，使用的就是表锁，而不是行锁！！！
  2. 由于InnoDB的行锁实现是针对索引字段添加的锁，不是针对行记录加的锁，因此虽然访问的是InnoDB引擎下表的不同行，但若使用相同的索引字段作为过滤条件，依然会发生锁冲突，只能串行进行，不能并发进行
  3. 即使SQL中使用了索引，但是经过MySQL的优化器后，若认为全表扫描比使用索引效率更高，此时会放弃使用索引，因此也不会使用行锁，而是使用表锁，比如对一些很小的表，MySQL就不会去使用索引。

间隙锁（gap lock）（串行化隔离级别怎么解决幻读问题？）

间隙锁是专门用于解决幻读这种问题的锁，它锁的是行与行之间的间隙，能够阻塞新插入的操作

间隙锁的引入也带来了一些新的问题，比如：降低并发度，可能导致死锁。

注意：读读不互斥，读写/写读/写写实互斥的，但是间隙锁之间是不冲突的，间隙锁会阻塞插入操作。另外，间隙锁在可重复读级别下才是有效的。

幻读场景：
在这里插入图片描述

第一类条件：范围查询
在这里插入图片描述

注：当使用索引时，经过MySQL优化器，认为全盘扫描比使用索引效率高，则变成表级锁，当前只能插入表头之前或表尾之后。

第二类条件：等值查询
引入上图场景所用表进行解读
在这里插入图片描述
注：若age是主键索引和唯一索引(值是不允许重复的)，那就只有行锁

间隙锁和next-key lock：
行锁和间隙锁合称为next-key lock，这个锁是左开右闭的区。

意向共享锁和意向排他锁

1、意向锁是由InnoDB存储引擎获取行锁之前自己获取的
2、意向锁之间都是兼容的，不会产生冲突
3、意向锁存在的意义是为了更高效的获取表锁（表格中的X和S指的是表锁，不是行锁！！！）
4、意向锁是表级锁，协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某
行。

InnoDB表级锁

在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择InnoDB的理由，但个别情况下也使用
表级锁；
1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突；
2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。
如：

LOCK TABLE user READ；读锁锁表
LOCK TABLE user WRITE; 写锁锁表

事务执行…

COMMIT/ROLLBACK; 事务提交或者回滚
UNLOCK TABLES; 本身自带提交事务，释放线程占用的所有表锁

死锁

MyISAM 表锁是 deadlock free 的， 这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，即锁的粒度比较小，这就决定了在 InnoDB 中发生死锁是可能的。

mysql> select * from test_dead_lock where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死锁问题一般都是我们自己的应用造成的，和多线程编程的死锁情况相似，大部分都是由于我们多个线程在获取多个锁资源的时候，获取的顺序不同而导致的死锁问题。因此我们应用在对数据库的多个表做更新的时候，不同的代码段，应对这些表按相同的顺序进行更新操作，以防止锁冲突导致死锁问题。

锁的优化建议

1.尽量使用较低的隔离级别
2.设计合理的索引并尽量使用索引访问数据，使加锁更加准确，减少锁冲突的机会提高并发能力
3.选择合理的事务大小，小事务发生锁冲突的概率小
4.不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序
存取表中的行。这样可以大大减少死锁的机会
5.尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
6.不要申请超过实际需要的锁级别
7.除非必须，查询时不要显示加锁

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

amp

MySQL行锁、表锁&间隙锁 的相关文章

随机推荐

  • 关于java里的Collections工具类的max和min以及Arrays工具的二分查找。

    标题和沙雕 xff0c 很乱 xff1a 本文主要介绍两个在Java util里的工具类里的一小部分小小的方法 xff1a Collections类的max 和min Arrays类的asList 和二分查找 数组和集合的转换 一 Coll

  • js基本输入输出，变量，数据类型，案例。

    文章目录 1 计算机编程基础 xff1a 2 JS3 变量4 数据类型a 5种简单数据类型 xff1a 案例 b typeof获取变量类型 xff1a c 转化为数值型的放法 xff1a d 转化为字符型的方法 案例 xff1a 5 扩展阅

  • Android 7.0Settings加载主界面流程

    新人一枚 xff0c 没有整机环境 xff0c 有什么写的不对欢迎批评指正 xff0c 万分感谢 xff01 Settings主界面加载时序图 xff08 这里很多判断逻辑我省略掉了 更多的是想把加载主界面流程跑通 xff09 这张流程图将

  • C# 获取图片，Pdf中的文字

    识别图片中的文字 首先把下载好的tessdata放在自己项目的bin Debug tessdata文件夹中 附一个tessdata的下载地址 xff1a https github com tesseract ocr tessdata 命名空

  • 1449-The user specified as a definer (‘mysql.infoschema‘@‘localhost‘) does not exit

    navicat连接MySQL数据库时1449 The user specified as a definer 把本机mysql数据库5 6版本的数据备份后 xff0c 卸载5 6 版本 xff0c 安装了最新的8 0 27版本 xff0c

  • 最新版Docker Desktop安装在windows10上会出现的WSL2错误

    有科技的可以去这个帖子看 xff0c 解决WSL是最新版也无法运行docker的情况 查了很多帖子都是牛头不对马嘴 xff0c 不说废话直接上解决方案 1 Docker运行出现的问题 Docker Core HttpBadResponseE

  • C#各种官网文档链接

    目录 1 WinFrom NET Framework 2 TeeChart 3 C 教程 xff1a C 入门经典教程 4 C语言中文网 1 WinFrom NET Framework 官方文档YYDS xff0c 以前忽略了 xff0c

  • PMS（PackageManagerService）原理简单介绍，启动过程源码简单解析

    文章目录 前言1 PMS2 源码和关键方法SystemServerPackageManagerServiceParallelPackageParserPackageParser 3 细节总结4 时序图startuml代码参考材料 前言 先想

  • Android Studio 提示 Unable to load class ‘org.slf4j.LoggerFactory‘.

    将项目切换成Project模式 路径gradle wrapper gradle wrapper properties 将distributionUrl改为https services gradle org distributions gra

  • OpenCV 在 Android Studio 的使用教程

    本文内容是本人经过多次踩坑 xff0c 并参考网上众多OpenCV On Android的配置教程总结而来 xff0c 尽希望能帮助学习移动图像处理的朋友们少走弯路 xff0c 如有转载 xff0c 请标明出处 开发环境 Android S

  • 去哪儿网2019秋招笔试题

    1 题目描述 xff1a 给出一个由 100 100 之间整数组成的数组 xff0c 求其相加和最大的连续子数组 输入 一个连续整数组成的数组 输出 子数组相加的最大值 样例输入 1 2 3 2 4 6 样例输出 7 2 题目描述 xff1

  • IEEE论文参考文献引用格式

    IEEE论文参考文献引用格式 格式要求字体段落格式 期刊格式书写顺序书写要求 xff1a 作者格式的书写文章名的书写格式期刊名字简写卷号 xff0c 期号 会议格式专利书籍链接 URL 了解一个期刊书写格式最快方法 xff0c 请先进入该期

  • python使用numpy加载和保存txt文件

    python使用numpy加载和保存txt文件 问题 xff1a 1 如何将array保存到txt文件中 xff1f 2 如何将存到txt文件中的数据读出为ndarray类型 xff1f 解决 xff1a 直接用numpy中的方法 1 nu

  • HTML标签，CSS选择器，属性，盒子模型，浮动

    文章目录 HTML标签 xff0c 表格 xff0c 表单HTML 标签 表格 table表单 form1 表单域 xff0c 表单元素 xff0c 提示信息 CSS选择器 xff0c 属性 xff0c 显示模式 xff0c 背景图 xff

  • SVM连续值预测

    SVM连续值预测 分类问题回归问题一 导入库和数据二 数据预处理三 模型训练和评估 使用svm既可以实现分类问题 xff0c 即输出是标签的种类 xff0c 例如手写数字识别 Iris鸢尾花分类 xff0c 同时也能实现连续值的预测 xff

  • 最新解决git拉取远程仓库失败问题：Failed to connect to github.com port 443: Timed out.

    最新解决git拉取远程仓库失败问题 xff1a Failed to connect to github com port 443 Timed out 本地git拉取 pull 或抓取 fetch 远程github仓库出现 Failed to

  • 回溯算法及剪枝

    回溯算法及剪枝 理论基础模板框架实例思路 剪枝 回溯算法的本质是暴力穷举 xff0c 即使用递归控制for循环嵌套的数量 xff0c 本身不是一个高效的算法 尽管可以使用剪枝来提高效率 xff0c 但是还是改不了穷举的本质 回溯法 xff0

  • MySQL索引的底层实现原理

    索引的底层实现原理 数据库索引是存储在磁盘上的 xff0c 当数据量大时 xff0c 就不能把整个索引全部加载到内存了 xff0c 只能逐一加载每一个磁盘块 xff08 对应索引树的节点 xff09 xff0c 索引树越低 xff0c 越

  • MySQL事务

    事务概念 一个事务是由一条或者多条对数据库操作的SQL语句所组成的一个不可分割的单元 xff0c 只有当事务中的所有操作都正常执行完啦 xff0c 整个事务才会被提交给数据库 xff1b 如果有部分事务处理失败 xff0c 那么事务就要回退

  • MySQL行锁、表锁&间隙锁

    事务隔离级别的实现原理 xff1a 锁 表级锁 amp 行级锁 表级锁 xff1a 对整张表加锁 开销小 xff0c 加锁快 xff0c 不会出现死锁 xff1b 锁粒度大 xff0c 发生锁冲突的概率高 xff0c 并发度低 行级锁 xf

热门标签