1

I'm using Java 8, MySQL 5.6, Tomcat 7 (and its connection pool) and the MySQL J/Connector JDBC 5.1.35.

What I'm doing is calling three to six stored procedures (most are pure DML, some have SELECT statements intertwined with them). I set them up with prepareCall("{call NameOfTheStoredProcedute(?,?,...,?)}")

I execute two of them with executeQuery() and the others with executeUpdate().

Before commit() has been called, an exception is thrown and rollback() is being called. At this point I would have expected the data in DB tables would be reverted, but that did not happen! Note that no exceptions were thrown during rollback.

Code path being executed:

final Context initCtx = new InitialContext();
DataSource ds = (DataSource) initCtx.lookup("java:comp/env/jdbc/eukm");
Connection conn = ds.getConnection();
conn.setAutoCommit(false);

CallableStatement cs = conn.prepareCall("{call Store(?)}");
cs.setInt(1, 34); 

ResultSet rs = cs.executeQuery();
rs.next();
int newId = rs.getInt(1);
rs.close();

cs.close();

cs = conn.prepareCall("{call Add(?, ?)}");
cs.setInt(1, 34);
cs.setInt(2, 1);
cs.executeUpdate();
cs.close();

conn.rollback();
conn.close();
Howie
  • 2,760
  • 6
  • 32
  • 60
  • 1
    One possible reason could be that the stored procs commit within their body. – ramp May 13 '15 at 12:37
  • @ramp they do not. At least, I don't see any `COMMIT;`s in the code. Some sprocs do have two separate DML statements, though. Could that be an issue? – Howie May 13 '15 at 12:38
  • 1
    Switch on sql logging on your db to see whats happening. It could be that setAutocommit is not working – farrellmr May 13 '15 at 14:37
  • Did that and autocommit was set. However I've also noticed that a lot of my table are on MyISAM engine (which does not support transactions!). So that must be the reason rollback doesn't work. – Howie May 14 '15 at 05:48

0 Answers0