autocommit and NoTxConnectionManager

[colin_daly]

Hi,

JBossRC2

we have a datasource to a legacy database which
does not handle transactions and after migrating
from 2.4.4 we hit the autocommit problem.

So we changed the code attribute in our
-service.xml to NoTxConnectionManager and commented
out the TransactionManager attribute. We confirmed
this because the datasource does not have a TransactionManager attribute in JNDI.
However, we are still getting autocommit exceptions
from the JDBC driver.

is there anything else which must change
in the -service.xml to get this to work.
We tried changing every occurence of LocalTx/
LocalTransaction to NoTx/NoTransaction and many
combinations of both but no luck.
(The OldRARDeployment name attibute cannot be
changed from Local or our datasource stops getting deployed in JNDI.)

I'm out of options... can anyone help.

regards,
Colin.

[davidjencks]

More info please. What exactly is the problem and where is it occurring? The NoTxConnectionManager doesn't try to do anything whatsoever with transactions.... so it is not at fault IMO

Do you have a jca adapter or are you using a jca-jdbc wrapper? It is likely that the Local wrapper calls setAutocommit on your connections. If this is the problem, you can probably spend a couple minutes looking at the Local wrapper and commenting out the checkTransaction implementation.

[shanness]

Hi David,

Thanks for the prompt reply, it helped. It seems one of our legacy data sources won't accept AUTOCOMMIT, yet it is non-transactional.

Anyhow, I have patched the local wrapper (connector/src/main/org/jboss/resource/adapter/jdbc/local/LocalManagedConnection.java)
as you suggested, and it fixed the problem.

I don't know how many other data sources have the same problem as mine (Transoft), but would it be possbile to make this an attribute so I don't have to maintain a patched jboss?

Thanks again David.

Regards,

Craig O'Shannessy

[townsendmerino]

I also need this, but wouldn't it be better to make the NoTxConnectionManager deployable?

[davidjencks]

I don't understand what you are trying to say. All 3 connection managers are deployable; that is what the db-service.xml files have, the deployment descriptor for one or the other.

Also, the suggested change was to the Local jdbc wrapper, which is a jca adapter, and sort of marginally part of jboss, not the connection manager, which is definitely part of jboss.

We could have a no -tx wrapper, more or less to go along with the local tx and xa. However I would like to know just how many "jdbc" drivers don't support any transactions at all before spending much time on this.

[clmich]

Sybase jConnect 5.2 and 5.5 for Adaptive Server Enterprise 11+ has the same problem (I posted a msg a few days ago on http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/

On the JonAS forum, I've seen that they "solved" the
setAutoCommit() problem by catching the "SET CHAINED not allowed" exception, doing a new "commit" and then reissuing the setAutoCommit().

Not really a solution, certainly not portable, but can help on the short term.

Anyway, many thanks to the jBoss team for their job...

From ConnectionImpl.java:

/**
* In a JDBC-XA driver, autocommit is false if we are in a global Tx
*/
public void setAutoCommit(boolean autoCommit) throws SQLException {
logger.log(BasicLevel.DEBUG, " entering");
try {
if (autoCommit == false) {
if (autocommit_unset == false) { // cache for optimization
actConn.setAutoCommit(false);
autocommit_set = false;
autocommit_unset = true;
}
} else {
if (autocommit_set == false) { // cache for optimization
actConn.setAutoCommit(true);
autocommit_set = true;
autocommit_unset = false;
}
}
} catch (SQLException e) {
String s = e.getMessage().toLowerCase();
if (s.indexOf("set chained command not allowed") != -1) {
logger.log(BasicLevel.DEBUG, "failed...");
logger.log(BasicLevel.DEBUG, "Committing then retrying");
try {
// These lines for Sybase only, hoping they don't broke
// anything for others DBs.
actConn.commit();
actConn.setAutoCommit(autoCommit); // Shouldn't fail now.
logger.log(BasicLevel.DEBUG, "succeeded after retry");
} catch (SQLException se) {
logger.log(BasicLevel.ERROR,"" + autoCommit + ") failed again after retry");
logger.log(BasicLevel.ERROR,"Exception: "+se);
xac.notifyError(e);
throw(e);
}
} else {
logger.log(BasicLevel.ERROR,"setAutoCommit(" + autoCommit + ") failed: "+e);
xac.notifyError(e);
throw(e);
}
}
}

[buzztroll]

I have a similar problem to this. I am using NoTxConnectionManager in an RA. Within the ra i contact the java:/DefaultDS datastore. On the connection to it i setAutoCommit(true) and i get the following error:

15:34:45,213 ERROR [STDERR] java.sql.SQLException: You cannot set autocommit during a managed transaction!