Error: "Lock wait timeout exceeded; try restarting transaction "

2

I'm developing a java application that uses Hibernate to do the mapping. I created the DAO's of classes, but when doing insert, update and delete operations it returns me this MySQL error.

Here are my classes (I'll only post the insertion method so it does not get too long):

MasterDAO.java - Parent class of all other daos, to make creation easier.

public class MasterDAO {

    public Session getSession(){
        return HibernateUtil.getSessionFactory().openSession();
    }

    public void inserirObjeto(Object obj){
        Session s = getSession();
        s.beginTransaction();
        s.save(obj);
        s.getTransaction().commit();
        s.close();
   }
}

AlunoDAO.java - Dao of the student that extends MasterDAO:

public class AlunoDAO extends MasterDAO{

    public void inserirAluno(Aluno aluno){
        inserirObjeto(aluno);
    }
}

Inserted.java - Class with main method to insert objects into the database

public class InsereDados {

    public static void main(String[] args) {

        Aluno aluno = new Aluno();
        aluno.setNome("João Neto");
        aluno.setCpf(777555);
        aluno.setDataNascimento(new Date(new String("09/05/1995")));
        aluno.setMatricula(2012030);
        aluno.setRg(123456);

        AlunoDAO alunoDAO = new AlunoDAO(); 
        alunoDAO.inserirAluno(aluno);
}

Running this generates the error:

  

Lock wait timeout exceeded; try restarting transaction

The funniest thing is that sometimes it works, sometimes it goes normal, sometimes it does not. How to solve this problem?

    
asked by anonymous 26.11.2014 / 19:13

1 answer

2

As @Victor mentioned, there may be "prey" transactions (ghosts) that lock the entire table.

One possible cause of this is a critical code problem: Missing handling errors .

In case of any failure in the inserirObjeto method, the transaction may not end with commit or rollback and the Hibernate session may never be closed.

Place a treatment with try/catch/finally , where block finally should always guarantee the closure of open resources.

Example:

public void inserirObjeto(Object obj){
    Session s = null;
    Transaction tx = null;
    try {
        s = getSession();
        tx = s.beginTransaction();
        s.save(obj);
        tx.commit();
    } catch (Exception e) {
        if (tx != null) tx.rollback();
    } finally {
        if (s != null) s.close();
    }
}

If possible, restart the SQL server to avoid current blocking problems and re-test your application using the above principles.

    
26.11.2014 / 20:39