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

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

  •  
  •   jiobanma ·
    banmajio · 144 天前 · 1210 次点击
    这是一个创建于 144 天前的主题,其中的信息可能已经有所发展或是发生改变。
    • 有大佬知道怎么处理吗?
    • 一批数据需要批量更新,代码中处理的时候是 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 条附言  ·  144 天前
    整个 service 加了事务,@DSTransactional ,try catch 捕获一场,catch 中手动回滚
    27 条回复    2022-01-06 14:10:51 +08:00
    Canon1014
        1
    Canon1014  
       144 天前
    student_code 上有什么索引吗,我遇到类似情况就尽量把 where 条件改为主键了
    插个眼,蹲个大佬
    jiobanma
        2
    jiobanma  
    OP
       144 天前
    @Canon1014 student_code 没有索引
    leviathan0992
        3
    leviathan0992  
       144 天前
    只有这个一条 SQL 还是有别的 SQL 并发, 假如只有这个一条打开 autocommit = 1 试试.
    jiobanma
        4
    jiobanma  
    OP
       144 天前
    @leviathan0992 没有并发,顶多就是针对这张表这边在更新 那边有数据查询。
    leviathan0992
        5
    leviathan0992  
       144 天前
    那就改成 autocommit = 1
    themostlazyman
        6
    themostlazyman  
       144 天前
    studentCode 是不是有重复的,盲猜可能是 for 循环外边有个事务,两次循环中有重复的 studentCode
    themostlazyman
        7
    themostlazyman  
       144 天前
    studentCode 不是索引,应该是全表锁,studentCode 建成索引的化是间隙锁,会减少死锁,如果 studentCode 在表中唯一,可考虑添加唯一索引
    jiobanma
        8
    jiobanma  
    OP
       144 天前
    @themostlazyman student_code 没有加索引,在表中是唯一的,加上索引会好一点吗?
    jiobanma
        9
    jiobanma  
    OP
       144 天前
    @themostlazyman 外层没有事务了,整个接口都是同步的,没有异步的操作,也只有这一个接口会进行数据的增删改
    themostlazyman
        10
    themostlazyman  
       144 天前
    @jiobanma 应该加唯一索引就没问题了
    themostlazyman
        11
    themostlazyman  
       144 天前
    @jiobanma service 有事务的话,你得确保数据中的 studentCode 也不能重复。
    flyfanc
        12
    flyfanc  
       144 天前
    估计同一事务里面同时更新一个 student_code 的数据了
    jiobanma
        13
    jiobanma  
    OP
       144 天前
    @flyfanc 讲道理不应该啊,这个事务里面是同步进行的,没有异步的操作
    jiobanma
        14
    jiobanma  
    OP
       144 天前
    @themostlazyman 这个 student_code 还不能加唯一索引,因为会有逻辑删除的数据,可能会存在两条以上的数据,有效的有一条,无效的有多条。如果我用 id 作为条件,是不是也可以避免死锁
    Chinsung
        15
    Chinsung  
       144 天前
    studentCode 没索引,应该是全表锁,因为 update 要加锁而 mysql 无法确认锁的范围。
    给 studentCode 加索引,或者写个子查询来根据 id 批量更新。
    fkdog
        16
    fkdog  
       144 天前
    批量更新在并发度不高的情况下就很容易造成死锁。
    特别是隔离级别在 RR 的情况下。
    跟 mysql 锁机制有关。
    lu5je0
        17
    lu5je0  
       144 天前
    @jiobanma 逻辑删除可以加 student_code 和删除时间戳的联合唯一索引
    themostlazyman
        18
    themostlazyman  
       144 天前
    @jiobanma 我试了 mysql 同一个事务中多条相同非索引条件的更新没有触发死锁,你可以试下先查再根据 id 更,查的时候可以上悲观锁。mybatis 的一级缓存在同一事务 for 操作容易有问题,建议可以禁用 mybatis 一级缓存试下。
    jiobanma
        19
    jiobanma  
    OP
       144 天前
    @themostlazyman
    @lu5je0
    @fkdog
    @Chinsung
    谢谢大佬们 我试一下
    admol
        20
    admol  
       144 天前
    1. 能稳定复现吗?
    2. 可以尝试减小批量更新的条数
    3. student_code 最好能加索引(不能唯一,也可以普通),或者换成根据主键 ID 更新
    4. 批量更新前对 student_code 进行排序,如果换成主键也是
    fmumu
        21
    fmumu  
       144 天前
    锁的问题,减少锁占用,更新用主键,或者唯一键
    lance6716
        22
    lance6716  
       143 天前 via Android
    隔离级别换 rc ,有没有效果帮忙 at 我一下
    swim2sun
        23
    swim2sun  
       143 天前 via iPhone
    update 前把 problemStudentVos 按 student_code 进行排序
    jiobanma
        24
    jiobanma  
    OP
       143 天前
    @lance6716
    @admol
    @swim2sun
    同样的代码,是一个定时任务在执行。本地、测试、线上,三个环境都能正常执行,定时任务的日志都只打印一次。但是预发布每次启动定时任务,都会同时打印两次日志,并产生死锁。推断为两次并行的定时任务一起执行了,所以产生了死锁。
    swim2sun
        25
    swim2sun  
       143 天前
    @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
       143 天前
    @swim2sun 1. 不批量更新的话,数据量太大了,这个定时任务本来就是为了跑数据的。2. 其实只有一个环境会出现死锁,查看日志发现,这个环境每次都是定时任务同时出发两次,两个线程同时去执行导致了死锁。正常情况下不会出现同时执行的情况。所以应该是这个环境的定时任务有问题。
    vone
        27
    vone  
       143 天前
    另外建立一个 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   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2678 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 12:44 · PVG 20:44 · LAX 05:44 · JFK 08:44
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.