0

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`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

1
  • 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?
    – bbaird
    Sep 14 '20 at 19:10
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.

6
  • 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 '20 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 '20 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 '20 at 13:21
  • What's the business key for an Appointment? Sep 14 '20 at 18:43
  • I think it will be only (assigneeId, eventId)
    – j10
    Sep 16 '20 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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