Hi guys, I found this really cool bug dealing with Orion Server 1.5.2 and IBM DB2's JDBC implementation. I don't know if it's a) me (likely) b) orion (perhaps) c) ibm's jdbc implementation (wouldn't doubt it)
I BCC'ed a IBM dude, so for his info, orion server is found at www.orionserver.com but here it goes... Remember that transaction problem I had? Well I solved it. I'm going to include some messy source code because I want to make sure the problem has the best chance of being replicated... but of course there might be differing opinions. ;) The bug is this. I set autocommit to false... but then I update this table: create table lim (cap int); insert into lim values (600); Then I run this program which increments the value of cap by 10, then rolls it back. If I include the statement: DatabaseMetaData dbmd = conn.getMetaData(); ** and/or the statement ** out.println ("isolation: " + conn.getTransactionIsolation()); In my code... it seems to turn autocommit back on. If I throw that statement back into the comment section, things work fine again. Neat isn't it? Now can any one tell me where the problem might lie? I mean it's cool if you can't... but it should be noted for future reference should you run into the same problem as I have. Alright then, enjoy the code... Jeff Here goes... import java.util.Map; import java.util.Hashtable; import java.io.PrintWriter; import java.io.IOException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.ServletException; import javax.naming.InitialContext; import javax.rmi.PortableRemoteObject; import java.sql.*; import javax.sql.DataSource; public class XTest extends HttpServlet { public void doPost (HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { log ("Start XTest"); PrintWriter out = res.getWriter(); out.println ("XTest start"); out.println ("here we go..."); Connection conn = null; try { InitialContext ctx = new InitialContext(); DataSource ds = (DataSource) ctx.lookup ("jdbc/THEDS"); conn = ds.getConnection(); /* DatabaseMetaData dbmd = conn.getMetaData(); out.println ("isolation: " + conn.getTransactionIsolatio n()); out.println ("tx_none: " + Connection.TRANSACTION_NONE); if (dbmd.supportsTransactionIsolationLevel(Connection.TR ANSACTION_NONE)) out.println ("Supports TX_none"); else out.println ("NO suuport for TX_NONE"); out.println ("tx_rd_com: " + Connection.TRANSACTION_READ _COMMITTED); if (dbmd.supportsTransactionIsolationLevel(Connection.TR ANSACTION_READ_COMMITTED)) out.println ("Supports TX_rd_com"); out.println ("tx_rd_uncom: " + Connection.TRANSACTION_RE AD_UNCOMMITTED); if (dbmd.supportsTransactionIsolationLevel(Connection.TR ANSACTION_READ_UNCOMMITTED)) out.println ("Supports TX_rd_uncom"); out.println ("tx_rpt_rd: " + Connection.TRANSACTION_REPE ATABLE_READ); if (dbmd.supportsTransactionIsolationLevel(Connection.TR ANSACTION_REPEATABLE_READ)) out.println ("Supports TX_rpt_rd"); out.println ("tx_ser: " + Connection.TRANSACTION_SERIALI ZABLE); if (dbmd.supportsTransactionIsolationLevel(Connection.TR ANSACTION_SERIALIZABLE)) out.println ("Supports TX_ser"); */ try { conn.setAutoCommit (false); } catch (Exception e) { out.println ("setAC no workie"); } if (conn.getAutoCommit()) { out.println ("crap it's autocommiting"); } else { out.println ("it's claiming no auto "); } PreparedStatement ps = conn.prepareStatement ("update lim set cap = cap + 10 "); int n = ps.executeUpdate (); conn.rollback(); out.println ("yeah ok" + n + "changes"); /* int done = 0; ps = conn.prepareStatement ("select * from flag"); //ps.setQueryTimeout (60); while (done == 0) { ResultSet rs = ps.executeQuery(); while (rs.next()) { if (rs.getInt("cap") == 666) { out.println ("rollback" + n + "changes") ; conn.rollback(); done = 1; break; } if (rs.getInt("cap") == 777) { //out.println ("commit" + n + "changes") ; out.println ("rollback" + n + "changes") ; //conn.commit(); conn.rollback(); done = 1; break; } } } */ ps = conn.prepareStatement ("select * from lim"); ResultSet rs = ps.executeQuery(); rs.next(); out.println ("lim: "+rs.getString("cap")); ps = conn.prepareStatement ("select * from flag"); rs = ps.executeQuery(); rs.next(); out.println ("flag: "+rs.getString("cap")); conn.close(); } catch (Exception e) { out.println ("not ok"); out.println ("try: " + e.getMessage()); try { conn.rollback(); conn.close(); } catch (SQLException se) { out.println ("can't rollback or close" + e.getMessage()); } } } public void doGet (HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException { doPost (req, res); } }