EclipseLinkJpaDialect: Unexpected default isolation levels

[avgustinmm]

Affects: 6.0.4
(and before)

---

It seems that in some cases EclipseLinkJpaDialect doesn't handle correctly isolation levels.
For instance in the following flow:

1. Execute transaction with SERIALIZABLE isolation level -> EclipseLinkJpaDialect#beginTransaction sets to DatabaseLogin / DatabasePlatform the isolation level and probably all is ok and the SERIALIZABLE isolation level is applied. After that transaction the DatabasePlatform's isolation level isn't cleared / reset and stays SERIALIZABLE
2. Then start a new transaction with DEFAULT isolation level which, according to documentation, should be the default isolation of the underlying database. However, since EclipseLinkJpaDialect#beginTransaction doesn't do anything with isolation levels (when used DEFAULT) the isolation level in DatabasePlatform stays SERIALIZABLE and it seems that isolation level is used instead of DEFAULT.

I suppose that EclipseLinkJpaDialect#beginTransaction should set the DEFAULT isolation level to DatabasePlatform instead explicitly doing nothing in that case. Or, somehow DatabasePlatform's isolation level shall be reset after transaction.

I'm attaching a reproducer (reproducer.zip, main method to be considered is Reproducer#repoduce) that shows that:

• before first transaction the DatabasePlatform isolation level is -1 (DEFAULT)
• after a transaction with SERIALIZABLE isolation level in the DatabasePlatform stays SERIALIZABLE - not reset
• the method reproduce fails (against mysql) with:

com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

• when however line 40 (entityManager.unwrap(UnitOfWork.class).getPlatform().setTransactionIsolation(-1);) is uncommented and default level is reset to -1 (as at the beginning) the reproduce method passes. So we have different behavior of depending on the DatabasePlatform's isolation level before the last 2 transactions
• If you comment / remove the first transaction (SERIALIZABLE) and execute just the last 2 transactions - reproduce passes. So, the behavior of the last 2 transaction is influenced by the first one - which should not be the case

Note: reproducer is configured to use local mysql database (see the application properties) .

[jhoeller]

Relates to #24802 - the isolation level support for EclipseLink is somewhat broken and only really works properly in single-threaded scenarios with consistent isolation level declarations. We need to revisit what we're doing about isolation levels there: It might be better to deprecate isolation level support with EclipseLink to begin with - since we do not have APIs for proper per-transaction transaction isolation there but rather apparently just a system-wide shared isolation level setting in DatabaseLogin.