How do I prevent JPA inserts from locking a database table?

Question

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.

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

Here is the SQL server error:

Error Message: Lock request time out period exceeded.

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

Answer 1

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

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.

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.

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.

Answer 2

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

Answer 3

Do you insert all 700 rows with the same transaction?

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.

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.