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.
