{"id":2207,"date":"2022-08-27T11:41:54","date_gmt":"2022-08-27T03:41:54","guid":{"rendered":"https:\/\/coderbee.net\/?p=2207"},"modified":"2022-08-27T11:45:45","modified_gmt":"2022-08-27T03:45:45","slug":"hikaricp-%e4%b8%8e-sqltimeoutexception","status":"publish","type":"post","link":"https:\/\/coderbee.net\/index.php\/framework\/20220827\/2207","title":{"rendered":"HikariCP \u4e0e SQLTimeoutException"},"content":{"rendered":"

\u6700\u8fd1\u78b0\u5230\u4e00\u4e2a\u95ee\u9898\uff1a\u9879\u76ee\u7684\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u4f7f\u7528\u7684\u662f HkiariCP\uff0c\u5bf9\u6bcf\u4e2a SQL \u8bed\u53e5\u7684\u6267\u884c\u8d85\u65f6\u65f6\u95f4\u8bbe\u7f6e\u4e3a 30\u79d2\uff0c\u7ed3\u679c\u6709\u4e2a SQL \u8d85\u65f6\u4e86\uff0c\u629b\u51fa\u5f02\u5e38 SQLTimeoutException\uff0c\u5e94\u7528\u5c42\u56de\u6eda\u4e8b\u52a1\u65f6\u629b\u51fa\u4e86\u8fde\u63a5\u5df2\u5173\u95ed\u7684\u5f02\u5e38\u3002\u4f46\u4e8b\u5b9e\u4e0a\u4e8b\u52a1\u5374\u63d0\u4ea4\u4e86\u3002<\/p>\n

\u5199\u4e86\u4e2a\u7b80\u5355\u7684\u4ee3\u7801\u6765\u6a21\u62df\u751f\u4ea7\u573a\u666f\uff1a
\n\u5728 Spring \u7684\u58f0\u660e\u5f0f\u4e8b\u52a1\u5185\uff0c\u6709\u4e00\u4e2a insert \u64cd\u4f5c\uff0c\u7136\u540e\u662f\u4e00\u4e2a update \u64cd\u4f5c\uff0c\u5728\u6570\u636e\u5e93\u5ba2\u6237\u7aef\u6267\u884c select for update<\/code> \u628a\u8981\u66f4\u65b0\u7684\u884c\u9501\u4f4f\uff0c\u8fd9\u6837 update \u64cd\u4f5c\u5c31\u4f1a\u8d85\u65f6\u3002<\/p>\n

\u591a\u6b21\u8c03\u8bd5\u53d1\u73b0 HikariCP \u5728\u78b0\u5230 SQL \u5f02\u5e38\u65f6\u6709\u4e2a\u68c0\u67e5\u673a\u5236\uff0c\u6ee1\u8db3\u7279\u5b9a\u6761\u4ef6\u7684\u5f02\u5e38\u4f1a\u76f4\u63a5\u5173\u95ed\u5e95\u5c42\u6570\u636e\u5e93\u8fde\u63a5\uff0cSpring \u62ff\u5230\u7684\u662f\u8fde\u63a5\u7684\u4ee3\u7406\uff0c\u7531\u4e8e\u8fde\u63a5\u5df2\u5173\u95ed\uff0c\u81ea\u7136\u6ca1\u6cd5\u56de\u6eda\u4e8b\u52a1\uff0c\u4f1a\u78b0\u5230\u8fde\u63a5\u5df2\u5173\u95ed\u5f02\u5e38\u3002<\/p>\n

<\/p>\n

HikariProxyPreparedStatement<\/p>\n

public int executeUpdate() throws SQLException {\n    try {\n        return super.executeUpdate();\n    } catch (SQLException var2) {\n        throw this.checkException(var2);\n    }\n}\n\nfinal SQLException checkException(SQLException e)\n{\n  return connection.checkException(e);\n}\n<\/code><\/pre>\n
ProxyConnection<\/p>\n
final SQLException checkException(SQLException sqle)\n{\n  boolean evict = false;\n  SQLException nse = sqle;\n  final SQLExceptionOverride exceptionOverride = poolEntry.getPoolBase().exceptionOverride;\n  for (int depth = 0; delegate != ClosedConnection.CLOSED_CONNECTION && nse != null && depth < 10; depth++) {\n     final String sqlState = nse.getSQLState();\n     if (sqlState != null && sqlState.startsWith(\"08\")\n         || nse instanceof SQLTimeoutException\n         || ERROR_STATES.contains(sqlState)\n         || ERROR_CODES.contains(nse.getErrorCode())) {\n\n        if (exceptionOverride != null && exceptionOverride.adjudicate(nse) == DO_NOT_EVICT) {\n           break;\n        }\n\n        \/\/ broken connection\n        evict = true;\n        break;\n     }\n     else {\n        nse = nse.getNextException();\n     }\n  }\n\n  if (evict) {\n     SQLException exception = (nse != null) ? nse : sqle;\n     LOGGER.warn(\"{} - Connection {} marked as broken because of SQLSTATE({}), ErrorCode({})\",\n        poolEntry.getPoolName(), delegate, exception.getSQLState(), exception.getErrorCode(), exception);\n     leakTask.cancel();\n     poolEntry.evict(\"(connection is broken)\");  \/\/ \u8fd9\u91cc\u7531\u5f02\u6b65\u7ebf\u7a0b\u6765\u5173\u95ed\u5e95\u5c42\u7684\u7269\u7406\u8fde\u63a5\n     delegate = ClosedConnection.CLOSED_CONNECTION; \/\/ \u8fde\u63a5\u4ee3\u7406\u88ab\u66ff\u6362\u4e3a\u5df2\u5173\u95ed\u7684\uff0cSpring \u81ea\u7136\u65e0\u6cd5\u56de\u6eda\u4e8b\u52a1\n  }\n\n  return sqle;\n}\n<\/code><\/pre>\n
\u90a3\u4e48\u8fd8\u6709\u4e2a\u95ee\u9898\uff0c\u4e3a\u4ec0\u4e48\u8fde\u63a5\u5173\u95ed\u4f1a\u63d0\u4ea4\u4e8b\u52a1\uff0c\u5f97\u770b\u770b JDBC Connection \u7684\u6587\u6863\uff0c\u6709\u5982\u4e0b\u7684\u7279\u522b\u8bf4\u660e\uff1a\u8fde\u63a5\u5173\u95ed\u65f6\uff0c\u4e8b\u52a1\u662f\u63d0\u4ea4\u8fd8\u662f\u56de\u6eda\u53d6\u51b3\u4e8e\u65f6\u5177\u4f53\u7684\u5b9e\u73b0\uff0c\u6bd5\u7adf JDBC \u53ea\u662f\u4e00\u4e2a\u89c4\u8303\u3001\u4e0a\u5c42 API \u3002
\nhttps:\/\/docs.oracle.com\/javase\/8\/docs\/api\/java\/sql\/Connection.html#close–<\/p>\n
It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the \nclose method.  If the \nclose method is called and there is an active transaction, the results are implementation-defined. \n<\/code><\/pre>\n
\u90a3\u4e48\u600e\u4e48\u7531\u4e0a\u5c42\u5e94\u7528\u6765\u51b3\u5b9a\u662f\u63d0\u4ea4\u8fd8\u662f\u56de\u6eda\u4e8b\u52a1\u5462\uff0c\u4ece checkException<\/code> \u65b9\u6cd5\u53ef\u4ee5\u53d1\u73b0\uff0c\u5982\u679c\u914d\u7f6e\u4e00\u4e2a SQLExceptionOverride \u5b9e\u73b0\u7c7b\u4e14\u5176\u65b9\u6cd5 adjudicate(nse)<\/code> \u8fd4\u56de DO_NOT_EVICT<\/code> \u5c31\u4e0d\u4f1a\u76f4\u63a5\u5173\u95ed\u5e95\u5c42\u8fde\u63a5\u3002<\/p>\n
\u6211\u4eec\u53ef\u4ee5\u5b9a\u5236\u4e00\u4e2a SQLExceptionOverride<\/code> \u5b9e\u73b0\u5982\u4e0b\uff0c\u7136\u540e\u901a\u8fc7\u914d\u7f6e\u5c5e\u6027 exceptionOverrideClassName<\/code> \u6765\u6307\u5b9a\u3002<\/p>\n
public class MyExceptionOverride implements SQLExceptionOverride {\n    public Override adjudicate(SQLException sqlException) {\n        \/\/ HikariCP \u78b0\u5230 SQLException \u4e0d\u76f4\u63a5\u5173\u95ed\u5e95\u5c42\u8fde\u63a5\uff0c\u7531\u4e0a\u5c42\u5e94\u7528\u6765\u51b3\u5b9a\u3002\n        return Override.CONTINUE_EVICT;\n    }\n}\n<\/code><\/pre>\n
\n
\u6b22\u8fce\u5173\u6ce8\u6211\u7684\u5fae\u4fe1\u516c\u4f17\u53f7: coderbee\u7b14\u8bb0<\/strong> \u3002
\n<\/p>\n","protected":false},"excerpt":{"rendered":"
\u6700\u8fd1\u78b0\u5230\u4e00\u4e2a\u95ee\u9898\uff1a\u9879\u76ee\u7684\u6570\u636e\u5e93\u8fde\u63a5\u6c60\u4f7f\u7528\u7684\u662f HkiariCP\uff0c\u5bf9\u6bcf\u4e2a SQL  … \u7ee7\u7eed\u9605\u8bfb →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[255],"tags":[344,360],"_links":{"self":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/2207"}],"collection":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/comments?post=2207"}],"version-history":[{"count":3,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/2207\/revisions"}],"predecessor-version":[{"id":2210,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/2207\/revisions\/2210"}],"wp:attachment":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/media?parent=2207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/categories?post=2207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/tags?post=2207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}