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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

• 请不要在回答技术问题时复制粘贴 AI 生成的内容

这是一个创建于 1083 天前的主题，其中的信息可能已经有所发展或是发生改变。

有大佬知道怎么处理吗？
一批数据需要批量更新，代码中处理的时候是 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 中手动回滚

27 条回复 • 2022-01-06 14:10:51 +08:00

Canon1014

2022-01-05 14:19:31 +08:00

student_code 上有什么索引吗，我遇到类似情况就尽量把 where 条件改为主键了
插个眼，蹲个大佬

jiobanma

2022-01-05 14:39:00 +08:00

@Canon1014 student_code 没有索引

leviathan0992

2022-01-05 14:39:50 +08:00

只有这个一条 SQL 还是有别的 SQL 并发，假如只有这个一条打开 autocommit = 1 试试.

jiobanma

2022-01-05 15:18:51 +08:00

@leviathan0992 没有并发，顶多就是针对这张表这边在更新那边有数据查询。

leviathan0992

2022-01-05 15:23:13 +08:00

那就改成 autocommit = 1

themostlazyman

2022-01-05 15:24:57 +08:00

studentCode 是不是有重复的，盲猜可能是 for 循环外边有个事务，两次循环中有重复的 studentCode

themostlazyman

2022-01-05 15:29:12 +08:00

studentCode 不是索引，应该是全表锁，studentCode 建成索引的化是间隙锁，会减少死锁，如果 studentCode 在表中唯一，可考虑添加唯一索引

jiobanma

2022-01-05 15:33:07 +08:00

@themostlazyman student_code 没有加索引，在表中是唯一的，加上索引会好一点吗？

jiobanma

2022-01-05 15:34:06 +08:00

@themostlazyman 外层没有事务了，整个接口都是同步的，没有异步的操作，也只有这一个接口会进行数据的增删改

themostlazyman

2022-01-05 15:35:07 +08:00

@jiobanma 应该加唯一索引就没问题了

themostlazyman

2022-01-05 15:39:41 +08:00

@jiobanma service 有事务的话，你得确保数据中的 studentCode 也不能重复。

flyfanc

2022-01-05 15:54:08 +08:00

估计同一事务里面同时更新一个 student_code 的数据了

jiobanma

2022-01-05 15:57:04 +08:00

@flyfanc 讲道理不应该啊，这个事务里面是同步进行的，没有异步的操作

jiobanma

2022-01-05 16:17:35 +08:00

@themostlazyman 这个 student_code 还不能加唯一索引，因为会有逻辑删除的数据，可能会存在两条以上的数据，有效的有一条，无效的有多条。如果我用 id 作为条件，是不是也可以避免死锁

Chinsung

2022-01-05 16:18:16 +08:00

studentCode 没索引，应该是全表锁，因为 update 要加锁而 mysql 无法确认锁的范围。
给 studentCode 加索引，或者写个子查询来根据 id 批量更新。

fkdog

2022-01-05 16:25:30 +08:00

批量更新在并发度不高的情况下就很容易造成死锁。
特别是隔离级别在 RR 的情况下。
跟 mysql 锁机制有关。

lu5je0

2022-01-05 16:26:49 +08:00

@jiobanma 逻辑删除可以加 student_code 和删除时间戳的联合唯一索引

themostlazyman

2022-01-05 16:32:58 +08:00

@jiobanma 我试了 mysql 同一个事务中多条相同非索引条件的更新没有触发死锁，你可以试下先查再根据 id 更，查的时候可以上悲观锁。mybatis 的一级缓存在同一事务 for 操作容易有问题，建议可以禁用 mybatis 一级缓存试下。

jiobanma

2022-01-05 16:35:05 +08:00

@themostlazyman
@lu5je0
@fkdog
@Chinsung
谢谢大佬们我试一下

admol

2022-01-05 16:38:58 +08:00

1. 能稳定复现吗？
2. 可以尝试减小批量更新的条数
3. student_code 最好能加索引（不能唯一，也可以普通），或者换成根据主键 ID 更新
4. 批量更新前对 student_code 进行排序，如果换成主键也是

fmumu

2022-01-05 20:15:04 +08:00

锁的问题,减少锁占用,更新用主键,或者唯一键

lance6716

2022-01-05 23:03:26 +08:00 via Android

隔离级别换 rc ，有没有效果帮忙 at 我一下

swim2sun

2022-01-06 01:02:23 +08:00 via iPhone

update 前把 problemStudentVos 按 student_code 进行排序

jiobanma

2022-01-06 09:45:13 +08:00

@lance6716
@admol
@swim2sun
同样的代码，是一个定时任务在执行。本地、测试、线上，三个环境都能正常执行，定时任务的日志都只打印一次。但是预发布每次启动定时任务，都会同时打印两次日志，并产生死锁。推断为两次并行的定时任务一起执行了，所以产生了死锁。

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

2022-01-06 10:16:02 +08:00

@swim2sun 1. 不批量更新的话，数据量太大了，这个定时任务本来就是为了跑数据的。2. 其实只有一个环境会出现死锁，查看日志发现，这个环境每次都是定时任务同时出发两次，两个线程同时去执行导致了死锁。正常情况下不会出现同时执行的情况。所以应该是这个环境的定时任务有问题。

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