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

Have you tried the `FOR UPDATE` option to `SELECT`? This puts a lock on all the records that you read, and other transactions that use the option won't be able to read them until your transaction completes. — Barmar, Aug 15 '14 at 22:35
Not an answer to your question: a unique key on `start` or `end` (alone) is obviously a bad idea. You may have reservations for different devices starting or ending at the same time. Either remove those constraints, or include the device in the key (though that's not enough of a constraint to avoid overlapping reservations). — jcaron, Aug 15 '14 at 22:38
@Barmar No I did not try it yet, but it looks link an answer. How do you think it will impact performance, cause i read it will lock all that records so other requests will have to wait. Is this only solution to this problem? — Walkerr47, Aug 16 '14 at 07:20
@jcaron yes Im aware that thats not enough. As I said in question, I will optimize table structure later, but thanks I will remember your note. — Walkerr47, Aug 16 '14 at 07:24
I haven't used it myself, but the way I understand it is that it only locks the records that are selected, not all the records in the table. So a different query that selects unrelated records will not have to wait. But a query that selects the same or overlapping records will wait, which is what you want. — Barmar, Aug 16 '14 at 07:35
@Barmar, the problem with using `FOR UPDATE` here is that you need to have something to select and update, while here it's a matter of inserting new data. Unless OP pre-inserts a line for each possible slot "fragment" (with an availability indicator), it won't work. Either need to use another table for locking purposes (e.g. a table where there's a row for each day, and only one day can be updated at a time), or use some form of global lock, or use some form of daemon that will ensure requests can only be processed serially. — jcaron, Aug 16 '14 at 09:18
@jcaron is probably right. When the question said "user A may see free slot (12:00 - 12:06)" I thought there was actually something in the table with those times. But if what they're seeing is the _absence_ of a row, then my suggestion won't work. — Barmar, Aug 16 '14 at 09:22
@Barmar yes, what I meant was that user sees this slot virtually in browser, but it does not exists in db yet. — Walkerr47, Aug 16 '14 at 13:19
Then he's right. You can't put a lock on "virtual rows", only on actual rows. — Barmar, Aug 16 '14 at 13:22
@jcaron I think I understand it now. Sadly. So now I have to do something like this: 1. Create a table lock with attribute day (with unique constraint). Before making reservations I will do select of reservation day from table lock with `FOR UPDATE`. That will lock this row till transacion commits/rollbacks and then just make reservation as before. Is that corrent or am I missing something? — Walkerr47, Aug 16 '14 at 13:24
That should work, though I'm more familiar with postgresql than mysql so I wouldn't be able to tell you if there are any hidden dangers lurking about :-) — jcaron, Aug 16 '14 at 16:13

score 6 · Accepted Answer · edited May 23 '17 at 12:30

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 ;-)

Wow, this is very nice. I guess this would work with autocommit enabled. But what if I used transactions. Two threads would concurently run these inserts and commited. Wouldnt they both still select the same old state and insert slots possibly in collision? Or I dont got it? :) I also read about isolation levels, but even with read commited Im not sure it would work safely. thanks — Walkerr47, Aug 23 '14 at 22:13
"Concurrently" doesn't really exists. I mean we say "concurrently" when the block of code isn't atomic and execution of more instances can be interleaved. 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 is atomic at the database engine level. Of course it needs that you check in your code if it has succeeded or failed checking the number of affected records return value because that is how Optimistic Lock works. — Diego Mazzaro, Aug 24 '14 at 09:09

MySQL Transaction: SELECT + INSERT

1 Answers1

An Example to explain better

...or with Pessimistic Locking

Linked