`EclipseLinkConnectionHandle` can fail against transaction isolation race condition

[jhoeller]

@jhoeller, all
I've found another related issue with shared DatabaseLogin object. I'm not sure if I should add the new issue to this one (I see there is a commit that handles the reported issue). However, my issue seems related - shared DatabaseLogin is probably the core issue for both. If needed I could open new issue.

Spring orm version I get the issue with is 6.2.15.

In Eclipse hawkBit we have sporadic failures of one of our tests DistributedLockTest.keepLockAlive -> e.g. https://github.com/eclipse-hawkbit/hawkbit/actions/runs/20857780854/job/59929050215.

What we see there is:

Internal Exception: org.h2.jdbc.JdbcSQLDataException: Invalid value "-1" for parameter "isolation level" [90008-232]

Error:  org.eclipse.hawkbit.repository.jpa.cluster.DistributedLockTest.keepLockAlive -- Time elapsed: 2.706 s <<< ERROR!
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 4.0.9.v202601021151-061974965b9a70b72fdcb96dae36a4d6ef5df9ce): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.h2.jdbc.JdbcSQLDataException: Invalid value "-1" for parameter "isolation level" [90008-232]
Error Code: 90008
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:334)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.checkTransactionIsolation(DatabaseAccessor.java:342)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.connectInternal(DatabaseAccessor.java:319)
	at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.reconnect(DatasourceAccessor.java:618)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.reconnect(DatabaseAccessor.java:1820)
	at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.incrementCallCount(DatasourceAccessor.java:346)
	at org.eclipse.persistence.internal.databaseaccess.DatasourceAccessor.beginTransaction(DatasourceAccessor.java:270)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicBeginTransaction(AbstractSession.java:761)
	at org.eclipse.persistence.sessions.server.ClientSession.addWriteConnection(ClientSession.java:757)
	at org.eclipse.persistence.sessions.server.ServerSession.acquireClientConnection(ServerSession.java:271)
	at org.eclipse.persistence.sessions.server.ClientSession.getAccessor(ClientSession.java:391)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.getAccessor(UnitOfWorkImpl.java:1963)
	at org.eclipse.persistence.internal.jpa.EntityManagerImpl.unwrap(EntityManagerImpl.java:2932)
	at org.springframework.orm.jpa.vendor.EclipseLinkJpaDialect$EclipseLinkConnectionHandle.getConnection(EclipseLinkJpaDialect.java:185)
	at org.springframework.jdbc.datasource.ConnectionHolder.getConnection(ConnectionHolder.java:162)
	at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
	at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:653)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:754)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:767)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:889)
	at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:918)
	at org.springframework.integration.jdbc.lock.DefaultLockRepository.lambda$isAcquired$3(DefaultLockRepository.java:423)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	at org.springframework.integration.jdbc.lock.DefaultLockRepository.isAcquired(DefaultLockRepository.java:420)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:360)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:724)
	at org.eclipse.hawkbit.repository.jpa.cluster.DistributedLockRepository$$SpringCGLIB$$0.isAcquired(<generated>)
	at org.eclipse.hawkbit.repository.jpa.cluster.DistributedLockTest.keepLockAlive(DistributedLockTest.java:171)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
Caused by: org.h2.jdbc.JdbcSQLDataException: Invalid value "-1" for parameter "isolation level" [90008-232]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:658)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.getInvalidValueException(DbException.java:298)
	at org.h2.engine.IsolationLevel.fromJdbc(IsolationLevel.java:67)
	at org.h2.jdbc.JdbcConnection.setTransactionIsolation(JdbcConnection.java:715)
	at com.zaxxer.hikari.pool.ProxyConnection.setTransactionIsolation(ProxyConnection.java:450)
	at com.zaxxer.hikari.pool.HikariProxyConnection.setTransactionIsolation(HikariProxyConnection.java)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.checkTransactionIsolation(DatabaseAccessor.java:338)
	... 33 more

As far as I see in EclipseLinkJpaDialect.beginTransaction there is implemented transactionIsolationLock in order to do isolation level changes atomic. However, on EclipseLinkJpaDialect.EclipseLinkConnectionHandle.getConnection the Eclipse link, under the hood, again deals with transaction isolation level - and if it is concurrently with beginTransaction it could fail.
The isolation level seems to be handled in EclipseLink DatabaseAccessor:

protected void checkTransactionIsolation(AbstractSession session) throws DatabaseException {
        if ((!this.isInTransaction) && (this.login != null) && (((DatabaseLogin)this.login).getTransactionIsolation() != -1)) {
            try {
                getConnection().setTransactionIsolation(((DatabaseLogin)this.login).getTransactionIsolation());
            } catch (java.sql.SQLException sqlEx) {
                DatabaseException commException = processExceptionForCommError(session, sqlEx, null);
                if (commException != null) throw commException;
                throw DatabaseException.sqlException(sqlEx, this, session, false);
            }
        }
    }

Originally posted by @avgustinmm in #24802