【新炬網路名師大講堂】tomcat 環境下連線池洩露故障處理一例

某移動業務系統正常訪問量就是50併發量，系統設定最大資料來源連線數是200個，但每隔10分鐘，監測資料來源連線數就達到200個以上，導致系統無法正常使用，開發商嘗試採用多臺伺服器進行負載處理，連線三天問題依舊。

接到這種問題，第一時間想到應該是連線洩露了，造成連線沒有釋放，可按照如下步驟進行排查:

1.檢查資料來源連線配置

資料來源連線配置分兩種，一種是採用中介軟體自帶資料來源配置，另外一種情況是應用程式自己使用第三方開源資料連線池。需要檢查連線池是否配置了連線未使用超時設定，讓系統自動關閉不活動連線

2.監測資料連線情況

利用第三方監測程式對資料來源連線進行監測，可採用probe進行監測，如果程式使用了Proxool連線池，Proxoolw會自動提供監測資料來源功能介面

3.執行緒情況

當資料來源連線較高生成執行緒資訊，看執行緒做什麼，鎖在哪

jrcmd.exe pid  print_threads >threaddump.log

“http-apr-8082-exec-114″ id=213 idx=0×374 tid=9344 prio=5 alive, native_blocked, daemon
at jrockit/net/SocketNativeIO.readBytesPinned(Ljava/io/FileDescriptor;[BIII)I(Native Method)
at jrockit/net/SocketNativeIO.socketRead(SocketNativeIO.java:32)
at java/net/SocketInputStream.socketRead0(Ljava/io/FileDescriptor;[BIII)I(SocketInputStream.java)
at java/net/SocketInputStream.read(SocketInputStream.java:129)
at oracle/net/ns/Packet.receive()V(Unknown Source)
at oracle/net/ns/DataPacket.receive()V(Unknown Source)
at oracle/net/ns/NetInputStream.getNextPacket()V(Unknown Source)
at oracle/net/ns/NetInputStream.read([BII)I(Unknown Source)[inlined]
at oracle/net/ns/NetInputStream.read([B)I(Unknown Source)[inlined]
at oracle/net/ns/NetInputStream.read()I(Unknown Source)[optimized]
at oracle/jdbc/driver/T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1099)[optimized]
at oracle/jdbc/driver/T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1070)[inlined]
at oracle/jdbc/driver/T4C8Oall.receive(T4C8Oall.java:478)[optimized]
at oracle/jdbc/driver/T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)[inlined]
at oracle/jdbc/driver/T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796)[optimized]
at oracle/jdbc/driver/OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)[optimized]
at oracle/jdbc/driver/T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836)
at oracle/jdbc/driver/OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)[optimized]
at oracle/jdbc/driver/OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)[optimized]
at oracle/jdbc/driver/OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
    ^– Holding lock:  lock]
    ^– Holding lock:  lock]
