一文教你学会定位线上MySQL锁超时问题

前言：

昨晚我正在床上睡得着着的，突然来了一条短信。

什么？线上的订单无法取消！

我赶紧登录线上系统，查看业务日志。

发现有MySQL锁超时的错误日志。

不用想，肯定有另一个事务正在修改这条订单，持有这条订单的锁。

导致当前事务获取不到锁，一直等待，直到超过锁超时时间，然后报错。

既然问题已经清楚了，接下来就轮到怎么排查一下到底是哪个事务正在持有这条订单的锁。

好在MySQL提供了丰富的工具，帮助我们排查锁竞争问题。

现场复现一个这个问题：

创建一张用户表，造点数据：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

事务1，更新id=1的用户姓名，不提交事务：

begin;
update user set name='一灯' where id=1;

事务2，删除id=1的数据，这时候会产生锁等待：

begin;
delete from user where id=1;

接下来，我们就通过MySQL提供的锁竞争统计表，排查一下锁等待问题：

先查一下锁等待情况：

select * from information_schema.innodb_lock_waits;

可以看到有一个锁等待的事务。

然后再查一下正在竞争的锁有哪些？

select * from information_schema.innodb_locks;

可以看到，MySQL统计的非常详细：

lock_trx_id 表示事务ID

lock_mode 表示排它锁还是共享锁

lock_type 表示锁定的记录，还是范围

lock_table 锁的表名

lock_index 锁定的是主键索引

再查一下正在执行的事务有哪些？

select * from information_schema.innodb_trx;

可以清楚的看到正在执行的事务有两个，一个状态是锁等待（LOCK WAIT），正在执行的SQL也打印出来了：

delete from user where id=1;

正是事务2的删除语句。

不用问，第二条，显示正在运行状态（RUNNING）的事务就是正在持有锁的事务1，MySQL线程id（trx_mysql_thread_id）是193。

我们用MySQL线程id查一下事务线程id：

select * from performance_schema.threads where processlist_id=193;

找到对应的事务线程id是218，然后再找一下这个线程正在执行的SQL语句：

select THREAD_ID,CURRENT_SCHEMA,SQL_TEXT
from performance_schema.events_statements_current
where thread_id=218;

可以清楚的看到这个线程正在执行的SQL语句就是事务1的update语句。

持有锁的SQL语句找到了，接下来再去找对应的业务代码也就轻而易举了。

以上是基于MySQL5.7版本，在MySQL8.0版本中有些命令已经删除了，替换成了其他命令，下篇文章再讲一下MySQL8.0怎么定位MySQL锁超时问题。

