mysql死锁根本原因 - 冬眠

Question

我有一个冬眠的代码片段，如下所示。

此方法是一种常见的方法，当我使用两个不同的锁（在平行流中）时，我最终会有死锁方案。我试图了解死锁的根本原因。

[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状态

------------------------
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)

Answer 1

当在可序列化的隔离中执行交易时，读取语句获取共享锁。
请找到下面的详细信息

1. 线程1尝试通过获取密钥来插入记录。对于插入，我们无法持有钥匙。因此，MySQL在插入之前在最新记录上获得了共享锁。
2. Thread 2尝试更新最新记录，并在插入之前获取共享锁。

现在，线程1和螺纹2正在争夺相同的锁，并导致僵局。