【 TiDB 使用环境】生产环境
【 TiDB 版本】 6.5.2
【复现路径】做过哪些操作出现的问题
逻辑代码:如下:
//多线程批量插入
insert into customer_reach_standard_record valuse(…);
//多线程读取统计:
select count(*) from customer_reach_standard_record ;
或者查询等:
SELECT t1.customer_no FROM customer_reach_standard_record t1 where t1.customer_no = ? AND t1.standard_type = ? limit 1
【遇到的问题:业务侧Lock异常
应用侧错误日志:
The error may exist in class path resource [mapper/CustomerReachStandardRecordMapper.xml]
The error may involve defaultParameterMap
The error occurred while setting parameters
SQL: SELECT t1.customer_no FROM customer_reach_standard_record t1 where t1.customer_no = ? AND t1.standard_type = ? limit 1
Cause: java.sql.SQLException: other error: key is locked (backoff or cleanup) primary_lock: 74800000000096F1C55F69800000000000000103800000000003163F039721E34F1700B002 lock_version: 442039310006091794 key: 74800000000096F1C55F729721E34F1700B002 lock_ttl: 3000 txn_size: 5 use_async_commit: true min_commit_ts: 442039310006091795
; uncategorized SQLException; SQL state [HY000]; error code [1105]; other error: key is locked (backoff or cleanup) primary_lock: 74800000000096F1C55F69800000000000000103800000000003163F039721E34F1700B002 lock_version: 442039310006091794 key: 74800000000096F1C55F729721E34F1700B002 lock_ttl: 3000 txn_size: 5 use_async_commit: true min_commit_ts: 442039310006091795; nested exception is java.sql.SQLException: other error: key is locked (backoff or cleanup) primary_lock: 74800000000096F1C55F69800000000000000103800000000003163F039721E34F1700B002 lock_version: 442039310006091794 key: 74800000000096F1C55F729721E34F1700B002 lock_ttl: 3000 txn_size: 5 use_async_commit: true min_commit_ts: 442039310006091795
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
at com.sun.proxy.$Proxy198.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:90)
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.$Proxy224.getByCustomerAndType(Unknown Source)
at com.yss.reward.service.impl.CustomerReachStandardRecordServiceImpl.lambda$checkCustomerBatch$0(CustomerReachStandardRecordServiceImpl.java:113)
at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: other error: key is locked (backoff or cleanup) primary_lock: 74800000000096F1C55F69800000000000000103800000000003163F039721E34F1700B002 lock_version: 442039310006091794 key: 74800000000096F1C55F729721E34F1700B002 lock_ttl: 3000 txn_size: 5 use_async_commit: true min_commit_ts: 442039310006091795
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.execute$original$BR1eDmhd(PreparedStatement.java:1242)
at com.mysql.jdbc.PreparedStatement.execute$original$BR1eDmhd$accessor$r4nNE4ok(PreparedStatement.java)
at com.mysql.jdbc.PreparedStatement$auxiliary$kUNBnNaX.call(Unknown Source)