When importing a staged file we commit only every 50 records For an authority import we are also merging, which can affect many more biblios, and these all end up in the transaction. This can cause tables locks and issues across Koha Why do we not commit each record as we go?
(In reply to Nick Clemens from comment #0) > When importing a staged file we commit only every 50 records > > For an authority import we are also merging, which can affect many more > biblios, and these all end up in the transaction. > > This can cause tables locks and issues across Koha > > Why do we not commit each record as we go? Historically, it was probably for speed reasons, but since we're using background jobs I think committing each record as we go should be fine in terms of performance. Back in the day, we didn't use transactions either, so they wouldn't have been an issue back then. I work on another system which is quite busy, and I've had to commit more frequently to avoid deadlocks and other transaction related issues. It is much slower but it's much more reliable.
Created attachment 159413 [details] [review] Bug 35438: Transact each record import separately When importing a staged file we commit every 50 records For an authority import we are also merging, which can affect many more biblios, and these all end up in the transaction. This can cause tables locks and issues across Koha Test Plan: 1) Apply this patch 2) prove t/db_dependent/ImportBatch.t
Created attachment 159415 [details] [review] Bug 35438: Transact each record import separately When importing a staged file we commit every 50 records For an authority import we are also merging, which can affect many more biblios, and these all end up in the transaction. This can cause tables locks and issues across Koha Test Plan: 1) Apply this patch 2) prove t/db_dependent/ImportBatch.t Signed-off-by: David Nind <david@davidnind.com>
The idea of surrounding the whole job with a transaction was to be able to "retry" it in case it fails. If we process only a subset it won't be possible (unless parsing the "report").
(In reply to Jonathan Druart from comment #4) > The idea of surrounding the whole job with a transaction was to be able to > "retry" it in case it fails. If we process only a subset it won't be > possible (unless parsing the "report"). I think it was a great theory and and bad practice. Especially since we are transacting 50 at a time and not entire batches ( perish the thought! ).
Created attachment 159442 [details] [review] Bug 35438: Transact each record import separately When importing a staged file we commit every 50 records For an authority import we are also merging, which can affect many more biblios, and these all end up in the transaction. This can cause tables locks and issues across Koha Test Plan: 1) Apply this patch 2) prove t/db_dependent/ImportBatch.t Signed-off-by: David Nind <david@davidnind.com>
Created attachment 159443 [details] [review] Bug 35438: Remove skip_intermediate_commit parameter It no longer does anything.
Looking here
Created attachment 160917 [details] [review] Bug 35438: Transact each record import separately When importing a staged file we commit every 50 records For an authority import we are also merging, which can affect many more biblios, and these all end up in the transaction. This can cause tables locks and issues across Koha Test Plan: 1) Apply this patch 2) prove t/db_dependent/ImportBatch.t Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Created attachment 160918 [details] [review] Bug 35438: Remove skip_intermediate_commit parameter It no longer does anything. Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Pushed for 24.05! Well done everyone, thank you!
Pushed to 23.11.x for 23.11.02
Backported to 23.05.x for upcoming 23.05.09