From 444a519f09901302ea2d061b787af25edfaafd90 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Mon, 12 Jun 2023 14:20:53 -0400 Subject: [PATCH] Bug 33987: Combine multiple db updates one in BatchCommitRecords When replacing existing records BatchCommitRecords will the table import_records will be updated three times for three different fields by three different queries. Not only is this inefficient ( especially for large batches ), it seems that this is causing the dreaded "Lock wait timeout exceeded; try restarting transaction" error on some mysql/mariadb configurations. 1) Test plan 2) Download a marc record from Koha 3) Modify the title of that same bib in Koha 4) Stage the downloaded record and overlay the existing record 5) Verify the title has reverted to the original title from the downloaded record! Signed-off-by: Sam Lau Signed-off-by: Marcel de Rooy Signed-off-by: Katrin Fischer Signed-off-by: Tomas Cohen Arazi (cherry picked from commit ab91409f7f3c18142e0694fab80effd7c0b35980) Signed-off-by: Martin Renvoize (cherry picked from commit 818ee1c312a0685719ca3f136f61a94e88195efa) Signed-off-by: Pedro Amorim --- C4/ImportBatch.pm | 7 +++---- 1 file changed, 3 insertions(+), 4 deletions(-) diff --git a/C4/ImportBatch.pm b/C4/ImportBatch.pm index 65c0743ffd..cbcdeb5936 100644 --- a/C4/ImportBatch.pm +++ b/C4/ImportBatch.pm @@ -681,14 +681,13 @@ sub BatchCommitRecords { ModAuthority($recordid, $marc_record, GuessAuthTypeCode($marc_record)); $query = "UPDATE import_auths SET matched_authid = ? WHERE import_record_id = ?"; } - my $sth = $dbh->prepare_cached("UPDATE import_records SET marcxml_old = ? WHERE import_record_id = ?"); - $sth->execute($oldxml, $rowref->{'import_record_id'}); + # Combine xml update, SetImportRecordOverlayStatus, and SetImportRecordStatus updates into a single update for efficiency, especially in a transaction + my $sth = $dbh->prepare_cached("UPDATE import_records SET marcxml_old = ?, status = ?, overlay_status = ? WHERE import_record_id = ?"); + $sth->execute( $oldxml, 'imported', 'match_applied', $rowref->{'import_record_id'} ); $sth->finish(); my $sth2 = $dbh->prepare_cached($query); $sth2->execute($recordid, $rowref->{'import_record_id'}); $sth2->finish(); - SetImportRecordOverlayStatus($rowref->{'import_record_id'}, 'match_applied'); - SetImportRecordStatus($rowref->{'import_record_id'}, 'imported'); } elsif ($record_result eq 'ignore') { $recordid = $record_match; $num_ignored++; -- 2.20.1