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
thread?

Any input would be appreciated.

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