How to add slots for Slot table such that there are no clash I have a Slot Table which has following fields :

   CREATE TABLE `Slot` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `starttime` datetime NOT NULL,
      `endtime` datetime NOT NULL,
      `assigned_To_Id` int(10) unsigned DEFAULT NULL,

      PRIMARY KEY (`id`),
      KEY `appointment_assigned_to_id_foreign` (`assigned_To_Id`),
      CONSTRAINT `appointment_assigned_to_id_foreign` FOREIGN KEY (`assigned_To_Id`) REFERENCES `Employee` (`id`),
    )
 ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1


Now, the admins can add slot for a employee(assigned_To_Id) for a particular day by specifying the slots :
e.g. :

7 July Slots : 
11:45-12:00
12:00-13:00
and slot time = 15 mins (above time range will be divided in slot time intervals of 15 mins and assigned_To_Id = 1


Now before adding the slots we need to check if there are any clashes for the slots with existing data in db which are assigned to 1.

select count(*) from Slots
where 
(
    (starttime &gt;= '2018-07-07 11:45:00' and  ('2018-07-07 11:59:59' between starttime and endtime))
     or
    (starttime Between '2018-07-07 11:45:00' and  '2018-07-07 11:59:59' and endtime &lt;= '2018-07-07 11:59:59')
    or
    ('2018-07-07 11:45:00' Between starttime and endtime  and '2018-07-07 11:59:59' Between starttime and endtime)
)


or 
(
    (starttime &gt;= '2018-07-07 12:00:00' and  ('2018-07-07 12:59:59' between starttime and endtime))
     or
    (starttime Between '2018-07-07 12:00:00' and  '2018-07-07 12:59:59' and endtime &lt;= '2018-07-07 12:59:59')
    or
    ('2018-07-07 12:00:00' Between starttime and endtime  and '2018-07-07 12:59:59' Between starttime and endtime)
)
 and assigned_To_Id = 1
 so if there are 'n' slots --&gt; check for each slot


Like for every time slot give by admin we have 3 conditions which we can use to check if there is any time clash or any existing slot

if the query returns a count of 0, that means we are good to add slots 
like divide 11:45-12:00 into slots of 15 mins and 12:00-13:00 also in slots of 15 mins.

My question is : How can I run this insert along with the count(*) query such that if two admins try to do similar thing only one of them succeeds.

Can we combine insert statement with condition ?
Is stored procedure a good idea for this one ?
BEGIN;
SELECT ... FOR UPDATE;
check for no overlaps (or whatever)
INSERT (or update) new entry
COMMIT;


In InnoDB, locking the whole table is equivalent to locking every row.  Do I need to explain that that is a lot more costly than locking a single row?

Your SELECT ... FOR UPDATE should pessimistically lock whatever rows you don't want mucked with until the end of the transaction.  Often it is a single row (lock one row ... update that row).  Or, it could involve locking several rows before deciding which row(s), if any, will actually be updated.

You should minimize the number of rows locked -- for speed of the SELECT, for speed of the ROLLBACK/COMMIT, and for minimizing interference with other queries.

Unless you have hundreds of UPDATEs happening per second, locking too many rows is not a big deal -- after all, your transaction should be designed to finished in only a few milliseconds, not the default `innodb_lock_wait_timeout=50 (seconds).