Could not open JPA EntityManager for transaction. The connection is closed.

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Getting jobs failed due to the below error message:

Publish failed for job: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

Environment

All supported TDM releases and platforms.

 

Resolution

The startup.log includes the stack dump of the problem.

The error entry in the job file has the following message

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction;
nested exception is javax.persistence.PersistenceException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.

The startup log shows a large number occurring around the same time. A typical stack has the following format
INFO   | jvm 1    | 2020/05/26 09:42:01 | org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction;
nested exception is javax.persistence.PersistenceException: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
INFO   | jvm 1    | 2020/05/26 09:42:01 | at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
INFO   | jvm 1    | 2020/05/26 09:42:01 | at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
INFO   | jvm 1    | 2020/05/26 09:42:01 | at com.ca.tdm.servicecommon.database.DatabaseAccessor.BeginTransaction(DatabaseAccessor.java:128)
INFO   | jvm 1    | 2020/05/26 09:42:01 | at com.ca.tdm.servicecommon.database.DatabaseAccessor.BeginTransaction(DatabaseAccessor.java:113)
INFO   | jvm 1    | 2020/05/26 09:42:01 | at com.ca.tdm.publ.store.TDMPublishStoreTable.insertRow(TDMPublishStoreTable.java:394)
INFO   | jvm 1    | 2020/05/26 09:42:01 | at com.ca.tdm.publ.model.TDMPublishJobStart.checkRow(TDMPublishJobStart.java:1383)

The Portal will need to use the PG internal tables to decide if there were duplicates. However in this case the connection to the repository was closed.

Since we use a connection pool to the repository, there are two possible scenarios causing the problem:

- scenario 1: The connection to the repository was lost. In this case, the only way for the Portal to function again is to do a service restart.

- Scenario 2: The database session was lost for a specific connection in the connection pool.

Each time the Portal needs a connection from the connection pool, it checks the connectivity to the database before borrowing it.This is enforced by the following entries in application.properties

spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.validation-query=select 1

It is therefore very unlikely that the Portal would 'borrow' an invalid database connection from the connection pool.

Logically, the only explanation is that the database session was dropped from outside the tool and therefore invalidating the connection to the database.