JPA, InnoDB, and locking in multi-threaded app

--001485f27552b118db04738c1c14
Content-Type: text/plain; charset=ISO-8859-1

Hi everyone,

I have an application that spawns various threads for inserting into various
table. Additionally, another thread is launched to delete old records.
They all use JPA and entity managed transactions. I think I'm getting
locking issues, but I'm not sure. So, I'd like to get your thoughts before
I add stack output to the app. My development tests are good, but errors
creep up in production after running for a few hours (about 8-9 hours in).

Each "inserting" thread is bound to a single table, and a table can have
multiple threading persisting objects to it. These threads do perform some
SELECT operation, but not to manipulate data - just to check for existence.
These threads act every 2 minutes.

The purging thread issues a DELETE (which, to my understanding, JPA
translates directly to a DELETE statement consistent with the underlining
db. In my case, MySQL InnoDB tables). It attempts to delete old records
(say, 6 months old), once every hour.

Both are types of threads start to get errors after running for a long
period. I make use I close my EntityManager object, and re-initialize my
EntityManagerFactory if emf.isOpen() returns false.

So, I'm trying to understand what locking is taking place here. Since my
tables are InnoDB, I thought row-level locking was used. Do "DELETE ..
WHERE .." statements lock an entire table, thus effect SELECTs from another
thread?

Any input would be appreciated.

--001485f27552b118db04738c1c14--