I use injected EntityManagerFactory for scheduled operation:
@PersistenceUnit
private EntityManagerFactory entityManagerFactory;
@Scheduled(cron="0 0/10 * * * *")
private void scheduledOperation() {
int rows = 0;
try {
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
rows = em.createNativeQuery("UPDATE table SET ...").executeUpdate();
em.getTransaction().commit();
} catch (Exception ex) {
logger.error("Exception while scheduledOperation. Details: " + ex.getMessage());
}
DateTime now = new DateTime(DateTimeZone.UTC);
logger.info("Scheduled operation completed. Rows affected: {}. UTC time: {}", rows, now);
}
When the application is started, scheduled operation runs every 10 minutes. So first several times operation works as well, but after some time this gone with error:
ERROR - ConnectionHandle - Database access problem. Killing off this
connection and all remaining connections in the connection pool. SQL State = 08S01
Whats happens? How I can keep connection, or take working connection for each scheduled operation?
That's because you don't ever close the EntityManager and the associated connections might hang indefinitely.
Change your code to this instead:
EntityManager em = null;
try {
em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
rows = em.createNativeQuery("UPDATE table SET ...").executeUpdate();
em.getTransaction().commit();
} catch (Exception ex) {
logger.error("Exception while scheduledOperation. Details: " + ex.getMessage());
em.getTransaction().rollback();
} finally {
if(em != null) {
em.close();
}
}
And always call rollback on failure. Don't assume transactions will rollback automatically because this is a database specific implementation.