The best way to handle multiple inserts

Question

We are currently using play 1.2.5 with Java and MySQL. We have a simple JPA model (a playback object that extends the model class), which we store in the database.

SimpleModel() test = new SimpleModel(); test.foo = "bar"; test.save(); 

In each web request, we store several instances of SimpleModel, for example:

 JPAPlugin.startTx(false); for (int i=0;i<5000;i++) { SimpleModel() test = new SimpleModel(); test.foo = "bar"; test.save(); } JPAPlugin.closeTx(false); 

We use JPAPlugin.startTx and closeTx to start and complete the transaction manually. Everything works fine if there is only one request executing a transaction. We noticed that if the second request tries to execute the loop at the same time, the second request gets a lock timeout, try to restart the transaction javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not insert: [SimpleModel] ", because the first request locks the table, but is not executed until the second query expires, which leads to several:

ERROR AssertionFailure: 45 - an assertion failed (this may indicate an error in sleep mode, but most likely due to unsafe use of the session) org.hibernate.AssertionFailure: null id in the SimpleModel record (do not clear the session after an exception occurs)

Another disinfection is that the use of CPU in inserts is crazy.

To fix this, I'm going to create a transaction queue to insert objects sequentially, but this will lead to huge insert times. What is the right way to deal with this situation?

Answer 1

JPAPlugin on Play Framwork 1.2.5 is not thread safe, and you cannot solve this problem with this version of Play.

This issue has been fixed in Play 2.x, but if you cannot migrate, try using sleep mode directly.

Answer 2

You do not need to process transactions yourself in this scenario.

Instead, put your inserts in a controller method or in an asynchronous job if the task takes a long time.

Work and controller process transactions.

However, make sure that this is really what you are trying to achieve. Each HTTP request that creates 5,000 records does not seem realistic. Perhaps it makes sense to have a container model with a collection?

Answer 3

Do you really need a transaction for the entire insert? Does it matter if the database is not locked during data import?

You can simply create a task and complete it for each insert:

 for (int i=0;i<5000;i++) { new Job() { doJob(){ SimpleModel() test = new SimpleModel(); test.foo = "bar"; test.save(); }.now(); } 

This will create a single transaction for each insertion and get rid of the database lock problem.