记录一次truncate导致MySQL夯住的故障

目录

环境信息：   

故障描述：   

处理过程：

原理分析：

    show processlist结果中的system lock含义：

     truncate原理：

1. TRUNCATE 的执行流程

2、TRUNCATE 表导致数据库夯住的原因

3、 TRUNCATE 表导致数据库夯住的解决方案

4、 kill TRUNCATE 语句失败后，主从数据不一致的原因：

5、为什么TRUNCATE TABLE users 会影响其他表的SQL

6、为什么 KILL 语句无法立刻终止 TRUNCATE

TRUNCATE 与 DELETE 的区别

环境信息：   

    mysql 5.7一主两从的架构。

故障描述：   

    truncate一张3000万行的大表，数据量约为45G，同时库中执行很多其他dml和select for update，主库夯死， 应用反馈没法打开，下游的接口不能查询，数据库活跃会话数在80左右。

    通过information_schema查询事务、锁、等待的SQL被阻塞住，迟迟没有结果。通过show processlist结果显示，数据库中truncate的SQL处于system lock状态，对其他表的查询、dml的SQL部分处于Opening tables状态以及部分在正常执行。期间从information_schema.innodb_locks和information_schema.innodb_lock_waits两个视图，偶尔成功，但均没有查询到任何锁等待和锁信息。

处理过程：

    1、通过show processlist拉取truncate会话信息，kill truncate语句后，通过show processlist发现truncate语句处于killed状态，没有被清理干净。查询information_schema.innodb_trx视图发现truncate语句仍然在执行。

    2、推测是有元数据锁等待，通过show processlist拉取所有应用会话，kill所有应用会话，发现数据库中的所有会话均为killed状态，数据库仍然夯住。

    3、断开一个从库的同步做数据保护，停止应用，通过stop命令重启数据库实例失败，最终通过kill方式关闭数据库，重启数据库实例后库中所有应用会话均被清理干净，主库恢复正常，应用打开，开始恢复对外服务。

    4、数据库恢复后，检查truncate过的表，主库中该表数据行数为0，所有数据均被清理干净。而未断开同步的从库该表数据仍然为三千多万行，此时可以确认主从数据不一致。

    5、经应用校验，主库数据无问题，最终以主库数据为准，重做两台从库的备机。

本次故障原因分析：

原理分析：

    show processlist结果中的system lock含义：

  • system lock 表示 TRUNCATE TABLE 正在等待释放相关资源（如 MDL 锁）。

  • 由于需要truncate的表可能有 活跃事务 在使用，导致 TRUNCATE 无法立即获取独占 MDL 锁，从而进入 system lock 状态。

     truncate原理：

     RUNCATE TABLE 是 DDL（数据定义语言） 语句，而不是普通的 DELETE 语句。它在 MySQL 内部的执行方式 和 DELETE 不同，主要依赖 表元数据重置 及 物理数据页回收，因此执行速度更快，同时影响锁机制、事务处理和 Binlog 记录方式。

    MDL 锁不会自动超时，如果 TRUNCATE TABLE 语句 等待其他事务结束的时间过长，大量 SQL 都会排队等待，导致数据库响应变慢甚至无法对外提供服务。

1. TRUNCATE 的执行流程

当执行 TRUNCATE TABLE table_name; 时，MySQL 主要做了以下几步：

（1）获取表的元数据锁（MDL）

  • TRUNCATE TABLE 是 DDL 语句，执行时需要 获取 MDL EXCLUSIVE（排他元数据锁），以防止其他并发操作（如 SELECT、INSERT、UPDATE）。

  • 如果有并发事务或查询未提交，TRUNCATE 可能会被 阻塞，一直等到其他事务释放锁。

（2）重建表（适用于 InnoDB 引擎）

