Attachment 152399 Details for Bug 33987 – Bug 33987 - Combine multiple db updates one in BatchCommitRecords

[patch] Bug 33987 - Combine multiple db updates one in BatchCommitRecords

Bug-33987---Combine-multiple-db-updates-one-in-Bat.patch (text/plain), 2.48 KB, created by Marcel de Rooy on 2023-06-16 07:10:44 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Marcel de Rooy

Created: 2023-06-16 07:10:44 UTC

Size: 2.48 KB

patch

obsolete

>From 00f1f1d7732fd68ad52f0ee3bd47cca6cf01c8a6 Mon Sep 17 00:00:00 2001
>From: Kyle M Hall <kyle@bywatersolutions.com>
>Date: Mon, 12 Jun 2023 14:20:53 -0400
>Subject: [PATCH] Bug 33987 - Combine multiple db updates one in
> BatchCommitRecords
>Content-Type: text/plain; charset=utf-8
>
>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 <samalau@gmail.com>
>
>Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
>---
> 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.30.2

Actions: View | Diff | Splinter Review

Attachments on bug 33987: 152282 | 152297 | 152399 | 152403 | 152406