5

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?

Rajat Goel
  • 2,207
  • 17
  • 35
  • 1
    It's clear from the `UPDATE` statements that you're using the `ValidityAuditStrategy` and its updating an older record's `REVEND` column when a new audit row is generated. You shouldn't be experiencing deadlocks unless you're attempting to perform some nested transaction behavior on the same entity. Without seeing your java code and transaction handling, its hard to say. – Naros Sep 04 '19 at 16:56
  • @Naros Thanks for your comment. Though still not able to resolve it but now I've got a bit better understanding. I've a few questions. Does MySQL has nested transactions? We are manually beginning and committing the transactions. I don't think there is any part where we are beginning a transaction inside an already running transaction. But we do flushing in between a transaction. Can manual flush causes nested transaction? – Rajat Goel Sep 05 '19 at 02:26
  • 1
    Please provide `SHOW CREATE TABLE VerificationActionLog_AUD` and some clues about what else is in the same transaction. Also, were these at the 'end' of the table? – Rick James Sep 06 '19 at 14:53
  • Explain what you mean by "flushing". – Rick James Sep 06 '19 at 14:57
  • We are using hibernate. A very good explanation is given at https://stackoverflow.com/questions/3220336/whats-the-use-of-session-flush-in-hibernate. Check answer 2 as well – Rajat Goel Sep 06 '19 at 16:03
  • So long as the _flush_ occurs as a part of the current bound transaction, that should have absolutely no impact. The row, data page, or table will be locked by the same session, so you shouldn't see a deadlock. Something else must be happening to cause this but without code, there is little anyone can really do to shed light on your problem. – Naros Sep 09 '19 at 01:43

1 Answers1

3

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:

Rajat Goel
  • 2,207
  • 17
  • 35