Managing logical constraint to handle concurrency in mySQL database insertion

Question

I'm going to create a booking system. In this system there are some person with limited capacity to meet people in defined period of time. E.g. person A can have meeting with at most 8 person on Sunday between 8:00 to 12:00.

In my database I have two tables about this: Timings which show predefined schedules and Bookings which shows bookings which fixed. These tables are defined as below:

CREATE TABLE IF NOT EXISTS `Timings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `day` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `start_time` int(11) NOT NULL,
  `end_time` int(11) NOT NULL,
  `capacity` int(11) NOT NULL,
  `personFK` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_person_timing` (`personFK`),
  KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci  AUTO_INCREMENT=12 ;



CREATE TABLE IF NOT EXISTS `Bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `order` int(11) NOT NULL,
  `booking_code` varchar(100) COLLATE utf8_persian_ci NOT NULL,
  `timingFK` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_booking_timing` (`timingFK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=1 ;

So when I'm going to book a new meeting, I should check if number of rows in Bookings table related to a specified timing is less than its capacity to allow new booking.

This is a necessary condition but it is not sufficient when concurrency happens. I mean if I check count of (timings where timingFK=XXX)< timing.capacity, for the last available booking, it may happen two people be allowed to book at same time because when they where checking the condition the last booking was still available. I want to know how can I implement semaphore-like thing to avoid two people book the last left position during inserting of new booking?

score 0 · Accepted Answer · answered Jul 21 '16 at 07:05

For maximum safety use a transaction + select for update.

For index records the search encounters, SELECT ... FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT ... LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

So you might have

START TRANSACTION;
SELECT COUNT(*) FROM Bookings WHERE somecondition FOR UPDATE
INSERT INTO timings ...
UPDATE Bookings ....
COMMIT;

Managing logical constraint to handle concurrency in mySQL database insertion

1 Answers1