RE: Database Rollback doesn't work

[George Sexton]

I thought transactions were one of those features MySQL decided that
real developers didn't need. Are you really certain mySQL Supports
transactions in the relatively low version you are using?

Also, I hope you don't need to scale. You are essentially locking the
table until the mail goes or fails. What if it wedges for 60 seconds
doing a DNS lookup?

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 19, 2003 9:30 AM
To: [EMAIL PROTECTED]
Subject: Database 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]