4

There are two functions A and B which are defined with Transactional annotation.

I am calling B from A.

@Transactional(value=Constants.READ_WRITE_REQUEST)
public int A(....){
    B();
}

@Transactional(propagation=Propagation.REQUIRES_NEW,value=Constants.READ_WRITE_REQUEST)
public int B(....){
     C();        
}

@Transactional(value=Constants.READ_WRITE_REQUEST)
public int C(....){
    ...
}

It was resulting into 

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction

    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:3491)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)

    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)

I removed the Propagation.REQUIRES_NEW from the function B and problem got resolved.

Is it because old transaction was holding the lock and new one was created? Any thoughts on this?

instanceOfObject
  • 2,936
  • 5
  • 49
  • 85

2 Answers2

7

Looks like some kind of dead lock, two transactions, the outer (from A) and the inner (from B) have a lock/try to lock the same resource.

mrembisz
  • 12,722
  • 7
  • 36
  • 32
Ralph
  • 118,862
  • 56
  • 287
  • 383
1

I had the same problem and this was my solution:
Since I didn't care to execute method B() immediately, I used annotation @Async from org.springframework.scheduling.annotation with function B() to make it executed in a separate thread.
PS: I hadn't value=Constants.READ_WRITE _REQUEST in my code

Ayoub Anbara
  • 407
  • 5
  • 10