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

Nandkumar Tekale
  • 16,024
  • 8
  • 58
  • 85
Dileep
  • 5,362
  • 3
  • 22
  • 38
  • 1
    possible duplicate http://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im – Subin Sebastian Dec 20 '12 at 06:38
  • 1
    optimize the queries, or increase transaction timeout. check what is happening using `show innodb status` ; – Subin Sebastian Dec 20 '12 at 06:40
  • @SubinS i am not using auto commit in my program, I think i need to optimism the code. Can u suggest some links to help me to set the MySQL. I am a beginner in this so need some help. I have used the "set innodb_lock_wait_timeout=100" but doesn't worked. – Dileep Dec 20 '12 at 10:37

7 Answers7

23

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 .
Community
  • 1
  • 1
Santosh
  • 17,667
  • 4
  • 54
  • 79
6

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

@Transactional(propagation = Propagation.REQUIRES_NEW)
Community
  • 1
  • 1
martoncsukas
  • 2,077
  • 20
  • 23
5

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.

Ravi Chhatrala
  • 324
  • 4
  • 14
  • 1
    This solution has some important side-effects which the developer has to be aware of. But it can help debugging the true issue behind the deadlock. – Guillaume F. Apr 25 '17 at 10:26
  • thanks but you did not mention who this helps solving that exception ? – shareef Jan 29 '18 at 09:32
  • I tried SET GLOBAL transaction_isolation='READ-COMMITTED'; and gave me error i wll google it plus i changed this too innodb_lock_wait_timeout 50 to 300 seconds – shareef Jan 29 '18 at 09:51
  • @GuillaumeF. What side effects? Better not even say anything at all or don't say anything. You're leaving out important information. don't half step – TheRealChx101 Dec 13 '22 at 07:38
  • @TheRealChx101: Comments are not replacing an answer; they are just comments. If you want to understand why changing the isolation level globally in a database has some important side-effects, I advise you to read the documentation of your database's isolation levels, the side-effects are always explained there. – Guillaume F. Dec 13 '22 at 12:37
1

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

Koffy
  • 768
  • 8
  • 19
  • how does checking the PK's uniqueness ensure no thread blocking – user_mda Nov 06 '17 at 16:04
  • Hibernate is a framework for mapping domain models to a relational database. It's a faithful messenger that trusts your commands. That's all it is. So you the consumer must give it sensible instructions. Like, not Creating identical records, but instead Update the existing record. – Koffy Nov 07 '17 at 02:38
1

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

janadari ekanayaka
  • 3,742
  • 2
  • 13
  • 17
  • What do you mean "it will work" ? For example, in my application, it breaks the intended behavior (store the data via self-reference). – RAM237 Apr 11 '22 at 09:30
0

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

gladiator
  • 722
  • 1
  • 9
  • 16
0

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.

Brian Ko
  • 1