在 InnoDB 存储引擎下，TRUNCATE 不会逐行删除数据，而是 重建表结构：

  1. 删除原表的表空间文件（.ibd）：

    • TRUNCATE 直接 删除 users.ibd 文件（如果 innodb_file_per_table=ON）。

    • 释放所有数据页，表的行数归零。

  2. 重新创建一个空的新表：

    • 生成一个新的 ibd 文件，表的 AUTO_INCREMENT 计数器也会被重置（除非 innodb_autoinc_persistent=ON）。

  3. 更新数据字典：

    • InnoDB 更新 information_schema 的数据字典，重新分配新的表 ID。

   注意：

  • TRUNCATE 不能回滚，因为它直接删除表空间文件，而不像 DELETE 那样记录事务日志。

  • AUTO_INCREMENT 计数器被重置，除非 innodb_autoinc_persistent=ON。

（3）删除 & 重新创建表的相关统计信息

  • MySQL 重新计算表的统计信息，并 清空 innodb_buffer_pool 缓存 中的该表相关数据。

  • 这可能会导致 TRUNCATE 后的第一次查询变慢，因为 需要重新生成索引统计信息。

2、TRUNCATE 表导致数据库夯住的原因

    truncate语句需要获取排他元数据锁，dml语句也需要获取共享元数据锁。

可能的原因：

  • 有长事务未提交，占用了 MDL 锁。

  • 有其他 DDL 语句在执行，与 TRUNCATE 发生冲突。

  • Binlog 复制未完成，导致 TRUNCATE 进入 system lock。

 3、 TRUNCATE 表导致数据库夯住的解决方案

   1、通过FLUSH TABLES tablename; 命令先释放 table_cache 资源。

   2、通过SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME = 'tablename';检查对应表是否有元数据锁。

   3、确认没有元数据锁后再执行 TRUNCATE

   4、如果释放表元数据锁资源后执行truncate数据库还是夯住，kill该会话

   5、如果kill会话不生效，重启数据库实例

 4、 kill TRUNCATE 语句失败后，主从数据不一致的原因：

    主库 TRUNCATE 执行过程中被 kill，该truncate语句不会被回滚，只能继续执行完成，但因为kill语句，Binlog 未写入完整，从库未同步truncate语句，从中相应表并未清理。

  5、为什么TRUNCATE TABLE users 会影响其他表的SQL

    虽然 TRUNCATE TABLE users; 只影响 users 表，但由于 MDL 锁争用 和 InnoDB 内部机制，会间接影响 其他表上的操作。

1.1. TRUNCATE TABLE 触发 MDL EXCLUSIVE 锁

  • MDL（Metadata Lock）

    • TRUNCATE TABLE users; 需要获取 users 表的 MDL EXCLUSIVE 锁。

    • 该锁会等到 所有涉及 users 表的事务结束后 才能执行。

  • 问题：

    • 如果有未提交的事务在访问 users 表（如 SELECT * FROM users），那么 TRUNCATE 会被阻塞，无法立即执行。

    • 这会导致新到来的 SELECT users、INSERT users、UPDATE users 排队等待，造成 Opening tables 状态。

1.2. TRUNCATE TABLE 触发 flush & purge 影响 InnoDB

  • TRUNCATE 不是简单的 DELETE，它会触发 表重建：

    • TRUNCATE TABLE users; 重新创建 users.ibd 文件。

    • 触发 InnoDB 的 flush & purge 机制，可能导致 InnoDB 短暂冻结（stalling）。

  • 问题：

    • 如果 flush & purge 耗时较长，那么 整个 InnoDB 层会短暂变慢，导致 其他表的 SQL 也可能变慢或进入 Opening tables 状态。

1.3. Opening tables 可能与 table_cache 相关

  • MySQL 需要在 table_cache 中找到表的描述信息（.frm、.ibd 等）：

    • 如果 某个线程持有 MDL EXCLUSIVE 锁（如 TRUNCATE users），而 table_cache 需要访问 users 的元数据，就可能导致等待。

  • 问题：

    • 由于 table_cache 竞争，其他表的 SQL 也可能进入 Opening tables 状态。

    • 但 未受影响的表（未被 table_cache 竞争影响的表）仍能正常运行，所以只有部分 SQL 卡住，部分仍可执行。

    

