Connection is read-only. Queries leading to data modification are not allowed SQL Error: 0, SQLState: S1009

JPA specification does not support changing the transaction isolation level and that is the reason HibernateExtendedJpaDialect and IsolationSupportSessionTransactionData classes have been developed in the wild (Disclaimer: I am merely copying and pasting the ones I found below but all credits go their original authors).

We went ahead and tried HibernateExtendedJpaDialect just to notice that randomly entity persistence would fail with:

WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 0, SQLState: S1009
ERROR [org.hibernate.util.JDBCExceptionReporter] - Connection is read-only. Queries leading to data modification are not allowed

When you compare the code of the two classes you can identify extra cleanup which is essential to avoid leaving existing connections in a dirty state. This is very important especially for pooled connections of course:

package com.sample.utils.jpa;

import java.sql.Connection;
import java.sql.SQLException;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;

import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.orm.jpa.vendor.HibernateJpaDialect;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionException;

public class HibernateExtendedJpaDialect extends HibernateJpaDialect {

    private Logger logger = LoggerFactory.getLogger(HibernateExtendedJpaDialect.class);

    /**
     * This method is overridden to set custom isolation levels on the connection
     * @param entityManager
     * @param definition
     * @return
     * @throws PersistenceException
     * @throws SQLException
     * @throws TransactionException
     */
    @Override
    public Object beginTransaction(final EntityManager entityManager,
            final TransactionDefinition definition) throws PersistenceException,
            SQLException, TransactionException {
        Session session = (Session) entityManager.getDelegate();
        if (definition.getTimeout() != TransactionDefinition.TIMEOUT_DEFAULT) {
            getSession(entityManager).getTransaction().setTimeout(definition.getTimeout());
        }

        entityManager.getTransaction().begin();
        logger.debug("Transaction started");

        session.doWork(new Work() {

            @Override
            public void execute(Connection connection) throws SQLException {
                logger.debug("The connection instance is {}", connection);
                logger.debug("The isolation level of the connection is {} and the isolation level set on the transaction is {}",
                        connection.getTransactionIsolation(), definition.getIsolationLevel());
                DataSourceUtils.prepareConnectionForTransaction(connection, definition);
            }
        });

        return prepareTransaction(entityManager, definition.isReadOnly(), definition.getName());
    }

}

package com.sample.utils.jpa;

import java.sql.Connection;
import java.sql.SQLException;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;

import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.orm.jpa.vendor.HibernateJpaDialect;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionException;

public class IsolationSupportHibernateJpaDialect extends HibernateJpaDialect {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private Logger logger = LoggerFactory.getLogger(IsolationSupportHibernateJpaDialect.class);

    /**
     * This method is overridden to set custom isolation levels on the connection
     * 
     * @param entityManager
     * @param definition
     * @return
     * @throws PersistenceException
     * @throws SQLException
     * @throws TransactionException
     */
    @SuppressWarnings("deprecation")
    @Override
    public Object beginTransaction(final EntityManager entityManager, final TransactionDefinition definition)
        throws PersistenceException, SQLException, TransactionException {
        boolean infoEnabled = false;
        boolean debugEnabled = false;
        Session session = (Session) entityManager.getDelegate();
        if (definition.getTimeout() != TransactionDefinition.TIMEOUT_DEFAULT) {
            getSession(entityManager).getTransaction().setTimeout(definition.getTimeout());
        }

        Connection connection = session.connection();
        infoEnabled = logger.isInfoEnabled();
        debugEnabled = logger.isDebugEnabled();
        if (infoEnabled) {
            logger.info("Connection Info: isolationlevel={} , instance={} ", connection.getTransactionIsolation(), connection);
            logger.info("Transaction Info: IsolationLevel={} , PropagationBehavior={} , Timeout={} , Name={}",
                new Object[] { definition.getIsolationLevel(), definition.getPropagationBehavior(), definition.getTimeout(),
                        definition.getName() });
        }
        if (debugEnabled) {
            logger.debug("The isolation level of the connection is {} and the isolation level set on the transaction is {}",
                connection.getTransactionIsolation(), definition.getIsolationLevel());
        }
        Integer previousIsolationLevel = DataSourceUtils.prepareConnectionForTransaction(connection, definition);
        if (infoEnabled) {
            logger.info("The previousIsolationLevel {}", previousIsolationLevel);
        }

        entityManager.getTransaction().begin();
        if (infoEnabled) {
            logger.debug("Transaction started");
        }

        Object transactionDataFromHibernateJpaTemplate = prepareTransaction(entityManager, definition.isReadOnly(),
            definition.getName());

        return new IsolationSupportSessionTransactionData(transactionDataFromHibernateJpaTemplate, previousIsolationLevel,
            connection);
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.springframework.orm.jpa.vendor.HibernateJpaDialect#cleanupTransaction(java.lang.Object)
     */
    @Override
    public void cleanupTransaction(Object transactionData) {
        super.cleanupTransaction(((IsolationSupportSessionTransactionData) transactionData)
            .getSessionTransactionDataFromHibernateTemplate());
        ((IsolationSupportSessionTransactionData) transactionData).resetIsolationLevel();
    }

    private static class IsolationSupportSessionTransactionData {

        private final Object sessionTransactionDataFromHibernateJpaTemplate;
        private final Integer previousIsolationLevel;
        private final Connection connection;

        public IsolationSupportSessionTransactionData(Object sessionTransactionDataFromHibernateJpaTemplate,
            Integer previousIsolationLevel, Connection connection) {
            this.sessionTransactionDataFromHibernateJpaTemplate = sessionTransactionDataFromHibernateJpaTemplate;
            this.previousIsolationLevel = previousIsolationLevel;
            this.connection = connection;
        }

        public void resetIsolationLevel() {
            if (this.previousIsolationLevel != null) {
                DataSourceUtils.resetConnectionAfterTransaction(connection, previousIsolationLevel);
            }
        }

        public Object getSessionTransactionDataFromHibernateTemplate() {
            return this.sessionTransactionDataFromHibernateJpaTemplate;
        }

    }

}