JPA 2.0 How to handle deadlock (Eclipselink JPA2.0 MySQL)

Question

I trying a code to add/get/update and I have used EclipseLink provider and JPA2.0. and MySQL.

The below code is throwing an error saying a deadlock happened. The issue is happening randomly. I wanted to know how to handle deadlock.

Here is the error message:

    javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
    Internal Exception: java.sql.SQLException: null,  message from server: "Deadlock found when trying to get lock; try restarting transaction"
    Error Code: 1213
    Call: UPDATE activitylog SET timestampdate = ? WHERE (logid = ?)
        bind => [2013-11-19 20:10:38.583, 1]
    Query: UpdateObjectQuery(test.ActivityLog@dd3314)

Here is the code that I am trying:

    public class TestMain {
        public static void main(String[] args) {
            for(int j = 0; j < 10; j ++) {
                Thread thread = new Thread(new Runnable() {

                    @Override
                    public void run() {
                        for (int i = 0; i < 200; i++) {
                            ActivityLogDAO activityLogDAO = new ActivityLogDAO();
                            try {
                                ActivityLog theActivityLog = new ActivityLog();
                                theActivityLog.setTimestampdate(new Date());
                                theActivityLog.setMyId(i);
                                activityLogDAO.insert(theActivityLog);

                                ActivityLog activityLog = activityLogDAO.getActivityLog(theActivityLog);

                                activityLog.setTimestampdate(new Date());
                                activityLogDAO.update(activityLog);

                            } catch (Exception e) {
                                e.printStackTrace();
                            }

                        }
                    }
                });
                thread.start();
            }
        }
    }

Here is the Entity class

    @Entity
    @Table(name="activitylog")
    public class ActivityLog implements Serializable {

        private static final long serialVersionUID = 1L;

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

        @Column(name="myid")
        private long lMyId;

        @Temporal(TemporalType.TIMESTAMP)
        @Column(name="timestampdate", nullable=true)
        private Date timestampdate;


        public long getMyId() {
            return lMyId;
        }

        public void setMyId(long lMyId) {
            this.lMyId = lMyId;
        }

        public long getLogid() {
            return logid;
        }

        public void setLogid(long logid) {
            this.logid = logid;
        }

        public Date getTimestampdate() {
            return timestampdate;
        }

        public void setTimestampdate(Date timestampdate) {
            this.timestampdate = timestampdate;
        }

    }

here is my DAO class:

    public class ActivityLogDAO {
        private EntityManagerFactory _entityManagerFactory = null;
        private EntityManager _entityManager = null;

        public ActivityLogDAO() {
            _entityManagerFactory = Persistence.createEntityManagerFactory("MyTestOnLock");
            _entityManager = _entityManagerFactory.createEntityManager();
        }

        protected EntityManager getEntityManager() {
            return _entityManager;
        }

        protected void setEntityManager(EntityManager _entityManager) {
            this._entityManager = _entityManager;
        }

        public ActivityLog insert(ActivityLog theActivityLog) throws Exception {
            if(null == theActivityLog) {
                throw new Exception("Invalid ActivityLog Object");
            }

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

            System.out.println("inserting");
            getEntityManager().persist(theActivityLog);
            getEntityManager().getTransaction().commit();
            System.out.println("inserted");

            return theActivityLog;
        }

        public ActivityLog getActivityLog(ActivityLog theActivityLog) throws Exception {
            if(null == theActivityLog) {
                throw new Exception("Invalid ActivityLog Object");
            }

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

            System.out.println("trying to get object");
            Query query = getEntityManager().createQuery("SELECT m FROM ActivityLog m WHERE m.lMyId = :lMyId");
            query.setParameter("lMyId", theActivityLog.getMyId());
            //deadlock happens here.
            @SuppressWarnings("unchecked")
            List<ActivityLog> resultList = query.getResultList();
            System.out.println(resultList.size());
            System.out.println("got object");
            if(null == resultList || 0 == resultList.size()) {
                return null;
            } else {
                return resultList.get(0);
            }
        }

        public ActivityLog update(ActivityLog theActivityLog) throws Exception {
            if(null == theActivityLog) {
                throw new Exception("Invalid ActivityLog Object");
            }

            if(false == getEntityManager().getTransaction().isActive()) {
                getEntityManager().getTransaction().begin();
            }
            System.out.println("trying to update object");
            Query query = getEntityManager().createQuery("UPDATE ActivityLog m SET m.timestampdate = :timestampdate WHERE m.lMyId = :lMyId");
            query.setParameter("lMyId", theActivityLog.getMyId());
            query.setParameter("timestampdate", theActivityLog.getTimestampdate());

            int executeUpdate = query.executeUpdate();
            getEntityManager().getTransaction().commit();
            System.out.println("object updted.");

            if(0 == executeUpdate) {
                return null;
            }

            return theActivityLog;
        }
    }

