title: Encountering Database Deadlock with JPA when persisting objects with persisted references
body: <p>I have been struggling with this problem for a few days and I can't quite find a solution that I am happy with.  I can probably work around it via various degrees of indirection, but this seems like the sort of thing I should be able to do and while I've found a few other people who might have my problem, none of them seem to have the exact problem or there are no answers provided. </p>
  
  <p>While I've done this successfully using Hibernate before, I haven't been able to get it to work with JPA, which is what I am focusing on doing now.</p>
  
  <p>Preliminary information:</p>
  
  <ul>
  <li>Guice 3.0 application using the persist extension, hibernate (w/ annotations), and c3p0 (with up to 5 connections). We are injecting the EntityManager from the provider, and I can confirm that the EntityManager object remains the same as does the transaction object throughout this process.</li>
  <li>Designed as part of a web application, current problems are only occurring in automated integration tests that use JUnit and dbUnit. Access is against a singleton object (.in(Scopes.SINGLETON)), but that Singleton object only depends on the injector and @Transactional to be thread safe.</li>
  <li>In the database there is a files table which is mapped to an annotated FileContainer and a mimetype table that is mapped to an object MIMEType.  In the application when we create a new file we retrieve the MIMEType object first as a NamedQuery and persist it.</li>
  <li>Database is PostgreSQL 8.4, using the postgresql-8.4-702.jdbc3.jar.</li>
  <li>The system is moderately picky about the circumstances under which it fails: I need to spawn a separate thread and perform the access from there.</li>
  </ul>
  
  <p>The mapping for the mimetype field in th FileContainer object is:</p>
  
  <p></p>
  
  <pre><code>@ManyToOne(fetch=FetchType.EAGER, cascade={})  
  @JoinColumn(name="mimetype_id", referencedColumnName="id", nullable=false, updatable=true)
  </code></pre>
  
  <p></p>
  
  <p>The MIMEType object is annotated as follows:</p>
  
  <p></p>
  
  <pre><code>@Entity
  @Table(name="mimetypes")  
  @org.hibernate.annotations.Immutable  
  @Cacheable(true)  
  @NamedQuery(name="mt.ext", query="from MIMEType where extension = :ext",  
      hints= {@QueryHint(name="org.hibernate.fetchSize", value="1"),  
              @QueryHint(name="org.hibernate.readOnly", value="true")})
  </code></pre>
  
  <p></p>
  
  <p>All above annotations are the javax.persistence versions.</p>
  
  <p>The <code>addFile</code> method is annotated with <code>@com.google.inject.persist.Transactional</code>.   Changing this to using an injected EntityManager in the class does not appear to change the outcome. </p>
  
  <p>The process for creating the object goes like this:</p>
  
  <ol>
  <li>Grab the EntityManager and the FileDAO from the injector.  These are both coming from a provider.</li>
  <li>Grab the MIMEType object we will be using from the database by calling em.createNamedQuery("mt.ext", MIMEType.class).setParameter("ext", extension).getSingleResult();</li>
  <li>Create the <code>FileContainer</code> object and populate it, including a call to `setMimetype(MIMEType)` with the object we just retrieved</li>
  <li>Call <code>dao.save(fileContainer)</code>, which sets a basic field in <code>FileContainer</code> object and then calls <code>em.persist(Object)</code>.  Pulling this out of the DAO and using the <code>EntityManager</code> injected earlier does not change the outcome.</li>
  </ol> 
  
  <p>At this point as soon as <code>EntityManager.flush()</code> occurs (wherever that happens--at the end of the transaction or before it) the INSERT happens and the code deadlocks. </p>
  
  <p>When I check <code>pg_stat_activity</code> and compare it against <code>pg_locks</code> I see that the insert statement is being blocked by an "idle in transaction" connection and that it is in a waiting state.  Removing the insert of the MIMEType (and setting the column to allow for nulls) allows the code to proceed normally, and checking the values of the MIMEType indicates that it was retrieved correctly.</p>
  
  <p>Any thoughts or ideas are appreciated. </p>
  
  <p>EDIT:</p>
  
  <p>This is the hierarchy, hopefully it clarifies the order of operations and what is going on:</p>
  
  <ul>
  <li>Testing Harness (Initializes Injector and uses a runner to inject it into the specific test classes)</li>
  <ul>
  <li>Test Case</li>
  <ul>
  <li>Thread Started</li>
  <li>Enter transactional block on singleton object (initialized with same injector) from within the thread, confirmed that the <code>EntityManager</code> objects are identical in all of the DAO objects and where the fault is happening.</li>
  <li><code>EntityManager</code> object is consistently acquired with an <code>injector.getInstance(EntityManager.class)</code> call or by acquiring the <code>Provider</code> object.</li>
  <li>Go through the above process, all in the same thread.</li>
  <li>Freeze on <code>flush()</code></li>
  </ul>
  </ul>
  </ul>
  
  <p>The code does not appear to have anything that should be thread-local that is being shared between threads and everything is being initialized with the injector, which is what is baffling to me. </p>