How to enforce non-overlapping ranges in a table

[Joe Chan]

I'm trying to teach myself web development by learn to use Ruby on Rails and mySQL in a toy project. What I'm trying to do is a web base reservation app for lab machines. I have a number of machines, each can be reserved on hourly basis. So I set up a the following tables: users, machine, reservations. The reservation has a user id and machine id as foreign keys, a date, a start hour and end hour. My question is this: obviously for this work, each reservation row for a machine must not overlap. For example, it would be bad if it allows User A to reserve machine 1 from 12pm to 6pm, and User B to reserve machine 1 from 2pm to 3pm. How would I go about ensuring that reservations don't overlap in multi-user environment with a multi-threaded webserver? Simple checking the table before adding a row won't work, as the check and insert a row together is not atomic. I suspect the problem may be that the implicit constraint on the reservations table that the ranges described by the rows must not overlapped is not explicitly described by appropriate relations in the database schema. Any suggestions on how to solve this problem?

Thanks,