title: MySQL trigger + SELECT FOR UPDATE not locking
body: <p>I’ve got a table of bookings with start and end times, and no two bookings can overlap.</p>
  
  <p>I need to check that a new booking won’t overlap with any existing bookings.  However we’ve got very high load so there’s a race condition: two overlapping bookings can be both successfully inserted because the first booking was inserted after the second booking checked for overlaps.</p>
  
  <p>I’m trying to solve this by taking a lock on a related resource using a BEFORE INSERT database trigger.</p>
  
  <pre><code>DELIMITER //
  
  CREATE TRIGGER booking_resource_double_booking_guard BEFORE INSERT ON booking_resource
  FOR EACH ROW BEGIN
        DECLARE overlapping_booking_resource_id INT DEFAULT NULL;
        DECLARE msg VARCHAR(255);
  
        -- Take an exclusive lock on the resource in question for the duration of the current
        -- transaction. This will prevent double bookings.
        DECLARE ignored INT DEFAULT NULL;
        SELECT resource_id INTO ignored
          FROM resource
         WHERE resource_id = NEW.resource_id
           FOR UPDATE;
  
        -- Now we have the lock, check for optimistic locking conflicts:
        SELECT booking_resource_id INTO overlapping_booking_resource_id
          FROM booking_resource other
          WHERE other.booking_from &lt; NEW.booking_to
          AND other.booking_to &gt; NEW.booking_from
          AND other.resource_id = NEW.resource_id
          LIMIT 1;
  
        IF overlapping_booking_resource_id IS NOT NULL THEN
          SET msg = CONCAT('The inserted times overlap with booking_resource_id: ', overlapping_booking_resource_id);
         SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
       END IF;
    END
  //
  </code></pre>
  
  <p>If I put this trigger in the database and insert two bookings asynchronously from the command line, this trigger successfully blocks the overlapping booking.  I’ve tried this with putting a SLEEP before the last IF statement in the trigger, to make sure that the lock has really been taken out.</p>
  
  <p>However, I have a load testing environment in Jenkins which runs a lot of bookings concurrently using jMeter.   When I put this trigger there and run the load tests, no overlapping bookings are caught, i.e. double bookings are made.</p>
  
  <p>Some checks I’ve done:</p>
  
  <ul>
  <li>I’ve logged out the SQL queries that the load test script generates when creating a booking, and it is the same as the SQL I use in the command line.</li>
  <li>The trigger is definitely being triggered in the load test environment, and it is definitely not catching any overlapping bookings.  I ascertained this by inserting the “overlapping_booking_resource_id” variable from the trigger into another table.  All the values were null.</li>
  <li>The trigger works in the load test environment when inserting bookings from the command line, i.e. it prevents the overlapping booking from being inserted.</li>
  <li>If I make the constraint for what a “double booking” is slightly too strict, i.e. adjacent bookings count as double bookings, then I do see things being caught by the trigger – that is, the apache log records several errors with the message ‘The inserted times overlap with booking_resource_id:’</li>
  </ul>
  
  <p>I’m wondering if maybe the lock is only taken out until the end of the trigger, and there is still a race condition between the end of the trigger and actually inserting into the table.  However this doesn’t explain why none of the overlapping bookings are ever caught.</p>
  
  <p>I’m really stuck now.  Does anyone have any ideas as to what I have done wrong?</p>