JPA deadlock with @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)

Question

in my application I have a deadlock problem and do not know if and how I could handle it. I am using JPA 2.1 (with eclipselink) on a glassfish server.

There are two EJBs. OuterBean should write a LogEntry to database and calls InnerBean in a loop. InnerBean itself should write a LogEntry and do some other stuff (more database manipulation on other entities). The invocations of InnerBean#execute() are independent from each other, meaning if one method fails (rollback) the others should keep running. Therefore InnerBean#execute() runs in its own transaction.

When executing the code below I get an java.sql.SQLException: Lock wait timeout exeeded; try restarting transaction under MySQL and a java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock under Oracle (Postgres just waits for eternity; probably because of a bad configured database).

I am no database/JPA expert but I guess the problem is that two transactions want to write in the same datatable. What I do not understand is that there is a problem at all beause these database manipulations are inserts that should be independent from each other. Is there a way that I can implement this use case (do I need to use bean managed transactions, are there any annotations I can use, can I force a commit in OuterBean#execute() after the em.persist(logEntry); and before the loop so that the transaction locks are released anyhow)?

@Stateless
public class OuterBean
{
    @PersistenceContext(unitName = "PU_LOGGER")
    private EntityManager em;
    @EJB
    private InnerBean innerBean;

    public void execute()
    {
        LogEntry logEntry = new LogEntry();
        logEntry.setDate(new Date());
        logEntry.setMessage("OuterBean#execute()");
        em.persist(logEntry);

        for(int i = 0; i < 10; ++i)
        {
            innerBean.execute();
        }
    }
}

@Stateless
public class InnerBean
{
    @PersistenceContext(unitName = "PU_LOGGER")
    private EntityManager em;

    @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
    public void execute()
    {
        LogEntry logEntry = new LogEntry();
        logEntry.setDate(new Date());
        logEntry.setMessage("InnerBean#execute()" + Math.random());
        em.persist(logEntry);
    }
}

---

EDIT

Here is the log that is generated

FINER:   client acquired: 1990452734
FINER:   TX binding to tx mgr, status=STATUS_ACTIVE
FINER:   acquire unit of work: 38847372
FINEST:   persist() operation called on: LogEntry{id=null, message=OuterBean#execute(), date=Mon Jul 18 09:00:27 CEST 2016}.
FINER:   TX beginTransaction, status=STATUS_ACTIVE
FINEST:   Connection acquired from connection pool [default].
FINEST:   Execute query DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + #PREALLOC_SIZE WHERE SEQ_NAME = #SEQ_NAME")
FINEST:   reconnecting to external connection pool
FINE:   UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
    bind => [2 parameters bound]
FINEST:   Execute query ValueReadQuery(name="SEQUENCE" sql="SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = #SEQ_NAME")
FINE:   SELECT SEQ_COUNT FROM SEQUENCE WHERE SEQ_NAME = ?
    bind => [1 parameter bound]
FINEST:   local sequencing preallocation for SEQ_GEN: objects: 50 , first: 301, last: 350
FINEST:   assign sequence to the object (301 -> LogEntry{id=null, message=OuterBean#execute(), date=Mon Jul 18 09:00:27 CEST 2016})
FINER:   client acquired: 187184807
FINER:   TX binding to tx mgr, status=STATUS_ACTIVE
FINER:   acquire unit of work: 2098992041
FINEST:   persist() operation called on: LogEntry{id=null, message=InnerBean#execute()0.3957184758563761, date=Mon Jul 18 09:00:28 CEST 2016}.
FINER:   TX beginTransaction, status=STATUS_ACTIVE
FINEST:   Connection acquired from connection pool [default].
FINEST:   Execute query DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
FINEST:   reconnecting to external connection pool
FINE:   UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
    bind => [2 parameters bound]

Answer 1

OK I have solved the problem. The answer to it was hidden in my question:

can I force a commit in OuterBean#execute() after the em.persist(logEntry); and before the loop

I created a separate EJB only for persisting the LogEntry, to run it in a new transaction itself.

@Stateless
public class OuterBean
{

    @EJB
    private SeparateLoggingBean separateLoggingBean;

    @EJB
    private InnerBean innerBean;


    public void execute()
    {
        LogEntry logEntry = new LogEntry();
        logEntry.setDate(new Date());
        logEntry.setMessage("OuterBean#execute()");
        separateLoggingBean.persistLogEntry(logEntry);

        for(int i = 0; i < 10; ++i)
        {
            innerBean.execute();
        }
        System.out.println("!ready!");
    }
}

@Stateless
public class SeparateLoggingBean
{
    @PersistenceContext(unitName = "PU_LOGGER")
    private EntityManager em;

    @TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
    public void persistLogEntry(LogEntry logEntry)
    {
        em.persist(logEntry);
    }
}

Anyway I think @Chris is right with his second comment. The problem seem to be a deadlock in the eclipselink sequence generation. There seems to be a solution (http://www.eclipse.org/eclipselink/documentation/2.6/jpa/extensions/persistenceproperties_ref.htm#BABIDAGH) for this, but I did not get it to work properly. In the mentioned link it is said that the property

<property name="eclipselink.connection-pool.sequence" value="true"/>

needs to be put in the persistence.xml. Unfortunately in my tests it made no difference if I used the property or not. I have found few examples for this well known problem. Often some outdated properties are used from older eclipselink versions, which I did not found in the current (2.6) documentation. It was not clear to me either if I do need to specify a separate non-jta connection-pool or if eclipselink is managing the pool by itself if I specify the eclipselink.connection-pool.sequence property.

Well it would be nice to find some further information on this, but I am happy that my application works at least.