javadatabasespringhibernatebonecp

connection gone out while scheduled operation in Spring with BoneCP: "Database access prob lem. Killing off this connection..."


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?


Solution

  • 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.