CODEWITHSUNDEEP
javaspringtransactions
instanceOfObject
instanceOfObject

Reputation: 2984

Propagation.REQUIRES_NEW causing LockWaitTimeOutException

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?

Upvotes: 6

Views: 8199

Answers (2)

Ayoub Anbara
Ayoub Anbara

Reputation: 473

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

Upvotes: 1

Ralph
Ralph

Reputation: 120781

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.

Upvotes: 7

Related Questions