mysql - 如何解决MySQL由于hibernate envers审计而导致的死锁？

Question

虽然并行运行几个事务，但大多数时候我都会遇到死锁:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-09-04 06:19:12 0x2b01917c7700
*** (1) TRANSACTION:
TRANSACTION 14470484, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 4
MySQL thread id 69372, OS thread handle 47285779531520, query id 10366178979 172.31.19.11 master updating
update `VerificationActionLog_AUD` set `REVEND`=427956 where `id`=138136 and `REV`<> 427956 and `REVEND` is null
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7307 page no 1108 n bits 128 index PRIMARY of table `TestDB`.`VerificationActionLog_AUD` trx id 14470484 lock_mode X waiting
Record lock, heap no 60 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
...

*** (2) TRANSACTION:
TRANSACTION 14470485, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 4
MySQL thread id 69395, OS thread handle 47285735814912, query id 10366178981 172.31.19.11 master updating
update `VerificationActionLog_AUD` set `REVEND`=427957 where `id`=138137 and `REV`<> 427957 and `REVEND` is null
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7307 page no 1108 n bits 128 index PRIMARY of table `TestDB`.`VerificationActionLog_AUD` trx id 14470485 lock_mode X locks rec but not gap
Record lock, heap no 60 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7307 page no 1108 n bits 128 index PRIMARY of table `TestDB`.`VerificationActionLog_AUD` trx id 14470485 lock_mode X waiting
Record lock, heap no 60 PHYSICAL RECORD: n_fields 27; compact format; info bits 0
...

*** WE ROLL BACK TRANSACTION (2)

我试图推断这些陈述所解释的内容。据我了解，事务2持有TestDB.VerificationActionLog_AUD主索引的锁。与此同时，事务 2 也在等待相同的锁。单个事务怎么可能持有并等待同一个锁？

我从这些陈述中推断出错误了吗？我该如何继续解决这些僵局。另外，死锁仅适用于由 envers 在幕后维护的 AUD 表，如何解决这个问题？

Answer 1

这是由于间隙锁而发生的。间隙锁是对索引记录之间间隙的锁定，或者对第一个索引记录之前或最后一个索引记录之后的间隙的锁定

假设您有相邻的 id ，1 和 2。当从 2 个不同的 session 同时执行过程时，每个 session 都会在两个索引记录上放置间隙锁(使用 id 值 1 和 2 - 也可能是 0、4、5，但为了简单起见，我们假设为 2)，并且它们中的每一个都必须等待另一个释放锁才能执行插入。

Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock *."

解决方案:

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated). Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

引用文献:

• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks
• https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
• https://dba.stackexchange.com/questions/147948/why-does-this-simple-transaction-deadlock-with-mysql