[Bug 33987] New: Combine multiple db updates in C4::ImportBatch::BatchCommitRecords for efficiency/avoiding possible deadlocks
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Bug ID: 33987 Summary: Combine multiple db updates in C4::ImportBatch::BatchCommitRecords for efficiency/avoiding possible deadlocks Change sponsored?: --- Product: Koha Version: master Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Tools Assignee: koha-bugs@lists.koha-community.org Reporter: kyle@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |kyle@bywatersolutions.com |ity.org | See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=33972 Severity|enhancement |normal -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 --- Comment #1 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 152282 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152282&action=edit 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! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Sam Lau <samalau@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Sam Lau <samalau@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152282|0 |1 is obsolete| | --- Comment #2 from Sam Lau <samalau@gmail.com> --- Created attachment 152297 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152297&action=edit 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 <samalau@gmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 --- Comment #3 from David Cook <dcook@prosentient.com.au> --- Comment on attachment 152297 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152297 Bug 33987 - Combine multiple db updates one in BatchCommitRecords Review of attachment 152297: --> (https://bugs.koha-community.org/bugzilla3/page.cgi?id=splinter.html&bug=33987&attachment=152297) ----------------------------------------------------------------- ::: C4/ImportBatch.pm @@ +682,5 @@
$query = "UPDATE import_auths SET matched_authid = ? WHERE 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'});
Is there a typo on this line? I see two commas in a row? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152297|0 |1 is obsolete| | --- Comment #4 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- Created attachment 152399 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152399&action=edit 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 <samalau@gmail.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Marcel de Rooy <m.de.rooy@rijksmuseum.nl> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |m.de.rooy@rijksmuseum.nl QA Contact|testopia@bugs.koha-communit |m.de.rooy@rijksmuseum.nl |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 --- Comment #5 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to David Cook from comment #3)
Comment on attachment 152297 [details] [review] Bug 33987 - Combine multiple db updates one in BatchCommitRecords
Review of attachment 152297 [details] [review]: -----------------------------------------------------------------
::: C4/ImportBatch.pm @@ +682,5 @@
$query = "UPDATE import_auths SET matched_authid = ? WHERE 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'});
Is there a typo on this line? I see two commas in a row?
I removed the second comma. It didnt matter but it should not be there -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 --- Comment #6 from Marcel de Rooy <m.de.rooy@rijksmuseum.nl> --- (In reply to Kyle M Hall from comment #0)
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.
Patch is fine. Would be nice if this would prevent a deadlock. But I dont think that this is the ultimate solution. We need to dig a bit deeper still probably. Continuing on 33972 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152399|0 |1 is obsolete| | --- Comment #7 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Created attachment 152403 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152403&action=edit 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 <samalau@gmail.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #152403|0 |1 is obsolete| | --- Comment #8 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Created attachment 152406 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=152406&action=edit 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 <samalau@gmail.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl> Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 --- Comment #9 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Fixed the commit message ;) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to master Version(s)| |23.11.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 --- Comment #10 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 23.11. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Martin Renvoize <martin.renvoize@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |Pushed to stable Version(s)|23.11.00 |23.11.00,23.05.02 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 --- Comment #11 from Martin Renvoize <martin.renvoize@ptfs-europe.com> --- Thanks for all the hard work! Pushed to 23.05.x for the next release -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 Pedro Amorim <pedro.amorim@ptfs-europe.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|23.11.00,23.05.02 |23.11.00,23.05.02,22.11.08 released in| | Status|Pushed to stable |Pushed to oldstable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33987 --- Comment #12 from Pedro Amorim <pedro.amorim@ptfs-europe.com> --- Nice work everyone! Pushed to 22.11.x for next release -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org