美文网首页后台开发笔记
mysql更新死锁问题

mysql更新死锁问题

作者: Figo_OU | 来源:发表于2021-04-19 21:55 被阅读0次
最近小组来了个新的小伙伴--小明！

然后在项目上了test环境后。在运行过程中，突然报了这样的错误。哦？一看就是数据库死锁的问题呀。新加的功能更新操作有点问题呀！

11:12:39.138 [http-nio-8070-exec-6] ERROR druid.sql.Statement - {conn-10005, pstmt-20171} execute error. update master_task_plan set is_instance=? where project_id=?
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
    at com.alibaba.druid.filter.FilterAdapter.preparedStatement_execute(FilterAdapter.java:1081)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
    at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
    at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:627)
    at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
    at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
    at sun.reflect.GeneratedMethodAccessor216.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    at com.sun.proxy.$Proxy252.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
    at sun.reflect.GeneratedMethodAccessor224.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
    at com.bzl.bps.db.interceptor.InsertIdInterceptor.intercept(InsertIdInterceptor.java:75)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    at com.sun.proxy.$Proxy250.update(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor224.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
    at com.sun.proxy.$Proxy250.update(Unknown Source)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
    at sun.reflect.GeneratedMethodAccessor295.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
    at com.sun.proxy.$Proxy125.update(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    at com.sun.proxy.$Proxy152.updateInstance(Unknown Source)
    at com.bzl.bps.planschedule.service.impl.MasterTaskPlanServiceImpl.saveTaskPlan(MasterTaskPlanServiceImpl.java:1074)
    at com.bzl.bps.planschedule.service.impl.MasterTaskPlanServiceImpl.saveAsTaskPlan(MasterTaskPlanServiceImpl.java:543)
    at com.bzl.bps.planschedule.service.impl.MasterTaskPlanServiceImpl$$FastClassBySpringCGLIB$$571c290e.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
    at com.bzl.bps.planschedule.service.impl.MasterTaskPlanServiceImpl$$EnhancerBySpringCGLIB$$e446eb3b.saveAsTaskPlan(<generated>)
    at com.bzl.bps.planschedule.rest.MasterTaskPlanRest.saveAsTaskPlan(MasterTaskPlanRest.java:70)
    at com.bzl.bps.planschedule.rest.MasterTaskPlanRest$$FastClassBySpringCGLIB$$7ba93760.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:47)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
    at com.bzl.bps.planschedule.rest.MasterTaskPlanRest$$EnhancerBySpringCGLIB$$77c39170.saveAsTaskPlan(<generated>)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:652)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:733)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at com.bzl.bps.utils.filer.XssFilter.doFilter(XssFilter.java:30)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)

“小明，你写的update操作有点问题，需要改改”
“我测试没啥问题的呀。babababala!~”
“没问题为啥会报错？你这报错前端就是系统异常。”
....
十分钟后，我不知道怎么复现的耶。哥，不如你教教我。
os:“明显你比我年纪大呀！”

这类问题主要发生在高并发、还有就是多个事务对同一个表做更新操作的时候引起的。显然他不知道为啥。

原因：

  1. 我们数据库用的是MySQL。
  2. 我们用的是MySQL的innodb引擎。
  3. innodb在更新时用的时行级锁。
  4. 行级锁锁的不是记录，而是锁的索引。
  5. 对于没有索引的操作会用表级锁。

再来看你写的sql。

update master_task_plan set is_instance=#{isInstance} where project_id=#{projectId}

主键索引是id列，然后给project_id添加了辅助索引。
调用这句sql的时候，会：

  1. 先给project_id索引上锁，然后根据project_id查询出主键id，
  2. 再获取主键索引的锁，然后根据主键id去查对应的值。
    如果步骤1和2之间，来了一个事务B，
    update master_task_plan set text="123" where id = #{id} and project_id=#{projectId}
    那么就会：
    a. 先给主键索引上锁，然后等待project_id索引。问题在步骤1，已经锁住了project_id索引，所以才会造成死锁呀！

根据主键索引更新数据的地方实在太多了。索引很有可能就发生死锁的情况了呀！

解决方案：

//1.先获取需要更新的记录的主键，给加个写锁（排他锁）
select id from user_item where user_id=? and item_id=? FOR UPDATE
//2. 逐条更新
...
真高兴小明你加入我们的团队啊！>_<

补充:
一般来说如果已经报错了，说明事务已经回滚了。只要有一个线程回滚了，那么另一个线程就可以继续执行。但如果特殊情况下，死锁没有消除。那么就需要用命令来将线程杀掉了。

查询是否锁表
show OPEN TABLES where In_use > 0;
查询到相对应的进程 === 然后 kill id
查询进程
show processlist
杀掉死锁的线程
KILL mysql的线程id
查看数据库当前的进程，看一下有无正在执行的慢SQL记录线程。
show processlist;
查看当前的事务
当前运行的所有事务
SELECT * FROM information_schema.innodb_trx;
当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs;
如果出现1109 - Unknown table 'INNODB_LOCKS' in information_schema则要使用👇下面语句
mysql8.0后，查询当前出现的锁
SELECT * FROM performance_schema.data_lock_waits;
锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits;

其实平时的项目中，还是wait lock比较常见。所以避免的方法还是：

  • delete update 操作避免使用非索引字段为条件，尽量使用主键索引来更新删除

相关文章

  • MySQL更新死锁问题

    转载：http://ju.outofmemory.cn/entry/216512 就是如何利用乐观锁来解决并发问题...

  • mysql更新死锁问题

    最近小组来了个新的小伙伴--小明！ 然后在项目上了test环境后。在运行过程中，突然报了这样的错误。哦？一看就是数...

  • mysql死锁-非主键索引更新引起的死锁

    [mysql死锁-非主键索引更新引起的死锁] 背景：最近线上经常抛出mysql的一个Deadlock，细细查来，长...

  • MySQL笔记-锁、事务与并发控制

    MySQL服务器逻辑架构 MySQL并发控制 MySQL死锁问题 MySQL中的事务

  • MySql更新超时和死锁问题分析

    一、更新超时 最近在生产上遇到了这样的一个问题，在执行到具体的update语句时，出现了SQL state [nu...

  • 漫谈死锁

    一、前言 每个MySQL DBA和开发大概率都会遇到死锁问题，本文是自己对死锁相关知识总结,介绍死锁是什么，MyS...

  • Mysql死锁如何排查：insert on duplicate死

    前言 遇到Mysql死锁问题，我们应该怎么排查分析呢？之前线上出现一个insert on duplicate死锁问...

  • 经典问题处理记录

    1.mysql update操作死锁问题 故障描述 日志监控发现少量数据库死锁异常 问题原因 设计方案使用悲观锁s...

  • mysql多线程update死锁问题

    最近想起之前处理过的一个mysql 死锁问题，是在高并发下update批量更新导致的，这里探讨一下发生的原因，以及...

  • mysql for update 死锁问题

    在RR条件， id是主键，插入五条数据 我们可以发现 针对事务一：select * from user where...

网友评论

    本文标题：mysql更新死锁问题

    本文链接：https://www.haomeiwen.com/subject/ydablltx.html

    延伸阅读

    深度阅读

    • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

    栏目导航

    热点阅读

    后台开发笔记

    关于我们|服务条款|联系我们|mysql更新死锁问题|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！