Not able to see uncommitted updates made within same oracle

[csib]

Hello,

Code that worked fine under commns-dbcp is not working under jboss. Essentially subsequent read after write in same transaction is not seeing the records written. Oracle's default behavior is to be able to see updates made within same transaction even though not commited. Not sure why we are seing this in Jboss.

Data Source config:

<

local-tx-datasource>
 <jndi-name>myDS</jndi-name>
 <connection-url>jdbc:oracle:thin:myhost:mydb</connection-url>
 <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
 <user-name>user</user-name>
 <password>pass</password>
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
 <track-statements>true</track-statements>
 </local-tx-datasource>

Code snippet:

Connection con = null;
ResultSet rset = null;

InitialContext initctx = new InitialContext();
DataSource ds = (DataSource)initctx.lookup("java:/myDS");
con = ds.getConnection();
if (con != null)
{
 Statement stmt = con.createStatement();

......
 stmt.execute(insertQuery); //inserts records in a temp_keys table
 int updateCount = stmt.getUpdateCount();
//This prints count fine
 logger.debug("Update count:" + updateCount);
 String testQuery = "Select count(*) as CNT from TMP_KEYS";
 rset = stmt.executeQuery(testQuery);
//This Prints -1
 logger.debug("COUNT TMP TABLE: " + stmt.getUpdateCount());
if(rset.next()){
 //This prints 0
 logger.debug("COUNT of ROWS in TMP Table: " + rset.getInt("CNT"));
}
....

Any pointers appreciated.

Tx
-Sib

[adrian.brock]

See the FAQ on enabling debug.

Also check getWarnings() on the connection and statement

Look at the Oracle logs.

[csib]

Did not get any clue turning trace ON. Also no warnings found. But setting auto commit to false on connection did the trick. Appears that auto commit set to true causes separate transaction per statement execution. Since default is "true", still at a loss how it was working in dbcp.

[adrian.brock]

What you describe as the JBoss behaviour is also a pretty good summary of the spec.

[hbaxmann]

Just a try: IMHO the 'autocommit' "feature" is only a side effect. If you are sending serveral statements to the DB the jdbc-connection pooling is not reusing the current connection and oracle is not able to get previous context for sending the uncommited rows?

Alex?

bax

[adrian.brock]

Any pooling considersations are not in play here, he only does getConnection() once.

[hbaxmann]

I am, in the case of oracle, not sure if the JBoss connection has a static one-to-one mapping with the native oracle connection on the oracle server. if it is not explicitely required, like via XA.

bax