at net/sourceforge/jdbclogger/core/PreparedStatementWrapper.executeQuery(PreparedStatementWrapper.java:402)
at sun/reflect/GeneratedMethodAccessor531.invoke(Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;(Unknown Source)
at sun/reflect/DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)[optimized]
at java/lang/reflect/Method.invoke(Method.java:597)[optimized]
at org/logicalcobwebs/proxool/ProxyStatement.invoke(ProxyStatement.java:100)[inlined]
at org/logicalcobwebs/proxool/ProxyStatement.intercept(ProxyStatement.java:57)[optimized]
at $java/sql/Statement$$EnhancerByProxool$$2fb99b38.executeQuery()Ljava/sql/ResultSet;()[optimized]
at org/hibernate/jdbc/AbstractBatcher.getResultSet(AbstractBatcher.java:208)[optimized]
at org/hibernate/loader/Loader.getResultSet(Loader.java:1808)[optimized]
at org/hibernate/loader/Loader.doQuery(Loader.java:697)[inlined]
at org/hibernate/loader/Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)[inlined]
at org/hibernate/loader/Loader.doList(Loader.java:2228)[inlined]
at org/hibernate/loader/Loader.listIgnoreQueryCache(Loader.java:2125)[inlined]
at org/hibernate/loader/Loader.list(Loader.java:2120)[inlined]
at org/hibernate/loader/custom/CustomLoader.list(CustomLoader.java:312)[inlined]
at org/hibernate/impl/SessionImpl.listCustomQuery(SessionImpl.java:1722)[optimized]
at org/hibernate/impl/AbstractSessionImpl.list(AbstractSessionImpl.java:165)[optimized]
at org/hibernate/impl/SQLQueryImpl.list(SQLQueryImpl.java:175)[optimized]
at com/gpdi/infores/pipeline/statistics/dao/hibernate/ComprehensiveDAOHibernate$4.doInHibernate(ComprehensiveDAOHibernate.java:139)[optimized]
at org/springframework/orm/hibernate3/HibernateTemplate.doExecute(HibernateTemplate.java:419)[inlined]
at org/springframework/orm/hibernate3/HibernateTemplate.execute(HibernateTemplate.java:339)[inlined]
at com/gpdi/infores/pipeline/statistics/dao/hibernate/ComprehensiveDAOHibernate.getQueryMapList(ComprehensiveDAOHibernate.java:121)[optimized]
at com/gpdi/infores/pipeline/flow/service/impl/InspectionScheduleServiceImpl.getAlsoArrange(InspectionScheduleServiceImpl.java:54)
at com/gpdi/infores/pipeline/flow/service/impl/InspectionScheduleServiceImpl$$FastClassByCGLIB$$889ae678.invoke(ILjava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;()
at net/sf/cglib/proxy/MethodProxy.invoke(MethodProxy.java:149)

從上面可以看出連線可能是鎖在資料來源ojdbc 上，可以嘗試採用替換最新ojdbc jar 版本

 

4.檢查系統程式碼連線是怎麼獲取及關閉

檢查這個方面，取決於你是否有豐富的開發經驗，因為目前開發一個應用系統，都會採用很多開源框架快速實現業務功能。以本次故障為例，開發商使用的spring 開源框架 管理資料來源連線：

public List findList(String hsql) {
  Session session =this.getSession();
Query q =session.createQuery(hsql);
return q.list();
}

如果採用getSession這種方式獲取連線，然後查詢資料時，Spring並不能夠自動管理連線，也就是說，每呼叫一次就申請一個連線，直到 連線池中的連線耗盡現象，Spring 建議採用getHibernateTemplate()採用這種方式進行操作連線，具體可參考如下spring 官方資料:

()

Note that this is not meant to be invoked from HibernateTemplate code but rather just in plain Hibernate code. Either rely on a thread-bound Session or use it in combination with releaseSession(org.hibernate.Session).

spring 官方說了很清楚了getSession 連線事務spring 不管理，如果使用這種方法需使用releaseSession()方法才能關閉連線
protected final void releaseSession(Session session) Close the given Hibernate Session, created via this DAO’s SessionFactory, if it isn’t bound to the thread (i.e. isn’t a transactional Session).
Typically used in plain Hibernate code, in combination with getSession() and convertHibernateAccessException(org.hibernate.HibernateException).

解決辦法

1.上面程式碼加入releaseSession()這個方法，進行手動關閉連線

public List findList(String hsql) {
Session session =this.getSession();
try {
Query q =session.createQuery(hsql);
return q.list();
}catch(Exception e){
System.out.print(e.getMessage());
}
finally {
   releaseSession(session);
}
return null;
}

2.在spring事務配置裡面加入此方面事務配置，讓不受管理的getSession 方法，自動被spring 事務進行管理

PROPAGATION_REQUIRED
PROPAGATION_REQUIRED
PROPAGATION_REQUIRED
PROPAGATION_REQUIRED
PROPAGATION_REQUIRED
PROPAGATION_REQUIRED
 PROPAGATION_REQUIRED
PROPAGATION_REQUIRED,readOnly



class=”org.springframework.orm.hibernate3.HibernateTransactionManager”>