Q:

innodb transactions and room reservation

I'm currently reading a lot about transactions in InnoDB, at this time i only ever used myISAM tables so i'm not very used to all this:
Here is my table scheme :
CREATE TABLE IF NOT EXISTS `reservations` (
  `id_reservation` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `id_room` mediumint(8) unsigned NOT NULL,
  `date_from` date NOT NULL,
  `date_to` date NOT NULL,
  `cancelled` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`id_reservation`),
  KEY `id_room` (`id_appartement`)
) ENGINE=InnoDB;

Now let's say i want to INSERT a new reservation for room ID 15, from 2012-02-15 to 2012-02-24, here's what I think i should do based on what i read :

start transaction :
START TRANSACTION;

INSERT the row
INSERT INTO reservations SET 
  id_room = 15, 
  date_from = '2012-02-15', 
  date_to='2012-02-24', 
  cancelled='N';

check if there is a reservation conflicting with the reservation i just made
SELECT * FROM reservations WHERE 
  id_room = 15 
  AND cancelled = 'N' 
  AND date_from < '2012-02-24' AND date_to > '2012-02-15' 
  AND id_reservation <> LAST_INSERT_ID();

if not, COMMIT;
else, ROLLBACK;

Problem is, in default isolation mode (REPEATABLE-READ), once i start the transaction, i won't see any other INSERT made outside this very transaction.
So what would happen if, just after step 1), another user inserts a conflicting reservation ?
Maybe i should use READ-COMMITED in this case? but wouldn't this lead to issues as well ?
Thanks for helping !

A:

What you have is fine, but you need to add one table and one query to your transaction.
The table needs one row for each id_room. You probably already have that table. 
So, right after START TRANSACTION do:
SELECT * FROM room WHERE id_room=15 FOR UPDATE;

That will block the next guy trying to put in a reservation for the same room until you either COMMIT or ROLLBACK your transaction, as long as the next guy also uses this FOR UPDATE syntax.
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

