(mysql)table metadata lock原因及解决

news/2024/2/28 16:33:12

背景

在项目的一次需求中,需要对一个表增加字段,然而在执行增加字段的sql语句时,卡住了很久都没提交到Mysql完成,而此时对外接口服务请求也卡住了,这时中断卡住的alter table 语句,服务慢慢恢复正常,如果不搞清楚这个问题的根源,不敢增加字段,因为会直接影响到服务

排查

通过show processlist 查看到在alter table语句执行卡住过程中,累计了大量状态为 Waiting for table metadata lock 的记录

然后查看当前的事务状态 执行 select * from information_schema.innodb_trx\G

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************trx_id: 421408771164000trx_state: RUNNINGtrx_started: 2019-07-02 14:27:09trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 11688....

发现了其中一条已经运行了很久的事务,我怀疑跟这个运行很久的而且没有提交的事务有关。

测试还原

在本地mysql开多个终端测试

session 1: 开启事务,执行select 语句,但不提交事务

mysql> begin;Query OK,0 rows affected (0.00 sec)mysql>select*from t1;+------+| c1   |+------+|1|+------+1 row inset(0.00 sec)

session 2:执行增加字段sql

mysql> alter table t1 add c2 int;

执行被阻塞了

mysql> show processlist;+----+------+-----------+------+---------+------+---------------------------------+---------------------------+|Id|User|Host| db   |Command|Time|State|Info|+----+------+-----------+------+---------+------+---------------------------------+---------------------------+|27| root | localhost | test |Query|141|Waitingfor table metadata lock | alter table t1 add c2 int ||29| root | localhost | test |Query|0| starting                        | show processlist          ||30| root | localhost | test |Sleep|210||NULL|+----+------+-----------+------+---------+------+---------------------------------+---------------------------+

可以看到alter table语句的状态为Waiting for table metadata lock

session 3 : 再次查询t1表

mysql>select*from t1;

也被阻塞了

mysql> show processlist;+----+------+-----------+------+---------+------+---------------------------------+---------------------------+|Id|User|Host| db   |Command|Time|State|Info|+----+------+-----------+------+---------+------+---------------------------------+---------------------------+|27| root | localhost | test |Query|141|Waitingfor table metadata lock | alter table t1 add c2 int ||28| root | localhost | test |Query|8|Waitingfor table metadata lock | select * from t1          ||29| root | localhost | test |Query|0| starting                        | show processlist          ||30| root | localhost | test |Sleep|210||NULL|+----+------+-----------+------+---------+------+---------------------------------+-------------------

select * from t1 再次查询t1表也是 Waiting for table metadata lock状态,说明由于 metadata lock的存在,会导致后面正常的查询都会因为等待锁而阻塞

再查看当前事务运行状态:

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************trx_id: 421408771166760trx_state: RUNNINGtrx_started: 2019-08-02 15:34:41trx_mysql_thread_id: 30

可以看到,session1的事务由于还没提交,所以这里能看到它的状态还是running

这时我们commit session1的事务,看看效果

session 1:

mysql>select*from t1;+------+| c1   |+------+|1|+------+1 row inset(0.00 sec)mysql> commit;Query OK,0 rows affected (0.00 sec)

session 2:

mysql> alter table t1 add c2 int;Query OK,0 rows affected (30.51 sec)
Records:0  Duplicates:0  Warnings:0

session 3:

mysql>select*from t1;+------+| c1   |+------+|1|+------+1 row inset(7.56 sec)

可以看到session1的事务提交后,session2 和session3 都正常执行了, 他们完成的时间分别是30秒和7秒

项目 autocommit 的设置

通过上面的还原测试,可以知道是由于事务没有提交而给表加了锁,导致后面alter语句因为等待锁而阻塞,从而影响后面的正常请求。

那说明我们的项目是默认开启了事务吗?

继续排查,项目是使用flask-sqlchemy的插件来管理mysql接入,然后查了下文档

在实例化sqlchemy的时候,会创建一个用于跟Mysql交互的session对象,看看源码

# db是这样使用的
db =SQLAlchemy()
db.__init__(app)....# 看看SQLAlchemy里面的session是怎么创建的classSQLAlchemy(object):def__init__(self, app=None, use_native_unicode=True, session_options=None,metadata=None, query_class=BaseQuery, model_class=Model,engine_options=None):...self.session =self.create_session(session_options)...defcreate_session(self, options):...return orm.sessionmaker(class_=SignallingSession, db=self,**options)# session 使用到是SignallingSession 这个类 classSignallingSession(SessionBase):...def__init__(self, db, autocommit=False, autoflush=True,**options):...

从 SignallingSession类的定义看来,autocommit=False,说明默认都给所有的sql执行开启事务,也就是说,哪怕是纯select语句,不需要加锁的select,我们的项目默认也需要开启事务,这对于Mysql MVCC的版本控制来说,是没必要的。

