<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0">
<base href="https://hibernate.atlassian.net">
<title>Message Title</title>
</head>
<body class="jira" style="color: #333333; font-family: Arial, sans-serif; font-size: 14px; line-height: 1.429">
<table id="background-table" cellpadding="0" cellspacing="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; background-color: #f5f5f5; border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0" bgcolor="#f5f5f5">
<!-- header here -->
<tbody>
<tr>
<td id="header-pattern-container" style="padding: 0; border-collapse: collapse; padding: 10px 20px">
<table id="header-pattern" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="header-avatar-image-container" valign="top" style="padding: 0; border-collapse: collapse; vertical-align: top; width: 32px; padding-right: 8px" width="32"> <img id="header-avatar-image" class="image_fix" src="https://avatar-cdn.atlassian.com/40769e88c0dc93d387a9e368c332fdf9?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%2F40769e88c0dc93d387a9e368c332fdf9%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue" height="32" width="32" border="0" style="border-radius: 3px; vertical-align: top"> </td>
<td id="header-text-container" valign="middle" style="padding: 0; border-collapse: collapse; vertical-align: middle; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px"> <a class="user-hover" rel="edwin" id="email_edwin" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=edwin" style="color:#6c797f;; color: #3b73af; text-decoration: none">Edwin Dalorzo</a> <strong>created</strong> an issue </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="email-content-container" style="padding: 0; border-collapse: collapse; padding: 0 20px">
<table id="email-content-table" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; border-spacing: 0; border-collapse: separate">
<tbody>
<tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<td class="email-content-rounded-top mobile-expand" style="padding: 0; border-collapse: collapse; color: #ffffff; padding: 0 15px 0 16px; height: 15px; background-color: #ffffff; border-left: 1px solid #cccccc; border-top: 1px solid #cccccc; border-right: 1px solid #cccccc; border-bottom: 0; border-top-right-radius: 5px; border-top-left-radius: 5px; height: 10px; line-height: 10px; padding: 0 15px 0 16px; mso-line-height-rule: exactly" height="10" bgcolor="#ffffff"> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff" bgcolor="#ffffff">
<table class="page-title-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td class="page-title-pattern-first-line " style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; padding-top: 10px"> <a href="https://hibernate.atlassian.net/browse/HHH" style="color: #3b73af; text-decoration: none">Hibernate ORM</a> / <a href="https://hibernate.atlassian.net/browse/HHH-12137" style="color: #3b73af; text-decoration: none"><img src="cid:jira-generated-image-avatar-fb89b006-bcc3-45bb-aa16-be0942701c10" height="16" width="16" border="0" align="absmiddle" alt="Bug" style="vertical-align: text-bottom"></a> <a href="https://hibernate.atlassian.net/browse/HHH-12137" style="color: #3b73af; text-decoration: none">HHH-12137</a> </td>
</tr>
<tr>
<td style="vertical-align: top;; padding: 0; border-collapse: collapse; padding-right: 5px; font-size: 20px; line-height: 30px; mso-line-height-rule: exactly" class="page-title-pattern-header-container"> <span class="page-title-pattern-header" style="font-family: Arial, sans-serif; padding: 0; font-size: 20px; line-height: 30px; mso-text-raise: 2px; mso-line-height-rule: exactly; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-12137" style="color: #3b73af; text-decoration: none">Improper Handling of Connections with Socket Timeouts</a> </span> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand wrapper-special-margin" style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff; padding-top: 10px; padding-bottom: 5px" bgcolor="#ffffff">
<table class="keyvalue-table" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Issue Type:</th>
<td class="has-icon" style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-avatar-fb89b006-bcc3-45bb-aa16-be0942701c10" height="16" width="16" border="0" align="absmiddle" alt="Bug" style="vertical-align: text-bottom"> Bug </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Affects Versions:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> 5.2.12 </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Assignee:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> Unassigned </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Components:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> hibernate-entitymanager </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Created:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> 28/Nov/2017 19:31 PM </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Environment:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> The following are the libraries I used to reproduce the problem: <br> <br> * Hibernate Entity Manager v5.2.12 <br> * Apache Commons DBCP2 v2.1.1 <br> * PostgreSQL JDBC Driver v9.2-1004-jdbc41 <br> * Spring/Spring ORM 5.0.2.RELEASE <br> <br> The following was my testing environment: <br> <br> * MacOS Sierra 10.12.6 <br> * PostgreSQL Server PostgreSQL 9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit <br> * Oracle JDK build 1.8.0_141-b15 <br> </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Labels:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> entitymanager hibernate socketTimeout </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Priority:</th>
<td class="has-icon" style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <img src="cid:jira-generated-image-static-major-7e3fad72-0efb-4197-8c64-1c3bc25f8675" height="16" width="16" border="0" align="absmiddle" alt="Major" style="vertical-align: text-bottom"> Major </td>
</tr>
<tr>
<th style="color: #707070; font: normal 14px/20px Arial, sans-serif; text-align: left; vertical-align: top; padding: 2px 0">Reporter:</th>
<td style="padding: 0; border-collapse: collapse; font: normal 14px/20px Arial, sans-serif; padding: 2px 0 2px 5px; vertical-align: top"> <a class="user-hover" rel="edwin" id="email_edwin" href="https://hibernate.atlassian.net/secure/ViewProfile.jspa?name=edwin" style="color:#6c797f;; color: #3b73af; text-decoration: none">Edwin Dalorzo</a> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand issue-description-container" style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff; padding-top: 5px; padding-bottom: 10px" bgcolor="#ffffff">
<table class="text-paragraph-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 2px">
<tbody>
<tr>
<td class="text-paragraph-pattern-container mobile-resize-text " style="padding: 0; border-collapse: collapse; padding: 0 0 10px"> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0; margin-top: 0"><b>The Problem</b></p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">Recently I started experiencing issues with our connection pool (Apache DBCP2) in several production applications . Every certain amount of time I was forced to restart different applications because they run out of connections in the database pool. </p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">After some investigation I discovered that the problem happened after a number of long running queries that had issued socket time outs. </p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">By inspecting our runtime environments using Java Mission Control, we could see that the JMX bean of our connection pool showed that all the connections in the pool were active and they were never been released.</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">Upon application restart everything would go back to normal operation. </p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0"><b>Investigation Discoveries</b></p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">After further investigation I managed to reproduce the scenario. All we needed to do was to force a socket timeout during a Hibernate transaction to cause that a connection in our DBCP2 pool got affected by the issue. </p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">It seems Hibernate does not close the affected connection and the pool keeps it as active forever eventually (and gradually) consuming all connections in the pool until it starts rejecting new requests because the pool is full.</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">I also discovered that by forcing <tt>hibernate.connection.release_mode</tt> to <tt>after_statement</tt> the problem go solved, however, it is my intention that we use <tt>after_transaction</tt> mode.</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0"><b>Testing Scenario</b></p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">I use Spring to configure my <tt>DataSource</tt>. I defined a single connection in the pool to make the test easier.</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-xml" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
<span class="code-tag" style="color: #000091"><bean id=<span class="code-quote" style="color: #009100">"dataSource"</span> class=<span class="code-quote" style="color: #009100">"org.apache.commons.dbcp2.BasicDataSource"</span> destroy-method=<span class="code-quote" style="color: #009100">"close"</span>></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"driverClassName"</span> value=<span class="code-quote" style="color: #009100">"org.postgresql.Driver"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"url"</span> value=<span class="code-quote" style="color: #009100">"jdbc:postgresql://batcave.dalorzo.com:5432/bcs"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"username"</span> value=<span class="code-quote" style="color: #009100">"edalorzo"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"password"</span> value=<span class="code-quote" style="color: #009100">"edalorzo"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"validationQuery"</span> value=<span class="code-quote" style="color: #009100">"SELECT 1"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"testOnBorrow"</span> value=<span class="code-quote" style="color: #009100">"true"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"initialSize"</span> value=<span class="code-quote" style="color: #009100">"0"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"maxTotal"</span> value=<span class="code-quote" style="color: #009100">"1"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"maxIdle"</span> value=<span class="code-quote" style="color: #009100">"0"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"minIdle"</span> value=<span class="code-quote" style="color: #009100">"0"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"maxWaitMillis"</span> value=<span class="code-quote" style="color: #009100">"10000"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"connectionProperties"</span> value=<span class="code-quote" style="color: #009100">"tcpKeepAlive=true;socketTimeout=15"</span>/></span>
<span class="code-tag" style="color: #000091"></bean></span>
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">I use Spring ORM to bootstrap my <tt>EntityManagerFactory</tt>. </p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-xml" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
<span class="code-tag" style="color: #000091"><bean id=<span class="code-quote" style="color: #009100">"entityManagerFactory"</span> class=<span class="code-quote" style="color: #009100">"org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"</span>></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"dataSource"</span> ref=<span class="code-quote" style="color: #009100">"dataSource"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"jpaVendorAdapter"</span>></span>
<span class="code-tag" style="color: #000091"><bean class=<span class="code-quote" style="color: #009100">"org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"</span>></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"database"</span> value=<span class="code-quote" style="color: #009100">"POSTGRESQL"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"showSql"</span> value=<span class="code-quote" style="color: #009100">"true"</span>/></span>
<span class="code-tag" style="color: #000091"></bean></span>
<span class="code-tag" style="color: #000091"></property></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"jpaDialect"</span>></span>
<span class="code-tag" style="color: #000091"><bean class=<span class="code-quote" style="color: #009100">"org.springframework.orm.jpa.vendor.HibernateJpaDialect"</span>></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"prepareConnection"</span> value=<span class="code-quote" style="color: #009100">"true"</span>/></span>
<span class="code-tag" style="color: #000091"></bean></span>
<span class="code-tag" style="color: #000091"></property></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"jpaProperties"</span>></span>
<span class="code-tag" style="color: #000091"><props></span>
<span class="code-tag" style="color: #000091"><prop key=<span class="code-quote" style="color: #009100">"hibernate.dialect"</span>></span>org.hibernate.dialect.PostgreSQL9Dialect<span class="code-tag" style="color: #000091"></prop></span>
<span class="code-tag" style="color: #000091"><prop key=<span class="code-quote" style="color: #009100">"hibernate.temp.use_jdbc_metadata_defaults"</span>></span>false<span class="code-tag" style="color: #000091"></prop></span>
<span class="code-tag" style="color: #000091"></props></span>
<span class="code-tag" style="color: #000091"></property></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"persistenceUnitName"</span> value=<span class="code-quote" style="color: #009100">"test-bug"</span>/></span>
<span class="code-tag" style="color: #000091"><property name=<span class="code-quote" style="color: #009100">"packagesToScan"</span>></span>
<span class="code-tag" style="color: #000091"><list></span>
<span class="code-tag" style="color: #000091"><value></span>com.dalorzo.domain<span class="code-tag" style="color: #000091"></value></span>
<span class="code-tag" style="color: #000091"></list></span>
<span class="code-tag" style="color: #000091"></property></span>
<span class="code-tag" style="color: #000091"></bean></span>
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">And this is how test it the issue:</p>
<ul>
<li>First I open a database console and get a lock of a record and keep that transaction open.</li>
<li>Then I debug my code below step by step. I make a first invocation of my <tt>processRequest</tt> method where I try to run a native query that will attempt to get a lock on that exact same record. Since it is already held by the transaction I started in the previous step, the query will eventually cause the socketTimeout.</li>
<li>I rollback the transaction, print the stack trace and close the entity manager.</li>
<li>At this point I should have my connection back in the DBCP2 pool.</li>
<li>Then, from the command line console I release the record I obtain in the first step above.</li>
<li>And proceed debugging my second method invocation. It should succeed this time, since I was supposed to have 1 connection in the pool and the record is no longer locked. However, I get an error reporting that my pool is full.</li>
</ul> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">You can see the code I used to testing here:</p>
<div class="code panel" style="border-width: 1px;; border: 1px solid #cccccc; background: #f5f5f5; font-size: 12px; line-height: 1.333; font-family: monospace; border: 1px solid #cccccc; -moz-border-radius: 3px; border-radius: 3px; margin: 9px 0">
<div class="codeContent panelContent" style="padding: 9px 12px">
<pre class="code-java" style="margin: 10px 0 0; margin-top: 0; max-height: 30em; overflow: auto; white-space: pre-wrap; word-wrap: normal">
<span class="code-keyword" style="color: #000091">import</span> org.springframework.context.ConfigurableApplicationContext;
<span class="code-keyword" style="color: #000091">import</span> org.springframework.context.support.ClassPathXmlApplicationContext;
<span class="code-keyword" style="color: #000091">import</span> javax.persistence.EntityManager;
<span class="code-keyword" style="color: #000091">import</span> javax.persistence.EntityManagerFactory;
<span class="code-keyword" style="color: #000091">import</span> javax.persistence.EntityTransaction;
<span class="code-keyword" style="color: #000091">import</span> javax.persistence.Query;
<span class="code-keyword" style="color: #000091">import</span> java.util.List;
<span class="code-keyword" style="color: #000091">public</span> <span class="code-keyword" style="color: #000091">class </span>TestDriveOfHibernate {
<span class="code-keyword" style="color: #000091">public</span> <span class="code-keyword" style="color: #000091">static</span> void main(<span class="code-object" style="color: #910091">String</span>[] args) {
<span class="code-keyword" style="color: #000091">try</span> (ConfigurableApplicationContext context = <span class="code-keyword" style="color: #000091">new</span> ClassPathXmlApplicationContext(<span class="code-quote" style="color: #009100">"classpath:application-context.xml"</span>)) {
EntityManagerFactory emf = context.getBean(EntityManagerFactory.class);
<span class="code-object" style="color: #910091">System</span>.out.println(emf.getProperties());
<span class="code-keyword" style="color: #000091">try</span> {
<span class="code-comment" style="color: #808080">//before running <span class="code-keyword" style="color: #000091; color: #808080">this</span> get a lock on the record by running the following on the pgslq console
</span> <span class="code-comment" style="color: #808080">//>
</span> <span class="code-comment" style="color: #808080">//pgsql> begin;
</span> <span class="code-comment" style="color: #808080">//pgsql> select tote_id from totes where facility = <span class="code-quote" style="color: #009100; color: #808080">'SLCW'</span> and tote_id = <span class="code-quote" style="color: #009100; color: #808080">'70005'</span> FOR UPDATE;
</span> <span class="code-comment" style="color: #808080">//>
</span> <span class="code-comment" style="color: #808080">//don't commit the transaction just yet
</span> <span class="code-comment" style="color: #808080">//now proceed to the first method invocation to force the socket timeout
</span> processRequest(emf);
<span class="code-comment" style="color: #808080">//now end the transaction you started in the console before you run <span class="code-keyword" style="color: #000091; color: #808080">this</span> second method invocation
</span> <span class="code-comment" style="color: #808080">//>
</span> <span class="code-comment" style="color: #808080">// end;
</span> <span class="code-comment" style="color: #808080">//
</span> <span class="code-comment" style="color: #808080">//at <span class="code-keyword" style="color: #000091; color: #808080">this</span> point the record is free to be taken and I should have one connection in the pool.
</span> processRequest(emf);
<span class="code-comment" style="color: #808080">//however, you will notice that the connection we used in the first invocation never got back to the pool
</span> <span class="code-comment" style="color: #808080">//and <span class="code-keyword" style="color: #000091; color: #808080">this</span> second method reports the pool is full, and fails.
</span> }
<span class="code-keyword" style="color: #000091">finally</span> {
emf.close();
}
}
}
<span class="code-keyword" style="color: #000091">private</span> <span class="code-keyword" style="color: #000091">static</span> void processRequest(EntityManagerFactory emf) {
EntityManager em = emf.createEntityManager();
<span class="code-keyword" style="color: #000091">try</span> {
EntityTransaction tx = em.getTransaction();
tx.begin();
<span class="code-keyword" style="color: #000091">try</span> {
Query query = em.createNativeQuery(<span class="code-quote" style="color: #009100">"select tote_id from totes where facility = <span class="code-quote" style="color: #009100">'SLCW'</span> and tote_id = <span class="code-quote" style="color: #009100">'70005'</span> FOR UPDATE"</span>);
List<<span class="code-object" style="color: #910091">Object</span>> totes = query.getResultList();
<span class="code-object" style="color: #910091">System</span>.out.println(totes);
tx.commit();
}
<span class="code-keyword" style="color: #000091">catch</span> (Exception e) {
<span class="code-keyword" style="color: #000091">try</span> {
tx.rollback();
} <span class="code-keyword" style="color: #000091">catch</span> (Exception ex) {
ex.printStackTrace();
}
e.printStackTrace();
}
}
<span class="code-keyword" style="color: #000091">finally</span> {
em.close();
}
}
}
</pre>
</div>
</div> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0"><b>The Exceptions</b></p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">When the socket timeout kicks in, this is what I see in the stack trace</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">{{Nov 28, 2017 9:27:12 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions<br> WARN: SQL Error: 0, SQLState: 08006<br> Nov 28, 2017 9:27:12 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions<br> ERROR: An I/O error occured while sending to the backend.<br> org.hibernate.TransactionException: Unable to rollback against JDBC Connection<br> at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.rollback(AbstractLogicalConnectionImplementor.java:121)<br> at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.rollback(JdbcResourceLocalTransactionCoordinatorImpl.java:250)<br> at org.hibernate.engine.transaction.internal.TransactionImpl.rollback(TransactionImpl.java:100)<br> at com.backcountry.fulfillment.TestDriveOfHibernate.processRequest(TestDriveOfHibernate.java:58)<br> at com.backcountry.fulfillment.TestDriveOfHibernate.main(TestDriveOfHibernate.java:29)<br> Caused by: org.postgresql.util.PSQLException: This connection has been closed.<br> at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:822)<br> at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:839)<br> at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:492)<br> at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:492)<br> at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.rollback(AbstractLogicalConnectionImplementor.java:116)<br> ... 4 more<br> javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet<br> at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149)<br> at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)<br> at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423)<br> at org.hibernate.query.Query.getResultList(Query.java:146)<br> at com.backcountry.fulfillment.TestDriveOfHibernate.processRequest(TestDriveOfHibernate.java:52)<br> at com.backcountry.fulfillment.TestDriveOfHibernate.main(TestDriveOfHibernate.java:29)<br> Caused by: org.hibernate.exception.JDBCConnectionException: could not extract ResultSet<br> at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:115)<br> at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)<br> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)<br> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)<br> at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69)<br> at org.hibernate.loader.Loader.getResultSet(Loader.java:2168)<br> at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931)<br> at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893)<br> at org.hibernate.loader.Loader.doQuery(Loader.java:938)<br> at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)<br> at org.hibernate.loader.Loader.doList(Loader.java:2692)<br> at org.hibernate.loader.Loader.doList(Loader.java:2675)<br> at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507)<br> at org.hibernate.loader.Loader.list(Loader.java:2502)<br> at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335)<br> at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2162)<br> at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1016)<br> at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:152)<br> at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414)<br> ... 3 more<br> Caused by: org.postgresql.util.PSQLException: An I/O error occured while sending to the backend.<br> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:281)<br> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559)<br> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)<br> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)<br> at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)<br> at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)<br> at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)<br> ... 17 more<br> Caused by: java.net.SocketTimeoutException: Read timed out<br> at java.net.SocketInputStream.socketRead0(Native Method)<br> at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)<br> at java.net.SocketInputStream.read(SocketInputStream.java:171)<br> at java.net.SocketInputStream.read(SocketInputStream.java:141)<br> at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:143)<br> at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:112)<br> at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:71)<br> at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:269)<br> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1700)<br> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)<br> ... 23 more<br> }}</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">But in the second method invocation, when the connection should be available and record should be free I get the following:</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">{{Nov 28, 2017 9:29:04 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions<br> WARN: SQL Error: 0, SQLState: null<br> Nov 28, 2017 9:29:04 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions<br> ERROR: Cannot get a connection, pool error Timeout waiting for idle object<br> <span class="error">[main]</span> INFO org.springframework.context.support.ClassPathXmlApplicationContext - Closing org.springframework.context.support.ClassPathXmlApplicationContext@31ef45e3: startup date <span class="error">[Tue Nov 28 21:26:54 CST 2017]</span>; root of context hierarchy<br> <span class="error">[main]</span> INFO org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean - Closing JPA EntityManagerFactory for persistence unit 'test-api'<br> Exception in thread "main" org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection<br> at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)<br> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)<br> at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)<br> at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:109)<br> at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136)<br> at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getConnectionForTransactionManagement(LogicalConnectionManagedImpl.java:254)<br> at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.begin(LogicalConnectionManagedImpl.java:262)<br> at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.begin(JdbcResourceLocalTransactionCoordinatorImpl.java:214)<br> at org.hibernate.engine.transaction.internal.TransactionImpl.begin(TransactionImpl.java:56)<br> at com.backcountry.fulfillment.TestDriveOfHibernate.processRequest(TestDriveOfHibernate.java:49)<br> at com.backcountry.fulfillment.TestDriveOfHibernate.main(TestDriveOfHibernate.java:35)<br> Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object<br> at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)<br> at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)<br> at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)<br> at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:35)<br> at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:106)<br> ... 7 more<br> Caused by: java.util.NoSuchElementException: Timeout waiting for idle object<br> at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:449)<br> at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:363)<br> at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)<br> ... 11 more<br> }}</p> <p style="margin-top:0;margin-bottom:10px;; margin: 10px 0 0">I tested the exact same scenario using EclipseLink 2.5.0 and it worked just fine.</p> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td class="email-content-main mobile-expand " style="padding: 0; border-collapse: collapse; border-left: 1px solid #cccccc; border-right: 1px solid #cccccc; border-top: 0; border-bottom: 0; padding: 0 15px 0 16px; background-color: #ffffff" bgcolor="#ffffff"> <script type="application/ld+json">
{
"@context": "http://schema.org",
"@type": "EmailMessage",
"description": "View Issue",
"potentialAction": {
"@type": "ViewAction",
"target": "https://hibernate.atlassian.net/browse/HHH-12137?inbox=true&",
"name": "View Issue"
},
"publisher": {
"@type": "Organization",
"name": "Atlassian",
"url": "https://www.atlassian.com"
}
}
</script>
<table id="actions-pattern" cellspacing="0" cellpadding="0" border="0" width="100%" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 1px">
<tbody>
<tr>
<td id="actions-pattern-container" valign="middle" style="padding: 0; border-collapse: collapse; padding: 10px 0 10px 24px; vertical-align: middle; padding-left: 0">
<table align="left" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td class="actions-pattern-action-icon-container" style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 0; vertical-align: middle"> <a href="https://hibernate.atlassian.net/browse/HHH-12137#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none"> <img class="actions-pattern-action-icon-image" src="cid:jira-generated-image-static-comment-icon-ed893db8-67f5-4b03-9152-75c76269e0ed" alt="Add Comment" title="Add Comment" height="16" width="16" border="0" style="vertical-align: middle"> </a> </td>
<td class="actions-pattern-action-text-container" style="padding: 0; border-collapse: collapse; font-family: Arial, sans-serif; font-size: 14px; line-height: 20px; mso-line-height-rule: exactly; mso-text-raise: 4px; padding-left: 5px"> <a href="https://hibernate.atlassian.net/browse/HHH-12137#add-comment" target="_blank" title="Add Comment" style="color: #3b73af; text-decoration: none">Add Comment</a> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table> </td>
</tr>
<!-- there needs to be content in the cell for it to render in some clients -->
<tr>
<td class="email-content-rounded-bottom mobile-expand" style="padding: 0; border-collapse: collapse; color: #ffffff; padding: 0 15px 0 16px; height: 5px; line-height: 5px; background-color: #ffffff; border-top: 0; border-left: 1px solid #cccccc; border-bottom: 1px solid #cccccc; border-right: 1px solid #cccccc; border-bottom-right-radius: 5px; border-bottom-left-radius: 5px; mso-line-height-rule: exactly" height="5" bgcolor="#ffffff"> </td>
</tr>
</tbody>
</table> </td>
</tr>
<tr>
<td id="footer-pattern" style="padding: 0; border-collapse: collapse; padding: 12px 20px">
<table id="footer-pattern-container" cellspacing="0" cellpadding="0" border="0" style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="footer-pattern-mobile-separated-links" class="mobile-resize-text" width="100%" colspan="2" style="padding: 0; border-collapse: collapse; color: #999999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px"> Get Jira notifications on your phone! Download the Jira Cloud app for <a href="https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail" style="color: #3b73af; text-decoration: none">Android</a> or <a href="https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8" style="color: #3b73af; text-decoration: none">iOS</a>
<hr> </td>
</tr>
<tr>
<td id="footer-pattern-text" class="mobile-resize-text" width="100%" style="padding: 0; border-collapse: collapse; color: #999999; font-size: 12px; line-height: 18px; font-family: Arial, sans-serif; mso-line-height-rule: exactly; mso-text-raise: 2px"> This message was sent by Atlassian Jira <span id="footer-build-information">(v1001.0.0-SNAPSHOT#100073-<span title="af7f5196b8ccab2346cf947fa277b2af49d30c34" data-commit-id="af7f5196b8ccab2346cf947fa277b2af49d30c34}">sha1:af7f519</span>)</span> </td>
<td id="footer-pattern-logo-desktop-container" valign="top" style="padding: 0; border-collapse: collapse; padding-left: 20px; vertical-align: top">
<table style="border-collapse: collapse; mso-table-lspace: 0; mso-table-rspace: 0">
<tbody>
<tr>
<td id="footer-pattern-logo-desktop-padding" style="padding: 0; border-collapse: collapse; padding-top: 3px; opacity: 0.150"> <img id="footer-pattern-logo-desktop" src="cid:jira-generated-image-static-footer-desktop-logo-82d40b06-3d10-4391-b723-64860447bc76" alt="Atlassian logo" title="Atlassian logo" width="192" height="24" class="image_fix"> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table> </td>
</tr>
</tbody>
</table>
<img src="https://u4043402.ct.sendgrid.net/wf/open?upn=YNXLe5Uzuivj8jGpuXra04BSJ-2BS7raa1puTAtyVUP-2F0vngXMYTK-2FS3Irgj0Y-2FFD4P5sm2F75giQLDJioKNXLQqi7xM3DDdRZV-2B-2Fj5JzIEDqcCD3KcXX4xi3D0QvRCNsJ23q6Hxkix4pslhy9Z5XZ5igtUl1w3P4fPM7Nx-2B9U7A7ayVbOu16kuE97qEas6pktQa0uz-2B-2FfsbkLj40XYF2wS0XzcR-2FSU5OPMkJiqkRpphp4Dy1chQjIoczRhVzYgYb8" alt="" width="1" height="1" border="0" style="height:1px !important;width:1px !important;border-width:0 !important;margin-top:0 !important;margin-bottom:0 !important;margin-right:0 !important;margin-left:0 !important;padding-top:0 !important;padding-bottom:0 !important;padding-right:0 !important;padding-left:0 !important;"/>
</body>
</html>