6、为什么 KILL 语句无法立刻终止 TRUNCATE

（1）DDL 操作不受事务管理

  • TRUNCATE TABLE 不会记录 undo log，所以它 无法回滚。

  • 一旦 TRUNCATE 语句开始执行，它会 删除表数据并重建表空间，这部分操作不能简单通过 KILL 终止并回滚。

  • 即使 KILL 了会话，MySQL 仍需要等待 TRUNCATE 彻底完成，以保证表的完整性。

（2）DDL 操作涉及元数据变更

  • TRUNCATE 需要获取 元数据锁（MDL EXCLUSIVE），以阻止其他会话对表进行并发访问。

  • TRUNCATE 的执行流程通常如下：

    1. 获取 MDL 排他锁，阻止其他事务访问该表。

    2. 删除表的 .ibd 文件（如果 innodb_file_per_table=ON）。

    3. 重新创建一个新的空表。

    4. 释放 MDL 锁。

  • 如果在执行 TRUNCATE 期间 KILL 进程，MySQL 需要等待所有涉及的元数据修改完成，否则可能导致表结构损坏。

TRUNCATE 与 DELETE 的区别

对比项TRUNCATE TABLEDELETE FROM table
语句类型DDLDML
数据删除方式直接删除表空间文件，重建表按行删除，每次删除都会写入 redo log 和 undo log
事务支持不支持事务，不能回滚支持事务，可以回滚
执行速度快，O(1) 级别慢，O(n) 级别，受行数影响
Binlog 记录记录 DDL 操作记录 DELETE 影响的每一行
AUTO_INCREMENT 影响重置（除非 innodb_autoinc_persistent=ON）不会重置
索引和表统计信息重置统计信息统计信息保持不变
触发器（Trigger）不会触发触发器会触发 DELETE 相关的触发器
外键约束不允许 TRUNCATE 关联外键表DELETE 允许 

 总结：

  • 如果你想快速清空表数据，且不需要事务回滚，TRUNCATE 是更好的选择。

  • 如果只想删除部分数据，或者希望事务支持，DELETE 更合适。

     

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

二项式分布（Binomial Distribution）

二项式分布（Binomial Distribution）

二项式分布（Binomial Distribution） 定义 让我们来看看玩板球这个例子。假设你今天赢了一场比赛，这表示一个成功的事件。你再比了一场，但你输了。如果你今天赢了一场比赛，但这并不表示你明天肯定会赢。我们来分配一个…
阅读更多...
【算法工程】大模型开发之windows环境的各种安装

【算法工程】大模型开发之windows环境的各种安装

1. 背景 最近由于研究需要，我购置了两块3090显卡，以便在家中进行一些小规模的实验。为此，还更换了主机。当然，新系统上少不了要安装各种开发环境。从开发体验来看，macOS无疑更为流畅，但为了确保所有环境都能…
阅读更多...
论文阅读笔记：Denoising Diffusion Probabilistic Models (2)

论文阅读笔记：Denoising Diffusion Probabilistic Models (2)

