行锁升级表锁如何避免?表锁后如何排查?

2024-04-03 20:36

本文主要是介绍行锁升级表锁如何避免?表锁后如何排查?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、那些场景会造成行锁升级表锁

说明:

InnoDB引擎3种行锁算法(Record Lock、Gap Lock、Next-key Lock)都是锁定的索引。

当触发X锁(写锁)的where条件 无索引 或 索引失效 时,查询的方式就变成全表扫描,也就是扫描所有的聚集索引记录。

为什么要把不匹配的记录也加锁呢?

这里针对的是默认的事务隔离级别:可重复读(RR),因为要解决 不可重复读 和 幻读问,所以在遍历扫描聚集索引记录时,为了防止扫描过的索引被其他事务修改(不可重复读问题) 或 间隙被其他事务插入记录(幻读问题),从而导致数据不一致,所以MYSQL的解决方案就是 把所有扫描过的索引记录和间隙都锁上,这时也就出现了我们看到的锁表。

无索引:

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

UPDATE user_info SET user_name = 'keke' WHERE remark = 'keke1'

索引失效:

explain验证:

索引失效的场景很多,我们可以用explain验证:

explain 返回的 key 不是你期望的索引,而是primary;

explain 返回的 type 是 index 或 all

MYSQL成本计算分析认为全表扫描成本更低时:

同样的SQL,传入的参数不同,explain 的结果也不同,有时会走索引、有时索引又会失效!

这里的原因是:更具传入的入参不同、导致 结果集不同。再正式扫描前、MYSQL会进行成本计算。计算走那个索引快,就算命中了索引,但是发现还不如全表扫描快,那么也会不走索引,而是进行全表扫描,这时就会导致索引失效!

关于成本计算:它是先计算不同索引的I/O成本 和 CPU成本,然后进行对比,那个成本低就选择那个执行!

二、如何避免:

1.禁止 WHERE 条件使用无索引列进行 更新/删除

2.尽可能使用聚集索引进行更新/删除

3.如果确实需要用到 非聚集索引进行更新/删除时:

使用explain检测索引是否会失效

避免对索引列进行 类型转换、函数、运算符等会造成升级的操作!

尽可能减少检索条件范围,范围越大就越可能被MySQL成本计算太高,从而导致索引失效!

4.尽可能控制事务的大小、减少锁定的时间

5.使用读已提交(RC)事务隔离级别

读已提交(RC)事务隔离级别,由于没有间隙锁(Gap Lock),所以它的加锁规则相对简单,都是针对匹配索引记录加的Record Lock、因为不用解决 不可重复读 和 幻读 问题,所以也就不存在 锁表了

三、锁表应该如何分析排查

查看InnoDB_row_lock%相关变量

show status like 'innodb_row_lock%';

字段

说明

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系统库提供的:查看事务、锁、锁等待的 数据表 来分析

系统表

说明

INNODB_TRX

查看事务

INNODB_LOCKS

查看锁

INNODB_LOCK_WAITS

查看锁等待

PROCESSLIST

查看连接情况

INNODB_TRX:

下面对 innodb_trx 表的每个字段进行解释:

字段

说明

trx_id

事务ID。只读事务和非锁事务是不会创建id的

trx_state

事务状态,有以下几种状态:RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING

trx_started

事务开始时间

trx_requested_lock_id

事务当前正在等待锁的标识,可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息

trx_wait_started

事务开始等待的时间

trx_weight

事务的权重。代表修改的行数和被事务锁住的行数。为了解决死锁,innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高,即使改变的行和锁住的行比其他事务低。

trx_mysql_thread_id

事务线程 ID,可以和 PROCESSLIST 表 JOIN

trx_query

事务正在执行的 SQL 语句。

trx_operation_state

事务当前操作状态

trx_tables_in_use

当前事务执行的 SQL 中使用的表的个数

trx_tables_locked

当前执行 SQL 的行锁数量。因为只是行锁,不是表锁,表仍然可以被多个事务读和写

trx_lock_structs

事务保留的锁数量。

trx_lock_memory_bytes

事务锁住的内存大小,单位为 BYTES

trx_rows_locked

事务锁住的记录数。包含标记为 DELETED,并且已经保存到磁盘但对事务不可见的行

trx_rows_modified

事务更改的行数。

trx_concurrency_tickets

该值代表当前事务在被清掉之前可以多少工作,由 innodb_concurrency_tickets系统变量值指定。

trx_isolation_level

当前事务的隔离级别

trx_unique_checks

是否打开唯一性检查的标识

trx_foreign_key_checks

是否打开外键检查的标识

trx_last_foreign_key_error

最后一次的外键错误信息

trx_adaptive_hash_latched

自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区,一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制,默认值为8

trx_adaptive_hash_timeout

是否为了自适应hash索引立即放弃查询锁,或者通过调用mysql函数保留它。当没有自适应hash索引冲突,该值为0并且语句保持锁直到结束。在冲突过程中,该值被计数为0,每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区(由 innodb_adaptive_hash_index_parts参数控制),值保持为0

INNODB_LOCKS:

只有发生阻塞才会有数据,可以查看上锁的详细信息

字段

说明

lock_id

锁 ID

lock_trx_id

拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。

lock_mode

锁的模式。有如下锁类型:行级锁包括:S、X、IS、IX,分别代表:共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括:S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC,分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。

lock_type

锁的类型

lock_table

被锁定的或者包含锁定记录的表的名称。

