mysql - 管理逻辑约束以处理 mySQL 数据库插入中的并发性

Question

我要创建一个预订系统。在这个系统中，有些人在规定的时间段内会见人的能力有限。例如。星期天 8:00 到 12:00 之间，A 最多可以与 8 人会面。

在我的数据库中，我有两个关于此的表:Timings显示预定义的时间表和 Bookings显示固定的预订。这些表定义如下:

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 ;

所以当我要预定新 session 时，我应该检查 Bookings 中的行数与指定时间相关的表小于其允许新预订的容量。

这是必要条件，但在并发发生时还不够。我的意思是如果我检查 count of (timings where timingFK=XXX)< timing.capacity ，对于最后一个可用的预订，可能会允许两个人同时预订，因为当他们检查条件时，最后一个预订仍然可用。我想知道如何实现类似信号量的功能来避免两个人在插入新预订时预订最后一个左边的位置？

Answer 1

为了最大程度的安全，请使用事务 + 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.)

所以你可能有

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