Why is hibernate session not closed after transaction is completed

Question

I use Spring + hibernate for database access (mysql). For read only methods, I need to change the underlying connection status to read only so I can send read operations to slaves (in master-slave).

In the following code, I use hibernate session to change the underlying connection to read only in getEmployer method. This part works properly. I assume after the getEmployer method is executed, the session will be closed (since it is annotated by @Transactional). So when the saveEmployer method below is called, a new session will be started. Therefore the connection.isReadOnly() in saveEmployer should return false. But when I call saveEmployer method (after calling getEmployer method), the connection.isReadOnly turns out to be true.

I believe that the session is not closed after getEmployer method is executed. My understanding is that when methods are annotated with @Transactional, the session will be closed after the transaction is committed. Did I do anything wrong here? Why the connection.isReadOnly() in the saveEmployer method is true? Thanks.

@Service
class public EmployerServiceImpl implements EmployerService{
    @Inject EmployerRepository employerRepository;
    @PersistenceContext
    EntityManager em;

    @Transactional
    public void getEmployer(long id){
        Session session = em.unwrap(Session.class);
        session.doWork(new Work(){
            @Override
            public void execute(Connection connection) throws SQLException{
                connection.setReadOnly(true);
            }
        });
        ......
        this.employerRepository.get(id);
    }

    @Transactional
    public void saveEmployer(){
        Session session = em.unwrap(Session.class);
        session.doWork(new Work(){
            @Override
            public void execute(Connection connection) throws SQLException{
               log.warn("connection status = " + connection.isReadOnly());
            }
        });
        ......
        this.employerRepository.save(employer);
    }
}

Here is the java configuration of dataSource in root context

@Bean
public DataSource springJpaDataSource()
{
    JndiDataSourceLookup lookup = new JndiDataSourceLookup();
    return lookup.getDataSource("jdbc/SpringJpa");
}

@Bean
public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean()
{
    Map<String, Object> properties = new Hashtable<>();
    properties.put("javax.persistence.schema-generation.database.action",
            "none");
    properties.put("hibernate.search.default.directory_provider",
            "filesystem");
    properties.put("hibernate.search.default.indexBase", "../searchIndexesApplyJob");

    HibernateJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
    adapter.setDatabasePlatform("org.hibernate.dialect.MySQL5InnoDBDialect");

    LocalContainerEntityManagerFactoryBean factory =
            new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(adapter);
    factory.setDataSource(this.springJpaDataSource());
    factory.setPackagesToScan("com.peer.site.entities", "com.peer.site.message",
            "com.peer.site.converters");
    factory.setSharedCacheMode(SharedCacheMode.ENABLE_SELECTIVE);
    factory.setValidationMode(ValidationMode.NONE);
    factory.setJpaPropertyMap(properties);
    return factory;
}

@Bean
public PlatformTransactionManager jpaTransactionManager()
{
    return new JpaTransactionManager(
            this.entityManagerFactoryBean().getObject()
    );
}

Answer 1

I figure out the reason. In tomcat document, it says

The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog if these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection, the pool itself will not reset them.

What happens is that the connection is reused. But the connection pool does not reset its state. So if you modify the connection status, you have to reset it since the connection pool will not reset it.

As others suggested, I set readOnly = true in @Transactional annotation which is a much cleaner way of doing it. But note that the version of the org.springframework has to be >= 4.1. Prior to 4.1, the readOnly will only change the flush mode but not set the connection to readOnly.