Cannot change transaction isolation level in the middle of a transaction

[whiskeysierra]

Steps to reproduce

• default-auto-commit: false
• default-transaction-isolation: 2
• @Transactional(isolation = SERIALIZABLE)

Observed behaviour

PSQLException: Cannot change transaction isolation level in the middle of a transaction.
  at org.postgresql.jdbc.PgConnection.setTransactionIsolation(PgConnection.java:880)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Most tests fail, though some of them actually work, even with writes.

Expected behaviour

• all steps to reproduce work without exception

Investigation

• https://jira.spring.io/browse/SPR-8870
• https://wso2.org/jira/browse/REGISTRY-2276
• Could not open JDBC Connection for transaction; nested exception is org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction. brettwooldridge/HikariCP#102
• Transaction isolation applied to running transaction in HikariPool  brettwooldridge/HikariCP#24
• #7865 Spring Batch : Fix PostgreSQL with defaultAutoCommit=false spring-projects/spring-boot#7866
• https://stackoverflow.com/questions/18121078/wso2-api-manager-on-postgresql-raising-exception-when-user-logging-to-a-store
• Added AbstractCursorItemReader.connectionAutoCommit spring-projects/spring-batch#446
• https://stackoverflow.com/questions/4453782/why-set-autocommit-to-true

According to most docs/issues it looks like the problem usually occurs when connection validation is enabled and the test query is executed inside a transaction but without commit or rollback. But tests are disabled in our case (test-on-borrow: false) and it still fails.

[whiskeysierra]

Looks like it's a test artifact.

This works if running the service locally:

• default-auto-commit: false
• test-on-borrow: false
• default-transaction-isolation: 2
• @Transactional(isolation = SERIALIZABLE)

This one doesn't:

• default-auto-commit: false
• default-transaction-isolation: 2
• @Transactional(isolation = SERIALIZABLE)

Neither works in the tests though.

[whiskeysierra]

During tests the database is resetted (using a couple of TRUNCATE and ALTER SEQUENCE RESTART statements. This was not running inside a transaction.