How to Avoid Lock wait timeout exceeded exception.?

Question

    java.sql.SQLException: Lock wait timeout exceeded; try restarting tra
nsaction at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.ja
va:2077)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
2228)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:
208)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
        at org.hibernate.loader.Loader.doQuery(Loader.java:697)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Lo
ader.java:259)
        at org.hibernate.loader.Loader.loadEntity(Loader.java:1885)
        ... 131 more

I am getting repeated lock timeout exceeded exception while I update the records.

I am using Java Struts 2.1 Hibernate configuration. DB Used is MYSQL.

Anyone know how to solve it..??

Answer 1

Here are some suggestions:

1. ‘Lock wait timeout’ occurs typically when a transaction is waiting on row(s) of data to update which is already been locked by some other transaction.
2. Most of the times, the problem lies on the database side. The possible causes may be a inappropriate table design, large amount of data, constraints etc.
3. Please check out this elaborate answer .

Answer 2

It could be caused by the misuse of the following annotation as well, like in this StackOverflow article:

@Transactional(propagation = Propagation.REQUIRES_NEW)