批量修改：update inner join导致死锁

问题sql:

update i_r_cmd t10  
inner join (
      select
        t10.tenant_id,          
        t10.r_cmd_code,          
        sum(case when t10.`report_status`=10 then 1 else 0 end) as reportSuccessCount,          
        sum(case when t10.`report_status`=20 then 1 else 0 end) as reportFailCount      
        from i_r_cmd_device as t10      
        where t10.tenant_id=?          
            and t10.r_cmd_code=?  
) as t11 on t10.tenant_id=t11.tenant_id and t10.code=t11.r_cmd_code      
    set t10.report_success_count=t11.reportSuccessCount,      
        t10.report_fail_count=t11.reportFailCount  
where t10.tenant_id=?      
    and t10.code=?

异常信息：

2021-12-21 19:05:29,119 [org.springframework.kafka.KafkaListenerEndpointContainer#0-0-C-1-1][TraceId:] ERROR KAFKA_CONSUMER.LOG - [处理异常：
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/by/px/ipl/domain/dao/RCmdDao.java (best guess)
### The error may involve com..px.ipl.domain.dao.RCmdDao.updateReportSuccessFailCount-Inline
### The error occurred while setting parameters
### SQL: 
update i_r_cmd t10  
inner join (
      select
        t10.tenant_id,          
        t10.r_cmd_code,          
        sum(case when t10.`report_status`=10 then 1 else 0 end) as reportSuccessCount,          
        sum(case when t10.`report_status`=20 then 1 else 0 end) as reportFailCount      
        from i_r_cmd_device as t10      
        where t10.tenant_id=?          
            and t10.r_cmd_code=?  
) as t11 on t10.tenant_id=t11.tenant_id and t10.code=t11.r_cmd_code      
    set t10.report_success_count=t11.reportSuccessCount,      
        t10.report_fail_count=t11.reportFailCount  
where t10.tenant_id=?      
    and t10.code=?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction]
org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/by/px/ipl/domain/dao/RCmdDao.java (best guess)
### The error may involve com.by.px.ipl.domain.dao.RCmdDao.updateReportSuccessFailCount-Inline
### The error occurred while setting parameters
### SQL: 
update i_r_cmd t10  
inner join (
      select
        t10.tenant_id,          
        t10.r_cmd_code,          
        sum(case when t10.`report_status`=10 then 1 else 0 end) as reportSuccessCount,          
        sum(case when t10.`report_status`=20 then 1 else 0 end) as reportFailCount      
        from i_r_cmd_device as t10      
        where t10.tenant_id=?          
            and t10.r_cmd_code=?  
) as t11 on t10.tenant_id=t11.tenant_id and t10.code=t11.r_cmd_code      
    set t10.report_success_count=t11.reportSuccessCount,      
        t10.report_fail_count=t11.reportFailCount  
