springboot mybatis

您所在的位置：网站首页 › mysql设置执行sql超时时间 › springboot mybatis

springboot mybatis

2024-07-11 10:10| 来源: 网络整理| 查看: 265

mybatis-plus全局控制 mybatis-plus: configuration: # 单个SQL执行超时时间(含insert,delete,select)，一般应用不宜过长，单位秒 default-statement-timeout: 30 超时异常 ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request ; Statement cancelled due to timeout or client request; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:76) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) at com.sun.proxy.$Proxy133.selectList(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) mybatis单个SQL控制写XML 写法，单位秒 ... Mapper类写法，单位秒 @Mapper public interface DemoMapper extends BaseMapper { @Select("SELECT * FROM DEMO") @Options(timeout = 30) // 设置超时时间为 30 秒 List select(); } 超时异常同上(略) spring事务超时控制整个事务的超时时间，单位是秒。它的原理大致是事务启动时，事务上下文会计算一个到期时间deadLine（当前时间+超时时间），当mybatis -> prepareStatement时，会调用 SpringManagedTransaction 的getTimeOut，该方法会计算事务剩余时间timeToLiveInSeconds（deadLine-System.currentTimeMillis()），如果timeToLiveInSeconds小于0则报错TransactionTimedOutException，大于0根据情况设置为statement 的queryTimeOut （参照下文的源码分析）

情况1：事务剩余时间 timeToLiveInSeconds小于 statement 本身的 queryTimeOut，则 statement 的 setQueryTimeout 设置为 timeToLiveInSeconds

情况2: 事务剩余时间 timeToLiveInSeconds大于 statement 本身的 queryTimeOut，则 statement 的 setQueryTimeout 设置为 queryTimeOut

@Transactional(timeout = 15) public int test(){ demoMapper.select(); demoMapper.insert(...); // 其他数据库操作 }

根据它的原理及测试，可能会出现以下两种异常。

事务超时异常： TransactionTimedOutException org.springframework.transaction.TransactionTimedOutException: Transaction timed out: deadline was Thu Dec 14 16:06:47 CST 2023 at org.springframework.transaction.support.ResourceHolderSupport.checkTransactionTimeout(ResourceHolderSupport.java:155) at org.springframework.transaction.support.ResourceHolderSupport.getTimeToLiveInMillis(ResourceHolderSupport.java:144) at org.springframework.transaction.support.ResourceHolderSupport.getTimeToLiveInSeconds(ResourceHolderSupport.java:128) at org.mybatis.spring.transaction.SpringManagedTransaction.getTimeout(SpringManagedTransaction.java:125) at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:87) at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) statement执行超时异常： MySQLTimeoutException com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:113) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:372) at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) 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.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) at com.sun.proxy.$Proxy197.execute(Unknown Source) at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) JDBC也可以配置socket超时时间配置socketTimeout 单位毫秒，一般不推荐配这个 jdbc:mysql://localhost:3066/testdb?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&serverTimezone=Asia/Shanghai &socketTimeout=60000 总结

mybatis 执行SQL超时，底层也是用了 Jdbc 的 Statment 的 setQueryTimeout

mybatis设置超时源码（spring5.2.15+mybatis3.5.9）

BaseStatementHandler.java

public abstract class BaseStatementHandler implements StatementHandler { protected void setStatementTimeout(Statement stmt, Integer transactionTimeout) throws SQLException { Integer queryTimeout = null; if (mappedStatement.getTimeout() != null) { queryTimeout = mappedStatement.getTimeout(); } else if (configuration.getDefaultStatementTimeout() != null) { queryTimeout = configuration.getDefaultStatementTimeout(); } if (queryTimeout != null) { stmt.setQueryTimeout(queryTimeout); } StatementUtil.applyTransactionTimeout(stmt, queryTimeout, transactionTimeout); }

StatementUtil.java

public class StatementUtil { private StatementUtil() { // NOP } /** * Apply a transaction timeout. *

* Update a query timeout to apply a transaction timeout. *

* @param statement a target statement * @param queryTimeout a query timeout * @param transactionTimeout a transaction timeout * @throws SQLException if a database access error occurs, this method is called on a closed Statement */ public static void applyTransactionTimeout(Statement statement, Integer queryTimeout, Integer transactionTimeout) throws SQLException { if (transactionTimeout == null) { return; } // 事务剩余时间小于 statement自己的queryTimeout，就用事务的剩余时间，所以在事务里面statement的超时时间是动态的。 if (queryTimeout == null || queryTimeout == 0 || transactionTimeout

【本文地址】

springboot mybatis

springboot mybatis

今日新闻

推荐新闻