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, -- Posted via http://www.ruby-forum.com/.
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/ Joe Chan wrote:> 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, >
why wouldn''t simple check work? On 3/25/06, Joe Chan <firstianus@yahoo.com> wrote:> > 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. >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060325/461bf3ea/attachment.html
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? -- Posted via http://www.ruby-forum.com/.
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. -- Posted via http://www.ruby-forum.com/.
Joe Chan wrote:> 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. >Rails has optimistic locking built in... see page 222 in AWDWR, or here: http://api.rubyonrails.com/classes/ActiveRecord/Locking.html
Jeremy Huffman
2006-Mar-26 04:19 UTC
[Rails] Re: Newbie question about database consistency
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:> Joe Chan wrote: > > 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. > > > 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
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.htmlOK, 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. -- Posted via http://www.ruby-forum.com/.
Ben Munat
2006-Mar-26 04:40 UTC
[Rails] Re: Re: Newbie question about database consistency
Joe Chan wrote:> 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. >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
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/Transactions/ClassMethods.html How about: Reservation.transaction do ...check for clash ..write new record end Does this help ? Alan -- Posted via http://www.ruby-forum.com/.
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/Transactions/ClassMethods.html > > How about: > > Reservation.transaction do > ...check for clash > ..write new record > end > > Does this help ? > > AlanI 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? -- Posted via http://www.ruby-forum.com/.
Jeremy Huffman
2006-Mar-28 03:00 UTC
[Rails] Re: Newbie question about database consistency
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:> 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/Transactions/ClassMethods.html > > > > 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? > > -- > 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
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-- Posted via http://www.ruby-forum.com/.