JPA2: deadlock using native query

Question

I am trying to fetch records from a table. So I used following native query (self join) to fetch. I am using Eclipse IDE on Windows, EclipseLink JPA2 and MySQL.

    select * from mytable as detail join (select max(timestamp) as maxtimestamp from mytable where id=" + theUser.getId() + " group by hnumber order by maxtimestamp limit " + <offset> + "," + <iTotalRecords> + ") as topconv on detail.timestamp=topconv.maxtimestamp order by detail.timestamp

I have a problem with deadlock that come with multi-threading. I am not sure whether it is JPA deadlock or MySQL deadlock.

I have following pojo class

    @Entity
    @Table(name="mytable")
    public class MyTable {

        @Id
        @GeneratedValue(strategy=GenerationType.SEQUENCE)
        @Column(name="mytableid")
        private long lMyTableId;

        @ManyToOne
        @JoinColumn(name="id", referencedColumnName="id")
        private User user; //this object has id, name and so on fields.

        @Column(name="timestamp", length=15, nullable=false)
        private String strTimestamp;

        @Column(name="hnumber", nullable=true)
        private String hNumber;

        //Getters and Setters.

    }

Here is method to fetch the records

    public List<MyTable> fetchRecords(User theUser, int iTotalRecords, long offset) throws Exception {
        if(null == theUser) {
            throw new Exception("Invalid input.");
        }

        EntityManager entityManager = getEntityManager();
        if(false == entityManager.getTransaction().isActive()) {
            entityManager.getTransaction().begin();
        }

        try {
            Query query = entityManager.createNativeQuery("select * from mytable as detail join (select max(timestamp) as maxtimestamp from mytable where id=" + theUser.getId() + " group by hnumber order by maxtimestamp limit " + offset + "," + iTotalRecords + ") as topconv on detail.timestamp=topconv.maxtimestamp order by detail.timestamp", MyTable.class);
            @SuppressWarnings("unchecked")
            List<MyTable> resultList = query.getResultList();
            return resultList;
        } catch(Throwable th) {
            throw new Exception(th.getMessage());
        } finally {
            closeEntityManager();
        }
    }

To get and close EntityManager

    private EntityManagerFactory _entityManagerFactory = Persistence.createEntityManagerFactory("JPATest");
    protected EntityManager getEntityManager() {
        _entityManager = _entityManagerFactory.createEntityManager();
        return _entityManager;
    }

    protected void closeEntityManager() {
        try {
            if(null != _entityManager) {
                _entityManager.close();
            }
        } catch(Throwable th){}
    }

my persistance.xml looks like this:

    <?xml version="1.0" encoding="UTF-8"?>
    <persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
        <persistence-unit name="JPATest">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

    <class>com.company.service.db.pojo.User</class>
    <class>com.company.service.db.pojo.MyTable</class>

    <properties>
    <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"></property>
    <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/mydb"></property>
    <property name="javax.persistence.jdbc.user" value="root"></property>
    <property name="javax.persistence.jdbc.password" value="root"></property>

    <!-- EclipseLink should create the database schema automatically   -->
    <property name="eclipselink.ddl-generation" value="create-or-extend-tables" /> 
    <property name="eclipselink.ddl-generation.output-mode" value="database" />
    <property name="eclipselink.id-validation" value="NULL"></property>
    <property name="eclipselink.logging.level" value="FINE"/>
    <property name="javax.persistence.lock.timeout" value="1000"/>
    <property name="eclipselink.target-database" value="MySQL"/>
    </properties>

    </persistence-unit>
    </persistence>

My application supports multi-threading.

To replicate the deadlock, I ran 100 threads with each thread executes the fetchRecords method. Records that are fetched each time are nearly about 200 or more.

Please let me know how to solve this issue. Any help is appreciated.

Answer 1

As per your description all the threads are performing only this read only query. What you may be experiencing is perhaps longer time due to "locking" and NOT "deadlock". Both are different things and locking per se is not an issue unless of-course it is for long duration. Locking is inherent to databases to provide consistent view of data and there are different types / levels of locks. You can read more about it here. On a cursory look your SQL is a usual select so I do not see any reason for locking too. For getting information about lock you can refer to this Once you are sure that its the query which is taking time you can use EXPLAIN to fine tune your query. Here is an excellent article. Perhaps appropriate indexes would help you too.

However to clear your suspicion you already have a great tool, you can run following on the MySQL command prompt

SHOW ENGINE INNODB STATUS\g

This will give you the information about deadlock if any.

Answer 2

Here is the solution for those who come across the issue. Connection doesn't get closed by JPA is it is Activated. That means the following I have to comment the code to begin the transaction.

    public List<MyTable> fetchRecords(User theUser, int iTotalRecords, long offset) throws Exception {
        if(null == theUser) {
            throw new Exception("Invalid input.");
        }

        EntityManager entityManager = getEntityManager();
        //if(false == entityManager.getTransaction().isActive()) {
        //  entityManager.getTransaction().begin();
        //}

        try {
            Query query = entityManager.createNativeQuery("select * from mytable as detail join (select max(timestamp) as maxtimestamp from mytable where id=" + theUser.getId() + " group by hnumber order by maxtimestamp limit " + offset + "," + iTotalRecords + ") as topconv on detail.timestamp=topconv.maxtimestamp order by detail.timestamp", MyTable.class);
            @SuppressWarnings("unchecked")
            List<MyTable> resultList = query.getResultList();
            return resultList;
        } catch(Throwable th) {
            throw new Exception(th.getMessage());
        } finally {
            closeEntityManager();
        }
    }

Answer 3

With the information given above, I can guess the following:

1.- The code has begin() but it's missing commit() or equivalent to release the transaction, as you pointed out @User12111111. However since you opted to remove it in your solution, make sure you are in contained-managed server. If you aren't there are more:

2.- It is possible that the resulting query MyTable objects be still managed if the persistence-unit JPATest is being reused within subsequent cycles. If you don't need updating the resulting objects, then make sure to detach them.

3.- Additionally, when a query lock too many rows, instead, the lock gets escalated to the hole table (for performance reasons). This may create dead-locks where you think it shouldn't. If you don't need row locking then make the query it read-only, as it was pointed out by @Shailendra.

4.- Last but not least, if you don't need repetable-reads and so on, you can simply be less restrictive with the dead-locks, by adjusting the isolation level.