Forum: Ruby on Rails Newbie question about database consistency

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
6e1313235d1f1c7f78e5161c53b2f73a?d=identicon&s=25 Joe Chan (firstian)
on 2006-03-25 03:01
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,
4005a47a8f2ceee49670b920593c1d52?d=identicon&s=25 Ben Munat (Guest)
on 2006-03-25 09:08
(Received via mailing list)
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/
7cda78d70ab775710f6fa24e0a5b68db?d=identicon&s=25 Emin Hasanov (Guest)
on 2006-03-25 11:11
(Received via mailing list)
why wouldn't simple check work?
6e1313235d1f1c7f78e5161c53b2f73a?d=identicon&s=25 Joe Chan (firstian)
on 2006-03-26 05:25
Emin Hasanov 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?
6e1313235d1f1c7f78e5161c53b2f73a?d=identicon&s=25 Joe Chan (firstian)
on 2006-03-26 05:36
Ben Munat 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.
4005a47a8f2ceee49670b920593c1d52?d=identicon&s=25 Ben Munat (Guest)
on 2006-03-26 05:42
(Received via mailing list)
Joe Chan 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
24e30ea2f4bfda89a9a915dd18247d05?d=identicon&s=25 Jeremy Huffman (Guest)
on 2006-03-26 06:19
(Received via mailing list)
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 Munat <bent@munat.com> wrote:
> >
> >
> Rails has optimistic locking built in... see page 222 in AWDWR, or here:
>
> http://api.rubyonrails.com/classes/ActiveRecord/Locking.html
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


--
Jeremy Huffman
http://www.jeremyhuffman.com
6e1313235d1f1c7f78e5161c53b2f73a?d=identicon&s=25 Joe Chan (firstian)
on 2006-03-26 06:20
Ben Munat wrote:
> Joe Chan 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

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.
4005a47a8f2ceee49670b920593c1d52?d=identicon&s=25 Ben Munat (Guest)
on 2006-03-26 06:40
(Received via mailing list)
Joe Chan 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
4daf0b71d5d9a3882e583c0e72eaf5dc?d=identicon&s=25 Alan Francis (Guest)
on 2006-03-27 14:41
Joe Chan 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/Tr...

How about:

Reservation.transaction do
  ...check for clash
  ..write new record
end

Does this help ?

Alan
6e1313235d1f1c7f78e5161c53b2f73a?d=identicon&s=25 Joe Chan (firstian)
on 2006-03-28 03:57
Alan Francis wrote:
> Joe Chan 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/Tr...
>
> 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?
24e30ea2f4bfda89a9a915dd18247d05?d=identicon&s=25 Jeremy Huffman (Guest)
on 2006-03-28 05:00
(Received via mailing list)
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 Chan <firstianus@yahoo.com> wrote:
> >
> a transaction applies here. Do you think think after writing a new
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


--
Jeremy Huffman
http://www.jeremyhuffman.com
6e1313235d1f1c7f78e5161c53b2f73a?d=identicon&s=25 Joe Chan (firstian)
on 2006-03-28 05:21
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 Huffman 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 Chan <firstianus@yahoo.com> wrote:
>> >
>> a transaction applies here. Do you think think after writing a new
>> Posted via http://www.ruby-forum.com/.
>> _______________________________________________
>> Rails mailing list
>> Rails@lists.rubyonrails.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>
>
> --
> Jeremy Huffman
> http://www.jeremyhuffman.com
This topic is locked and can not be replied to.