autocommit question

[wang.565]

I've created a web application using Sun Creator 2.1 and try to deploy it in JBoss 4.0.4. However, I saw the following exception:

14:33:26,329 ERROR [STDERR] java.sql.SQLException: You cannot commit with autocommit set!
14:33:26,329 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.jdbcCommit(BaseWra pperManagedConnection.java:545)
14:33:26,345 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.WrappedConnection.commit(WrappedConnection.java :334)
14:33:26,345 ERROR [STDERR] at com.sun.sql.rowset.internal.CachedRowSetXReader.readData(CachedRowSetXReader.ja va:244)
14:33:26,345 ERROR [STDERR] at com.sun.sql.rowset.CachedRowSetXImpl.execute(CachedRowSetXImpl.java:950)
14:33:26,345 ERROR [STDERR] at com.sun.sql.rowset.CachedRowSetXImpl.execute(CachedRowSetXImpl.java:1410)
...

As you can see, I used cachedrowset in my application. my data-source configuration in jboss is something like;

<local-tx-datasource> <connection-url>jdbc:microsoft:sqlserver://myserver:1433;DatabaseName=myt est;SelectMethod=Cursor</connection-url> <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-cla ss>
<connection-property name="auto-commit">false</connection-property>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>5</idle-timeout-minutes>
</local-tx-datasource>

Is there a simple work-around on this problem?

Thanks.

[weston.price]

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>

and remove the auto commit property.

2) Start a global transaction in your WebApplication via the UserTransaction object bound into JNDI to scope the work you want your JDBC connection to be enlisted in. See the JTA spec for examples.

3) Use EJB(X) and leverage CMT and declarative transaction management to do all of this for you.

More on datasource configuration can be found here:

http://wiki.jboss.org/wiki/Wiki.jsp?page=ConfigDataSources

[weston.price]

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

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

should be renamed to

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

But again, if you read my previous post, there are better, cleaner alternatives that the approach above. One of the drawbacks of doing this is the *-ds.xml file is that *all* connections created will have this property set. If you want to use the connection outside the context of a global transaction setting this property should really be done programmatically by you in your code.

[wang.565]

Thanks for the reply. I did change auto-commit to autoCommit, it gives the same exception.
I will try some alternatives listed. It seems there is no easy workaround on this problem. The reason is that a lot of webapplication code is generated by Sun Creator (for example, all those cachedrowset stuff). It works fine in Sun Application Server. So if there is some kind of one or a few lines change in configuration to make it compatiable with JBoss, that would be perfect.
Thanks.

[weston.price]

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.

[adrian.brock]

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.

[weston.price]

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.

Yes. The code appears to assume the autocommit setting to be false on connection creation, clearly a violation.

[amit4uall]

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

[vickyk]

"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

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

You are seeing this

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

http://java.sun.com/javase/6/docs/api/java/sql/Connection.html#commit()

[amit4uall]

"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.

Maybe that's the case, but the same piece of code is working fine on tomcat and websphere and the problem occurs when i deploy it on jboss after configuring a JDBC datasource.

[amit4uall]

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?).

[vickyk]

"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 am not aware of how other AS take care of all this , we are adhering to the specs .
You got to test the behavior with other AS , I can't explian you the behavior of other AS's as we don't write them :)

[iamdvr]

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.

[andey]

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