代码之家  ›  专栏  ›  技术社区  ›  eshwar

MySql死锁根本原因-休眠

  •  0
  • eshwar  · 技术社区  · 3 年前

    我在hibernate中有一个代码片段,结构如下。

    这种方法是一种常见的方法,当我使用两个不同的锁(在并行流中)时,我最终会遇到死锁场景。我正在努力了解死锁的根本原因。

    [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)
    
    0 回复  |  直到 3 年前