处理多个插入的最佳方法

Currently we are using play 1.2.5 with Java and MySQL. We have a simple JPA model (a Play entity extending Model class) we save to the database.

目前我们正在使用Java和MySQL的play 1.2.5。我们有一个简单的JPA模型(一个扩展Model类的Play实体)我们保存到数据库中。

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

At each web request we save multiple instances of the SimpleModel, for example:

在每个Web请求中,我们保存SimpleModel的多个实例,例如:

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

We are using the JPAPlugin.startTx and closeTx to manually start and end the transaction. Everything works fine if there is only one request executing the transaction. What we noticed is that if a second request tries to execute the loop simultaneously, the second request gets a "Lock wait timeout exceeded; try restarting transaction javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not insert: [SimpleModel]" since the first request locks the table but is not done until the second request times out. This results in multiple:

我们使用JPAPlugin.startTx和closeTx来手动启动和结束事务。如果只有一个请求执行事务,一切正常。我们注意到,如果第二个请求尝试同时执行循环,则第二个请求获得“超出锁定等待超时;尝试重新启动事务javax.persistence.PersistenceException:org.hibernate.exception.GenericJDBCException:无法插入:[SimpleModel ]“因为第一个请求会锁定表,但直到第二个请求超时才会执行。这导致多个:

ERROR AssertionFailure:45 - an assertion failure occured (this may indicate a bug in Hibernate, but is more likely due to unsafe use of the session) org.hibernate.AssertionFailure: null id in SimpleModel entry (don't flush the Session after an exception occurs)

ERROR AssertionFailure:45 - 发生断言失败(这可能表示Hibernate中存在错误,但更可能是由于会话的不安全使用)org.hibernate.AssertionFailure:SimpleModel条目中的null id(不要在会话后刷新会话)异常发生)

Another disinfect is that the CPU usage during the inserts goes crazy.

另一个消毒是插入过程中的CPU使用率变得很疯狂。

To fix this, I'm thinking to create a transaction aware queue to insert the entities sequentially but this will result in huge inserting times. What is the correct way to handle this situation?

为了解决这个问题,我想创建一个事务感知队列来顺序插入实体,但这会导致大量的插入时间。处理这种情况的正确方法是什么?

3 个解决方案

#1

JPAPlugin on Play Framwork 1.2.5 is not thread-safe and you will not resolve this using this version of Play.

Play Framwork 1.2.5上的JPAPlugin不是线程安全的,你不会使用这个版本的Play来解决这个问题。

That problem is fixed on Play 2.x, but if you can't migrate try to use hibernate directly.

Play 2.x上修复了这个问题,但如果你无法迁移,请尝试直接使用hibernate。

#2

You should not need to handle transactions yourself in this scenario.

在这种情况下,您不应该自己处理事务。

Instead either put your inserts in a controller method or in an asynchronous job if the task is time consuming.

相反,如果任务耗时,则将插入放在控制器方法或异步作业中。

Jobs and controller both handle transasctions.

工作和控制器都处理交易。

However check that this is really what you are trying to achieve. Each http request creating 5000 records does not seem realistic. Perhaps it would make more sense to have a container model with a collection?

但请检查这是否是您要实现的目标。创建5000条记录的每个http请求似乎都不现实。也许拥有一个带集合的容器模型更有意义?

#3

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

你真的需要整个插入的交易吗?数据导入期间数据库是否未锁定是否重要?

You can simply create a job and execute 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 insert and get rid of your database lock issue.

这将为每个插入创建一个事务并摆脱数据库锁定问题。

#1