lock_index

当 LOCK_TYPE=’RECORD’ 时,表示索引的名称;否则为 NULL。

lock_space

当 LOCK_TYPE=’RECORD’ 时,表示锁定行的表空间 ID;否则为 NULL。

lock_page

当 LOCK_TYPE=’RECORD’ 时,表示锁定行的页号;否则为 NULL。

lock_rec

当 LOCK_TYPE=’RECORD’ 时,表示一堆页面中锁定行的数量,亦即被锁定的记录号;否则为 NULL。

lock_data

当 LOCK_TYPE=’RECORD’ 时,表示锁定行的主键;否则为NULL。

INNODB_LOCK_WAITS:

只有发生锁等待才有数据,可以通过此表找出阻塞的事务ID 和 锁ID

字段

说明

requesting_trx_id

请求的事务ID

requested_lock_id

事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。

blocking_trx_id

阻塞的事务ID

blocking_lock_id

某一事务的锁的 ID,该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。

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}

学习自:【MySQL】说透锁机制(三)行锁升表锁如何避免? 锁表了如何排查?_为什么断开连接避免锁表-CSDN博客

这篇关于行锁升级表锁如何避免?表锁后如何排查?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!


原文地址:
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.chinasem.cn/article/873913

相关文章

Python UV安装、升级、卸载详细步骤记录

《PythonUV安装、升级、卸载详细步骤记录》:本文主要介绍PythonUV安装、升级、卸载的详细步骤,uv是Astral推出的下一代Python包与项目管理器,主打单一可执行文件、极致性能... 目录安装检查升级设置自动补全卸载UV 命令总结 官方文档详见:https://docs.astral.sh/

苹果macOS 26 Tahoe主题功能大升级:可定制图标/高亮文本/文件夹颜色

《苹果macOS26Tahoe主题功能大升级:可定制图标/高亮文本/文件夹颜色》在整体系统设计方面,macOS26采用了全新的玻璃质感视觉风格,应用于Dock栏、应用图标以及桌面小部件等多个界面... 科技媒体 MACRumors 昨日(6 月 13 日)发布博文,报道称在 macOS 26 Tahoe 中

SpringBoot排查和解决JSON解析错误(400 Bad Request)的方法

《SpringBoot排查和解决JSON解析错误(400BadRequest)的方法》在开发SpringBootRESTfulAPI时,客户端与服务端的数据交互通常使用JSON格式,然而,JSON... 目录问题背景1. 问题描述2. 错误分析解决方案1. 手动重新输入jsON2. 使用工具清理JSON3.

华为鸿蒙HarmonyOS 5.1官宣7月开启升级! 首批支持名单公布

《华为鸿蒙HarmonyOS5.1官宣7月开启升级!首批支持名单公布》在刚刚结束的华为Pura80系列及全场景新品发布会上,除了众多新品的发布,还有一个消息也点燃了所有鸿蒙用户的期待,那就是Ha... 在今日的华为 Pura 80 系列及全场景新品发布会上,华为宣布鸿蒙 HarmonyOS 5.1 将于 7

Java进程CPU使用率过高排查步骤详细讲解

《Java进程CPU使用率过高排查步骤详细讲解》:本文主要介绍Java进程CPU使用率过高排查的相关资料,针对Java进程CPU使用率高的问题,我们可以遵循以下步骤进行排查和优化,文中通过代码介绍... 目录前言一、初步定位问题1.1 确认进程状态1.2 确定Java进程ID1.3 快速生成线程堆栈二、分析

Linux CPU飙升排查五步法解读

《LinuxCPU飙升排查五步法解读》:本文主要介绍LinuxCPU飙升排查五步法,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录排查思路-五步法1. top命令定位应用进程pid2.php top-Hp[pid]定位应用进程对应的线程tid3. printf"%

在Spring Boot中实现HTTPS加密通信及常见问题排查

《在SpringBoot中实现HTTPS加密通信及常见问题排查》HTTPS是HTTP的安全版本,通过SSL/TLS协议为通讯提供加密、身份验证和数据完整性保护,下面通过本文给大家介绍在SpringB... 目录一、HTTPS核心原理1.加密流程概述2.加密技术组合二、证书体系详解1、证书类型对比2. 证书获

正则表达式r前缀使用指南及如何避免常见错误

《正则表达式r前缀使用指南及如何避免常见错误》正则表达式是处理字符串的强大工具,但它常常伴随着转义字符的复杂性,本文将简洁地讲解r的作用、基本原理,以及如何在实际代码中避免常见错误,感兴趣的朋友一... 目录1. 字符串的双重翻译困境2. 为什么需要 r?3. 常见错误和正确用法4. Unicode 转换的

ubuntu系统使用官方操作命令升级Dify指南

《ubuntu系统使用官方操作命令升级Dify指南》Dify支持自动化执行、日志记录和结果管理,适用于数据处理、模型训练和部署等场景,今天我们就来看看ubuntu系统中使用官方操作命令升级Dify的方... Dify 是一个基于 docker 的工作流管理工具,旨在简化机器学习和数据科学领域的多步骤工作流。

Java Optional避免空指针异常的实现

《JavaOptional避免空指针异常的实现》空指针异常一直是困扰开发者的常见问题之一,本文主要介绍了JavaOptional避免空指针异常的实现,帮助开发者编写更健壮、可读性更高的代码,减少因... 目录一、Optional 概述二、Optional 的创建三、Optional 的常用方法四、Optio