[Oracle] JDBC 변경 후 - Could not commit with auto-commit set on

현상

• 애플리케이션 환경
  • JDK 1.6 -> 1.8
  • ojdbc6 -> ojdbc8
  • Tomcat6 -> Tomcat8

애플리케이션 JDK 버전업 테스트를 위해 구 버전 JDK를 변경 후, ojdbc8로 변경한 후 실행하였더니 Could not commit with auto-commit set on 오류가 발생하였다.

java.sql.SQLException: Could not commit with auto-commit set on
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4439)
        at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4486)
        at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.commit(DelegatingConnection.java:358)
        at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.commit(DelegatingConnection.java:358)
        at org.sdf.rdb.TrxContext.commit(TrxContext.java:41)
        at org.sdf.slim.FrontServlet.initConfig(FrontServlet.java:74)
        at org.sdf.slim.FrontServlet.init(FrontServlet.java:48)
        at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1144)
        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1091)
        at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:983)
        at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4998)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5312)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1423)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1413)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

• auto-commit 오류 발생하는 경우
  • auto-commit 상태가 true인 상태에서 commit/rollback 호출
  • auto-commit 기본 상태가 변경되지 않은 상태에서 commit/rollback 호출

원인

ojdbc6 (버전 12) 이후부터 autocommit 부분 추가

• ojdbc6

public void commit(int paramInt) throws SQLException {
    disallowGlobalTxnMode(114);
    if (this.lifecycle != 1) {
        SQLException sQLException = DatabaseError.createSqlException(getConnectionDuringExceptionHandling(), 8);
        sQLException.fillInStackTrace();
        throw sQLException;
    }

• ojdbc7

public void commit(int paramInt) throws SQLException {
    disallowGlobalTxnMode(114);
    ​if (this.autoCommitSpecCompliant && getAutoCommit()) {
        throw (SQLException)DatabaseError.createSqlException(getConnectionDuringExceptionHandling(), 273).fillInStackTrace();
    }
    if (this.lifecycle != 1) {
        SQLException sQLException = DatabaseError.createSqlException(getConnectionDuringExceptionHandling(), 8);
        sQLException.fillInStackTrace();
        throw sQLException;
    ​}

처리 방안

1. 구 JDBC 사용

이전 jdbc를 그대로 사용합니다.

2. JVM 옵션 추가

JVM 실행 옵션에 -Doracle.jdbc.autoCommitSpecCompliant=false을 추가하여, JDBC옵션을 변경합니다.

3. 애플리케이션 소스에 AutoCommit 추가 함수 추가

con.setAutoCommit(false);

참고

• https://stackoverflow.com/questions/23953534/java-sql-sqlexception-could-not-commit-with-auto-commit-set-on-at-oracle-jdbc-d/57670792
• https://www.ibm.com/mysupport/s/question/0D50z000062kUDm/how-to-resolve-wtrn0107w-could-not-commit-with-autocommit-set-on-exceptions-in-websphere-application-server?language=ko

#ojdbc #ojdbc6 #ojdbc8