title: Hibernate hangs during SELECT with SQL server
body: <p>I'm adding support to our application to use SQL Server (it already works fine with MySQL) and hitting a problem with Hibernate/JDBC queries hanging after a few successes.  In this case the database is completely empty to start with.  The problem occurs while we are loading data into the database.</p>
  
  <p>Again this works fine with MySQL.</p>
  
  <p>Here's the hibernate sql statements (hangs on last entry):</p>
  
  <pre><code>Hibernate: select permission0_.dbID as dbID1_73_, permission0_.Created as Created2_73_, permission0_.dbVersion as dbVersio3_73_, permission0_.Origin as Origin4_73_, permission0_.Updated as Updated5_73_, permission0_.Entity as Entity6_73_ from Permission permission0_ where permission0_.Entity=?
  Hibernate: select permission0_.dbID as dbID1_73_, permission0_.Created as Created2_73_, permission0_.dbVersion as dbVersio3_73_, permission0_.Origin as Origin4_73_, permission0_.Updated as Updated5_73_, permission0_.Entity as Entity6_73_ from Permission permission0_ where permission0_.Entity=?
  Hibernate: select next value for hibernate_sequence
  Hibernate: insert into Permission (Created, dbVersion, Origin, Updated, Entity, dbID) values (?, ?, ?, ?, ?, ?)
  Hibernate: insert into Permission_Operations (Permission_id, T_element) values (?, ?)
  Hibernate: select permission0_.dbID as dbID1_73_, permission0_.Created as Created2_73_, permission0_.dbVersion as dbVersio3_73_, permission0_.Origin as Origin4_73_, permission0_.Updated as Updated5_73_, permission0_.Entity as Entity6_73_ from Permission permission0_ where permission0_.Entity=?
  Hibernate: select permission0_.dbID as dbID1_73_, permission0_.Created as Created2_73_, permission0_.dbVersion as dbVersio3_73_, permission0_.Origin as Origin4_73_, permission0_.Updated as Updated5_73_, permission0_.Entity as Entity6_73_ from Permission permission0_ where permission0_.Entity=?
  </code></pre>
  
  <p>When I use SQL Server Studio I see the last query as State=SUSPENDED and Wait=LCK_M_S (see image below).</p>
  
  <p><a href="https://i.stack.imgur.com/Xkvur.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Xkvur.jpg" alt="enter image description here"></a></p>
  
  <p>The last query will never finish/return.  I have left it running as much as 12 hours.  I did add a time-out of 60 seconds using javax.persistence.query.timeout hint with the Query just so it didn't hang forever.</p>
  
  <p>Configuration:</p>
  
  <ul>
  <li>SQL Server 2017 </li>
  <li>Hibernate 4.3.11.Final</li>
  <li>SQL Server Connector for JDBC 6.2.1.jre8</li>
  <li>Oracle Java 8</li>
  </ul>
  
  <p>My persistence.xml:</p>
  
  <pre><code>        &lt;property name="hibernate.hbm2ddl.auto" value="update" /&gt;
          &lt;property name="hibernate.id.new_generator_mappings" value="true" /&gt;
          &lt;property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider" /&gt;
          &lt;property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /&gt;
          &lt;property name="hibernate.c3p0.acquire_increment" value="1" /&gt;
          &lt;property name="hibernate.c3p0.min_size" value="5" /&gt;
          &lt;property name="hibernate.c3p0.max_size" value="25" /&gt;
          &lt;property name="hibernate.c3p0.max_statements" value="50" /&gt;
          &lt;property name="hibernate.c3p0.validate" value="true" /&gt;
          &lt;!-- Tried with and without this: 
          &lt;property name="hibernate.c3p0.preferredTestQuery" value="SELECT 1" /&gt;
          --&gt;
  </code></pre>
  
  <p>I also programetically set these values:</p>
  
  <pre><code>hibernate.connection.driver_class  : com.microsoft.sqlserver.jdbc.SQLServerDriver
  hibernate.connection.password      : &lt;MASKED&gt;
  hibernate.connection.url           : jdbc:sqlserver://sqlserver:1433;databaseName=xxx_test0
  hibernate.connection.username      : xxx
  hibernate.dialect                  : org.hibernate.dialect.SQLServer2012Dialect
  hibernate.show_sql                 : true
  </code></pre>