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 < queryTimeout) {
                      statement.setQueryTimeout(transactionTimeout);
                    }
                  }
                }
                

                jdbc 测试代码

                try (Connection connection = dataSource.getConnection()) {
                            // 创建 Statement 对象
                            Statement statement = connection.createStatement();
                            // 设置查询超时时间为 10 秒
                            int timeoutInSeconds = 10;
                            statement.setQueryTimeout(timeoutInSeconds);
                            // 执行查询
                            String sqlQuery = "SELECT sleep(12)";
                            ResultSet resultSet = statement.executeQuery(sqlQuery);
                            // 处理结果集
                            while (resultSet.next()) {
                                // 处理每一行数据
                            }
                            resultSet.close();
                            statement.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                

                 
                 做网站难不难  长沙seo优化公司  可视化网页设计工具  网站制作建设  珠海网站建设推广  长沙公司做网站