[FIXED] MySql dead lock root cause - Hibernate

Issue

I have a code snippet in hibernate, structured like below.

This method is a common method and when I use two different locks (In parallel stream) I end up having dead lock scenario. I am trying to understand the root cause of the dead lock.

[Spring/JPA/Hibernate/MySql]

@Transactional(isolation = Isolation.SERIALIZABLE)
public void someMethod(String lockName) { // Lock name can be of two types

     jpaRepository.lock(lockName, TIMEOUT_10_SECONDS);

     List<Object> values = jpaRepository.findByDate();

     Integer sequence = jpaRepository.getNextSequenceValue(); // Fetches next value from sequence generator

     //...... set sequence numbers to object

     jpaRepository.updateSequence(); // A named query here to update sequence with latest value

     jpaRepository.saveAll(); // JPA's persist call
}

Innodb status

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-03-24 00:05:03 0x16c113000
*** (1) TRANSACTION:
TRANSACTION 2000, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 54, OS thread handle 6131019776, query id 976 localhost 127.0.0.1 root update
/* mysql-connector-java-8.0.27 (Revision: e920b979015ae7117d60d72bcc8f077a839cd791 */ insert into SequenceId (invoice_id, post_processed_sequence_value, sequence_id, sequence_value) values (1648076703544, '79', '2000', 79)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 6 n bits 192 index invoice_id of table `sequenceid` trx id 2000 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 6 n bits 192 index invoice_id of table `sequenceid` trx id 2000 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) TRANSACTION:
TRANSACTION 1999, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 6132133888, query id 977 localhost 127.0.0.1 root update
/* mysql-connector-java-8.0.27 (Revision: e920b979015ae7117d60d72bcc8f077a839cd791) */ insert into SequenceId (ref_id, post_processed_sequence_value, sequence_id, sequence_value) values (1648076703512, '50046', '1000', 50046)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 6 n bits 192 index invoice_id of table `sequenceid` trx id 1999 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 6 n bits 192 index invoice_id of table `sequenceid` trx id 1999 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

Solution

When a transaction is executed in serializable isolation read statements acquire shared lock. Please find the details below

1. Thread 1 tries to insert record by acquiring key. For insert there is no way we can hold a key. So mysql acquires a shared lock on the latest record before inserting.
2. Thread 2 tries to update the latest record and acquires the shared lock before inserting.

Now Thread 1 and Thread 2 are competing for the same lock and it causes deadlock.

Answered By - eshwar
Answer Checked By - Candace Johnson (JavaFixing Volunteer)