解决办法:就是在实例化SQLAlchemy的时候,给一个参数,修改的session的autocommit=True:

db =SQLAlchemy(session_options={"autocommit":True})
db.__init__(app)

关于 table metadata lock

来自官网的介绍:

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends.

意思就是为了保证事务的串行执行,而启用的一个锁,这个锁只会在事务结束的时候释放,因此在事务提交或回滚钱,任何对这个表做的DDL操作,都是会阻塞的

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

这个 Metadata lock 是MySQL在5.5.3版本后引入了,为的是防止5.5.3以前的一个bug的出现:

当一个会话在主库执行DML操作还没提交时,另一个会话对同一个对象执行了DDL操作如drop table,而由于MySQL的binlog是基于事务提交的先后顺序进行记录的,因此在从库上应用时,就出现Q了先drop table,然后再向table中insert的情况,导致从库应用出错。

总结

  • 为了事务的串行话,和数据一致性, Mysql会对打开事务进行DML的表加上table metadata lock, 在事务提交前,其他的DDL操作会阻塞

  • 对于主要是查询数据的项目来说,默认不开启事务即可,如果确实需要,程序上手动开启事务

  • 需要使用到事务时,也要尽量缩小事务的运行时间,一个事务中不要包含太多的语句

  • 程序上对任何错误异常状况一定要捕捉后,回滚事务,否则事务脱离程序,只能等事务自己超时,手动关闭事务或者重启服务释放锁了


http://www.ppmy.cn/news/10338.html

相关文章

Python 字符串连接的七种方式

1.’’ 号连接用 ‘’连接字符串应该是最基本的方式了,话不多说,直接上代码。>>> text1 "Hello" >>> text2 "World" >>> text1 text2 HelloWorld优点:容易记忆。缺点:性能较差…

spring mvc为multipart/form-data添加JSON消息转换器

