Deadlock when running 2 transactional method with Spring Data and MySQL

Question

I am try to understand transactions using Spring Boot, Spring Data and MySQL.

I created service UpdateService which is used to update record in database. Methods change and change2 executes transactionally.

@Autowired
private UserRepo userRepo;

@Transactional(isolation = Isolation.SERIALIZABLE)
public void change() {
    User user = userRepo.findOne("Jan");

    write("before change", user);
    sleep(2000);
    user.setPassword("new password");
    write("after change", user);
}

@Transactional(isolation = Isolation.SERIALIZABLE)
public void change2() {
    User user = userRepo.findOne("Jan");

    write("before change2", user);
    user.setSecondName("new name");
    write("after change2", user);
}

and when I call change and change2 methods I received these logs:

before change User(name=Jan, secondName=Adam, password=Kowalski)
before change2 User(name=Jan, secondName=Adam, password=Kowalski)
after change2 User(name=Jan, secondName=new name, password=Kowalski)
after change User(name=Jan, secondName=Adam, password=new password)

...and after that also I got this exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

My question is this: Why does the transaction for change2 start before change is done? What is the reason of deadlock?

Answer 1

Bellow is my viewpoint:

why transaction change2 starts before change is done?

For mysql, first you need to care about the version of MySQL，and see this official site url about transaction. Specially, the description of Isolation Level SERIALIZABLE:

• This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommitis disabled. If autocommit is enabled(That says, the read operation of change won't block the read operation of change2), the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.

What is the reason of deadlock?

When read operation of change and change2 is executing, that says two transactions have hold the Shared Lock on the same row. When executing write operation of change(transaction t1) and change2(transaction t2), t1 will wait on t2 releases the Shared Lock and t2 also will wait on t1 releases the Shared Lock, so this will lead to dead lock.

I suggest you see the Transaction and Lock Mechanism of MySQL:

http://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/5.7/en/innodb-lock-modes.html

Hope to help you.