About No ManagedConnections available within configured blockingtimeout exception solve

 

recent reconstruction due to system and business needs , the need to migrate data online 100 million to the new library , due to business changes, new cousin table structure has changed , can not be directly used dba dump the way , you need to write your own conversion program migration. Today, when debugging , encountered an egg pain problem, that is a start query data are normal, but after a few queries timeout error log will report the specific log is as follows :

 

  

No ManagedConnections available within configured blocking timeout ( 5000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 5000 [ms] ))

 

 

found the next this error, all these comments are more, but the feeling never comes to the point , looked to find DBA , DBA directly represents the number of connections this library has been tight, also see from this error connection timed out , had so with that , but it is a start debugging a few times normal , and later reported abnormal , the feeling is certainly still a problem cause code number of connections tight , then carefully read the code and found that connection it should be said not closed ... is not closed fully . The PrepareStatement and ResultSet closed, but did not put the most important ... Connection closed Connection closed off to normal .

 

several online search results too misleading , so we recorded it, encounter this error , first confirm that your code is not relevant connection closed off , basically did not close the connection lead , and finally confirm the database number of connections is not really tight.

 

following code for reference :

 

  

 1         try{ 
 2             conn = sourceDs.getConnection(); 
 3             ps = conn.prepareStatement(selectTcBizOrder); 
 4             rs = ps.executeQuery(); 
 5             if(rs.next()){ 
 6                 result.put("auction_id", rs.getLong("auction_id")); 
 7                 result.put("logistics_status", rs.getInt("logistics_status")); 
 8                 result.put("attributes", rs.getString("attributes")); 
 9                 return result; 
10             }else{ 
11                 return null; 
12             } 
13         }catch(SQLException e){ 
14             LogFactory.getTaskLog().error("[select tc_biz_order SQLException], bizOrderId="+bizOrderId, e); 
15             return null; 
16         }catch(Exception e){ 
17             LogFactory.getTaskLog().error("[select tc_biz_order other Exception], bizOrderId="+bizOrderId, e); 
18             return null; 
19         }finally{ 
20             if(rs != null){ 
21                 try{ 
22                     rs.close(); 
23                 }catch(SQLException e){ 
24                     LogFactory.getTaskLog().error("[close ResultSet SQLException], bizOrderId="+bizOrderId, e); 
25                 } 
26             } 
27              
28             if(ps != null){ 
29                 try { 
30                     ps.close(); 
31                 } catch (SQLException e) { 
32                     LogFactory.getTaskLog().error("[close PreparedStatement SQLException], bizOrderId="+bizOrderId, e); 
33                 } 
34             } 
35              
36             if(conn != null){ 
37                 try{ 
38                     conn.close(); 
39                 }catch(SQLException e){ 
40                     LogFactory.getTaskLog().error("[close Connection SQLException], bizOrderId="+bizOrderId, e); 
41                 } 
42             } 
43         }