接论文阅读笔记：Denoising Diffusion Probabilistic Models (1) 3、论文推理过程 扩散模型的流程如下图所示，可以看出 q ( x 0 , 1 , 2 ⋯ , T − 1 , T ) q(x^{0,1,2\cdots ,T-1, T}) q(x0,1,2⋯,T−1,T)为正向加噪音过程， p ( x 0 , 1 , …
阅读更多...
vscode查看文件历史git commit记录

vscode查看文件历史git commit记录

方案一：GitLens 在vscode扩展商店下载GitLens 选中要查看的文件，vscode界面右上角点击GitLens的图标，选择Toggle File Blame 界面显示当前打开文件的所有修改历史记录 鼠标放到某条记录上，可以看到记录详情，选中O…
阅读更多...
【数据挖掘】Python基础环境安装配置

【数据挖掘】Python基础环境安装配置

【数据挖掘】Python基础环境安装配置 一、摘要二、安装Python3.13.2三、安装Jupyter Notebook四、安装Numpy和Pandas以及matplotlib五、安装scikit-learn库和seaborn库 一、摘要 本文主要介绍如何在Windows上安装Python3.13.2，然后基于该Python版本安装Jupyter not…
阅读更多...
DeepSeek写打台球手机小游戏

DeepSeek写打台球手机小游戏

DeepSeek写打台球手机小游戏 提问 根据提的要求，让DeepSeek整理的需求，进行提问，内容如下： 请生成一个包含以下功能的可运行移动端打台球小游戏H5文件： 要求 可以重新开始游戏 可以暂停游戏 有白球和其他颜色的球&am…
阅读更多...
SpringMVC的执行流程剖析和源码跟踪

SpringMVC的执行流程剖析和源码跟踪

目录 一、常用组件:1、DispatcherServlet2、HandlerMapping3、Handler4、HandlerAdapter:5、ViewResolver6、View 二、SpringMVC的执行流程:1、流程图 在这里插入图片描述2、文字解析流程图3、ContextLoaderListener 三、源码跟踪1、doService()方法2、doDispatch()方法逻辑分解…
阅读更多...
LeetCode hot 100 每日一题(13)——73. 矩阵置零

LeetCode hot 100 每日一题(13)——73. 矩阵置零

这是一道难度为中等的题目&#xff0c;让我们来看看题目描述&#xff1a; 给定一个 _m_ x _n_ 的矩阵&#xff0c;如果一个元素为 0 &#xff0c;则将其所在行和列的所有元素都设为 0 。请使用 原地 算法。 提示&#xff1a; m matrix.lengthn matrix[0].length1 < m, n …
阅读更多...
ubuntu 解挂载时提示 “umount: /home/xx/Applications/yy: target is busy.”

ubuntu 解挂载时提示 “umount: /home/xx/Applications/yy: target is busy.”

问题如题所示&#xff0c;我挂载一个squanfs文件系统到指定目录&#xff0c;当我使用完后&#xff0c;准备解挂载时&#xff0c;提示umount: /home/xx/Applications/yy: target is busy.&#xff0c;具体的如图所示&#xff0c; 这种提示通常是表明这个路径的内容正在被某些进…
阅读更多...
跟着StatQuest学知识06-CNN进行图像分类

跟着StatQuest学知识06-CNN进行图像分类

目录 一、CNN特点 二、CNN应用于图像分类 &#xff08;一&#xff09;使用过滤器 &#xff08;二&#xff09;通过ReLU激活函数 &#xff08;三&#xff09;应用新的滤波器&#xff08;池化&#xff09; &#xff08;四&#xff09;输入 &#xff08;五&#xff09;输出…
阅读更多...
MATLAB 控制系统设计与仿真 - 27

MATLAB 控制系统设计与仿真 - 27

状态空间的标准型 传递函数和状态空间可以相互转换&#xff0c;接下来会举例如何有传递函数转成状态空间标准型。 对角标准型 当 G(s)可以写成&#xff1a; 即&#xff1a; 根据上图可知&#xff1a; 约当标准型 当 G(s)可以写成&#xff1a; 即&#xff1a; 根据上图…
阅读更多...
Python网络编程入门

Python网络编程入门

一.Socket 简称套接字&#xff0c;是进程之间通信的一个工具&#xff0c;好比现实生活中的插座&#xff0c;所有的家用电器要想工作都是基于插座进行&#xff0c;进程之间要想进行网络通信需要Socket&#xff0c;Socket好比数据的搬运工~ 2个进程之间通过Socket进行相互通讯&a…
阅读更多...
C++ --- 多态

C++ --- 多态

1 多态的概念 多态(polymorphism)的概念&#xff1a;通俗来说&#xff0c;就是多种形态。多态分为编译时多态(静态多态)和运⾏时多 态(动态多态)&#xff0c;这⾥我们重点讲运⾏时多态&#xff0c;编译时多态(静态多态)和运⾏时多态(动态多态)。编译时 多态(静态多态)主要就是我…
阅读更多...
MQTT的安装和使用

MQTT的安装和使用

MQTT的安装和使用 在物联网开发中&#xff0c;mqtt几乎已经成为了广大程序猿必须掌握的技术&#xff0c;这里小编和大家一起学习并记录一下~~ 一、安装 方式1、docker安装 官网地址 https://www.emqx.com/zh/downloads-and-install/broker获取 Docker 镜像 docker pull e…
阅读更多...
ROS多机通信功能包——Multibotnet

ROS多机通信功能包——Multibotnet

引言 这是之前看到一位大佬做的集群通信中间件&#xff0c;突发奇想&#xff0c;自己也来做一个&#xff0c;实现更多的功能、更清楚的架构和性能更加高效的ROS多机通信的功能包 链接&#xff1a;https://blog.csdn.net/benchuspx/article/details/128576723 Multibotnet Mu…
阅读更多...
pfsense部署四（静态路由的配置）

pfsense部署四（静态路由的配置）

目录 一 . 介绍 二 . 配置过程 一 . 介绍 pfsense开源防火墙经常在进行组网时&#xff0c;通常会用于连接不同的网络&#xff0c;在这个时候进需要给pfsense配置路由&#xff0c;而这篇文章介绍的是静态路由的配置 二 . 配置过程 拓扑图&#xff1a; 本次实验使用ensp模拟器…
阅读更多...
干货！三步搞定 DeepSeek 接入 Siri

干货！三步搞定 DeepSeek 接入 Siri

Siri高频用户福音&#xff0c;接下来仅需3步教你如何将 DeepSeek 接入 Siri&#xff01;虽然苹果公司并没有给国行产品提供 ai 功能&#xff0c;但是我们可以让自己的 iPhone 更智能一点。虽然有消息称苹果和阿里巴巴将合作为中国iPhone用户开发AI功能&#xff0c;但我们可以先…
阅读更多...
自动学习和优化过程，实现更加精准的预测和决策的智慧交通开源了

自动学习和优化过程，实现更加精准的预测和决策的智慧交通开源了

智慧交通视觉监控平台是一款功能强大且简单易用的实时算法视频监控系统。它的愿景是最底层打通各大芯片厂商相互间的壁垒&#xff0c;省去繁琐重复的适配流程&#xff0c;实现芯片、算法、应用的全流程组合&#xff0c;从而大大减少企业级应用约95%的开发成本。通过高效的实时视…
阅读更多...
DeepSeek R1 本地部署指南 (3) - 更换本地部署模型 Windows/macOS 通用

DeepSeek R1 本地部署指南 (3) - 更换本地部署模型 Windows/macOS 通用

0.准备 完成 Windows 或 macOS 安装&#xff1a; DeepSeek R1 本地部署指南 (1) - Windows 本地部署-CSDN博客 DeepSeek R1 本地部署指南 (2) - macOS 本地部署-CSDN博客 以下内容 Windows 和 macOS 命令执行相同&#xff1a; Windows 管理员启动&#xff1a;命令提示符 CMD ma…
阅读更多...
使用 Node.js 读取 Excel 文件并处理合并单元格

使用 Node.js 读取 Excel 文件并处理合并单元格

使用 Node.js 读取 Excel 文件并处理合并单元格 在现代的数据处理任务中&#xff0c;Excel 文件是一种非常常见的数据存储格式。无论是数据分析、报表生成&#xff0c;还是数据迁移&#xff0c;Excel 文件都扮演着重要的角色。然而&#xff0c;处理 Excel 文件时&#xff0c;尤…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023