MySQL Transaction: SELECT + INSERT

Question

Im building web application - reservation system using php and mysql. System will allow users to make reservations of time intervals on some devices (user time working on that device).

I call these reservated time intervals slots. Slots are stored in mysql database table like this:

CREATE TABLE IF NOT EXISTS `slot` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`start` int(11) unsigned DEFAULT NULL,
`end` int(11) unsigned DEFAULT NULL,
`uid` int(11) unsigned DEFAULT NULL,
`group` int(11) unsigned DEFAULT NULL,
`message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`devices_id` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `start_2` (`start`),
UNIQUE KEY `end_2` (`end`),
KEY `index_foreignkey_slot_devices` (`devices_id`),
KEY `start` (`start`),
KEY `end` (`end`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=6997 ;  

(this table is created automaticaly by redbean orm and I did not optimized it yet)

So when user creates a reservation, a new row is inserted into this table. In columns start, end I keep unix timestamps of start and end of each reservation.

Another thing to keep in mind is that application allows different users see different timetables of same device. For example: user A has 6 minutes long intervals so she may see free slot (12:00 - 12:06) and also free slot (12:06 - 12:12), but user B has 4 minules long intervals, so he also among others sees slot (12:04 - 12:08). Every user or group of users can have different interval durations. So I must be sure that when both users A and B send request with those slots then only one of them succeeds. Which brings me to transactions and also my question.

I do it like this: - start transaction - select all slots of that day - run algorithm that checks for time collisions between selected reserved slots and requested slots - if there is no collision insert new row(s) in slot table, otherwise signal error to user - commit

Now you know what may happen when it runs concurrently. Im new to transactions and mysql, but a tried to test it and I have reason to believe that just being in transaction is not enough in this case, but Im not sure.

So my question is: how can I select, check for collisions and store reservation in one transaction properly.

thanks

Answer 1

What you need is locking. Transactions are "not strictly needed" indeed.

You can choose between "pessimistic locking" and "optimistic locking". The decision about which one of this two possibilities is up to you and has to be evaluated basically considering:

• the level of concurrency that you have
• the duration of the has-to-be-atomic operations on the database
• the complexity of the whole operation

I will recommend to read this two to build up an idea of the involved things:

• Optimistic vs. Pessimistic locking
• Optimistic locking in MySQL (here some examples that shows how transaction are not strictly needed)

An Example to explain better

This maybe is not so elegant but is only an example that shows how it is possible to do all without transaction (and even without the UNIQUE constraints). What is needed to do is to use the following combined INSERT + SELECT statemet and after its execution to check the number of affected rows. If the number of affected rows is 1 then it has succeded otherways (if it is 0) there have been a collision and the other party have won.

INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT @startTime, @endTime, @uid, @group, @message, @deviceId
FROM `slot`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= @endTime AND `end` >= @startTime
    AND `devices_id` = @deviceId)
GROUP BY (1);

This is an example of Optimistic Locking obtained without transactions and with a single SQL operation.

As it is written it has the problem that there needs to be at least one row already in the slot table in order it to work (otherways the SELECT clause will always return an empty recordset and in that case nothing is inserted evei if there are no collisions. THere are two possibilities to make it actually working:

• insert one dummy row in the table maybe with the date in the past

• rewrite so the main FROM clause refers to any table that has at least one row or better create one little table (maybe named dummy) with only one column and only one record in it and rewrite as following (note that there is no longer need for the GROUP BY clause)

  INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
  SELECT @startTime, @endTime, @uid, @group, @message, @deviceId
  FROM `dummy`
  WHERE NOT EXISTS (
      SELECT `id` FROM `slot`
      WHERE `start` <= @endTime AND `end` >= @startTime
      AND `devices_id` = @deviceId);
  

Here following a series of instruction that if you simply copy/paste shows the idea in action. I have assumed that you encode date/times on int fields as a number with the digits of date and time concatenated.

INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
VALUES (1008141200, 1008141210, 11, 2, 'Dummy Record', 14)

INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT 1408141206, 1408141210, 11, 2, 'Hello', 14
FROM `slot`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= 1408141210 AND `end` >= 1408141206
    AND `devices_id` = 14)
GROUP BY (1);

INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT 1408141208, 1408141214, 11, 2, 'Hello', 14
FROM `slot`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= 1408141214 AND `end` >= 1408141208
    AND `devices_id` = 14)
GROUP BY (1);

INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT 1408141216, 1408141220, 11, 2, 'Hello', 14
FROM `slot`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= 1408141220 AND `end` >= 1408141216
    AND `devices_id` = 14)
GROUP BY (1);

SELECT * FROM `slot`;

This is clearly an extreme example of Optimistic Locking but is very efficient in the end because all is done with only one SQL instruction and with low interaction (data exchange) between the database server and php code. Further there is practically no "real" locking.

...or with Pessimistic Locking

The same code can become a good Pessimistc Locking implementation just surrounding with explicit table lock/unlock instructions:

LOCK TABLE slot WRITE, dummy READ;

INSERT INTO `slot` (`start`, `end`, `uid`, `group`, `message`, `devices_id`)
SELECT @startTime, @endTime, @uid, @group, @message, @deviceId
FROM `dummy`
WHERE NOT EXISTS (
    SELECT `id` FROM `slot`
    WHERE `start` <= @endTime AND `end` >= @startTime
    AND `devices_id` = @deviceId);

UNLOCK TABLES;

Of course in this case (Pessimistic Locking) the SELECT and INSERT could be separated and some php code executed in-between. However this code remains very quick to execute (no data exchange with php, no intermediate php code) and so the duration of the Pessimistic Lock is the shortest possible. Keeping Pessimistic Lock as short as possible is a key point in order to avoid slowing down of the application.

Anyway you need to check the number of affected records return value in order to know if it succeeded since the code is practically the same and so you get the success/failure information in the same way.

Here http://dev.mysql.com/doc/refman/5.0/en/insert-select.html they say that "MySQL does not permit concurrent inserts for INSERT ... SELECT statements" so it should not be needed the Pessimistic Lock but anyway this can be a good option if you think that this will be changing in future versions of MySQL.

I am "Optimistic" that this will not change ;-)