-----Mensaje original----- De: Mufaddal Khumri [mailto:mufa...@wmotion.com] Enviado el: jueves, 04 de noviembre de 2004 0:58 Para: ja...@lists.mysql.com Asunto: Question about commits and rollbacks.

Hi all:

I want feedback from the MySQL/JDBC gurus here about this. I have the following method that is in the db package thats used by a web application. The method askFAQ

1. gets a connection from the connection pool 2. sets autocommit to false 3. does some work (ie. asks an FAQ) 4. Either calls commit or rollback depending the work done was successful or not 5. sets autocommit to true 6. releases the connection.

NOTE: cp.allocate() and cp.free(con) are synchronized methods and cp is a singleton object.

I Am using mysql-connector-java-3.0.15-ga-bin.jar with MySQL Server version 4.1.6-gamma-standard with Tomcat 5.0.28.

Everything works fine during normal usage of this method. Now when I execute the following method during a load test and cause exceptions to happen in step 3 above, my understanding is that it will try to call con.rollback() and then call cp.free(con).

I get the following exception though: java.sql.SQLException: Can't call rollback when autocommit=true at com.mysql.jdbc.Connection.rollback(Connection.java:1523)

Que 1: Have I done the handling of exceptions properly? ie. are my try catch and finally blocks fine or can I improve something there? Que 2: Why would it give me the exception? (I set autocommit to false in the beginning and cp is a singleton object (refer NOTE above))

Code:

public void askFAQ(FAQ f) { Connection con = cp.allocate();

try { con.setAutoCommit(false);

PreparedStatement pstmt = con.prepareStatement("INSERT INTO FAQ " + "(QUESTION, ANSWER, TYPE, ITEMID, USERID, ASKED, APPEAR_IN_LIST, OWNERID) VALUES(?, ?, ?, ?, ?, ?, ?, ?)");

String s = f.getQuestion(); StringReader sr = new StringReader(s); pstmt.setCharacterStream(1, sr, s.length());

s = new String(); sr = new StringReader(s); pstmt.setCharacterStream(2, sr, s.length());

pstmt.setInt(3, f.getType()); pstmt.setInt(4, f.getItemId()); pstmt.setInt(5, f.getUserId()); pstmt.setTimestamp(6, f.getAsked()); pstmt.setBoolean(7, f.isAppearInList()); pstmt.setInt(8, f.getOwnerId());

pstmt.executeUpdate();

pstmt.close();

con.commit(); } catch(SQLException ex) { try { con.rollback(); } catch(Exception e) { e.printStackTrace(); }

ex.printStackTrace(); } finally { try { con.setAutoCommit(true);

} catch(Exception ex) { ex.printStackTrace(); } finally { cp.free(con); } } }