EclipseLinkJpaDialect applies per-transaction isolation level to shared DatabasePlatform

[deanwong]

spring orm version: 5.1.4.RELEASE

In EclipseLinkJpaDialect class line 81

if (definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) {
	// Pass custom isolation level on to EclipseLink's DatabaseLogin configuration
	// (since Spring 4.1.2)
	UnitOfWork uow = entityManager.unwrap(UnitOfWork.class);
	uow.getLogin().setTransactionIsolation(definition.getIsolationLevel());
}

for instance:
If txA beginTransaction and there is Isolation.SERIALIZABLE around the repository class, the TransactionIsolation in uow.getLogin() will be changed to Isolation.SERIALIZABLE.
Then txB beginTransaction with Isolation.DEFAULT, the condition is not met, TransactionIsolation in uow.getLogin() still be SERIALIZABLE.

I think the right code should like this:

if (definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT
   && definition.getIsolationLevel() != entityManager.unwrap(UnitOfWork.class).getLogin().setTransactionIsolation()) {
	// ... 
}

[sbrannen]

@deanwong, I think you meant to use getTransactionIsolation() instead of setTransactionIsolation() -- right?

[deanwong]

@sbrannen yes

correct

definition.getIsolationLevel() != entityManager.unwrap(UnitOfWork.class).getLogin().getTransactionIsolation()

[jhoeller]

UnitOfWork is supposed to be an independent instance for every EntityManager, so I wonder why there would be old serialization settings exposed there? Every fresh UnitOfWork within a new transaction should come with EclipseLink's default settings... We just override those if necessary, and at the end of every transaction the UnitOfWork and its settings gets closed and disposed.

[deanwong]

We use Hikari as connection pool, does that affect the behavior?

[jhoeller]

I suspect that EclipseLink applies the transaction isolation level to the underlying JDBC Connection but does not restore the original isolation level there before it returns the Connection to the pool. As far as I can see, it does not expose the current connection isolation level via DatabaseLogin.getTransactionIsolation() though, so the suggested check above won't help. We'd rather have to make sure to restore the original JDBC isolation level ourselves, similar to how we do it with Hibernate. A bit of a shame since EclipseLink has a higher-level API for this, in contrast to Hibernate, so it seems a bit awkward to manually dive down to JDBC level for restoring.

[deanwong]

@jhoeller
I just create a minimize project to reproduce this problem. Hope that could help.
There are 4 interactions with DB, second query surrounding with SERIALIZABLE, and the following queries all use this isolation level.

[toalexivanov]

transactionIsolation field in
org.eclipse.persistence.internal.databaseaccess.DatabasePlatform is not
synchronized. This is the root cause.

DatabasePlatform stores various database settings that rarely
change at runtime. Perhaps that is what implementors of EclipseLink meant for
transactionIsolation as well, set it once and forget. This is how most
database applications use it anyway. It is rare for an application to use
transaction isolation level other than the default.

An instance of this class is called by JpaTransactionManager which is a Spring
bean in our application. Application accesses this bean from multiple threads.
Now you can imagine multiple service threads starting transactions with
different isolation levels concurrently. Also statement in
EclipseLinkAdapter.beginTransaction() checks requested transaction isolation
level and simply returns without setting it if it is equal to a default value.
Transaction isolation level is set to default automatically in Spring if it is
not set explicitly. In majority of our usages, we don't set this level explicitly
for one simple reason - default isolation level READ_COMMITTED is sufficient
for our purposes most of the time. However it means that transaction by explicitly
setting non-default isolation level overrides this level for all subsequent transactions.
Not only that, depending on timing, a transaction in one thread could silently
override transaction isolation level in another thread.

To remedy this problem I supplied CustomOraclePlatform bean where I override
setTransactionIsolation() and getTransactionIsolation(). These methods make use
of a ThreadLocal field represeting isolation level. Now each thread gets its own
isolation level. However transactions with different isolation levels
in the same thread would still experience the problem. Also cached thread pools
do not reset ThreadLocals. For that we need to prevent EclipseLinkJpaDialect
from invoking short-circuit check for default level. This can be
be done by translating TransactionDefinition.ISOLATION_DEFAULT to
TransactionDefinition.ISOLATION_READ_COMMITTED (or whatever default level is
appropriate for your database) in EclipseLinkJpaDialect.beginTransaction(). This
is exactly what I did in CustomEclipseLinkJpaDialect.

[jhoeller]

Well spotted, these calls internally delegate to the shared DatabasePlatform instance... despite appearing on an independent UnitOfWork instance and its DatabaseLogin. This means that EclipseLink's own support for transaction isolation levels is effectively unusable for our purposes :-( We'll see whether there is some way to apply an independent isolation level without modifying the DatabasePlatform implementation; otherwise we'll have to document this as being severely limited.