spring boot + jpa + postgrsql + external tomcat + jndi datasource
- 이 에러는 @Trasactional(with isolation configure)이 걸린 메소드를 호출 시 발생한다.
- 원인은 톰캣에 설정한 datasource의 defaultAutoCommit = false
- defaultAutoCommit 값을 지우거나 true로 설정하면 해결된다.
- 아마도 defaultAutoCommit 설정의 기본값이 true인듯.
- 여기를 보자 머리좋은 사람이 Reason을 달아놓았다.
Denuwanthi De Silva ‘s comment
Reason:
It seems like the issue is occurring from the pooling mechanism (jdbc-pool).
The ‘defaultAutoCommit’ is set to false for postgresql. Thus, the connection is in a transaction.
When the pooling mechanism makes the validation query, it is running inside that transaction.
Then, we try to do another operation, (set transaction isolation) by
dbConnection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
But, it seems like the jdbc-pool is not committing (or rollback) the validation query. Thus, the exception happens
Caused by: org.postgresql.util.PSQLException: Cannot change transaction isolation level in the middle of a transaction.
If jdbc-pool commit the validation query, & set the defaultAutoCommit=false again there, we will not have problem in doing another operation (setTransactionIsolation).
So as a workaround we set the ‘defaultAutoCommit=true’ in the datasource configuration, to make sure that the transaction is handled properly.
