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.

Written by

엘디는 사랑입니다.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store