MYSQL批量插入并发场景下的DEADLOCK

server-provider-info-2023-10-12.0.log:2023-10-12 09:40:50.593 [TID:bf623bded189486cbb0b6a64d81b64b4.357.16970748504097047] [4ed7b7943a8a47de912e4b644d70285e] [SimpleAsyncTaskExecutor-5080] INFO  com.emax.user.user.provider.UserTagAPIImpl:? - 签约完成，更新用户标签信息异常，用户id[1712282059297107970]org.apache.ibatis.exceptions.PersistenceException:server-provider-info-2023-10-12.0.log-### Error flushing statements.  Cause: org.apache.ibatis.executor.BatchExecutorException: com.emax.user.user.mapper.UserTagMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transactionserver-provider-info-2023-10-12.0.log-### Cause: org.apache.ibatis.executor.BatchExecutorException: com.emax.user.user.mapper.UserTagMapper.insert (batch index #1)failed. Cause: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transactionserver-provider-info-2023-10-12.0.log-  at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)server-provider-info-2023-10-12.0.log-  at org.apache.ibatis.session.defaults.DefaultSqlSession.flushStatements(DefaultSqlSession.java:254)server-provider-info-2023-10-12.0.log-  at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.saveBatch(ServiceImpl.java:127)server-provider-info-2023-10-12.0.log-  at com.baomidou.mybatisplus.extension.service.IService.saveBatch(IService.java:58)server-provider-info-2023-10-12.0.log-  at com.baomidou.mybatisplus.extension.service.IService$$FastClassBySpringCGLIB$$f8525d18.invoke()server-provider-info-2023-10-12.0.log-  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)server-provider-info-2023-10-12.0.log-  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)server-provider-info-2023-10-12.0.log-  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)server-provider-info-2023-10-12.0.log-  at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)server-provider-info-2023-10-12.0.log-  at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)server-provider-info-2023-10-12.0.log-  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)server-provider-info-2023-10-12.0.log-  at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)server-provider-info-2023-10-12.0.log-  at com.emax.user.user.service.UserTagManager$$EnhancerBySpringCGLIB$$67ca0067.saveBatch()server-provider-info-2023-10-12.0.log-  at com.emax.user.user.provider.UserTagAPIImpl.addUserTagByUser(UserTagAPIImpl.java:236)server-provider-info-2023-10-12.0.log-  at com.emax.user.user.provider.UserTagAPIImpl.addTagAfterSign(UserTagAPIImpl.java:116)server-provider-info-2023-10-12.0.log-  at com.emax.user.user.provider.UserTagAPIImpl$$FastClassBySpringCGLIB$$2547ce26.invoke()server-provider-info-2023-10-12.0.log-  at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)server-provider-info-2023-10-12.0.log-  at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)--server-provider-info-2023-10-12.0.log-  at io.seata.rm.datasource.StatementProxy.lambda$executeBatch$9(StatementProxy.java:129)server-provider-info-2023-10-12.0.log-  at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:73)server-provider-info-2023-10-12.0.log-  at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:51)server-provider-info-2023-10-12.0.log-  at io.seata.rm.datasource.StatementProxy.executeBatch(StatementProxy.java:129)server-provider-info-2023-10-12.0.log-  at sun.reflect.GeneratedMethodAccessor1346.invoke(Unknown Source)server-provider-info-2023-10-12.0.log-  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)server-provider-info-2023-10-12.0.log-  at java.lang.reflect.Method.invoke(Method.java:498)server-provider-info-2023-10-12.0.log-  at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:78)server-provider-info-2023-10-12.0.log-  at com.sun.proxy.$Proxy501.executeBatch(Unknown Source)server-provider-info-2023-10-12.0.log-  at com.baomidou.mybatisplus.core.executor.MybatisBatchExecutor.doFlushStatements(MybatisBatchExecutor.java:132)server-provider-info-2023-10-12.0.log-  ... 71 common frames omittedserver-provider-info-2023-10-12.0.log-Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transactionserver-provider-info-2023-10-12.0.log-  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)server-provider-info-2023-10-12.0.log-  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)server-provider-info-2023-10-12.0.log-  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)server-provider-info-2023-10-12.0.log-  at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)server-provider-info-2023-10-12.0.log-  at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1109)server-provider-info-2023-10-12.0.log-  at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:849)server-provider-info-2023-10-12.0.log-  ... 89 common frames omittedserver-provider-info-2023-10-12.0.log-

    /**     * 增加用户标签     * @param user 用户信息     * @param tagNameList 标签集合     */    private void addUserTagByUser(User user, List tagNameList){        log.info("用户添加标签入参信息，当前用户id[{}]，标签信息[{}]", user.getUserId(), JSON.toJSONString(tagNameList));        Set existTags = new HashSet();        final List userTagList = userTagManager.listUserTagByUserId(user.getUserId());        if(CollectionUtils.isNotEmpty(userTagList)){            existTags = userTagList.stream().map(UserTag::getTagName).collect(Collectors.toSet());        }        //遍历合集 如果不存在旧数据 直接都新增 存在 判断当前数据是否在旧数据中        List newInsertList = new ArrayList();        for(String name : tagNameList){            if (StringUtils.isNotBlank(name) && existTags.add(name)) {                newInsertList.add(userTagManager.buildEntity(user.getUserId(), user.getIdcardNo(), name));            }        }        userTagManager.saveBatch(newInsertList);    }

=====================================2023-10-12 13:49:17 0x7fea5ae87700 INNODB MONITOR OUTPUT=====================================Per second averages calculated from the last 61 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 3123131 srv_active, 0 srv_shutdown, 7715544 srv_idlesrv_master_thread log flush and writes: 10838675----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 3905404OS WAIT ARRAY INFO: signal count 27617015RW-shared spins 0, rounds 21456656, OS waits 2772950RW-excl spins 0, rounds 41132727, OS waits 487320RW-sx spins 227170, rounds 4363782, OS waits 66152Spin rounds per wait: 21456656.00 RW-shared, 41132727.00 RW-excl, 19.21 RW-sx------------------------LATEST DETECTED DEADLOCK------------------------2023-10-12 09:40:50 0x7fecedb1e700*** (1) TRANSACTION:TRANSACTION 404898333, ACTIVE 0 sec insertingmysql tables in use 1, locked 1LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1MySQL thread id 11375251, OS thread handle 140644580488960, query id 1031478425 10.128.0.160 emax_base updateINSERT INTO emax_user_tag  ( user_id,id_card_no,tag_name,create_time,update_time )  VALUES  ( 1712282059297107970,'A978B69811B82F4B08F929D26B360F2D980CDCB5F419F1DD46527FB90BE1E5A9','市场推广','2023-10-12 09:40:50.481','2023-10-12 09:40:50.481' )*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1155 page no 65295 n bits 312 index uniq_user_id_tag_name of table `emax_base`.`emax_user_tag` trx id 404898333 lock mode S waitingRecord lock, heap no 242 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 97c33f75fbadb002; asc   ?u    ;; 1: len 12; hex e5b882e59cbae68ea8e5b9bf; asc             ;; 2: len 8; hex 000000000044c0da; asc      D  ;;*** (2) TRANSACTION:TRANSACTION 404898332, ACTIVE 0 sec insertingmysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2MySQL thread id 11375224, OS thread handle 140655576868608, query id 1031478426 10.128.0.160 emax_base updateINSERT INTO emax_user_tag  ( user_id,id_card_no,tag_name,create_time,update_time )  VALUES  ( 1712282059297107970,'A978B69811B82F4B08F929D26B360F2D980CDCB5F419F1DD46527FB90BE1E5A9','信息技术服务','2023-10-12 09:40:50.481','2023-10-12 09:40:50.481' )*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 1155 page no 65295 n bits 312 index uniq_user_id_tag_name of table `emax_base`.`emax_user_tag` trx id 404898332 lock_mode X locks rec but not gapRecord lock, heap no 242 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 97c33f75fbadb002; asc   ?u    ;; 1: len 12; hex e5b882e59cbae68ea8e5b9bf; asc             ;; 2: len 8; hex 000000000044c0da; asc      D  ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 1155 page no 65295 n bits 312 index uniq_user_id_tag_name of table `emax_base`.`emax_user_tag` trx id 404898332 lock_mode X locks gap before rec insert intention waitingRecord lock, heap no 242 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 97c33f75fbadb002; asc   ?u    ;; 1: len 12; hex e5b882e59cbae68ea8e5b9bf; asc             ;; 2: len 8; hex 000000000044c0da; asc      D  ;;*** WE ROLL BACK TRANSACTION (1)
此处省略掉若干非关键信息行----------------------------END OF INNODB MONITOR OUTPUT============================