0

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 >= '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 <= '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 >= '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 <= '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 --> 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 ?
3
  • if two admins try to do similar thing only one of them succeeds Lock (R/W) table. Then do your select/insert operations. Then unlock table. No another admin can lock the table and perform any operations on that table until you unlock it.
    – Akina
    Jul 6, 2018 at 12:01
  • Can we do record level lock while using Insert and select query in one transaction
    – j10
    Jul 6, 2018 at 12:16
  • To do this, you can define your data model such that it is impossible to violate your rules. The best way to do this is to use constraints on your table. stackoverflow.com/questions/7522026/… Hopefully this article answers your question. Jul 6, 2018 at 13:48

1 Answer 1

2
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).

4
  • Thank you Rick. Rick, so we write Select * from Slots where assigned_To_Id = 1 FOR UPDATE -> so this will take a record level lock preventing any new inserts where assigned_To_Id = 1?
    – j10
    Jul 7, 2018 at 5:22
  • We are using Nodejs with Bookshelf ORM --> is it a good idea to run at App layer or DB level ? in case if App crashes the lock may never be released right ?
    – j10
    Jul 7, 2018 at 5:32
  • To prevent interfere we need to select all records - if no, records not selected are available for parallel selection... but if so, it's less expensive to lock whole table instead of lock all its records. Is it?
    – Akina
    Jul 7, 2018 at 17:12
  • 1
    @Akina - I elaborated.
    – Rick James
    Jul 7, 2018 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.