CODEWITHSUNDEEP
javamysqlhibernate
Dileep
Dileep

Reputation: 5440

How to Avoid Lock wait timeout exceeded exception.?

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

Upvotes: 31

Views: 110376

Answers (7)

janadari ekanayaka
janadari ekanayaka

Reputation: 5116

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

Upvotes: 1

Brian Ko
Brian Ko

Reputation: 1

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.

Upvotes: 0

martoncsukas
martoncsukas

Reputation: 2165

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

@Transactional(propagation = Propagation.REQUIRES_NEW)

Upvotes: 7

gladiator
gladiator

Reputation: 742

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

Upvotes: 0

Koffy
Koffy

Reputation: 768

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

Upvotes: 1

Ravi Chhatrala
Ravi Chhatrala

Reputation: 324

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.

Upvotes: 5

Santosh
Santosh

Reputation: 17893

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 .

Upvotes: 27

Related Questions