java.sql.SQLException: Could not commit with auto-commit set on at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)

Question

I just upgrade to new jdbc driver from classes12.jar to ojdbc7.jar

My app threw an exception when was running with ojdbc7.jar:

java.sql.SQLException: Could not commit with auto-commit set on
    at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)
    at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4490)
    at oracle.jdbc.driver.T4CConnection.doSetAutoCommit(T4CConnection.java:943)
    at oracle.jdbc.driver.PhysicalConnection.setAutoCommit(PhysicalConnection.java:4

My app still run normally with classes12.jar.

I researched on oracle:

This exception is raised for any one of the following cases:

When auto-commit status is set to true and commit or rollback method is called
When the default status of auto-commit is not changed and commit or rollback method is called
When the value of the COMMIT_ON_ACCEPT_CHANGES property is true and commit or rollback method is called after calling the acceptChanges method on a rowset

But i couldn't find mistake in my source. Please help me give more explaination about this error.

Make sure you're using JDK7: that's what the "7" means in "ojdbc7". The classes12.jar is JDK 1.2 vintage - you should have changed years ago. Don't be fooled by "my app still runs normally with classes12.jar". You should not use that ever again. — duffymo
– duffymo, Commented May 30, 2014 at 11:33
Given the callstack I'd suspect a bug in the driver. When switching between autocommit true and false (and vice versa), the driver must commit the active transaction, and it looks like this commit fails because the driver is in autocommit. On the other hand, I'd expect Oracle to thoroughly test their driver for these kind of basic state switches. — Mark Rotteveel
– Mark Rotteveel, Commented May 30, 2014 at 11:44
@MarkRotteveel: Please look at this question. stackoverflow.com/questions/27272317/… I think my question is related to this thread — UI_Dev
– UI_Dev, Commented Dec 3, 2014 at 13:24

Olafur Tryggvason · Accepted Answer · 2014-11-26 13:45:42Z

29

The latest OJDBC drivers are more compliant then they where. You can turn off this behavior for legacy code:

-Doracle.jdbc.autoCommitSpecCompliant=false

It's a JVM option.

answered Nov 26, 2014 at 13:45

Olafur Tryggvason

4,90427 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rushi Daxini · Accepted Answer · 2017-01-21 09:04:39Z

14

This kind of exceptions occur when the Oracle JDBC Driver (ojdbc6.jar) version 12 or above will be used. Version 12 and above of the driver is more strictly than earlier driver versions.

You can solve the problem, you have few options:

Change jar file to old version.( Below 12; usually issue occurs while migrating to new server)
Override behavior of new jar version(ojdbc6.jar) with setting below JVM arguments.

-Doracle.jdbc.autoCommitSpecCompliant=false

IBM WAS users, refer this link:
Set Auto Commit off in Java/SQL:

Java:

conn.setAutoCommit(false);

Oracle:

SET AUTOCOMMIT OFF

answered Jan 21, 2017 at 9:04

Rushi Daxini

1,7101 gold badge11 silver badges14 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

8

Root Cause -

PhysicalConnector.java in ojdbc6

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

PhysicalConnector.java in 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;
    }
    .
    .

We can see that in ojdbc7, there is some piece of code has been introduced. If autoCommitSpecCompliant and getAutoCommit() both are true, we will be getting exception.

Two Fixes Available -

set autoCommitSpecCompliant false
Below JVM parameter to be set
-Doracle.jdbc.autoCommitSpecCompliant=false
insert below piece of code before connection.commit()

connection.setAutoCommit(false);

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 27, 2019 at 8:42

vinS

1,4755 gold badges25 silver badges39 bronze badges

1 Comment

Mache Over a year ago

second fix worked as expected; wanted to make this work directly in the code rather than using a JVM parameter

Gavriel · Accepted Answer · 2022-11-17 07:10:26Z

1

There's another solution that you can do from code:

System.setProperty("oracle.jdbc.autoCommitSpecCompliant", "false");

answered Nov 17, 2022 at 7:10

Gavriel

19.3k13 gold badges73 silver badges116 bronze badges

Comments

Saikat · Accepted Answer · 2019-05-28 10:24:11Z

0

We are IBM WAS v9 with using ojbc6.jar Above configure applied to APP Server, Node agent and DMGR, then it works.

-Doracle.jdbc.autoCommitSpecCompliant=false

Dmgr: Deployment manager > Process definition > Java Virtual Machine Modify "Generic JVM arguments"

NodeAgent: Node agents > nodeagent > Process definition > Java Virtual Machine

WebSphere Application Server: Application servers > WebSphere_Portal > Process definition > Java Virtual Machine Modify "Generic JVM arguments"

edited May 28, 2019 at 10:24

Saikat

17.5k22 gold badges127 silver badges146 bronze badges

answered Nov 23, 2018 at 10:32

johnson tsang

11 bronze badge

Comments

Kevin · Accepted Answer · 2018-03-16 08:01:57Z

-1

Changing this in Hibernate properties worked for me

hibernate.connection.release_mode=auto

answered Mar 16, 2018 at 8:01

Kevin

4497 silver badges13 bronze badges

1 Comment

Mark Rotteveel Over a year ago

Nothing in the question suggests the OP was using Hibernate.

Collectives™ on Stack Overflow

java.sql.SQLException: Could not commit with auto-commit set on at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:4443)

6 Answers 6

Comments

Comments

Root Cause -

Two Fixes Available -

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Root Cause -

Two Fixes Available -

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related