2

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?

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
Nikolay Shabak
  • 576
  • 1
  • 7
  • 18
  • Why aren't you using Spring? Why are you creating the entity manager yourself instead of using one injected by spring and the same for your transaction why don't you let Spring handle that. – M. Deinum Sep 29 '14 at 05:39
  • I used JPA repository methods in my previous attempts, but i had same problem. If i used repositories in session-scoped code, all transactions working well, but for scheduled operations. – Nikolay Shabak Sep 29 '14 at 15:06
  • If you don't have a correct transaction and/or JPA setup it will not work. You are basically trying to work around the framework instead of working with it. – M. Deinum Sep 30 '14 at 05:45
  • I need to define special transaction manager for scheduled operations? – Nikolay Shabak Sep 30 '14 at 11:44
  • Only adding a transaction manager isn't enough. You need to indicate which methods need transaction by adding `@Transactional` next you need to tie things together with `@EnableTransactionManagement`. Then don't inject the `EntityManagerFactory` but the `EntityManager`. – M. Deinum Sep 30 '14 at 11:46
  • I had this all features, and just simple called repository method from scheduled method (with enabled "nativeQuery" option, but it doesn't matter). And I had same result (problems with transaction). I searched solution and found it through manual transaction management, especially for scheduled operations. Scope-based transactions (which I actually use in the application a lot) didn't work into scheduled operations. – Nikolay Shabak Sep 30 '14 at 14:14
  • They work if you have a proper setup. – M. Deinum Sep 30 '14 at 17:57

1 Answers1

2

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.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • 1
    Yes, i was testing it during 2 days, and it working well! You are my hero! I was confused between "old java way" and "Spring way" and forgot about closing transaction. I surely will read your "best practices" articles section. – Nikolay Shabak Sep 30 '14 at 11:42