mysql死锁报错

[zhaoli2333]

• 我已经在 issues 搜索过相关问题了,并没有重复的。

你是否希望来认领这个Bug。

「 N 」

环境信息

• KnowStreaming version : 3.0.1
• Operating System version : xxx
• Java version : 11

重现该问题的步骤

正常启动KnowStreaming

实际结果

后台日志间接性输出mysql死锁的错误日志，内容如下：

2022-10-28 14:00:00.846 ERROR 56082 --- [ool-7-thread-19] k.c.s.h.c.i.HealthCheckResultServiceImpl : method=batchReplace||clusterPhyId=1||checkResultList=[HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=213, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=213, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=234, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=234, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=239, passed=0), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=239, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=265, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=265, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=288, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=288, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=297, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=297, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=298, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=298, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=300, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=300, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=301, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=301, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=302, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=302, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_REQUEST_QUEUE_FULL, clusterPhyId=1, resName=303, passed=1), HealthCheckResultPO(dimension=1, configName=HC_BROKER_NETWORK_PROCESSOR_AVG_IDLE_TOO_LOW, clusterPhyId=1, resName=303, passed=1)]||errMsg=exception!

org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: java.sql.SQLTransactionRollbackException: (conn=34329) Deadlock found when trying to get lock; try restarting transaction
### The error may exist in URL [jar:file:/usr/local/KnowStreaming/libs/ks-km.jar!/BOOT-INF/lib/km-persistence-3.1.0-SNAPSHOT.jar!/mybatis/HealthCheckResultMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: insert into ks_km_health_check_result (dimension, config_name, cluster_phy_id, res_name, passed)     values              (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)      ,        (?, ?, ?, ?, ?)           on duplicate key update     dimension = dimension, config_name = config_name, cluster_phy_id = cluster_phy_id,     res_name = res_name, passed = passed
### Cause: java.sql.SQLTransactionRollbackException: (conn=34329) Deadlock found when trying to get lock; try restarting transaction
; (conn=34329) Deadlock found when trying to get lock; try restarting transaction; nested exception is java.sql.SQLTransactionRollbackException: (conn=34329) 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:91)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
        at com.sun.proxy.$Proxy106.insert(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
        at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:60)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
        at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
        at com.sun.proxy.$Proxy133.batchReplace(Unknown Source)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        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 org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
        at com.sun.proxy.$Proxy134.batchReplace(Unknown Source)
        at com.xiaojukeji.know.streaming.km.core.service.health.checkresult.impl.HealthCheckResultServiceImpl.batchReplace(HealthCheckResultServiceImpl.java:100)
        at com.xiaojukeji.know.streaming.km.task.health.AbstractHealthCheckTask.calAndUpdateHealthCheckResult(AbstractHealthCheckTask.java:56)
        at com.xiaojukeji.know.streaming.km.task.health.AbstractHealthCheckTask.processClusterTask(AbstractHealthCheckTask.java:33)
        at com.xiaojukeji.know.streaming.km.task.metrics.AbstractAsyncMetricsDispatchTask.lambda$asyncProcessSubTask$0(AbstractAsyncMetricsDispatchTask.java:33)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.sql.SQLTransactionRollbackException: (conn=34329) Deadlock found when trying to get lock; try restarting transaction
        at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:294)
        at org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:368)
        at org.mariadb.jdbc.message.ClientMessage.readPacket(ClientMessage.java:137)
        at org.mariadb.jdbc.client.impl.StandardClient.readPacket(StandardClient.java:819)
        at org.mariadb.jdbc.client.impl.StandardClient.readResults(StandardClient.java:758)
        at org.mariadb.jdbc.client.impl.StandardClient.readResponse(StandardClient.java:677)
        at org.mariadb.jdbc.client.impl.StandardClient.execute(StandardClient.java:620)
        at org.mariadb.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:93)
        at org.mariadb.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:256)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
        at jdk.internal.reflect.GeneratedMethodAccessor333.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
        at com.sun.proxy.$Proxy158.execute(Unknown Source)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
        at jdk.internal.reflect.GeneratedMethodAccessor722.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
        at com.sun.proxy.$Proxy156.update(Unknown Source)
        at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:56)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:85)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:181)
        at jdk.internal.reflect.GeneratedMethodAccessor512.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
        ... 26 common frames omitted

[RenChauncy]

111

[RenChauncy]

分我吧

[haoqi6677]

啊哈，这个好像有重复issue #662 @shirenchuang ，当时的解决办法，似乎只是降低了出现几率