Here is my persistance.xml

    <?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="MyTestOnLock">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

        <class>test.ActivityLog</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/locktest"></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-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="100"/>
    <property name="eclipselink.order-updates" value="true"/>
    <property name="eclipselink.connection-pool.sequence" value="max" />
    <property name="eclipselink.ddl-generation.output-mode" value="database" />
    <property name="eclipselink.target-database" value="MySQL" />

    </properties>

    </persistence-unit>

    </persistence>

The deadlock occurs when AcitivityDAO is trying updated. Is there a why to handle or avoid deadlock issue?

Any help is appreciated!!

---

I am getting back the following error:

      javax.persistence.PersistenceException: java.lang.NullPointerException

and

      javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
      Internal Exception: java.sql.SQLException: Deadlock found when trying to get lock; Try restarting transaction,  message from server: "Lock wait timeout exceeded; try restarting transaction"
      Error Code: 1205
      Call: UPDATE activitylog SET timestampdate = ? WHERE (myid = ?)
  bind => [2013-11-20 16:54:09.646, 0]
      Query: UpdateAllQuery(referenceClass=ActivityLog sql="UPDATE activitylog SET timestampdate = ? WHERE (myid = ?)")

I had used the same code which @Chris Ridal specified.

here is the code: Basically I tried running the MainTest class multiple times.

    public class MainTest {
        public static void main(String[] args) {
            updateActivityLog();
        }

        private static void updateActivityLog() {
            final PersistenceController persistenceController = new PersistenceController(Persistence.createEntityManagerFactory("MyTestOnLock"));
            for (int i = 0; i < 100; i++) {
                    try {
                        for(int j = 0; j < 200; j++) {
                            ActivityLog theActivityLog = new ActivityLog();
                            theActivityLog.setMyId(j);
                            theActivityLog.setTimestampdate(new Date());
                            persistenceController.update(theActivityLog);
                        }

                    } catch (Exception e) {
                        e.printStackTrace();
                    } 
            }
            persistenceController.commitAndClose();
        }
    }


    public class PersistenceController {
        private EntityManager manager;

        public PersistenceController(EntityManagerFactory factory)
        {
            /*
             * Normally you want to split your work up into separate transactions
             * (ie new entity managers), in a logical way which will depend on how
             * your application works. This class will do that for you if you keep
             * your factory. Note that factory's are expensive to create but entity
             * managers are cheap to create.
             */
            manager = factory.createEntityManager();
            manager.getTransaction().begin();
        }

        // Call ONCE on an object after creating it, it will stay in sync with the database even when you change it remotely
        public void persist(Serializable entityObj)
        {
            manager.persist(entityObj);
            manager.flush();
        }

        // Call to sync with database (even though you might not actually see the objects in the database until you commit)
        public void flush()
        {
            manager.flush();
        }

        /*
         * Call when you are done with your unit of work to commit the DB changes
         */
        public void commitAndClose()
        {
            manager.getTransaction().commit();
            manager.close();
        }

        public ActivityLog getActivityLog(ActivityLog theActivityLog) throws Exception {
            if(null == theActivityLog) {
                throw new Exception("Invalid ActivityLog Object");
            }
            if(false == manager.getTransaction().isActive()) {
                manager.getTransaction().begin();
            }

            System.out.println("trying to get object");
            Query query = manager.createQuery("SELECT m FROM ActivityLog m WHERE m.lMyId = :lMyId");
            query.setParameter("lMyId", theActivityLog.getMyId());

            @SuppressWarnings("unchecked")
            List<ActivityLog> resultList = query.getResultList();
            System.out.println(resultList.size());
            System.out.println("got object");
            if(null == resultList || 0 == resultList.size()) {
                return null;
            } else {
                return resultList.get(0);
            }
        }

        public ActivityLog update(ActivityLog theActivityLog) throws Exception {
            if(null == theActivityLog) {
                throw new Exception("Invalid ActivityLog Object");
            }
            if(false == manager.getTransaction().isActive()) {
                manager.getTransaction().begin();
            }
            ActivityLog activityLog = getActivityLog(theActivityLog);
            activityLog.setTimestampdate(theActivityLog.getTimestampdate());
            persist(activityLog);
            return theActivityLog;
        }

    }

---

Do I have to get EntityManager for every database insert or merge or update or delete? see below code, with this I am not seeing deadlock happening. Please confirm.

