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;
    }
}

If you haven't, perhaps look in to transactions using jpa and how transactions can help avoid deadlocks. There is good information on it. Make sure you focus on when a transaction releases a lock. — user2932397, Nov 19 '13 at 14:50
it doesn't seem possible from the code shown, as each thread will only touch a row it creates within each iteration. In the run that reproduces the issue, check what else is issuing statements to the object with logid =1 (or what ever value is shown in the error). — Chris, Nov 19 '13 at 18:11

score 4 · Answer 1 · edited May 23 '17 at 12:02

4

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.

edited May 23 '17 at 12:02

Community

1
1

answered Nov 20 '13 at 05:01

Chris Riddell

1,034
7
16

After updating rows in the example above, I used flush() even then a deadlock happened. – User12111111 Nov 20 '13 at 06:55
The problem is somewhere with your DAO implementation. You manage the transactions manually. Did you stop using the DAO as Chris said? If you encounter a deadlock this probably means that you are trying to start a new transaction from within an existing transaction. You should start the transaction at the outer boundary of the work you want to achieve. Never start one transaction inside another. Or even better: use a container that will manage the transactions for you. Give Spring Data a try. – mwhs Nov 20 '13 at 07:10
@mwhs, I just called it as DAO, but the code looks similar to what Chris said. However the code can be modified. And also I have to get the object ActivityLog based on MyId not based on LogId to update, because as a user I will not know the LogId generated by JPA to find() the object. The example that Chris gave doesn't have merge() or update(). How do I update a row based on a column and not based on Id generated and which could not cause a deadlock – User12111111 Nov 20 '13 at 07:33
It doesn't matter if you know the ID. You have the object reference. Once you called persist on an object it will stay in sync with the persistence layer. No need to call "update" again in one session. You begin and commit the transactions inside the work you are doing. Don't do that. start and stop the transactions before/after everything else (in your case). Do _NOT_ start/commit the transaction inside the insert method of your DAO/helper class. DO NOT DO IT. – mwhs Nov 20 '13 at 07:41
@mwhs understood, but as you said I have the object reference, if you look at getActivityLog() method. Does the returned object will reference to actual one, so the I can change the object? – User12111111 Nov 20 '13 at 07:57
@mwhs I mean here in the example I have the reference of that object. Lets assume there is no insert, how do I get reference of that object to update? – User12111111 Nov 20 '13 at 08:08
If you want to simulate how JPA will behave when updating an object after persisting it you need to do that in two separate transactions. The test you perform in your main() method is non-sensical. The generated ID is available after flush or after transaction boundary by calling the method getId() of the persisted object. – mwhs Nov 20 '13 at 08:14
@Hun, are you creating your equivalent `PersistenceController` class inside the for loop? If so, you should create it outside of the for loop, then close it after the for loop. As @mwhs said, there is no need to update the object, just call `persist(obj)` on it when you create it, then nexttime you call `flush()` or `commit()` the object will be automatically checked for changes (if you are using persist. If you are using merge, this would not be the case). Also try adding the `synchronized` keyword to the PersistenceController.flush() method if you are calling it from multiple threads – Chris Riddell Nov 20 '13 at 08:16
And if the above doesn't work try generating your own unique ID for the items rather than use sequence generation, I'd say the exception is caused by that (but this is cheating, because sequence generation is not the inherent problem) – Chris Riddell Nov 20 '13 at 08:19
Oh and to add to what I wrote above, make sure you wait for the threads to finish before closing the `PersistenceController`. You would probably want to use an `ExecutorService` to do that – Chris Riddell Nov 20 '13 at 08:22
And finally, for the other code you posted, you would not usually want to create one EntityManager and transaction for a single insert. I recommend you read up on the function of an EntityManager, transaction and the persist operations – Chris Riddell Nov 20 '13 at 08:37
@ChrisRidal yes, My controller is outside the loop. I have to update a row which should be done based on MyId and not based on LogId generated. Lets assume, I don't have a reference of the object and I have to update the row with MyId. Or Is there a reference code, which will help me in understanding the updating? Thanks. – User12111111 Nov 20 '13 at 08:39
@Hun If you are trying to query to get this object from the database, then you need to write an sql query with a where clause which you can do using JPA. If you have the item then you can find it in the normal way you would any java program, maybe that is iterating through a List or Map which you have kept yourself of the objects you persisted until you find the MyId that you are looking for. Once you have it, assuming it has already been persisted, you can simply make the changes to the object you have found and wait for the next commit to be called. – Chris Riddell Nov 20 '13 at 09:10
@ChrisRidal I just updated an answer on this page. http://stackoverflow.com/a/20094968/2741980 – User12111111 Nov 20 '13 at 11:47
@Hun [I don't have the rep to comment on that answer] Remove your update(activityLog) method completely, use persist(activityLog) in place of it. (Also you don't need to begin a new transaction for your query, you have one already in that class) – Chris Riddell Nov 20 '13 at 12:12
I think your problem is caused by beginning all the new transactions: You have one from when you create the `PersistenceController` class until you `commitAndClose()`. There is no need to create new ones. (Same for the query - you don't need to begin a new transaction for your query, you have one already in that class, its attached to the `EntityManager`). – Chris Riddell Nov 20 '13 at 12:21
@ChrisRidal if I call persist(activityLog), I think it will fail due to id, if id is missing, it will insert a new record rather than updating existing row. I wanted it based on MyId. – User12111111 Nov 20 '13 at 12:44
@ChrisRidal I am not creating new transactions for every update. – User12111111 Nov 20 '13 at 12:47
@ChrisRidal do you think http://stackoverflow.com/a/20089475/2741980 will help me in reducing the deadlock error? – User12111111 Nov 20 '13 at 13:19
Sorry, your right you are not creating new transactions. If you are generating the ID yourself then you can annotate your id to be the primary key with `@Id`, and remove the other ID. You just have to make sure your ID is unique, you could use a random integer etc to test. Another thing to note is that you would need to have committed the database before executing a query is going to do anything, if you are running the query in the same application. – Chris Riddell Nov 20 '13 at 14:26

score 2 · Answer 2 · edited May 23 '17 at 10:29

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).

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

2 Answers2