Deadlock even with LockModeType.PESSIMISTIC_WRITE

Question

When I trigger 10 requests in 1 second, org.springframework.dao.CannotAcquireLockException occurs.

Even though I marked my main method (calling repository methods inside) with @Transactional(isolation = Isolation.SERIALIZABLE) and my findBy method in repository has @Lock(LockModeType.PESSIMISTIC_WRITE) annotation.

On one environment everything works fine, but on the other environment error occurs. Any ideas why error occurs? Code is the same on both environments.

Stacktrace 1:

{"timestamp":"2017-08-21T12:18:16.16+0000","status":500,"error":"Internal Server Error","exception":"org.springframework.orm.jpa.JpaSystemException","message": "com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction; nested exception is javax.persistence.PersistenceException: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction","path":"/cars/toyota"}

Stacktrace 2:

{"timestamp":"2017-08-21T12:11:57.57+0000","status":500,"error":"Internal Server Error","exception":"org.springframework.dao.CannotAcquireLockException","message":"could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute statement","path":"/cars/toyota"}

Answer 1

The error is thrown when you try to lock a row but the row is already locked by another transaction. So the timeout happens.

Adding the @Transactional(isolation = Isolation.SERIALIZABLE) makes it even worse because serializable level means one transaction locks entire table (modern oracle could somehow control this but for MySQl it's in fact a queue of transactions waiting to lock the same table). So 10th transaction waits for all previous 9 transactions and fails.

Actually I don't see why you need locks on findBy method. Logically the method should not modify the table so no need to lock at all.

I would suggest to change the logic to make one single call faster or even introduce optimistic locks (versions based) if you have concurrent modifications. Hibernate allows this by adding @Version.