I’m a newbie for RoR, and also web dev and database design (but been
doing C++ apps for years). I’m trying to learn to use RoR and mySQL by
doing 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? Simple checking the table before adding a row won’t work. I
need something like a mutex on the entire table. Any suggestions?
Thanks,
I wonder if runt[1] might help you… you can use it to check dates and
times for overlap
before saving…
b
[1] http://runt.rubyforge.org/
why wouldn’t simple check work?
Emin H. wrote:
why wouldn’t simple check work?
If it’s a multi-user system running on a multi-threaded web server,
can’t someone commit an entry between the check of the current table
state, and the commit of a new row?
Joe C. wrote:
My actual question is not about checking whether there is an overlap,
but how to make (1) the check on the reservation table, and (2) the
commit of a new row, one atomic operation. Assuming that I run off a
multi-threaded web server, each user won’t even be using the same
instance of ruby, so a mutex will not suffice. I suppose I can always go
back to using go old fashion lock file. However, I imagine this must be
a very common problem that has a well known solution, and it’s just that
I don’t know much about ruby/rails/web development at all so it’s not
obvious to me what should be done.
Rails has optimistic locking built in… see page 222 in AWDWR, or here:
http://api.rubyonrails.com/classes/ActiveRecord/Locking.html
Ben M. wrote:
I wonder if runt[1] might help you… you can use it to check dates and
times for overlap
before saving…
b
[1] http://runt.rubyforge.org/
My actual question is not about checking whether there is an overlap,
but how to make (1) the check on the reservation table, and (2) the
commit of a new row, one atomic operation. Assuming that I run off a
multi-threaded web server, each user won’t even be using the same
instance of ruby, so a mutex will not suffice. I suppose I can always go
back to using go old fashion lock file. However, I imagine this must be
a very common problem that has a well known solution, and it’s just that
I don’t know much about ruby/rails/web development at all so it’s not
obvious to me what should be done.
Ben M. wrote:
Joe C. wrote:
My actual question is not about checking whether there is an overlap,
but how to make (1) the check on the reservation table, and (2) the
commit of a new row, one atomic operation. Assuming that I run off a
multi-threaded web server, each user won’t even be using the same
instance of ruby, so a mutex will not suffice. I suppose I can always go
back to using go old fashion lock file. However, I imagine this must be
a very common problem that has a well known solution, and it’s just that
I don’t know much about ruby/rails/web development at all so it’s not
obvious to me what should be done.
Rails has optimistic locking built in… see page 222 in AWDWR, or here:
Peak Obsession
OK, I read the doc, and I think I understand with it’s suppose to do.
This optimistic locking seems to be similar to locking down a row, so
that two clients can’t update the row at the same time without one
noticing that data is stale. However, I’m still unsure how to use this
in my situation. The complication (or perhaps a defect in the schema) is
that there is an implicit contraints between rows in the reservation
table, i.e., durations marked by two rows must not overlap.
Joe C. wrote:
a very common problem that has a well known solution, and it’s just that
This optimistic locking seems to be similar to locking down a row, so
that two clients can’t update the row at the same time without one
noticing that data is stale. However, I’m still unsure how to use this
in my situation. The complication (or perhaps a defect in the schema) is
that there is an implicit contraints between rows in the reservation
table, i.e., durations marked by two rows must not overlap.
Yeah, duh… I was thinking “locking” but not much past that.
It sure sounds like you just need a validation on insert (and update too
actually… if
they’re allowed to change their reservation times).
I think you should just query the db for the current slots and render
that to the user.
They select what they want and submit it… you validate before saving
and if the slot
ain’t ok anymore you send them back an error which just says “whoops…
that slot has
been reserved by someone else; please choose another”.
Any normal person would totally expect this kind of message for high
contention
reservations (think about buying concert tickets online for a hot show).
I think anything
more than that is overkill.
If you’re doing other work than just writing the reservation row, you
can put it all in a
transaction and test the time range right before commiting.
b
I’m not sure that will solve his issue, since a reservation is an
insert, not an update. Hopefully someone who knows more about rails
can give you a better solution than I.
You should be able to start a transaction, do a find for overlapping
rows, then insert yours. Depending on the transaction isolation mode
(I can’t tell you about MySQL’s options for this or the defaults) the
find will guarantee that no one else can do an insert that would dirty
your read until you commit your transaction. The find also guarantees
that if there is a pending insert on rows your find should select that
you’ll wait for them (otherwise your read would be dirtied).
On 3/25/06, Ben M. [email protected] wrote:
Rails has optimistic locking built in… see page 222 in AWDWR, or here:
Peak Obsession
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
–
Jeremy H.
Joe C. wrote:
Simple checking the table before adding a row won’t work. I
need something like a mutex on the entire table. Any suggestions?
From:
http://api.rubyonrails.com/classes/ActiveRecord/Transactions/ClassMethods.html
How about:
Reservation.transaction do
…check for clash
…write new record
end
Does this help ?
Alan
Alan F. wrote:
Joe C. wrote:
Simple checking the table before adding a row won’t work. I
need something like a mutex on the entire table. Any suggestions?
From:
Peak Obsession
How about:
Reservation.transaction do
…check for clash
…write new record
end
Does this help ?
Alan
I thought about something like, but I wasn’t sure about the semantics of
a transaction applies here. Do you think think after writing a new
record, I need to check again to make sure there are no new record
inserted by a second user between the check and the write? Or is that
second check unnecessary because the database ensures that already? My
mental model of a database (which is probably wrong) is that it’s like
an open file, the database engine provides no intrinsic access
synchronization, so any thread can write to the table any time. Perhaps
a transaction does provide the needed synchronization? Any thoughts on
that?
Bingo! That’s exactly the piece of information I was looking for. But
not knowing the terminology, it’s hard to know where to start looking.
Thanks!
Jeremy H. wrote:
The transaction guarantees that your read isn’t dirtied till the
transaction is committed. Essentially it locks part or all of the
table and serializes access to the relevant rows. If a concurrent
transaction ran it would block on the first one, and its read would
find that there is in fact an overlap when it finally completed. There
are some details that you can learn about if you study the isolation
modes for your particular DBMS but essentially this is what any
transactional database will provide.
On 3/27/06, Joe C. [email protected] wrote:
a transaction applies here. Do you think think after writing a new
Posted via http://www.ruby-forum.com/.
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
–
Jeremy H.
http://www.jeremyhuffman.com
The transaction guarantees that your read isn’t dirtied till the
transaction is committed. Essentially it locks part or all of the
table and serializes access to the relevant rows. If a concurrent
transaction ran it would block on the first one, and its read would
find that there is in fact an overlap when it finally completed. There
are some details that you can learn about if you study the isolation
modes for your particular DBMS but essentially this is what any
transactional database will provide.
On 3/27/06, Joe C. [email protected] wrote:
a transaction applies here. Do you think think after writing a new
Posted via http://www.ruby-forum.com/.
Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails
–
Jeremy H.