In a nutshell, when I try and get a connection after not having used a transaction for several minutes the first transaction setup fails.
When things are working, my logs show the following for a simple transaction:
DEBUG: org.springframework.transaction.annotation.AnnotationTransactionAttributeSource - Adding transactional method 'getRecord' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,timeout_30; ''
DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Creating new transaction with name [com.example.services.Service.getRecord]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,timeout_30; ''
DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Acquired Connection [jdbc:mysql://dev-db.example.com:3306/example, [email protected], MySQL Connector Java] for JDBC transaction
DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Switching JDBC Connection [jdbc:mysql://dev-db.example.com:3306/example, [email protected], MySQL Connector Java] to manual commit
However, if I haven't had any activity for several minutes, instead I will get this message:
DEBUG: org.springframework.transaction.annotation.AnnotationTransactionAttributeSource - Adding transactional method 'getRecord' with attribute: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,timeout_30; ''
DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Creating new transaction with name [com.example.services.Service.getRecord]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,timeout_30; ''
My observations so far:
- seems to be based on inactivity, but I've seen this behavior immediately after restarting my Tomcat although nothing else was hitting the database so I think it's inactivity against a network element such as my MySQL server.
- when my application starts up, it makes a few non-transactional requests from the database which have not had any problems, so it seems related to transactions.
- the timeout element in the @Transactional notation is not effective in this case. It seems to eventually time out, but takes 15 minutes (!).
- while this transaction request is busy timing out, I can make subsequent requests successfully.
- doesn't seem to be a starved local connection pool. I have seen this right after restarting the Tomcat.
When it finally times out (did I mention 15 minutes!) I get the following:
DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Acquired Connection [org.apache.commons.dbcp.PoolableConnection@3269c671] for JDBC transaction
DEBUG: org.springframework.jdbc.datasource.DataSourceTransactionManager - Switching JDBC Connection [connection is closed] to manual commit
DEBUG: org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
DEBUG: org.springframework.jdbc.datasource.DataSourceUtils - Could not close JDBC Connection
ERROR: java.sql.SQLException: Already closed.
ERROR: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 1,312,604 milliseconds ago. The last packet sent successfully to the server was 924,748 milliseconds ago.
Caused by: java.net.SocketException: Connection timed out
Running Spring 3.1.1, mysql 5.1.32, commons-dbcp 1.4 and commons-pool 1.5.4.
Does anyone know what this is?
