asked on

Commit throws exception in jboss when autocommit is set to true

I have encountered the error java.sql.SQLException: You cannot commit with autocommit set! while commiting the transaction. I am taking the connection via javax.sql.DataSource & I didn't set autocommit to false while taking connection the default is = true in this case as i've not done any entry regarding autocommit in my oracle-ds.xml file

My oracle-ds.xml is:
<?xml version="1.0" encoding="UTF-8"?>
<datasources>

<local-tx-datasource>
      <jndi-name>OracleDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@lxora.dlh.st.com:1521:adcsdev</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>adcsprd</user-name>
      <password>adcsprd</password>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
      <new-connection-sql>select 1 from dual</new-connection-sql>
      <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<min-pool-size>2</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <idle-timeout-minutes>1</idle-timeout-minutes>
      <metadata>
<type-mapping>Oracle8i</type-mapping>
</metadata>
</local-tx-datasource>

<local-tx-datasource>
      <jndi-name>OracleDS-QA</jndi-name>
      <connection-url>jdbc:oracle:thin:@eux047.sgp.st.com:1551:adcsqa</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>adcsprd</user-name>
      <password>jdhOHijB</password>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
      <new-connection-sql>select 1 from dual</new-connection-sql>
      <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<min-pool-size>2</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <idle-timeout-minutes>1</idle-timeout-minutes>
      <metadata>
<type-mapping>Oracle8i</type-mapping>
</metadata>
</local-tx-datasource>

</datasources>

By default the autocommit is true so why the error is coming after commiting the transaction. I've also made a test program with simple implementation as:

public class AutoCommitTest
{
      public static void main(String[] args)
      {
            Connection conn=null;
            Statement stmt=null;
            try
            {
                  Driver driver = (Driver) Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
                  DriverManager.registerDriver(driver);
                  conn = DriverManager.getConnection("jdbc:oracle:thin:@lxora.dlh.st.com:1521:adcsdev","adcsprd","adcsprd");
                  System.out.println("Conn===" + conn.getAutoCommit());
                  conn.setAutoCommit(true);
                  stmt=conn.createStatement();
                  int x=stmt.executeUpdate("update am_temp set name='aa'");
                  System.out.println("x=" + x);
                  conn.commit();
                  System.out.println("Table updated");
            }
            catch(Exception e)
            {
                  e.printStackTrace();
            }
            finally
            {
                  try{if(conn!=null) conn.close();} catch(Exception e) {}
            }
      }
}

in this case i received no error as " java.sql.SQLException: You cannot commit with autocommit set! "

Does jboss use any special internal handling mor managing the transaction. How can I come out from the
problem? Also the method _con.getMetaData().getJDBCMajorVersion() returns unsupported feature exception as i am using ojdbc14.jar. Is it better to user classes11.jar or ojdbc14.jar ??

Product Name : Document Management System
S/w:
-----
Java 1.5 update 11
jboss ver 4.0.3
Oracle 8i

elfe69

If you run in autocommit mode, you are not allowed to call conn.commit() or conn.rollback().

So you should:
- turn off autocommit
- remove the commit() & rollback() calls from your code

There are no other ways to avoid that error.

TimYates

What code is it trying to run when it falls over?

elfe69

Sorry, I forgot something, you should read:

So you should:
- EITHER turn off autocommit
- OR remove the commit() & rollback() calls from your code

ASKER CERTIFIED SOLUTION

keyurkarnik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anand_2000v

ASKER

I want to know that why the second program [AutoCommitTest ] works without throwing the exception and 1st one is not working is there any issue with jboss connection management ?

keyurkarnik

Ideally, the second program also should throw an exception, unless :

1. The driver being loaded does not throw an exception for commit when autocommi=true
2. For whatever reason, the autocommit is not being set to true in the second program

anand_2000v

ASKER

But I am using the same driver ojdbc14.jar in both the programs. How come it is possible that second program will throw exception ?

SOLUTION

keyurkarnik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

keyurkarnik

To set autocommit to false, try this :

<local-tx-datasource>
<blah blah blah>

<connection-property name="autoCommit">false</connection-property>

<blah blah blah>

</local-tx-datasource>

anand_2000v

ASKER

Here is my scenario

MY WEBAPP---------> DB

MY WEBAPP--------->3rd party tool------>DB

there are 2 different transactions on same DB one is directly interacting with db other is interacting with db by 3rd party tool now if I set the autocommit to false in both the transactions and I perform some manipulation in the common table then my table got locked I got stuck. So i think it is better to check by taking the status of autocommit in a boolean variable, and call commit/rollback conditionally or is there other way pls suggest ?

Also Is there any diff between when i set autocommit to false in my code itself or by doing <connection-property name="autoCommit">false</connection-property> ?

keyurkarnik

the <connection-property name="autoCommit">false</connection-property> tells JBoss that autocommit is false. So tehn it wont throw the exception
On the contarary, if you change it through code, JBoss wont know about it - its just a wrapper on top that does the check - so it will fail.

also, as I suggested earlier, use teh boolean for autocommit - it doesnt fail :)