We are working on an appointment scheduler. We have a table Appointment that stores an appointment with starttime, endtime, assigneeId and eventId.

We are using Bookshelf.js as an ORM with Node JS. The problem we are facing is if we send parallel thread requests using JMeter for the same combination of starttime, endtime, assigneeId, eventId and our app ends up booking an appointment for both the threads which is incorrect.

Only one of them should succeed and other one should fail. How do we go about solving this problem?

CREATE TABLE `Appointment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned NOT NULL,
  `account_Id` int(10) unsigned NOT NULL,
  `event_Id` int(10) unsigned NOT NULL,
  `assignee_Id` int(10) unsigned NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `book` tinyint(4) DEFAULT '0',
  `code` varchar(20) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `deleted_By_Id` int(10) unsigned DEFAULT NULL,
  `updated_Appointment_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_Appointment_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `appointments_code_unique` (`code`)

What we do is before inserting we see if any overlapping appointments exist in the DB. Iif not, then we add it but with multiple Node JS processes: both of the processes do not find any appointment, and both end up inserting.

First Solution : INSERT IGNORE but it's difficult in our case to define a Unique key based on starttime and endtime as we need to check for overlapping values as well.

Second Solution: Let both of them insert the record in the Appointment table and after the transaction is committed we check for overlap - if we find an overlap we delete the record which was added later. This will result in a waste of computation as well as ids.

Third Solution: Use an App Level Queue to manage all requests to the Appointment table and only 1 process will resolve all Queue messages. I wonder how this approach will scale.

How do I ensure at the time of insert that only one thread can book an appointment safely? Will a stored procedure be helpful? We are using transactions provided by Bookshelf.js

  • I'm not familiar enough with Bookshelf, can you 1. use actual keys instead of row identifiers (id in your example) 2. Use stored procedures in place of insert statements?
    – user212533
    Sep 14, 2020 at 19:10

1 Answer 1


It sounds like the business key for an Appointment is (assigneeId, eventId, starttime, endtime). Create a unique constraint on the business key (assigneeId, eventId, starttime, endtime) would fix the issue. You need to catch unique constraint error returned by the DB on your app.

  • Thank you. But it won't help. starttime and endtime could overlap with new incoming appointments so they can not be used to create unique key.
    – j10
    Sep 14, 2020 at 4:46
  • 1
    @j10 Do you mean overlaps in appointment times are not allowed for a given assigneeId and eventId? To check for overlaps, you can consider a CHECK constraint -- depending on the DBMS, add a CHECK constraint for matching assigneeId and eventId such that the new starttime and new endtime is not within the existing appointment time. Sep 14, 2020 at 7:16
  • Yes that would work. we also have a Group Type Appointment where we can multiple entries for same time slot. I think CHECK won't help in that case.
    – j10
    Sep 14, 2020 at 13:21
  • What's the business key for an Appointment? Sep 14, 2020 at 18:43
  • I think it will be only (assigneeId, eventId)
    – j10
    Sep 16, 2020 at 9:30

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.