title: MySQL Transaction: SELECT + INSERT
body: <p>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).</p>
  
  <p>I call these reservated time intervals slots. Slots are stored in mysql database table like this:</p>
  
  <pre><code>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 ;  
  </code></pre>
  
  <p>(this table is created automaticaly by redbean orm and I did not optimized it yet)</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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</p>
  
  <p>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.</p>
  
  <p>So my question is: how can I select, check for collisions and store reservation in one transaction properly.</p>
  
  <p>thanks</p>