MySQL trigger + SELECT FOR UPDATE not locking

A less elegant but more robust method would be to use a table made for locking records accross the system.

DELIMITER //CREATE TRIGGER booking_resource_double_booking_guard BEFORE INSERT ON booking_resourceFOR 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.    ---CHANGED HERE    REPEAT      BEGIN        DECLARE CONTINUE HANDLER FOR SQLWARNING          BEGIN            SET locked = FALSE;          END;        locked=TRUE;        INSERT INTO lockresource values(NEW.resource_id);      END;    UNTIL LOCKED END REPEAT;    ---TIL HERE      -- 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 < NEW.booking_to        AND other.booking_to > 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//---ADDED FROM HERE DELIMITER //CREATE TRIGGER booking_resource_double_booking_guard_after AFTER INSERT ON booking_resourceFOR EACH ROW BEGIN        DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;        delete from lockresource where lockid=NEW.resource_id;  END//

Anyway, that'd be the idea and would certainly prevent any loss of lock until completion of your validation.