到此这篇关于一文教你学会定位线上MySQL锁超时问题的文章就介绍到这了,更多相关MySQL锁超时内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL串行化隔离级别(间隙锁实现)

    MySQL串行化隔离级别(间隙锁实现)

    目录 一.间隙锁的概念 二.测试间隙锁范围加锁 场景1:用不可重复的主键id测试间隙锁 场景2:用可重复的age(有索引)测试间隙锁 场景3:实际情况需要具体分析用的到底是行锁还是表锁 三.测试间隙锁等值加锁 1. 测试不能重复的主键索引 2. 测试能重复的辅助索引 串行化隔离级别怎么解决幻读问题?先说下幻读的含义,幻读就是在事务中按照同样的条件前后两次查询的结果数据量不同. 解决串行化的幻读问题用间隙锁(gap lock),间隙锁是给不存在的记录加锁,要正确理解间隙,知道间隙的范围.条件无非就

  • Mysql事物锁等待超时Lock wait timeout exceeded;的解决

    目录 问题场景 原因分析 解决方案 参考信息 工作中同事遇到此异常,查找解决问题时,收集整理形成此篇文章. 问题场景 问题出现环境:1.在同一事务内先后对同一条数据进行插入和更新操作:2.多台服务器操作同一数据库:3.瞬时出现高并发现象: 不断的有一下异常抛出,异常信息: org.springframework.dao.CannotAcquireLockException: ### Error updating database.  Cause: java.sql.SQLException: L

  • 浅谈Mysql insert on duplicate key 死锁问题定位与解决

    目录 前言 死锁定位 insert on duplicate key的锁 问题解决 前言 最近在监测线上日志时发现我们一个Mysql业务db时常出现 dead lock,频次不高但却一直出现,定位后发现是在并发场景下的 insert on duplicate key update sql 出现的死锁.经过分析发现这种sql确实比较容易造成死锁,不太适用于我们目前的业务场景,于是更换后解决问题. 这篇文章就从分析死锁展开,到最终如何解决这样的问题 分享相应的思路. 死锁定位 我们目前生产环境使用M

  • MySQL表锁、行锁、排它锁及共享锁的使用详解

    MySQL表锁、行锁、排它锁及共享锁的使用详解

    目录 前言 一.事务隔离机制的选择 二.表级锁&行级锁 三.排它锁(Exclusive)和共享锁(Shared) 1. 测试不同事务之间排它锁和共享锁的兼容性 2. 测试行锁加在索引项上 四.串行化隔离级别测试 总结 前言 事务隔离级别的实现原理:简单来说就是各种锁机制和MVCC多版本并发控制 我们学习知识的时候,需要了解知识点出现的原因,什么情况下能用到这个知识 我们说到事务,就得说到事务的ACID特性,说到隔离性的时候,事务要能够允许并发执行,并发执行为了同时保证数据的安全性,一致性和并发的

  • MySQL数据库表被锁、解锁以及删除事务详解

    目录 背景 故障追踪 解决方案 第一步:查看表使用 第二步:查看进程 第三步:查看当前运行的所有事务 第四步:查看当前出现的锁 第五步:查询锁等待的对应关系 第六步:kill掉事务 MySQL的锁 MySQL锁表场景 Waiting for table metadata lock 场景一:长事务运行,阻塞DDL,继而阻塞所有同表的后续操作. 场景二:为提交事务,阻塞DDL,继而阻塞所有同表的后续操作. 场景三:显式事务失败操作获得锁,未释放 小结 总结 背景 在程序员的职业生涯中,总会遇到数据库

  • MySQL死锁使用详解及检测和避免方法

    MySQL死锁使用详解及检测和避免方法

    目录 前言 锁的释放与阻塞 死锁的发生和检测 查看锁信息(日志) 死锁的避免 前言 上一篇博客我们知道的Mysql事务的隔离机制和实现,以及锁的详细解析 链接: MySQL脏读幻读不可重复读及事务的隔离级别和MVCC.LBCC实现 在我们使用锁的时候,有一个问题是需要注意和避免的,我们知道,排它锁有互斥的特性.一个事务或者说一个线程持有锁的时候,会阻止其他的线程获取锁,这个时候会造成阻塞等待,如果循环等待,会有可能造成死锁. 这个问题我们需要从几个方面来分析,一个是锁为什么不释放,第二个是被阻塞

  • Mysql超详细讲解死锁问题的理解

    Mysql超详细讲解死锁问题的理解

    目录 1.什么是死锁? 2.Mysql出现死锁的必要条件 资源独占条件 请求和保持条件 不剥夺条件 相互获取锁条件 3. Mysql经典死锁案例 3.1 建表语句 3.2 初始化相关数据 3.3 正常转账过程 3.4 死锁转账过程 3.5 死锁导致的问题 4.如何解决死锁问题? 4.1 打破请求和保持条件 4.2 打破相互获取锁条件(推荐) 5.总结 1.什么是死锁? 死锁指的是在两个或两个以上不同的进程或线程中,由于存在共同资源的竞争或进程(或线程)间的通讯而导致各个线程间相互挂起等待,如果没

  • MySQL的表级锁,行级锁,排它锁和共享锁

    MySQL的表级锁,行级锁,排它锁和共享锁

    目录 前言 一.表级锁&行级锁 二.排它锁&共享锁 1. 测试不同事务之间排它锁和共享锁的兼容性 2. 测试行锁加在索引项上 三.串行化隔离级别测试 前言 如果我们和面试官聊到事务的问题,怎么回答呢? 先说下事务是什么,因为我们业务是比较复杂的,不可能一个sql就能解决的,涉及多个sql就组成一个事务.事务就是一组sql共同执行,要么完全成功,要么完全失败,不能出现部分成功或者部分失败的情况.一个事务有ACID特性(可以参考:事务的ACID特性和MySQL事务的隔离级别): 原子性:要么全

  • Mysql锁机制之行锁、表锁、死锁的实现

    Mysql锁机制之行锁、表锁、死锁的实现

    目录 一.Mysql锁是什么?锁有哪些类别? 二.行锁和表锁的区别 三.InnoDB死锁概念和死锁案例 死锁场景一之selectforupdate: 死锁场景二之两个update 四.程序开发过程中应该如何注意避免死锁 一.Mysql锁是什么?锁有哪些类别? 锁定义:    同一时间同一资源只能被一个线程访问    在数据库中,除传统的计算资源(如CPU.I/O等)的争用以外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性.有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据

  • 一文教你学会定位线上MySQL锁超时问题

    一文教你学会定位线上MySQL锁超时问题

    前言: 昨晚我正在床上睡得着着的,突然来了一条短信. 什么?线上的订单无法取消! 我赶紧登录线上系统,查看业务日志. 发现有MySQL锁超时的错误日志. 不用想,肯定有另一个事务正在修改这条订单,持有这条订单的锁. 导致当前事务获取不到锁,一直等待,直到超过锁超时时间,然后报错. 既然问题已经清楚了,接下来就轮到怎么排查一下到底是哪个事务正在持有这条订单的锁. 好在MySQL提供了丰富的工具,帮助我们排查锁竞争问题. 现场复现一个这个问题: 创建一张用户表,造点数据: CREATE TABLE

  • 一文教你学会Redis的事务

    一文教你学会Redis的事务

    目录 Redis 中的使用 开始事务 命令入队 执行事务 discard 命令 watch 命令 事务异常 命令错误 运行时异常 事务的 ACID 原子性 一致性 隔离性 持久 结语 Redis 作为内存的存储中间件,已经是面试的面试题必问之一了,今天一起来看看 Redis 的事务吧. 事务提供了一种"将多个命令打包,一次性提交并按顺序执行"的机制,提交后在事务执行中不会中断.只有在执行完所有命令后才会继续执行来自其他客户的消息. Redis 中的使用 Redis 通过 multi,e

  • 线上MYSQL同步报错故障处理方法总结(必看篇)

    前言 在发生故障切换后,经常遇到的问题就是同步报错,数据库很小的时候,dump完再导入很简单就处理好了,但线上的数据库都150G-200G,如果用单纯的这种方法,成本太高,故经过一段时间的摸索,总结了几种处理方法. 生产环境架构图 目前现网的架构,保存着两份数据,通过异步复制做的高可用集群,两台机器提供对外服务.在发生故障时,切换到slave上,并将其变成master,坏掉的机器反向同步新的master,在处理故障时,遇到最多的就是主从报错.下面是我收录下来的报错信息. 常见错误 最常见的3种情

  • MAC上Mysql忘记Root密码或权限错误的快速解决方案

    最近一段时间都在倒腾mantis发现总是连接mysql出错,就随手修改了root权限,导致登录不上了. 下面给大家分享还原root权限和更改root密码的最便捷方法. 1:装mysql workbench .可视化界面直接操作. 2:苹果->系统偏好设置->最下边点mysql 在弹出页面中 关闭mysql服务 3:进入终端 输入: cd /usr/local/mysql/bin/ 回车后 登录管理员权限 sudo su 回车后输入以下命令来禁止mysql验证功能 ./mysqld_safe -

  • LINUX启动/重启/停上MYSQL的命令(详解)

    如何启动/停止/重启MySQL 一.启动方式 1.使用 service 启动:service mysqld start 2.使用 mysqld 脚本启动:/etc/inint.d/mysqld start 3.使用 safe_mysqld 启动:safe_mysqld& 二.停止 1.使用 service 启动:service mysqld stop 2.使用 mysqld 脚本启动:/etc/inint.d/mysqld stop 3.mysqladmin shutdown 三.重启 1.使用

  • 查看linux服务器上mysql配置文件路径的方法

    查看linux服务器上mysql配置文件路径的方法

    在类NUIX的系统中,配置文件的位置一般在/etc/my.cnf 或者 /etc/MySQL/my.cnf 我们见过有些人尝试修改配置文件但是不生效,因为他们修改的并不是服务器读取的文件.例如Debian下,/etc/mysql/my.cnf才是MySQL读取的配置文件,而不是/etc/my.cnf. 如果不知道当前使用的配置文件路径,可以尝试下面的操作: 对于服务器上只有一个MySQL实例的典型安装,这个命令很有用 以上就是小编为大家带来的查看linux服务器上mysql配置文件路径的方法全部

  • 解决linux的redhat版上mysql字符乱码的问题

    解决linux的redhat版上mysql字符乱码的问题

    linux上安装了mysql服务,默认的字符编码不是utf8,所以会出现中文乱码. 解决方案如下: 1.修改文件/etc/my.cnf,在mysqld配置的最后一行加上character-set-server=utf8 vi /etc/my.cnf 2.重启mysqld服务 service mysqld restart 3.字符中文乱码解决好了 以上这篇解决linux的redhat版上mysql字符乱码的问题就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们.

  • IDEA连接不上MySQL端口号占用的解决

    命令行下能正常登陆MYSQL,navicat能正常连接MySQL,但是IDEA连接不上MySQL,emmm,什么情况... 看了一下错误提示: 一直以为是MySQL密码或者是访问权限的问题. 困扰n天... 某天,突然想到3306端口号占用,自己也很不解 但是查看了一下端口号,发现,3306端口号,竟然有两个端口号在使用... ps:查看端口号占用的命令:netstat -ano 禁掉进程的命令:taskkill -PID <进程号> -F 难道问题出在这里 按照PID尝试把其中一个进程给禁止

  • 线上MySQL的自增id用尽怎么办

    线上MySQL的自增id用尽怎么办

    目录 表定义自增值id InnoDB系统自增row_id Xid Innodb trx_id InnoDB数据可见性的核心思想 thread_id 总结 MySQL的自增id都定义了初始值,然后不断加步长.虽然自然数没有上限,但定义了表示这个数的字节长度,计算机存储就有上限.比如,无符号整型(unsigned int)是4个字节,上限就是2^32 - 1.那自增id用完,会怎么样? 表定义自增值id 表定义的自增值达到上限后的逻辑是:再申请下一个id时,得到的值保持不变. mysql> crea

  • 一文教你搞定Java Optional类判空操作

    一文教你搞定Java Optional类判空操作

    目录 概述 创建Optional实例 获取Optional中的值 判断Optional是否为空 Optional中的过滤.转换方法 概述 最近项目组内做code review,充斥着大量的.原始的.丑陋的判空语句,大致类似下面的代码: if (user != null) { Address address = user.getAddress(); if (address != null) { Country country = address.getCountry(); if (country

随机推荐