public class ActivityLogDAO {
    private EntityManagerFactory _entityManagerFactory = null;
    private EntityManager _entityManager = null;

    public ActivityLogDAO() {
        _entityManagerFactory = Persistence.createEntityManagerFactory("MyTestOnLock");
    }

    protected EntityManager getEntityManager() {
        return _entityManager;
    }

    protected void setEntityManager(EntityManager _entityManager) {
        this._entityManager = _entityManager;
    }

    public ActivityLog insert(ActivityLog theActivityLog) throws Exception {
        if(null == theActivityLog) {
            throw new Exception("Invalid ActivityLog Object");
        }

        _entityManager = _entityManagerFactory.createEntityManager();

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

        System.out.println("inserting");
        getEntityManager().persist(theActivityLog);
        getEntityManager().getTransaction().commit();
        System.out.println("inserted");

        return theActivityLog;
    }

    public ActivityLog getActivityLog(ActivityLog theActivityLog) throws Exception {
        if(null == theActivityLog) {
            throw new Exception("Invalid ActivityLog Object");
        }
        _entityManager = _entityManagerFactory.createEntityManager();

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

        System.out.println("trying to get object");
        Query query = getEntityManager().createQuery("SELECT m FROM ActivityLog m WHERE m.lMyId = :lMyId");
        query.setParameter("lMyId", theActivityLog.getMyId());
        //deadlock happens here.
        @SuppressWarnings("unchecked")
        List<ActivityLog> resultList = query.getResultList();
        System.out.println(resultList.size());
        System.out.println("got object");
        if(null == resultList || 0 == resultList.size()) {
            return null;
        } else {
            return resultList.get(0);
        }
    }

    public ActivityLog update(ActivityLog theActivityLog) throws Exception {
        if(null == theActivityLog) {
            throw new Exception("Invalid ActivityLog Object");
        }
        _entityManager = _entityManagerFactory.createEntityManager();

        if(false == getEntityManager().getTransaction().isActive()) {
            getEntityManager().getTransaction().begin();
        }
        System.out.println("trying to update object");
        Query query = getEntityManager().createQuery("UPDATE ActivityLog m SET m.timestampdate = :timestampdate WHERE m.lMyId = :lMyId");
        query.setParameter("lMyId", theActivityLog.getMyId());
        query.setParameter("timestampdate", theActivityLog.getTimestampdate());

        int executeUpdate = query.executeUpdate();
        getEntityManager().getTransaction().commit();
        System.out.println("object updted.");

        if(0 == executeUpdate) {
            return null;
        }

        return theActivityLog;
    }
}

Answer 1

For what it's worth:

Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

Sometimes it is just impossible to avoid a deadlock situation. However, to make deadlock less likely or less frequent, do not investigate too deep into the data access code. This issue is rather linked to the order of operations that lead to a deadlock. A formal way of avoiding deadlocks is to always lock and release resources in the same order. Easier said than done :)

Interesting resource: What is a deadlock?

You can track what concurrent transaction(s) is(are) involved in the deadlock with SHOW ENGINE INNODB STATUS (active transactions are listed in the "TRANSACTIONS" section, with extended details).

Answer 2

In general you do not need to use DAO's when using JPA.

Instead you might look to use a class like this (untested), bringing your own EntityManagerFactory:

public class PersistenceController
{
    private EntityManager manager;

    public PersistenceController(EntityManagerFactory factory)
    {
        /*
         * Normally you want to split your work up into separate transactions
         * (ie new entity managers), in a logical way which will depend on how
         * your application works. This class will do that for you if you keep
         * your factory. Note that factory's are expensive to create but entity
         * managers are cheap to create.
         */
        manager = factory.createEntityManager();
        manager.getTransaction().begin();
    }

    // Call ONCE on an object after creating it, it will stay in sync with the database even when you change it remotely
    public void persist(Serializable entityObj)
    {
        manager.persist(entityObj);
    }

    // Call to sync with database (even though you might not actually see the objects in the database until you commit)
    public void flush()
    {
        manager.flush();
    }

    /*
     * Call when you are done with your unit of work to commit the DB changes
     */
    public void commitAndClose()
    {
        manager.getTransaction().commit();
        manager.close();
    }

}

To use this, you would call persist(entityObj) when you have created an object, flush() to sync with the database (if you need to) and commitAndClose() when you are done. Keep the PersistenceController in a place that you can post to it when you need to persist an object or use its other operations.

Now your transactions will not occur concurrently and you will not get deadlocks.

Note: In production code you would use more exception management and split your work into different EntityManager transactions, which this class does for you if you dispose and create this PersistenceController class logically.