JPA Update Query -- Why won't my transaction work?

Question

I am having a performance problem with JavaDB (Derby) writing transactions. Each transaction takes more than 500ms and I might have hundreds of thousands a day. I am expecting to deploy on MySql and I don't know if I am going to have the same problem there, but I am trying to improve the performance on Derby before trying it.

One transaction involves updating a string of records -- as many as 6 like so:

public void update(List<Tally> list) {
    try {
        utx.begin();
        for (Tally tally : list) em.merge(tally);
        utx.commit();
    // etc

I wanted to see if an UPDATE query would work better, since JPA wouldn't have to keep track of the columns that updated and compose a SQL query (side question: is this a good theory?)

So I coded a named query that looks like this:

@NamedQuery(name="TallyUpdate",
   query="UPDATE Tally t SET t.vote = t.vote + 1 WHERE t.id IN :idSet"

That is operated like this:

@PersistenceContext protected EntityManager   em;
@Resource           protected UserTransaction utx;    
public void incrementVote(List<Long> idList) {
    Query q = em.createNamedQuery("TallyUpdate");
    q.setParameter("idSet", idList);
    try {
        utx.begin();
        q.executeUpdate();
        utx.commit();
    //etc

THE executeUpdate() call always throws an exception:

SEVERE: javax.persistence.TransactionRequiredException: executeUpdate is not supported for a Query object obtained through non-transactional access of a container-managed transactional EntityManager
    at com.sun.enterprise.container.common.impl.QueryWrapper.executeUpdate(QueryWrapper.java:225)

The JPA implementation is EclipseLink 2.3.0

What does that word-salad exception mean and how am I supposed to run the update?

RESULT POSTING:

I did move the createnamedQuery() call to after the utx.begin() call and the update started working. I am not sure why but I am going to run with it for now.

No result yet regarding the relative performance, but my posting above is misleading. When a vote is tallied it is tallied about 24-36 different ways, not 6. So there are six calls to the original update() method (which uses merge()) each with a list of about 6 records. It is this collection of calls that take more than 500ms. If the Tally table is empty then that benchmark is only about 27ms, which is about the speed I would like to see. The 500ms+ number is what I see when the table fills to 50,000 rows or more.

If anyone is interested in me posting the results of where I end up on this, please comment saying so.

P.S. this is a JSF application running under GlassFish, not EJB. I am not sure if there is any implication of that related to performance.

score 5 · Accepted Answer · answered Nov 05 '11 at 19:28

Since you have a container managed entity manager I'm guessing you have the thing injected into an EJB. Since that's the case that means that the transactions are managed by the container, i.e. you do not need to start them explicitlly in your code. By default a transaction start when an EJB method starts and ends when that method finishes. That's for stateless EJBs. For statefull EJBs the transaction can span multiple method calls, and end when the EJB "destroy" methods is called. So you should decide: you either use a container-managed entity manager (one injected into an EJB, as you have here) and you let the container manage the trasactions (via JTA) or you make an application-managed entity manager and you handle the transactions yourself in the code.

Regarding performance the Apache guys say Derby is perfectlly good for production, matching and sometimes even surpassing MySQL and PostgreSQL in certain scenarios as you can see in this Apache document:

http://home.online.no/~olmsan/publications/pres/apachecon05us/apachecon05.pdf

Take that with a grain of salt, Derby is not world renouned for its performance. And even this document shows that in certain situations Derby is a lot slower than MySQL. Also the CPU usage is bigger on Derby.

My advice: make a proof of concept aganins MySQL and do your benchmarks on that database. If that's what you're gonna use in production, it doesn't make much sense to do performance tune-ups of your app on a different database.

JPA Update Query -- Why won't my transaction work?

RESULT POSTING:

1 Answers1