RDS for MySQL InnoDB 行锁等待和锁等待超时的处理

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: RDS for MySQL InnoDB 行锁等待和锁等待超时的处理   1. Innodb 引擎表行锁等待和等待超时发生的场景 2.Innodb 引擎行锁等待情况的处理 2.1 Innodb 行锁等待超时参数 innodb_lock_wait_timeout 2.2 大量

RDS for MySQL InnoDB 行锁等待和锁等待超时的处理

 

1. InnoDB 引擎表行锁等待和等待超时发生的场景

2.InnoDB 引擎行锁等待情况的处理

2.1 InnoDB 行锁等待超时参数 innodb_lock_wait_timeout

2.2 大量行锁等待和行锁等待超时的处理


1. InnoDB 引擎表行锁等待和等待超时发生的场景

当一个 RDS for MySQL 连接会话等待另外一个会话持有的互斥行锁时,会发生 InnoDB 引擎表行锁等待情况。

通常情况下,持有该互斥行锁的会话(连接)会迅速的执行完相关操作并释放掉持有的互斥锁(事务提交或者回滚),进而等待的会话在行锁等待超时时间到来前获得该互斥行锁,进行下一步操作。

但在某些情况下,比如一个实例未感知到的来自客户端应用的数据库会话中断,持有该互斥行锁的会话长时间不释放该互斥行锁,此时如果有其他会话申请该互斥行锁,则会导致大量的行锁等待与行锁等待超时。

2. InnoDB 引擎行锁等待情况的处理 

本文提供的检查和处理方法,仅当正在发生 InnoDB 行锁等待的情况下才成立;因为 InnoDB 行锁等待默认超时时间为 50 秒,因此通常情况下不容易观察到行锁等待现场,可以通过将 innodb_lock_wait_timeout 参数设置为较大值来复现问题(生产环境不推荐使用过大的 innodb_lock_wait_timeout 参数值)。

2.1. InnoDB 行锁等待超时参数 innodb_lock_wait_timeout

# 参数 默认值 最小值 最大值 说明
1 innodb_lock_wait_timeout 50 1 1073741824 获取Innodb 行锁的等待时间,单位秒。可在会话级别设置

该参数控制 Innodb 行锁等待的超时时间,单位为秒,RDS 实例该参数的默认值为 50(秒)。

等待互斥锁的会话在等待 50 秒后会退出锁等待状态并返回下面的错误,这个行为称之为 InnoDB 引擎表行锁等待超时。

 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 

 innodb_lock_wait_00.png

可以通过下面的命令查看当前会话和全局的参数设置。

show variables like 'innodb_lock_wait_timeout'; -- 查看当前会话

show global variables like 'innodb_lock_w%'; -- 查看全局设置

该参数支持在会话级别修改,方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间,如下:

set innodb_lock_wait_timeout=1000; --设置当前会话 Innodb 行锁等待超时时间,单位秒

innodb_lock_wait_01.png

 

2.2. 大量行锁等待和行锁等待超时的处理

如果行锁等待和行锁等待超时持续发生,并且导致当前应用运行异常,那么需要获取到一直持有行锁的会话,并且终止该会话来释放持有的锁(会话对应的事务会回滚)。

2.2.1 检查导致锁等待和锁超时的会话

  • 注:
    下面的方法必须在行锁等待正在发生的时候进行检查。

方法 1: 通过 DMS  实例信息   Innodb 锁等待查看,如下图:

innodb_lock_wait_02.png

方法 2:通过 DMS  实例信息  实例会话查看,如下图

innodb_lock_wait_04.png

方法 3: 在 DMS 无法登录的情况下,可以通过执行下面的查询,获得导致行锁等待和行锁等待超时的会话。

# RDS for MySQL 5.6 / 5.7select l.* from ( select
 'Blocker' role,
    p.id,
    p.user,
    left(p.host, locate(':', p.host) - 1) host,
    tx.trx_id,
    tx.trx_state,
    tx.trx_started,
 timestampdiff(second, tx.trx_started, now()) duration,
 lo.lock_mode,
 lo.lock_type,
 lo.lock_table,
 lo.lock_index,
    tx.trx_query,
    lw.requesting_trx_id Blockee_trx
from
    information_schema.innodb_trx tx,
    information_schema.innodb_lock_waits lw,
 information_schema.innodb_locks lo,
    information_schema.processlist p
where
    lw.blocking_trx_id = tx.trx_id
 and p.id = tx.trx_mysql_thread_id
 and lo.lock_id = lw.blocking_lock_id
union
select
    'Blockee' role,
    p.id,
    p.user,
    left(p.host, locate(':', p.host) - 1) host,
    tx.trx_id,
    tx.trx_state,
    tx.trx_started,
 timestampdiff(second, tx.trx_started, now()) duration,
 lo.lock_mode,
 lo.lock_type,
 lo.lock_table,
 lo.lock_index,
    tx.trx_query,
	null
from
    information_schema.innodb_trx tx,
    information_schema.innodb_lock_waits lw,
 information_schema.innodb_locks lo,
    information_schema.processlist p
where
    lw.requesting_trx_id = tx.trx_id
 and p.id = tx.trx_mysql_thread_id
 and lo.lock_id = lw.requested_lock_id) l
 order by role desc, trx_state desc;

比如:

对于复杂的多个会话相互行锁等待情况,建议先终止 Role 为 Blocker 且 trx_state 为 RUNNING 的会话;终止后再次检查,如果仍旧有行锁等待,再终止新结果中的 Role 为 Blocker 且 trx_state 为 RUNNING 的会话。 

2.2.2 处理导致行锁等待和行锁等待超时的会话

对于标识为 Blocker 的会话(持有锁阻塞其他会话的 DML 操作,导致行锁等待和行锁等待超时),确认业务可以接受其对应的事务回滚的情况下,可以将其终止。

比如,可以通过 Kill 命令来今后会话终止。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
3月前
|
SQL 存储 关系型数据库
滴滴面试:明明 mysql 加的是 行锁,怎么就变 表锁 了?
滴滴面试:明明 mysql 加的是 行锁,怎么就变 表锁 了?
|
4月前
|
负载均衡 算法 关系型数据库
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL集群架构负载均衡故障排除与解决方案
本文深入探讨 MySQL 集群架构负载均衡的常见故障及排除方法。涵盖请求分配不均、节点无法响应、负载均衡器故障等现象,介绍多种负载均衡算法及故障排除步骤,包括检查负载均衡器状态、调整算法、诊断修复节点故障等。还阐述了预防措施与确保系统稳定性的方法,如定期监控维护、备份恢复策略、团队协作与知识管理等。为确保 MySQL 数据库系统高可用性提供全面指导。
|
4月前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
4月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
9月前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
771 2
|
10月前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
325 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
10月前
|
Java 关系型数据库 MySQL
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
544 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
|
10月前
|
数据可视化 关系型数据库 MySQL
【IDEA】配置mysql环境并创建mysql数据库
【IDEA】配置mysql环境并创建mysql数据库
1551 0
|
11月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
1138 15
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案

热门文章

最新文章

推荐镜像

更多
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等

登录插画

登录以查看您的控制台资源

管理云资源
状态一览
快捷访问