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)

Answer 3

Make sure the database tables are using InnoDB storage engine and READ-COMMITTED transaction isolation level.

You can check it by SELECT @@GLOBAL.tx_isolation, @@tx_isolation; on mysql console.

If it is not set to be READ-COMMITTED then you must set it. Make sure before setting it that you have SUPER privileges in mysql.

You can take help from http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html.

By setting this I think your problem will get solved.

Thank You.

Answer 4

(Respectfully ignoring DB specific answers)

When using a formal CRUD schema with all DB traffic channelled through a Singleton class you can still encounter thread-locking. This often occurs due to an oversight between yourself, a college, and the Hibernate team. Therefore, it is quickest to review your own code for bugs -- paying particular attention to hibernate's core rules.

Normally a CRUD interface has public 'Create', 'Read', 'Update' and 'Delete' methods that share common private methods. This is done for DRY best practise. However, in doing so, these methods will work flawlessly most of the time, but not all of the time.

So, how to test and solve thread-locking?

Ensure:

• session.save(myObj) actions firstly check on the PK's uniqueness

• All uniqueResult() queries indeed return 1 result, And;

  (Important!) Focus Test-cases that:

  • Introduce PK duplicates
  • Update/Read/Delete non-existent records/entries/rows

Finally, use @AfterSuite (TestNG) to delete all table entries. Any insufficient implementation will yield another thread lock on the aforementioned operation ... otherwise, you are golden.

Answer 5

remove @Transactional(propagation = Propagation.REQUIRES_NEW) and check.It will work

Answer 6

Check if your where clause is optimized.. i.e. using primary key and/or indexes

Answer 7

If all of above does not work, check the error message from mysql log. If it mentions about the size of log file, you need to increase it in the configuration and restart the mysql server.