Propagation.REQUIRES_NEW causing LockWaitTimeOutException

Issue

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?

Solution

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.

Answered By – Ralph

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0