【MySQL】说透锁机制(三)行锁升表锁如何避免? 锁表了如何排查?

这篇具有很好参考价值的文章主要介绍了【MySQL】说透锁机制(三)行锁升表锁如何避免? 锁表了如何排查?。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。


前言

在上文我们曾小小的提到过,在索引失效的情况下,MySQL会把所有聚集索引记录和间隙都锁上,我们称之为锁表,或叫行锁升表锁.

那么对于 行锁升表锁,有的同学误以为行锁 升级变成了 表锁,但实际上锁的类型并没有发生变化✍️,还是行锁! 只是表的所有聚集索引记录都被加上了行锁, 看起来像表锁, 所以提前澄清一下, 举个例子:

假设,表中有10万多条记录

  • 行锁升表锁
    会给10万多条索引记录加行锁, 锁的粒度小, 但开销非常大,示意图如下:
    为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java
  • 直接加 表锁
    只会加1个表锁,锁的粒度大, 但开销非常小,示意图如下:
    为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java

OK, 相信已经澄清了~ 那么对于行锁升表锁, 我们应该如何避免呢? 如果真被行锁锁表了又该如何分析排查呢? 别着急, 我们一步一步来, 干货满满, 建议先收藏!后面如果有需要了, 直接能找到这里来看.


哪些场景会造成行锁升表锁?

兵法有云:知己知彼,百战不殆!
所以在说如何避免之前,我们提前说一下哪些场景会造成行锁升表锁,建议还未看过前面两文的小伙伴先了解一下加锁规则:
【MySQL】说透锁机制(一)行锁 加锁规则 之 等值查询
【MySQL】说透锁机制(二)行锁 加锁规则 之 范围查询(你知道会锁表吗?)
那么对于看过前两篇文章的小伙伴,应该已经猜到了,场景肯定和索引有关!

没错, 就是 无索引索引失效!
那么原因呢? 你想过这里的原因吗?
为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java
解读:因为InnoDB引擎的 3种行锁算法(Record Lock、Gap Lock、Next-key Lock),都是锁定的索引,当触发X锁(写锁)的where条件无索引 或 索引失效 时, 查找的方式就会变成全表扫描,也就是扫描所有的聚集索引记录,到这我想大家都应该看懂了,但是可能还有个疑问,为什么要把不匹配的记录也加锁呢?
这里是针对于默认的事务隔离级别:可重复读(RR)事务隔离级别来说的, 因为在RR隔离级别下,需要解决不可重复读幻读问题, 所以在遍历扫描聚集索引记录时, 为了防止扫描过的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题), 从而导致数据不一致, 所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上, 这也就 发生了我们看到的锁表!💪💪💪

为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java
展开来说:

无索引
例如, 下面这个sql的 remark列 不是索引列, 如果按remark更新就是无索引更新.

update ct set abc = 1 
where remark = '阿根廷';

索引失效
索引失效的情况有很多, 我们本文不分析为什么失效, 也不会列举出所有失效的场景, 因为那不是本节的重点(我会考虑单独安排一篇详细讲解)。 这里直接用explain说话:

  • explain 返回的key不是你期望的索引, 而是PRIMARY;
  • explain 返回的typeindexall

如果同时满足上面这两个条件, 那么就说明索引失效了!

对于索引失效列几个常见的场景简单说明一下:

  • 复合索引未遵循最左前缀原则

例如,我新建一个复合索引:abc列name列,如下:

 ALTER TABLE `lock_test`.`ct`
 ADD INDEX `idx_abc_name`(`abc`, `name`);
但更新sql语句未按照最左前缀, 直接按`name=`更新,这样就会**导致索引失效**:
update ct set abc = 1 
where name = '阿根廷';

看一下explain的结果
为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java

  • like以%开头

例如,我新建一个普通索引:name列

ALTER TABLE `lock_test`.`ct`
ADD INDEX `idx_name`(`name`);

但更新sql语句使用了 like以%开头,这样也会导致索引失效

update ct set abc = 1 
where name like '%阿根廷';

看一下explain的结果
为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java

  • MySQL成本计算分析认为全表扫描成本更低时

这是比较特殊的情况. 同样的SQL, 传入的参数不同, explain的结果也不同, 有时会走索引, 但有时索引又失效! 😫
这里的原因:因为根据传入的参数不同 导致 结果集不同, 在正式扫描之前,MySQL会进行成本计算计算走哪个索引更快!结果一算,发现走索引还不如全表扫描快, 那么这时即使你用的是索引列等值 也不会走索引,会走全表扫描,这也就导致了索引失效
关于成本计算, 它是先计算不同索引的I/0成本和CPU成本, 然后进行对比, 哪个成本低就采用哪个索引来执行! 当然, 成本计算并不会真实执行, 所以速度非常快, 在上文【范围查询】时曾给过一个小的示例说明,这里不再重复赘述!
为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java

当然,索引失效的情况还有很多, 这里只是举几个例子让大家学会用explain分析, 如果不够过瘾,我后面紧接着会更新索引相关文章!记得关注我哦!


