Error: "Lock Wait timeout exceeded; Try restarting transaction"

Question

Error: "Lock Wait timeout exceeded; Try restarting transaction"

Asked 9 years, 10 months ago

Viewed 2,128 times

2

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

Follow my classes (I will post only the insertion method so it doesn’t get so long):

Masterdao.java - Mother class of all the 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 who extends the Masterdao:

public class AlunoDAO extends MasterDAO{

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

Insert data.java - Class with main method for inserting objects in 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 funny thing is that sometimes it works, sometimes it goes normal, sometimes not. How to solve this problem?

1

This has deadlock face. You can demonstrate how it is that Aluno relates to other entities? Also goes the basic question, if you send to the bank, from within the application, a simple SQL made in the hand from outside of Hibernate such as select * from aluno where 1=0, it works?

– Victor Stafusa

2014/11/26 at 19:25
1

Isn’t there some ghost connections in the database locking the entire table? I’ve seen it happen, and then you have to tear down those connections by force.

– Victor Stafusa

2014/11/26 at 19:26
Victor, at the moment I’m not at home, but as soon as I get home I put the relationship code, and as for running a simple code, it also hangs inside the Mysql Workbench, except select, which works both with Hibernate and without.

– João Neto

2014/11/26 at 19:32
If you lock inside Mysql Workbench, this is a strong indication that the problem isn’t java or Hibernate, it’s something in Mysql.

– Victor Stafusa

2014/11/26 at 19:34
Running the show processlist command, it shows some processes like Sleep. I don’t know if this has anything to do.

– João Neto

2014/11/26 at 19:35
But it always worked normal, so it can’t be some property of the tables I generated with Hibernate?

– João Neto

2014/11/26 at 19:37
:) I think you found the problem. Probably these processes are ghost connections. If you can kill them, see if Hibernate goes back to normal.

– Victor Stafusa

2014/11/26 at 19:37
Precisely these processes with Sleep are in the bank that I am manipulating with Hibernate.

– João Neto

2014/11/26 at 19:42

Show 3 more comments

1 answer

Browser other questions tagged java mysql hibernate

You are not signed in. Login or sign up in order to post.

by utluiz • **72,075** points · Answer 1 · 2014-11-26T19:39:07+00:00

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

A possible cause of this is a critical code problem: missing treat errors.

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

Put a treatment with try/catch/finally, where the block finally should always ensure 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 lock problems and re-test your application using the above principles.