首页 注册 登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
jiobanma
V2EX  ›  程序员

批量更新操作时， mysql 发生死锁

  •   
  •   jiobanma ·
    banmajio · 2022-01-05 13:55:48 +08:00 · 2318 次点击
    这是一个创建于 1057 天前的主题，其中的信息可能已经有所发展或是发生改变。
    • 有大佬知道怎么处理吗？
    • 一批数据需要批量更新，代码中处理的时候是 1000 条调用一次 update 方法。
    • mybatis 中是这么写的
    <update id="updateData">
        <foreach collection="problemStudentVos" item="problemStudentVo" index="index" open="" close="" separator=";">
            UPDATE warn_problem_student
            <trim prefix="set" suffixOverrides=",">
                    student_name = #{problemStudentVo.studentName}
            </trim>
            WHERE
            del_flag = 0
            AND student_code = #{problemStudentVo.studentCode}
   </foreach>
</update>
    • 数据量大概六七千条数据。
    • 报错信息为：
    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:3461)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
        at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:54)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:83)
        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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
        at com.sun.proxy.$Proxy375.update(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
        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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
        at com.sun.proxy.$Proxy158.update(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:65)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
        at com.sun.proxy.$Proxy365.updateData(Unknown Source)
        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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:50)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
        at com.sun.proxy.$Proxy366.updateData(Unknown Source)
        at cn.xdf.bj.bpm.synchro.service.impl.ProblemStudentServiceImpl.buildProblemStudent(ProblemStudentServiceImpl.java:245)
        at cn.xdf.bj.bpm.synchro.service.impl.ProblemStudentServiceImpl.syncProblemStudent(ProblemStudentServiceImpl.java:97)
        at cn.xdf.bj.bpm.synchro.service.impl.ProblemStudentServiceImpl$$FastClassBySpringCGLIB$$30163006.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 com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:50)
        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 cn.xdf.bj.bpm.synchro.service.impl.ProblemStudentServiceImpl$$EnhancerBySpringCGLIB$$6180472e.syncProblemStudent(<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.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:282)
        at org.springframework.cloud.context.scope.GenericScope$LockedScopedProxyFactoryBean.invoke(GenericScope.java:499)
        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 cn.xdf.bj.bpm.synchro.service.impl.ProblemStudentServiceImpl$$EnhancerBySpringCGLIB$$241b7c63.syncProblemStudent(<generated>)
        at cn.xdf.bj.bpm.synchro.controller.ProblemStudentController.syncProblemStudent(ProblemStudentController.java:58)
        at cn.xdf.bj.bpm.synchro.controller.ProblemStudentController$$FastClassBySpringCGLIB$$7e6986e8.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 com.baomidou.dynamic.datasource.aop.DynamicLocalTransactionAdvisor.invoke(DynamicLocalTransactionAdvisor.java:43)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
        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 cn.xdf.bj.bpm.synchro.controller.ProblemStudentController$$EnhancerBySpringCGLIB$$33e2348c.syncProblemStudent(<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.doGet(FrameworkServlet.java:898)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:626)
        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 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.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.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:97)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
        at org.apache.catalina.valves.RemoteIpValve.invoke(RemoteIpValve.java:764)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:888)
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1597)
        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)
    第 1 条附言  ·  2022-01-05 15:19:57 +08:00
    整个 service 加了事务，@DSTransactional ，try catch 捕获一场，catch 中手动回滚
  • Java
  • voke
  • lter
  • oce
    27 条回复  •  2022-01-06 14:10:51 +08:00
    Canon1014
        1
    Canon1014  
       2022-01-05 14:19:31 +08:00
    student_code 上有什么索引吗，我遇到类似情况就尽量把 where 条件改为主键了
    插个眼，蹲个大佬
    jiobanma
        2
    jiobanma  
    OP
       2022-01-05 14:39:00 +08:00
    @Canon1014 student_code 没有索引
    leviathan0992
        3
    leviathan0992  
       2022-01-05 14:39:50 +08:00
    只有这个一条 SQL 还是有别的 SQL 并发， 假如只有这个一条打开 autocommit = 1 试试.
    jiobanma
        4
    jiobanma  
    OP
       2022-01-05 15:18:51 +08:00
    @leviathan0992 没有并发，顶多就是针对这张表这边在更新 那边有数据查询。
    leviathan0992
        5
    leviathan0992  
       2022-01-05 15:23:13 +08:00
    那就改成 autocommit = 1
    themostlazyman
        6
    themostlazyman  
       2022-01-05 15:24:57 +08:00
    studentCode 是不是有重复的，盲猜可能是 for 循环外边有个事务，两次循环中有重复的 studentCode
    themostlazyman
        7
    themostlazyman  
       2022-01-05 15:29:12 +08:00
    studentCode 不是索引，应该是全表锁，studentCode 建成索引的化是间隙锁，会减少死锁，如果 studentCode 在表中唯一，可考虑添加唯一索引
    jiobanma
        8
    jiobanma  
    OP
       2022-01-05 15:33:07 +08:00
    @themostlazyman student_code 没有加索引，在表中是唯一的，加上索引会好一点吗？
    jiobanma
        9
    jiobanma  
    OP
       2022-01-05 15:34:06 +08:00
    @themostlazyman 外层没有事务了，整个接口都是同步的，没有异步的操作，也只有这一个接口会进行数据的增删改
    themostlazyman
        10
    themostlazyman  
       2022-01-05 15:35:07 +08:00
    @jiobanma 应该加唯一索引就没问题了
    themostlazyman
        11
    themostlazyman  
       2022-01-05 15:39:41 +08:00
    @jiobanma service 有事务的话，你得确保数据中的 studentCode 也不能重复。
    flyfanc
        12
    flyfanc  
       2022-01-05 15:54:08 +08:00
    估计同一事务里面同时更新一个 student_code 的数据了
    jiobanma
        13
    jiobanma  
    OP
       2022-01-05 15:57:04 +08:00
    @flyfanc 讲道理不应该啊，这个事务里面是同步进行的，没有异步的操作
    jiobanma
        14
    jiobanma  
    OP
       2022-01-05 16:17:35 +08:00
    @themostlazyman 这个 student_code 还不能加唯一索引，因为会有逻辑删除的数据，可能会存在两条以上的数据，有效的有一条，无效的有多条。如果我用 id 作为条件，是不是也可以避免死锁
    Chinsung
        15
    Chinsung  
       2022-01-05 16:18:16 +08:00
    studentCode 没索引，应该是全表锁，因为 update 要加锁而 mysql 无法确认锁的范围。
    给 studentCode 加索引，或者写个子查询来根据 id 批量更新。
    fkdog
        16
    fkdog  
       2022-01-05 16:25:30 +08:00
    批量更新在并发度不高的情况下就很容易造成死锁。
    特别是隔离级别在 RR 的情况下。
    跟 mysql 锁机制有关。
    lu5je0
        17
    lu5je0  
       2022-01-05 16:26:49 +08:00
    @jiobanma 逻辑删除可以加 student_code 和删除时间戳的联合唯一索引
    themostlazyman
        18
    themostlazyman  
       2022-01-05 16:32:58 +08:00
    @jiobanma 我试了 mysql 同一个事务中多条相同非索引条件的更新没有触发死锁，你可以试下先查再根据 id 更，查的时候可以上悲观锁。mybatis 的一级缓存在同一事务 for 操作容易有问题，建议可以禁用 mybatis 一级缓存试下。
    jiobanma
        19
    jiobanma  
    OP
       2022-01-05 16:35:05 +08:00
    @themostlazyman
    @lu5je0
    @fkdog
    @Chinsung
    谢谢大佬们 我试一下
    admol
        20
    admol  
       2022-01-05 16:38:58 +08:00
    1. 能稳定复现吗？
    2. 可以尝试减小批量更新的条数
    3. student_code 最好能加索引（不能唯一，也可以普通），或者换成根据主键 ID 更新
    4. 批量更新前对 student_code 进行排序，如果换成主键也是
    fmumu
        21
    fmumu  
       2022-01-05 20:15:04 +08:00
    锁的问题,减少锁占用,更新用主键,或者唯一键
    lance6716
        22
    lance6716  
       2022-01-05 23:03:26 +08:00 via Android
    隔离级别换 rc ，有没有效果帮忙 at 我一下
    swim2sun
        23
    swim2sun  
       2022-01-06 01:02:23 +08:00 via iPhone
    update 前把 problemStudentVos 按 student_code 进行排序
    jiobanma
        24
    jiobanma  
    OP
       2022-01-06 09:45:13 +08:00
    @lance6716
    @admol
    @swim2sun
    同样的代码，是一个定时任务在执行。本地、测试、线上，三个环境都能正常执行，定时任务的日志都只打印一次。但是预发布每次启动定时任务，都会同时打印两次日志，并产生死锁。推断为两次并行的定时任务一起执行了，所以产生了死锁。
    swim2sun
        25
    swim2sun  
       2022-01-06 10:09:42 +08:00
    @jiobanma 你有试一下我说的方法吗？ problemStudentVos 如果没有排序的话，并发时产生死锁的概率很大。

    例如，两个线程， 线程 1 拿到的集合是{ A, B, C }， 线程 2 拿到的是{ B, A, C}，
    线程 1 锁了 A ，同时线程 2 锁了 B ，
    这就会导致接下来线程 1 等待 2 释放 B 的锁，线程 2 等待 1 释放 A 的锁，这就产生了死锁。

    解决方法有两种：
    1. 不用这所谓的批量更新，每次更新都用单独的事务，这样更新完每条记录都会释放锁
    2. 批量更新先对集合进行排序，这样并发时拿锁的顺序是一致的就不会产生死锁
    jiobanma
        26
    jiobanma  
    OP
       2022-01-06 10:16:02 +08:00
    @swim2sun 1. 不批量更新的话，数据量太大了，这个定时任务本来就是为了跑数据的。2. 其实只有一个环境会出现死锁，查看日志发现，这个环境每次都是定时任务同时出发两次，两个线程同时去执行导致了死锁。正常情况下不会出现同时执行的情况。所以应该是这个环境的定时任务有问题。
    vone
        27
    vone  
       2022-01-06 14:10:51 +08:00
    另外建立一个 ods_warn_problem_student 表，储存 studentName 、studentCode 。更新时先往这个表里插入， 然后执行：
    UPDATE warn_problem_student t
    join ods_warn_problem_student ods ON ods.studentCode= t.studentCode
    SET t.studentName=ods.studentName
    where case when t.studentName=ods.studentName then 0 else 1 end =1
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3109 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 13:37 · PVG 21:37 · LAX 05:37 · JFK 08:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.