How to resolve deadlock in MySQL due to hibernate envers auditing?

Question

While running few transactions in parallel, most of the time I get deadlock as :

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

I'm trying to infer what these statements are explaining. According to my understanding, transaction 2 is holding a lock on the primary index of TestDB.VerificationActionLog_AUD. At the very same time transaction 2 is also waiting for the same lock. How is it possible that a single transaction is holding and waiting for same lock?

I'm I inferring wrong from these statements? How can I proceed forward to resolve these deadlocks. Also deadlocks are for AUD tables only which are maintained behind the scenes by envers, how to resolve this?

Answer 1

It is happening because of Gap locks. A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record

Say you have adjacent id , 1 and 2. When procedure executed simultaneously from 2 different sessions, each of them put a gap lock on two index-records (with id values 1 and 2 - maybe 0 ,4,5 as well ,but let's assume just 2 for simplicity sake), and each of them has to wait for another one to release the lock to perform an insert.

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 *."

Solution:

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.

Refrences:

• 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