如何避免?

此时, 咱们已经清楚的知道了 可能造成 行锁升表锁 的场景,那么应对起来也就更有底气了,我的建议是:

  • 禁止where条件使用无索引列进行更新/删除
    这是我们最应该做到的!除了会锁表,性能也是真的不好!
  • 尽可能使用聚集索引进行更新/删除
    这是我们能做到的最优做法
  • 确实需要使用非聚集索引 进行更新/删除,需要确认:
    • 使用explain检查是否会索引失效!
    • 避免对 索引列 进行类型转换、函数、运算符等会造成升级的情况!
    • 尽可能减少检索条件范围, 范围越大就越可能被MySQL成本计算太高,从而导致索引失效!
  • 尽可能控制事务大小,减少锁定时间
    涉及事务加锁的sql语句尽可能放在事务最后执行!
  • 推荐使用读已提交(RC)事务隔离级别
    这条非常重要!
    对于读已提交(RC)事务隔离级别,由于没有间隙锁(Gap Lock),所以它的加锁规则相当简单,都是针对匹配索引记录加Record Lock,因为不用解决不可重复读幻读问题,所以也就不存在 锁表了。

    前面两文咱们说的都是基于可重复读(RR)事务隔离级别,因为引入了间隙锁(Gap Lock),所以情况变的复杂, 而在RC下, 情况变的简单.


如何分析排查?

咱们只能做到尽可能避免, 根据墨菲定律:只要有可能 就一定会发生!
所以我们必须掌握锁表应该如何分析排查

查看InnoDB_row_lock%相关变量

show status like 'innodb_row_lock%';

为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java

字段 说明
Innodb_row_lock_current_waits 当前正在等待锁定的数量
Innodb_row_lock_time 等待总时长: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg 等待平均时长: 每次等待所花平均时间
Innodb_row_lock_time_max 从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits 等待总次数: 系统启动后到现在总共等待的次数

从上述值,我们可以看出我们行锁的整体情况,有助于我们分析。

查看 INFORMATION_SCHEMA系统库

我们可以通过 INFORMATION_SCHEMA系统库提供的:查看事务锁等待的 数据表 来分析.

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查看连接情况
select * from INFORMATION_SCHEMA.PROCESSLIST;
  • 通过 INNODB_LOCK_WAITS 可以找出阻塞的事务id和锁id
-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
字段 说明
requesting_trx_id 请求的事务id
requested_lock_id 请求的锁id
blocking_trx_id 阻塞的事务id
blocking_lock_id 阻塞的锁id

我这里模拟一个锁等待,然后查询,可以清晰的看到谁阻塞了谁
为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java

温馨提示:只有发生锁等待才有数据

  • 通过 INNODB_LOCKS 可以查看上锁的详细信息
-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;

这和我们通过show engine innodb status\G;看到的结果类似, 略…, 也是只有发生阻塞才会有数据.

  • 通过 INNODB_TRX 可以查看 事务的状态、阻塞开始时间、阻塞的sql、线程id等等
 -- 查看事务
 select * from INFORMATION_SCHEMA.INNODB_TRX;

这个表很关键, 对于我们排查来说必不可少, 一些关键字段说明如下:

字段 说明
trx_id 事务id
trx_state 事务状态,LOCK WAIT代表发生了锁等待
trx_started 事务开始时间
trx_requested_lock_id 请求锁id, 事务当前正在等待锁的标识,可以join关联INNODB_LOCKS.lock_id
trx_wait_started 事务开始锁等待的时间
trx_weight 事务的权重
trx_mysql_thread_id 事务线程 ID,可以join关联PROCESSLIST.ID
trx_query 事务正在执行的 SQL 语句
trx_operation_state 事务当前操作状态
trx_isolation_level 当前事务的隔离级别

当发生阻塞时,我们来看一下数据:
为什么断开连接避免锁表,mysql,数据库,大数据,mysql,行锁升表锁,java
一目了然,哪个SQL从什么时间开始阻塞,线程id是多少,看的一清二楚.

  • 通过 PROCESSLIST 可以查看连接情况
-- 查看连接情况
select * from INFORMATION_SCHEMA.PROCESSLIST;

通过这个表,我们可以定位到事务所在的主机.

字段 说明
ID 线程ID, 可以JOIN INNODB_TRX.trx_requested_lock_id
USER 连接用户
HOST 连接主机 ip:port
DB 连接的数据库
  • 如何kill某个事务?

通过对上面的表进行查询, 当我们发现某个事务阻塞了很多事务, 并且执行时间很长时, 我们可以手动中止它, 只需要找到INNODB_TRX.trx_mysql_thread_id,然后调用kill命令:

kill {INNODB_TRX.trx_mysql_thread_id}

总结

本文主要介绍了:

  • 哪些场景会造成行锁升表锁
    无索引索引失效
  • 如何避免
    建议中最重要的一条:尽可能使用 读已提交(RC)事务隔离级别
  • 如何分析排查
    最重要的两个分析表:INFORMATION_SCHEMA.INNODB_TRXINFORMATION_SCHEMA.INNODB_LOCK_WAITS,以及手动中止 kill {INNODB_TRX.trx_mysql_thread_id}

