MySQL lock wait timeout with a form submit

user_19110865 0
Viewed 29

Here is my doPost method of my servlet that in theory add data in db

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    Date d=(Date) request.getAttribute("data");
    DatiAnagrificiImpl dai=new DatiAnagrificiImpl();
    dai.addDatiAnagrafici(new DatiAnagraficiBean(request.getParameter("username"),request.getParameter("cap"),request.getParameter("nome")
                                            ,request.getParameter("cognome"),request.getParameter("telefono"),d));
}

And here the method that will insert data in db

enter public void addDatiAnagrafici(DatiAnagraficiBean dab) {
    // TODO Auto-generated method stub
    try {
        Statement s=c.createStatement();
        s.executeUpdate("insert into Dati_anagrafici values('"+dab.getIdUtente()+"','"+dab.getIdCittà()+"',"
                        + "'"+dab.getNome()+"','"+dab.getCognome()+"','"+dab.getTelefono()+"','"+dab.getDataNascita()+"')");
    }catch(SQLException e) {
        
    }finally {
        al.add(dab);
    }
}

But when I try to do that with a servlet I have this error

ERROR 1205 (HY000): Lock wait timeout exceeded;

Someone knows how to resolve? If I do the same thing with a normal java class is all fine.

mysql html java
1 Answers

It appears you have a transaction that's not completing. In MySQL, try running these to get a view into what's going on:

SHOW ENGINE INNODB STATUS;
SHOW FULL PROCESSLIST;

One or both should provide some clues.

A few issues stand out in your code. The first thing I would address is that the Statement is never closed (but should be). You can close the statement in a few places, but as the simplest example, you could close it right after you call executeUpdate(), like this:

Statement s = c.createStatement();
s.executeUpdate("...");
s.close();

Another easy change: when catching an exception, do something with it (instead of "swallowing" the exception silently). It's possible there's an exception being thrown, but the code above will hide that exception. For example, instead of this:

catch (SQLException e) {

}

try this:

catch (SQLException e) {
    e.printStackTrace();
}

A final improvement would be to rework the code a bit to use "try-with-resources" - a nice convenience to automatically close a resource for you (in this case, your Statement). That could look like this:

Connection connection = getConnection();
try (Statement statement = connection.createStatement()) {
    statement.executeUpdate("...");
} catch (SQLException e) {
    e.printStackTrace();
}

EDIT: One more thing.. the connection can (and should) be committed by calling commit() (or rollback() if something went wrong). Here's a final edit to try which shows commit() in the main try block, and a rollback() in the catch block to undo the transaction if an exception is thrown:

Connection connection = getConnection();
try (Statement statement = connection.createStatement()) {
    statement.executeUpdate("...");
    connection.commit();
} catch (SQLException e) {
    e.printStackTrace();
    try {
        connection.rollback();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}
user_113749570
Related