From be156d9ad9e5bcfadab34d44f90e04fd61e256ad Mon Sep 17 00:00:00 2001
From: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Date: Thu, 9 Feb 2017 12:44:38 +0100
Subject: [PATCH] Bug 15854: Use a READ and WRITE LOCK on message_queue
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

To make sure we will not never get a race conditions for these kinds of
notices, we need to add a LOCK on the message_queue table.

This does not smell the best way to do that, but I faced deadlock issues
when I tried to use "UPDATE FOR"

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
https://dev.mysql.com/doc/refman/5.7/en/commit.html

To test this patch, or another solution, you need to apply manually this
change:

         my $message = C4::Message->find_last_message($borrower, $type, $mtt);
         unless ( $message ) {
+            sleep(1);
             C4::Message->enqueue($letter, $borrower, $mtt);
         } else {

And repeat the test plan from first patch.
Do not forget to truncate the message_queue table.

Followed test plans, works as expected.
Signed-off-by: Marc Véron <veron@veron.ch>

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Brendan A Gallagher <brendan@bywatersolutions.com>
---
 C4/Circulation.pm | 7 +++++++
 1 file changed, 7 insertions(+)

diff --git a/C4/Circulation.pm b/C4/Circulation.pm
index 7f442c38c3..efdf817364 100644
--- a/C4/Circulation.pm
+++ b/C4/Circulation.pm
@@ -3341,6 +3341,7 @@ sub SendCirculationAlert {
     });
     my $issues_table = ( $type eq 'CHECKOUT' || $type eq 'RENEWAL' ) ? 'issues' : 'old_issues';
 
+    my $schema = Koha::Database->new->schema;
     my @transports = keys %{ $borrower_preferences->{transports} };
     for my $mtt (@transports) {
         my $letter =  C4::Letters::GetPreparedLetter (
@@ -3358,13 +3359,19 @@ sub SendCirculationAlert {
             }
         ) or next;
 
+        $schema->storage->txn_begin;
+        C4::Context->dbh->do(q|LOCK TABLE message_queue READ|);
+        C4::Context->dbh->do(q|LOCK TABLE message_queue WRITE|);
         my $message = C4::Message->find_last_message($borrower, $type, $mtt);
         unless ( $message ) {
+            C4::Context->dbh->do(q|UNLOCK TABLES|);
             C4::Message->enqueue($letter, $borrower, $mtt);
         } else {
             $message->append($letter);
             $message->update;
         }
+        C4::Context->dbh->do(q|UNLOCK TABLES|);
+        $schema->storage->txn_commit;
     }
 
     return;
-- 
2.39.5