最后

如果感觉不错,欢迎订阅本专栏,后面还有更详细的MySQL知识陆续放出。
关注我 天罡gg 分享更多干货: https://blog.csdn.net/scm_2008
大家的「关注 + 点赞 + 收藏」就是我创作的最大动力!谢谢大家的支持,我们下文见!文章来源地址https://www.toymoban.com/news/detail-846602.html


到了这里,关于【MySQL】说透锁机制(三)行锁升表锁如何避免? 锁表了如何排查?的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL中的表锁,行锁,排它锁,共享锁

    表锁与行锁 1 ) 概念 在使用mysql的时候,如果同时向 mysql 里边批量进行 更新 , 插入 或 删除 动作 数据库里的数据不会出问题, 在 mysql内部,它其实自带了一个锁的功能 而它内部有的是用了锁,有的没有用锁,没用锁的需要咱们利用锁来自行处理 mysql 从范围的角度来讲,支

    2024年02月03日
    浏览(36)
  • 数据库update语句到底是行锁还是表锁?

    先说结论: 无索引的情况下,如果不走主键,那么update为表锁;有索引的情况下,走索引或者走主键(效果一样),那么update变为行锁。 大致把问题分为两种情况:有索引或者没有索引 先建一个没有索引的表,模拟一些数据,并且把事务自动提交关掉。 我们先在第一个查询

    2023年04月09日
    浏览(49)
  • 【MySQL】事务隔离机制 -- 必须说透

    如何控制 并发 是数据库领域中非常重要的问题之一,MySQL为了解决并发带来的问题,设计了事务隔离机制、锁机制、MVCC机制等,用一整套机制来解决并发问题,本文主要介绍事务隔离机制。 事务transaction(简写tx),在数据库中,事务是指一组逻辑操作,这些操作要么全部执

    2024年02月15日
    浏览(38)
  • 华纳云:MySQL中如何书写update避免表锁

    要在 MySQL 中编写 UPDATE 语句以避免表锁定,您可以考虑以下几个方面: 1.使用合适的索引: 确保您的表上存在适当的索引,这将有助于提高 UPDATE 查询的性能并减少锁定时间。通过为 WHERE 条件列创建索引,MySQL 可以更快地定位要更新的行,而不需要锁定整个表。 2.减少影响的

    2024年02月16日
    浏览(37)
  • Hive表锁机制原理以及各种场景的解决方案

    hive 锁机制(S锁,X锁) 是由hive的事务管理器出发,锁的原理是 一张表简称A表,我们对A表做查询操作的时候,就会获取到 A表的S锁(共享锁), 如果对A表做alter 等其他操作就会获取A表的X锁(排他锁) 如果A表同时拥有S锁和X锁,A表就会死锁。死锁后的现象就是做drop truncate 等操作会

    2024年02月02日
    浏览(30)
  • MySQL行锁浅析

    MySQL是非常流行的关系型数据库,许多系统都使用它来存储和管理数据。在高并发环境下,为了保证数据的一致性和可靠性,数据库需要实现并发控制,其中包括锁机制。MySQL提供了两种锁类型,一种是表级锁,另一种是行级锁。本文将深入探讨MySQL中的行级锁,包括行锁概念

    2024年02月07日
    浏览(39)
  • Mysql行锁失效情况分析

    数据库版本:使用Mysql8.0.32作为测试版本,InnoDB引擎。 本文阐述Mysql行锁失效升级为表的场景。 使用远程终端打开两个数据库服务器(Linux)窗口,使用如下命令登录: 回车,输入数据库密码. 测试数据库为: test 关闭自动提交 测试数据表为: 其中id是主键。 插入两条id分别为

    2024年02月02日
    浏览(43)
  • Mysql InnoDB行锁深入理解

    Record Lock 称为记录锁,锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的: 当一个事务对一条记录加了 S 型记录锁后,其他事务也可以继续对该记录加 S 型记录锁(S 型与 S 锁兼容),但是不可以对该记录加 X 型记录锁(S 型与 X 锁不兼容); 当一个事务对一条记录加了

    2024年01月22日
    浏览(50)
  • MySQL中锁的简介——表级锁-表锁

    2024年02月15日
    浏览(34)
  • MySQL锁(读锁、共享锁、写锁、S锁、排它锁、独占锁、X锁、表锁、意向锁、自增锁、MDL锁、RL锁、GL锁、NKL锁、插入意向锁、间隙锁、页锁、悲观锁、乐观锁、隐式锁、显示锁、全局锁、死锁)

    本文说明的是MySQL锁,和操作系统或者编程语言的锁无关。 作用:在并发情况下让数据正确的读写。 优点:并发情况下对数据读写可控,防止出错。 缺点:降低性能、增加难度。 数据操作类型划分 读锁(共享锁、S锁) 写锁(排它锁、独占锁、X锁) 粒度划分 表级锁 S锁、

    2024年03月10日
    浏览(47)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包