where t10.tenant_id=?      
    and t10.code=?
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
        at com.sun.proxy.$Proxy161.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.$Proxy196.updateReportSuccessFailCount(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.by.px.mountain.core.common.aop.PerformanceInstrumentInterceptor.invoke(PerformanceInstrumentInterceptor.java:30)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
        at com.sun.proxy.$Proxy197.updateReportSuccessFailCount(Unknown Source)
        at com.by.px.ipl.domain.repository.rCmdDomainRepository.updateReportSuccessFailCount(rCmdDomainRepository.java:200)
        at com.by.px.ipl.domain.repository.rCmdDomainRepository$$FastClassBySpringCGLIB$$5967492e.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.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
        at com.by.px.mountain.core.common.aop.PerformanceInstrumentInterceptor.invoke(PerformanceInstrumentInterceptor.java:30)
        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.by.px.ipl.domain.repository.rCmdDomainRepository$$EnhancerBySpringCGLIB$$b6cfb8d1.updateReportSuccessFailCount(<generated>)
        at com.by.px.ipl.domain.partial.report.action.cmd.rCmdReportLogAction.doAction(rCmdReportLogAction.java:74)
        at com.by.px.ipl.domain.partial.report.ReportDispatcherDomain.executeAction(ReportDispatcherDomain.java:91)
        at com.by.px.mountain.core.common.service.seviceTemplateImpl$1.doInTransaction(seviceTemplateImpl.java:49)
        at com.by.px.mountain.core.common.service.seviceTemplateImpl$1.doInTransaction(seviceTemplateImpl.java:46)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
        at com.by.px.mountain.core.common.service.seviceTemplateImpl.execute(seviceTemplateImpl.java:45)
        at com.by.px.ipl.domain.partial.report.ReportFactory.dispatcher(ReportFactory.java:41)
        at com.by.px.ipl.domain.kafka.KafkaConsumer.listen(KafkaConsumer.java:53)
        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.messaging.handler.invocation.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:171)
        at org.springframework.messaging.handler.invocation.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:120)
        at org.springframework.kafka.listener.adapter.HandlerAdapter.invoke(HandlerAdapter.java:48)
        at org.springframework.kafka.listener.adapter.MessagingMessageListenerAdapter.invokeHandler(MessagingMessageListenerAdapter.java:329)
        at org.springframework.kafka.listener.adapter.BatchMessagingMessageListenerAdapter.invoke(BatchMessagingMessageListenerAdapter.java:170)
        at org.springframework.kafka.listener.adapter.BatchMessagingMessageListenerAdapter.onMessage(BatchMessagingMessageListenerAdapter.java:162)
        at org.springframework.kafka.listener.adapter.BatchMessagingMessageListenerAdapter.onMessage(BatchMessagingMessageListenerAdapter.java:58)
        at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeBatchOnMessage(KafkaMessageListenerContainer.java:1702)
        at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeBatchOnMessageWithRecordsOrList(KafkaMessageListenerContainer.java:1693)
        at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeBatchOnMessage(KafkaMessageListenerContainer.java:1651)
        at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.doInvokeBatchListener(KafkaMessageListenerContainer.java:1586)
        at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeBatchListener(KafkaMessageListenerContainer.java:1479)
        at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.invokeListener(KafkaMessageListenerContainer.java:1462)
        at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.pollAndInvoke(KafkaMessageListenerContainer.java:1128)
        at org.springframework.kafka.listener.KafkaMessageListenerContainer$ListenerConsumer.run(KafkaMessageListenerContainer.java:1031)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.lang.Thread.run(Thread.java:748)
Caused by: 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:121)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
        at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:388)
        at sun.reflect.GeneratedMethodAccessor247.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.mysql.cj.jdbc.ha.MultiHostConnectionProxy$JdbcInterfaceProxy.invoke(MultiHostConnectionProxy.java:105)
        at com.sun.proxy.$Proxy318.execute(Unknown Source)
        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.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 sun.reflect.GeneratedMethodAccessor232.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.by.px.ipl.web.interceptor.MybatisSqlLogInterceptor.intercept(MybatisSqlLogInterceptor.java:65)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
        at com.sun.proxy.$Proxy317.update(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor232.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.$Proxy317.update(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor232.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.$Proxy317.update(Unknown Source)
        at sun.reflect.GeneratedMethodAccessor232.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.by.px.ipl.web.interceptor.MybatisSqlLogInterceptor.intercept(MybatisSqlLogInterceptor.java:65)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
        at com.sun.proxy.$Proxy317.update(Unknown Source)
        at com.baomidou.mybatisplus.core.executor.MybatisReuseExecutor.doUpdate(MybatisReuseExecutor.java:55)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at sun.reflect.GeneratedMethodAccessor411.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.$Proxy316.update(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
        at sun.reflect.GeneratedMethodAccessor359.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:426)
        ... 60 common frames omitted
2021-12-21 19:05:29,124 [org.springframework.kafka.KafkaListenerEndpointContainer#0-0-C-1-1][TraceId:] INFO  KAFKA_CONSUMER.LOG - [seviceTemplate.execute(...) 

解决方案：

拆解为单个sql去修改，去掉inner join update。

update join会导致锁表。