[Koha-bugs] [Bug 26457] DB DeadLock when renewing checkout items

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26457

--- Comment #3 from didier <didier.gautheron at biblibre.com> ---
(In reply to David Cook from comment #1)
> I'm more familiar with PostgreSQL deadlocks than MySQL deadlocks, but it
> looks to me like 2 separate Koha processes are trying to add renewals for
> different items for the same borrower, and perhaps the lock contention is
> over the issuesborridx index.
> 
> Looking at Bug 20086, I see that those are unnecessarily large transactions.
> It's better to keep transactions as small as possible, especially to avoid
> scenarios like this.
> 
> Looking at koha-tmpl/intranet-tmpl/prog/js/checkouts.js, I see that the
> renewals are POSTed to "/cgi-bin/koha/svc/renew" asynchronously, so that's
> how there would be multiple AddRenewal transactionsissuesborridx running for the same
> borrower at the same time...
It's also our analysis, there's redundant indexes on issues which may on may
not compound the problem.
 PRIMARY KEY (`issue_id`),
  UNIQUE KEY `itemnumber` (`itemnumber`),
  KEY `issuesborridx` (`borrowernumber`),
  KEY `itemnumber_idx` (`itemnumber`),
  KEY `branchcode_idx` (`branchcode`),
  KEY `bordate` (`borrowernumber`,`timestamp`),

itemnumber_idx  and issuesborridx are redundant.
> 
> Looking at
> https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-
> locks, I think my theory is correct about the issue being contention over
> the issuesborridx.
> 
> Now, I haven't noticed this / haven't gotten any reports of this, but I
> don't think we really use fines, so this report is interesting. 
> 
> I'm guessing that your renewals must be processed *very* slowly, and that
> your 2nd transaction is rolled back, because your 1st transaction is taking
> too long to release a lock on issuesborridx. 
> 
> I'd say the solution is to redo the transaction handling for
> C4::Circulation::AddRenewal.
I'm not sure block to redo is idempotent... 
> 
> Didier, do you been able to reproduce this issue in koha-testing-docker?
> That would help in fixing the problem.
I'll try, maybe adding a fat sleep(5) in transaction block.

-- 
You are receiving this mail because:
You are watching all bug changes.
You are the assignee for the bug.