List:General Discussion« Previous MessageNext Message »
From:Grover Blue Date:September 14 2009 4:25pm
Subject:JPA, InnoDB, and locking in multi-threaded app
View as plain text  
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

Any input would be appreciated.

JPA, InnoDB, and locking in multi-threaded appGrover Blue14 Sep