javamysqlpersistenceentitymanagerswing-app-framework

How should manage Database transactions using Entity Manager in a relatively large application?


I have developed a fairly large CRUD application, using a MYSQL Database and Swing Application framework and javax.persistence. My question is how should I best manage my transactions given the javax.persistence.Entitymanager? Currently, I have one instance of Entity manager held by the Application class. It is passed to all requesting pages, which in turn use it to persist and merge entities. I start a transaction on application start-up, and commit (and restart) each time a change is made. Is this correct? or should I hold a seperate Entity Manager for each Component / page? When should I commit? All these questions arised now, because I have recently started to get exceptions of the type: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction Error Code: 1205 which led me to believe I am doing something wrong in my management of database transactions....

Thanks in advance for any help you can give me!


Solution

  • Starting a transaction on application start-up is not the best idea. Transactions should be as short lived as possible because each of them locks the database. I mean, each time a transaction is started, no other thread can write to the database. The way you are doing things is exactly the opposite: your database is not locked only only during small periods of time. That is the likely cause of the error you are getting.

    In general, the recommended way of managing the transaction is the following:

    EntityManager em = EMF.getEM();
        em.getTransaction().begin();
        // your persist, merge code goes here
        em.getTransaction().commit();
        em.close();
    

    The EMF class is this one:

    public class EMF {
        private static EntityManagerFactory emf;
        static {
            emf = Persistence.createEntityManagerFactory("MyEMF");
        }
        public static EntityManager getEM(){
            return emf.createEntityManager();
        }
    }
    

    This way your transaction only locks the database during the time your persistence code is executing. Note that using the class EMF your entity manager factory is created only once. This is good because creating it is computationally expensive. However, once it is created making an instance of the entity manager is very cheap. This short tutorial explains it fairly well.