This is correct.

The <local-tx-datasource> will automatically set the auto commit value of the connection to false depending upon the context of the global transaction. Typically, there is no reason for you to use this datasource *and* set the auto commit value yourself. If you want to use this datasource outside of the context of a global transaction you can simply aquire the connection and set the value yourself. Note, this is not really the way this data source is intended for use. As a result, you have a few options:


1) Use the <no-tx-datasource>. Use of this datasource will put the responsiblity of the transaction on you. After acquiring the DataSource you will want to do the setAutoCommit(false), do your JDBC work, and then commit. This type of datasource funtions much like a non-managed datasource running outside of the application server though you still get pooling, can leverage security etc. With you particular config, you can do this simply by renaming the

 <local-tx-datasource>

 to

 <no-tx-datasource>

Note, the reason your setting is not working is because of the name. The

<connection-property name="auto-commit">false</connection-property>

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

The simple answer is that the code is attempting to do a commit on the underlying datasource in the wrong context. Again, the easiest thing for you to do is to use the <no-tx-datasource> and control the autocommit value yourself programmatically at runtime.

Chances are Sun Application Server via a service specific deployment descriptor or the runtime environment is managing this setting somehow for your deployment. This effecitvely (again, my best guess) amounts to a proprietary feature that does not integrate with JBoss.

Actually, the real issue is that JBoss's jdbc resource adapter
keeps track of the auto-commit state itself so it can optimize its usage:
http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJCALazyAutoCommit

Part of that, is the assumption that all connections are in getAutoCommit()==true
state when they are created (as per spec).

Support for this anti-spec behaviour would require the JBoss JDBC
resource adapter to do a getAutoCommit() on every new connection
rather than assuming a value of true.

Support for this anti-spec behaviour would require the JBoss JDBC
resource adapter to do a getAutoCommit() on every new connection
rather than assuming a value of true.

I am still getting the exception even after changing
<local-tx-datasource>
to
<no-tx-datasource>


My oracle-ds.xml looks like:

<?xml version="1.0" encoding="UTF-8"?>



<no-tx-datasource>
<jndi-name>jdbc/syfact</jndi-name>
<connection-url>jdbc:oracle:thin:@172.16.90.112:1521:syfactge</connection-url>

<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>abc</user-name>
xyz
<min-pool-size>5</min-pool-size>
<max-pool-size>100</max-pool-size>

<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>





<type-mapping>Oracle9i</type-mapping>

</no-tx-datasource>



and the exception is:
java.sql.SQLException: You cannot commit with autocommit set!
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcComm
it(BaseWrapperManagedConnection.java:545)
at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConne
ction.java:334)
at com.syfact.core.util.DBConduit.endTransaction(DBConduit.java:1500)...



Thanks and Regards,
Amit

"amit4uall" wrote:

and the exception is:
java.sql.SQLException: You cannot commit with autocommit set!
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcComm
it(BaseWrapperManagedConnection.java:545)
at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConne
ction.java:334)
at com.syfact.core.util.DBConduit.endTransaction(DBConduit.java:1500)...



Thanks and Regards,
Amit

SQLException - if a database access error occurs or this Connection object is in auto-commit mode

"vickyk" wrote:

Your problem is not related to this thread you would be calling the commit() on the connection whose autocommit property is set to true.

Thanks for your help. You diagnosed the problem correctly.
I was calling commit() on the connection whose autocommit property was set to true.


But I still have doubts regarding different application servers behaving differently for that same code(Is it so that only jboss checks the autocommit property?).

"amit4uall" wrote:

But I still have doubts regarding different application servers behaving differently for
that same code(Is it so that only jboss checks the autocommit property?).

I have tried to run autoCommit(true) for all actions to DB. But due to JCA (ironjacamar-jdbc-1.3.4.Final ) handling of connection it made it impossible.

There is a jca  (ironjacamar-jdbc-1.3.4.Final )  connector which tries to manage the connection locally, by wrapping it inside ./org/jboss/jca/adapters/jdbc/jdk7/WrappedConnectionJDK7.java.

There is two local variables in ./org/jboss/jca/adapters/jdbc/BaseWrapperManagedConnection.java

   /** JDBC auto-commit */

   protected boolean jdbcAutoCommit = true;

   /** Underlying auto-commit */

   protected boolean underlyingAutoCommit = true;

#1) is supposed to hold what is the current setting of jdbc connection auto commit.

#2) is supposed to initialize with underlying physical connection auto commit value.

But #2 is not doing that thing and just hard initiated with value true.

If the underlying database give connection with autocommit false then it would not be able to run first statement in autocommit true even if we set #1 as true. Because Both #1 and #2 have same value and the method checkTransaction does logic check as below. whcih cann be passed and won't do underlying con object setAutoCommit method.

   void checkTransaction() throws SQLException

   {

         // Check autocommit

         if (jdbcAutoCommit != underlyingAutoCommit)

         {

            con.setAutoCommit(jdbcAutoCommit);

            underlyingAutoCommit = jdbcAutoCommit;

         }

   }

The solution would be

#1) Run jca connection setAutoCommit(false) and execute dummy SELECT statement .. and then run jca connection setAutoCommit(true) and proceed with application workflow

The problem with this approach is unnecessary SELECT call

#2) Fxi the JCA connector to initialize underlyingAutoCommit property with value of actual database connection getautoCommit value

There is a flag jta="flase" for data source which should try to make conenction unmanaged but I have not tried that.

JBoss datasources follow the JCA specs with regards to setting autocommit on connections. I have attach a couple of articles below which describe the behavior[1], [2]. It also includes a snippet from the JCA specification describing the behaviour of auto commit for JCA connections.

So if the connection is retrieved outside of a transaction, then JBoss will set the autocommit to on, as per the spec.

If you wish to set it to false, you would need to do it programmatically on the connection itself. Another option is to get the connection directly, however then you loose the benefits of pooling.

Since you are seeing "You cannot commit with autocommit set!', as you suspected the autocommit will be set to on. As per the JCA spec, JBoss will be setting the autocommit to on when the connection is used outside of a transaction, so you must not be performing this operation within an application server transaction.

[1] https://access.redhat.com/solutions/193963

[2] https://access.redhat.com/solutions/63124

The JCA Spec 1.5, section 15.5.3.1 states: It requires that auto-commit is set to true by driver:

---------------------------------------------------------------

A resource adapter must manage the auto-commit mode as follows:

A transactional resource adapter must set the auto-commit mode of Connection instances to on when used outside a transaction.

Therefore the non-spec compliant connection property "autoCommit=false" won't work, JBoss is required by the spec to set the autocommit to true.

------------------------------------------------------------------

To get around this, you can programatically set auto-commit to false on the connection object by calling "setAutoCommit(false)"

Please see here[3] for more information:

[3]https://access.redhat.com/solutions/185293