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

Question

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!

Cedillo

Answer 1

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.

Balalaika

Thank you a lot! at last someone explained that point to me... one question though: Why do you make the emf static in your example? is it OK to make it a private member of EMF? In my case, the building of the emf is somewhat more complex, so I prefer to do it inside an init function of my application class. I was thinking of the following: public class EMF { private EntityManagerFactory emf; static { emf = Persistence.createEntityManagerFactory("MyEMF"); } public static EntityManager getEM(){ return emf.createEntityManager(); } } – Cedillo

Well, the main reason behing making emf static is that the method getEM is static also, and it is not possible to reference a non-static field from a static context. Since I am not going to instantiate the class EMF, it made much more sense to make static the method getEM. If you do not want to make emf a static field, you will also need to make getEM non-static. – Balalaika