Thanks for the answer. I have discovered that there are transactions if you use InnoDB or BDB tables. But I have decided to implement the transactionality in the code... seems a better solution.
Monica > Monica > > I might be wrong but I think the version of mySQL you are using does not > support transactionality/rollback. mySQL is a speedy product but the > designers take the view that the > Application should be responsible for things like transactionality. > > Hope this Helps > > Michael > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, December 19, 2003 10:31 AM > To: [EMAIL PROTECTED] > Subject: [DBCP]Rollback doesn't work > > > Hi! > > I have a simple webapp that allows users to register. The user is > inserted in the DB and a confirmation mail is sent to the user. If the > mail fails I'd like the DB to rollback the transaction, but it doesn't > do it. The new user entry is kept in the DB. I'm using Tomcat 4.1 and > mySQL 2.3.2. > > I include the code, log and server.xml. > > Thanks for your help. > > Monica > > > ***CODE: > > Connection dbCon = null; > boolean isSuccess = false; > try { > //inser user in db > dbCon = new DBUtil().getDBConnection(); > dbCon.setAutoCommit(false); > userDBPopulator.insertEntry(user, dbCon); > > //send mail to user > [snip] > isSuccess = true; > } > //rollback if fail > catch (MailException e) { > log.error("MailException " + e.getMessage(), e); > log.debug("trying to rollback and close"); > try { > dbCon.rollback(); > } > catch (SQLException e1) { > log.error("SQLException rollbacking " + e1.getMessage(), > e1); > } > try { > dbCon.close(); > } > catch (SQLException e1) { > log.error("SQLException closing connection to DB " + > e1.getMessage(), e1); > } > } > catch (SQLException e) { > log.error("SQLException " + e.getMessage(), e); > try { > dbCon.rollback(); > } > catch (SQLException e1) { > log.error("SQLException rollbacking " + e1.getMessage(), > e1); > } > try { > dbCon.close(); > } > catch (SQLException e1) { > log.error("SQLException closing connection to DB " + > e1.getMessage(), e1); > } > throw new ServletException(e); > } > > //commit user entry > if( isSuccess){ > log.debug("commiting"); > try { > dbCon.commit(); > } > catch (SQLException e1) { > log.error("SQLException commiting " + e1.getMessage(), > e1); > throw new ServletException("SQLException commiting " + > e1.getMessage(), e1); > } > try { > dbCon.close(); > } > catch (SQLException e1) { > log.error("SQLException closing connection to DB " + > e1.getMessage(), e1); > } > } > > ****LOG: > > 2003-12-19 15:44:12,546 DEBUG [Thread-3] (RegisterServlet.java:200) - > handle register request 2003-12-19 15:44:12,562 INFO [Thread-3] > (UserDBReader.java:202) - No user found with name monica 2003-12-19 > 15:44:12,578 DEBUG [Thread-3] (MailHelper.java:88) - Sending mail to m > 2003-12-19 15:44:12,625 ERROR [Thread-3] (MailHelper.java:121) - > Messaging > Exception: Sending failed > 2003-12-19 15:44:12,625 ERROR [Thread-3] (MailHelper.java:124) - Next > Messaging Exception: Invalid Addresses; 2003-12-19 15:44:12,640 DEBUG > [Thread-3] (RegisterServlet.java:282) - trying to rollback and close > > > ***SERVER.XML > > <Resource name="jdbc/allukmasterDB" > auth="Container" > type="javax.sql.DataSource"/> > > <ResourceParams name="jdbc/allukmasterDB"> > <parameter> > <name>factory</name> > > <value>org.apache.commons.dbcp.BasicDataSourceFactory</value> > </parameter> > <parameter> > <name>maxActive</name> > <value>100</value> > </parameter> > <parameter> > <name>maxIdle</name> > <value>30</value> > </parameter> > <parameter> > <name>maxWait</name> > <value>10000</value> > </parameter> > <parameter> > <name>username</name> > <value>*******</value> > </parameter> > <parameter> > <name>password</name> > <value>********</value> > </parameter> > <parameter> > <name>driverClassName</name> > <value>com.mysql.jdbc.Driver</value> > </parameter> > <parameter> > <name>url</name> > > <value>jdbc:mysql://myMachine.com/myDB?autoReconnect=true</value> > </parameter> > <parameter> > <name>removeAbandoned</name> > <value>true</value> > </parameter> > <parameter> > <name>logAbandoned</name> > <value>true</value> > </parameter> > </ResourceParams> > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