在我的Spring MVC服务器中,我想要接收包含文件(图像)和一些JSON元数据的multipart / form-data请求。 我可以在JSON部分具有Content-Typeapplication/json的情况下构建格式良好的多部分请求。 Spring服务的形式如下: RequestMapping(value MY_URL, met…

多线程(4)

文章目录1.单例模式2. 阻塞队列3. 定时器4.线程池前言 :   前面的一些文章,我们已经将有关多线的基础知识了解了, 下面我们来写一些关于多线程的代码案例,来强化对多线程的理解,另外通过这些案例补充一下其他方面的知识。 1.单例…

Visio 学习笔记 —— Visio 的键盘快捷方式

Visio 学习笔记 —— Visio 的键盘快捷方式常用热键常用对话框文本操作快速访问工具栏参考资料常用热键 操作热键指针工具Ctrl 1文本工具Ctrl 2连接线Ctrl 3连接点工具Ctrl Shift 1裁剪工具Ctrl Shift 2文本块工具Ctrl Shift 4组合所选的形状Ctrl G解散组合Ctrl Sh…

metasploitable3安装之利用vagrant

下载box, https://app.vagrantup.com/rapid7/boxes/metasploitable3-ub1404 下载后重命名为metasploitable3-ub1404 以管理员启动git bash,进入到下载的box文件所在的目录, 直接: vagrant init metasploitable3-ub1404启动&#…

vue的过渡动画(有vue的动画库和ui库的介绍)

一、概念 Vue 在插入、更新或者移除 DOM 时&#xff0c;提供多种不同方式的应用过渡效果。 二、默认过渡 <template><div><button click"isShow!isShow">显示/隐藏</button><transition appear><h1 v-show"isShow" cl…

性能优化系列之『HTTP-2 :升级HTTP-2的好处有哪些?如何升级?』

文章の目录一、HTTP/2 概念二、HTTP/2 优点三、HTTP/2 站点的优势四、在 Nginx 上启用 HTTP/21、升级 OpenSSL2、重新编译3、验证 HTTP/24、浏览器请求截图写在最后一、HTTP/2 概念 HTTP/2&#xff08;超文本传输协议第2版&#xff0c;最初命名为 HTTP 2.0&#xff09;&#x…

软件测试之维护性测试

维护性测试用于评估系统能够被预期的维护人员修改的有效性和效率的程度&#xff0c;可从模块化、可重用性、易分析性、易修改性、易测试性、易维护性 1)模块化&#xff1a;评估由独立组件组成的系统或计算机程序&#xff0c;其中一个组件的变更对其他组件的影响大小程度&#x…

【unity3D】Audio Source组件

&#x1f497; 未来的游戏开发程序媛&#xff0c;现在的努力学习菜鸡 &#x1f4a6;本专栏是我关于游戏开发的学习笔记 &#x1f236;本篇是unity的Audio Source组件 Audio Source组件常用的属性解释&#xff1a; AudioClip&#xff1a;在这里指定需要播放的音频片段。Output …

基于java+Springboot操作系统教学交流平台详细设计实现

基于javaSpringboot操作系统教学交流平台详细设计实现 博主介绍&#xff1a;5年java开发经验&#xff0c;专注Java开发、定制、远程、文档编写指导等,csdn特邀作者、专注于Java技术领域 作者主页 超级帅帅吴 Java毕设项目精品实战案例《500套》 欢迎点赞 收藏 ⭐留言 文末获取源…

【地铁上的Redis与C#】数据类型--string类型数据的扩展操作

本篇文章我们来讲一下string类型数据的扩展操作&#xff0c;我们先来看一下假设场景。 场景一 在大型企业级应用中&#xff0c;分表操作是基本操作&#xff0c;也是常见操作&#xff0c;用多张表存储同类型的数据&#xff0c;那么这里就存在一个问题&#xff0c;主键必须唯一…

冒泡排序的JavaScript代码实现

// 定义一个函数用于实现冒泡排序 function bubbleSort(arr) {// 定义一个变量用于表示是否发生交换的标志let swapped;// 循环遍历数组中的每一个元素for (let i 0; i < arr.length; i) {// 初始化标志变量为 falseswapped false;// 内层循环&#xff0c;用于比较相邻的两…

【Kotlin】空安全 ③ ( 手动空安全管理 | 非空断言操作符 !! | 使用 if 语句判空 )

文章目录一、非空断言操作符 !!二、使用 if 语句判空一、非空断言操作符 !! Kotlin 中的 可空类型 变量 , 在运行时 可以选择 不启用 安全调用 操作 , 在调用 可空类型 变量 成员 与 方法 时 , 使用 非空断言操作符 !! , 如果 可空类型 变量为 空 , 则 直接抛出 空指针异常 K…

用 Python selenium爬取股票新闻并存入mysql数据库中带翻页功能demo可下载

用 Python selenium爬取实时股票新闻并存入mysql数据库中1.分析需求2.创建表3.分析需要爬取的网页内容4.python里面selenium进行爬虫操作1.添加包2.连接数据库3.selenium爬虫前配置4.对股票新闻内容爬取并存入mysql中5.翻页功能6.运行程序首先我们先明确我们的主要目标就是要爬…

前后端的身份认证

1、Web 开发模式 目前主流的 Web 开发模式有两种&#xff0c;分别是&#xff1a; 基于服务端渲染的传统 Web 开发模式基于前后端分离的新型 Web 开发模式 1.1、服务端渲染的 Web 开发模式 服务端渲染的概念&#xff1a;服务器发送给客户端的 HTML 页面&#xff0c;是在服务器…

【Java】包装类型与引用类型的使用场景

前言 Java的基本数据类型总共有8种&#xff0c;包括3类&#xff1a;数值型&#xff0c;字符型&#xff0c;布尔型&#xff0c;其中 数值型&#xff1a; 整数类型&#xff1a;byte、short、int、long浮点类型&#xff1a;float、double 字符型&#xff1a;char 布尔型&#x…

2022年5大平台编程语言排行榜(Tiobe、Github、Stack Overflow、IEEE Spectrum、PYPL)

排行榜 名次TiobeGithubStackOverflowIEEEPYPL综合五家第1名PythonJavaScriptJavaScriptPythonPythonPython第2名CPythonHTML/CSSCJavaJavaScript第3名CJavaSQLCJavaScriptJava第4名JavaTypescriptPythonC#C#C/C#并列第5名C#C#TypescriptJavaC/CC/C#并列第6名Visual BasicCJav…

开源天气时钟项目删减和更新

开源天气时钟项目删减和更新&#x1f4cc;原项目开源地址&#xff1a;https://gitee.com/liuzewen/ESP8266-SSD1306-Watch-mini ✨本文只针对Arduino IDE平台代码进行删减和更新。 &#x1f4fa;按键菜单功能 &#x1f33c;天气时钟功能整体架构描述 代码中所使用的库&…

C和C++大全

第一章 编程基础 9 1.1 通俗地理解什么是编程语言 9 1.2 C语言究竟是一门怎样的语言&#xff1f; 11 1.3 C语言是菜鸟和大神的分水岭 12 1.4 进制详解&#xff1a;二进制、八进制和十六进制 13 1.5 进制转换&#xff1a;二进制、八进制、十六进制、十进制之间的转换 15 1.6 数据…

(软考)系统架构师大纲

考试要求&#xff1a; 掌握计算机硬软件与网络的基础知识;熟悉信息系统开发过程;理解信息系统开发标准、常用信息技术标准;熟悉主流的中间件和应用服务器平台;掌握软件系统建模、系统架构设计基本技术;熟练掌握信息安全技术、安全策略、安全管理知识;了解信息化、信息技术有关…
最新文章