Encountering Database Deadlock with JPA when persisting objects with persisted references

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.

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.

Preliminary information:

• 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.
• 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.
• 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.
• Database is PostgreSQL 8.4, using the postgresql-8.4-702.jdbc3.jar.
• 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.

The mapping for the mimetype field in th FileContainer object is:

@ManyToOne(fetch=FetchType.EAGER, cascade={})  
@JoinColumn(name="mimetype_id", referencedColumnName="id", nullable=false, updatable=true)

The MIMEType object is annotated as follows:

@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")})

All above annotations are the javax.persistence versions.

The addFile method is annotated with @com.google.inject.persist.Transactional. Changing this to using an injected EntityManager in the class does not appear to change the outcome.

The process for creating the object goes like this:

1. Grab the EntityManager and the FileDAO from the injector. These are both coming from a provider.
2. Grab the MIMEType object we will be using from the database by calling em.开发者_如何学编程createNamedQuery("mt.ext", MIMEType.class).setParameter("ext", extension).getSingleResult();
3. Create the FileContainer object and populate it, including a call to `setMimetype(MIMEType)` with the object we just retrieved
4. Call dao.save(fileContainer), which sets a basic field in FileContainer object and then calls em.persist(Object). Pulling this out of the DAO and using the EntityManager injected earlier does not change the outcome.

At this point as soon as EntityManager.flush() occurs (wherever that happens--at the end of the transaction or before it) the INSERT happens and the code deadlocks.

When I check pg_stat_activity and compare it against pg_locks 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.

Any thoughts or ideas are appreciated.

EDIT:

This is the hierarchy, hopefully it clarifies the order of operations and what is going on:

• Testing Harness (Initializes Injector and uses a runner to inject it into the specific test classes)
• Test Case
• Thread Started
• Enter transactional block on singleton object (initialized with same injector) from within the thread, confirmed that the EntityManager objects are identical in all of the DAO objects and where the fault is happening.
• EntityManager object is consistently acquired with an injector.getInstance(EntityManager.class) call or by acquiring the Provider object.
• Go through the above process, all in the same thread.
• Freeze on flush()

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.

---

The only thing I'm picking up on from what you've said here that seems likely to be the problem is this:

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.

Guice-Persist units of work and transactions are scoped to a thread using ThreadLocal. I can definitely see something like that happening if you are doing something on a separate thread (though I'm not sure how exactly). Could you explain in more detail when and how this separate thread is being used? From inside the @Transactional method? Is it using an EntityManager that was injected on the original thread?

Edit: Hmm... I don't see anything wrong with what you're doing. Seems like the work and the transaction are all being done on one thread. Not sure what's going on there.

---

I found the problem.

Elsewhere in the code I had an unrelated but ongoing transaction that was being kicked off in an @Before method. For some reason the EntityManager was blocking on that transaction completing. This wasn't a problem when everything was in the same thread and for certain read-only operations, but when it got split into a separate thread and trying to write it started waiting on the other transaction, which wasn't finishing in the time of the transaction and touched many of the same tables.