How to ensure no duplicate records are added when using multiple Node JS processes

Question

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

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.