如何防止JPA插入锁定数据库表？

I'm using Hibernate backed JPA as my persistence layer. I have a multi-threaded process that aggregates data from a Soap call and then stores a row in a database. The process usually ends up inserting about 700 rows and takes about 1 - 3 hours, with the Soap calls being the main bottleneck.

我正在使用Hibernate支持的JPA作为我的持久层。我有一个多线程进程，它聚合来自Soap调用的数据，然后在数据库中存储一行。该过程通常最终会插入大约700行，大约需要1-3个小时，而Soap调用是主要的瓶颈。

During this entire process the table I am inserting into locks up and will not return select statements in a timely manner.

在整个过程中，我插入的表会锁定，并且不会及时返回select语句。

Here is the SQL server error:

这是SQL服务器错误：

Error Message: Lock request time out period exceeded.

错误消息：超出锁定请求超时时间。

How do I avoid locking my database table during this lengthy process?

在这漫长的过程中，如何避免锁定数据库表？

3 个解决方案

#1

Do you insert all 700 rows with the same transaction?

您是否使用相同的事务插入所有700行？

Where is your transaction boundary? If you could maybe lower your transaction bounday, i.e. only transaction the actual insert operation so the lock is brielfy held.

您的交易边界在哪里？如果你可以降低你的交易边界，即只交易实际的插入操作，那么锁定就是brielfy。

If you need to have the whole process to be atomic it might be an idea to write it into a temp table and then do a bulk insert (fast) to insert it into the main table.

如果你需要将整个过程变为原子过程，那么将它写入临时表然后进行批量插入（快速）以将其插入主表可能是一个想法。

#2

You probably need to change your isolation level.

您可能需要更改隔离级别。

Here is some information: http://www.interview-questions-tips-forum.net/index.php/Your-Questions-on-Java/JDBC-Transaction/Transaction-Isolation-Levels

以下是一些信息：http：//www.interview-questions-tips-forum.net/index.php/Your-Questions-on-Java/JDBC-Transaction/Transaction-Isolation-Levels

It talks about the different isolation levels, and what they can help guard against. The general way to do this is to start with the strictist, and then go lower if you need better response times, while keeping in mind the requirements of data integrity/false reads.

它讨论了不同的隔离级别，以及它们可以帮助防范的内容。执行此操作的一般方法是从strictist开始，然后在需要更好响应时间时降低，同时牢记数据完整性/错误读取的要求。

edit

编辑

Spring Transaction levels are used to abstract JDBC (or whatever) transaction isolation levels at the transaction manager. They are defined in the TransactionDefinition class, and are static members.

Spring事务级别用于在事务管理器中抽象JDBC（或其他）事务隔离级别。它们在TransactionDefinition类中定义，并且是静态成员。

TransactionDefinition.ISOLATION_DEFAULT
Default isolation

TransactionDefinition.ISOLATION_READ_UNCOMMITTED
Lowest level of isolation; allows transactions to see uncommitted modifications from other transactions

TransactionDefinition.ISOLATION_READ_COMITTED
Cannot read uncommitted data

TransactionDefinition.ISOLATION_REPEATABLE_READ
Ensures repeatable reads

TransactionDefinition.ISOLATION_SERIALIZABLE
Most reliable; all transactions are executed atomically, and are treated as though they occurred serially.

There are also transaction propagation levels. You may be using a transaction for pure reads, which might be overkill - reads do not require transactions, writes should ALWAYS have a transaction around them. The propagation levels are definite in TransactionDefinition as well. These are used, usually in a spring wiring file, to define the serialization and propagation for a particular call. If you have an example of your wiring up, I might be able to give some more hints/information.

还有事务传播级别。您可能正在使用事务进行纯读取，这可能是过度的 - 读取不需要事务，写入应始终围绕它们进行事务处理。 TransactionDefinition中的传播级别也是明确的。这些通常用在弹簧布线文件中，用于定义特定调用的序列化和传播。如果您有一个接线示例，我可能会提供更多提示/信息。

#3

Are you trying to make 700 soap requests in one JPA transaction? Don't do that. :-)

您是否尝试在一次JPA交易中提出700个肥皂请求？不要那样做。 :-)

#1