https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43989 Sam Young <sam.young@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43989 Sam Young <sam.young@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #210372|0 |1 is obsolete| | --- Comment #2 from Sam Young <sam.young@bywatersolutions.com> --- Created attachment 210373 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=210373&action=edit Bug 43989: Prevent concurrent holds queue updates from dropping holds -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43989 --- Comment #3 from Sam Young <sam.young@bywatersolutions.com> --- Bug 43989: Prevent concurrent holds queue updates from dropping holds When two holds queue updates for the same record run at the same time, one can fail with a deadlock and is never retried, so the hold stays off the Holds queue. This locks the record while its queue entries are rebuilt, so updates run one after the other, and retries an update that hits a deadlock. To test: 1. Before applying the patch, save the script below as /tmp/race.pl 2. Run: cd /kohadevbox/koha && perl -I. /tmp/race.pl 3. Observe that Update 2 fails with "Deadlock found when trying to get lock" 4. Apply patch 5. restart_all 6. Run: cd /kohadevbox/koha && perl -I. /tmp/race.pl 7. Observe that both Update 1 and Update 2 are OK 8. prove t/db_dependent/Koha/BackgroundJob/BatchUpdateBiblioHoldsQueue.t 9. Observe that all tests pass The script creates a record with one item and a hold, then runs two holds queue updates for it at the same time: use Modern::Perl; use t::lib::TestBuilder; use C4::HoldsQueue; use C4::Reserves qw(AddReserve); use Koha::Database; my $builder = t::lib::TestBuilder->new; my $item = $builder->build_sample_item; my $patron = $builder->build_object( { class => 'Koha::Patrons', value => { branchcode => $item->homebranch } } ); AddReserve( { branchcode => $item->homebranch, borrowernumber => $patron->id, biblionumber => $item->biblionumber, priority => 1 } ); # Widen the window between the DELETE and the INSERTs no warnings 'redefine'; my $orig = \&C4::HoldsQueue::CreatePicklistFromItemMap; *C4::HoldsQueue::CreatePicklistFromItemMap = sub { sleep 2; $orig->(@_) }; for my $n ( 1 .. 2 ) { next if fork; Koha::Database->schema->storage->_dbh->{InactiveDestroy} = 1; Koha::Database->schema->storage->disconnect; sleep $n; my $storage = Koha::Database->schema->storage; eval { $storage->txn_begin; C4::HoldsQueue::update_queue_for_biblio( { biblio_id => $item->biblionumber, delete => 1 } ); $storage->txn_commit; say "Update $n: OK"; 1; } or do { $storage->txn_rollback; say "Update $n: FAILED - " . ( $@ =~ /(Deadlock found[^;]*)/ ? $1 : $@ ); }; exit; } wait for 1 .. 2; $item->biblio->delete; $patron->delete; -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43989 Sam Young <sam.young@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #210374|0 |1 is obsolete| | --- Comment #5 from Sam Young <sam.young@bywatersolutions.com> --- Created attachment 210375 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=210375&action=edit Bug 43989: Prevent concurrent holds queue updates from dropping holds Bug 43989: Prevent concurrent holds queue updates from dropping holds When two holds queue updates for the same record run at the same time, one can fail with a deadlock or lock wait timeout and is never retried, so the hold stays off the Holds queue. To test: 1. Enable RealTimeHoldsQueue and restart_all 2. Place a hold on a record with one available item, pickup at the item's library 3. Go to Circulation > Holds queue and confirm the hold is listed 4. In a terminal, open koha-mysql kohadev and run: BEGIN; DELETE FROM hold_fill_targets WHERE biblionumber=<the record's biblionumber>; Leave it open, this acts like another holds queue update running for the record 5. In the staff interface, edit the item (e.g. add a public note) and save 6. Wait 1 minute (more than 50 seconds, less than 2 minutes), then run ROLLBACK; in the mysql terminal 7. Go to Administration > Manage jobs and open the latest "Holds queue update" job 8. Observe "Some errors occurred" with "Lock wait timeout exceeded", the update failed and was not retried 9. Apply patch and restart_all 10. Repeat steps 4-7 11. Observe "Holds queue successfully updated", the update waited for the lock, retried and succeeded 12. prove t/db_dependent/Koha/BackgroundJob/BatchUpdateBiblioHoldsQueue.t 13. Observe that all tests pass -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43989 Sam Young <sam.young@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #210375|0 |1 is obsolete| | --- Comment #6 from Sam Young <sam.young@bywatersolutions.com> --- Created attachment 210376 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=210376&action=edit Bug 43989: Prevent concurrent holds queue updates from dropping holds When two holds queue updates for the same record run at the same time, one can fail with a deadlock or lock wait timeout and is never retried, so the hold stays off the Holds queue. To test: 1. Enable RealTimeHoldsQueue and restart_all 2. Place a hold on a record with one available item, pickup at the item's library 3. Go to Circulation > Holds queue and confirm the hold is listed 4. In a terminal, open koha-mysql kohadev and run: BEGIN; DELETE FROM hold_fill_targets WHERE biblionumber=<the record's biblionumber>; Leave it open, this acts like another holds queue update running for the record 5. In the staff interface, edit the item (e.g. add a public note) and save 6. Wait 1 minute after saving (more than 50 seconds, less than 2 minutes), then run ROLLBACK; in the mysql terminal 7. Go to Administration > Manage jobs and open the latest "Holds queue update" job 8. Observe "Some errors occurred" with "Lock wait timeout exceeded", the update failed and was not retried 9. Apply patch and restart_all 10. Repeat steps 4-7 11. Observe "Holds queue successfully updated", the update waited for the lock, retried and succeeded 12. prove t/db_dependent/Koha/BackgroundJob/BatchUpdateBiblioHoldsQueue.t 13. Observe that all tests pass -- You are receiving this mail because: You are watching all bug changes.