How to Avoid Lock wait timeout exceeded exception.?

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

@Transactional(propagation = Propagation.REQUIRES_NEW)

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.

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 .

(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.