Hi, If two end user accesses the same patient information at the same time, Say User A User B Read Patient data Read Patient Data Made some changes Made some changes Commit change Commit change. (Should abort) Since patient data has being changed before User B commit the change back, I want to roll back the commit. The normal way to handle it is when User B read the data, there is a column to indicate the timestamp (or a version number) of the patient data and when it commits, it will check the timestamp first, if the timestamp changed (say User A has commits a new change), then the commit will abort. Does ActiveRecord have build in function to handle it or I have to check by myself?>From the source code, I didn''t see anything but want to make sure.Thanks chong -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Steve Kellock Sent: Thursday, December 09, 2004 1:50 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: Re: [Rails] Validation/Constraints I''ll bite. ActiveRecord::Base.transaction do "Look ma! I''m ACID compliant at the OO level!" end But if you want to perform optimistic locking on your db, go for it. I don''t think AR should enforce that "design" decision though. Also, IMHO, validation (just like security) should be everywhere in your app... front-end thru to the back-end. So you''re right saying it should be in the db... but it should also be in the app to some extent. Again, IMHO. Cheers... On Thu, 9 Dec 2004 11:44:21 -0800, Eric Ocean <subscriber-npdh1YFn4ZDQT0dZR+AlfA@public.gmane.org> wrote:> At the risk of heating things up even more, I wanted to comment on > DHH''s assertion that people should just move all their > validation/integrity code out of the database. This approach is doomed > to fail as things get more complicated because you can''t implementACID> semantics efficiently at the OO layer because you do not have accessto> all of the data, with current values, at commit time. This is a common > OO layer problem (every single one has it, AFAIK), and by beingsubtle,> is even more likely to be uncaught. > > The problem goes like this: when you commit values to the database,the> DB layer should check to see if those objects have been changed since > they were fetched. If they have, an exception should be thrown. Idon''t> get the sense that AR actually does this now, because to implement it, > you have to keep an extra copy of the rows fetched around to compare > against the current values in the database, and I didn''t see that inmy> walk through of the code. Correct me if I''m wrong here. > > However, even with the above check, inconsistencies can still creepin.> The problem is that we''re only checking for changes against those rows > that we''re committing. If any of the values in those rows are based on > values in rows we''re not committing, those uncommitted rows won''t be > checked for changes. This is the fundamental problem with OO mapping > layers, and I''ve yet to find a framework that handles it correctly. > (WebObjects doesn''t, and last I checked, Hibernate doesn''t either.) > > The solution is to do validation/constraints in the database. Only the > database has access to all of the data at commit time. Only the > database knows what values are current. Only the database can insure > that the values in the database stay consistent. It''s not possible, > fundamentally, to do this at the OO level _without_ re-implementingthe> database at that level--which defeats the purpose of an OO layer > entirely. > > I bring this up because I''m getting the impression on the list that > "you can do validation in the OO layer or in the database--your > choice." As if the only tradeoff is ease of implementation. The > tradeoff is also correctness, integrity, etc. Implementing constraints > in the database is only "harder" than OO validation if you''recomparing> apples to oranges. > > Of course, many applications (and probably a great many web > applications) won''t bump into these problems. In those cases, DHH''s > approach is perfectly fine and probably preferable. Another solutionis> to use something like Madeleine (although Madeleine doesn''t implement > transactions at this point). > > Best, Eric > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
On Thu, 9 Dec 2004 14:40:08 -0600, ChongQing Xiao <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote:> Since patient data has being changed before User B commit the change > back, I want to roll back the commit. The normal way to handle it is > when User B read the data, there is a column to indicate the timestamp > (or a version number) of the patient data and when it commits, it will > check the timestamp first, if the timestamp changed (say User A has > commits a new change), then the commit will abort. > > Does ActiveRecord have build in function to handle it or I have to check > by myself? > >From the source code, I didn''t see anything but want to make sure.AFAIK, you would have to implement this check on your own. Shouldn''t be to difficult with the validations framework, though. (Or, if you''re using a database more powerful than MySQL, you could impliment it as a database constraint which would probably be better.) -- Regards, John Wilger ----------- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don''t know," Alice answered. "Then," said the cat, "it doesn''t matter." - Lewis Carrol, Alice in Wonderland
On Friday, December 10, 2004, 7:59:00 AM, John wrote:> On Thu, 9 Dec 2004 14:40:08 -0600, ChongQing Xiao > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote: >> Since patient data has being changed before User B commit the change >> back, I want to roll back the commit. The normal way to handle it is >> when User B read the data, there is a column to indicate the timestamp >> (or a version number) of the patient data and when it commits, it will >> check the timestamp first, if the timestamp changed (say User A has >> commits a new change), then the commit will abort. >> >> Does ActiveRecord have build in function to handle it or I have to check >> by myself? >> >>From the source code, I didn''t see anything but want to make sure.> AFAIK, you would have to implement this check on your own. Shouldn''t > be to difficult with the validations framework, though. (Or, if you''re > using a database more powerful than MySQL, you could impliment it as a > database constraint which would probably be better.)Could you talk through the nature of this constraint? At a high level, what are you telling the database? How would ActiveRecord behave when the DB cries foul? Gavin
For web application, I don''t think you can implement the constraint in the database level to address the problem. In most databases, before you update the data, you can issue Select * from Patient where ID = "abc" for Update And this will prevent any other select for patient "abc" and wait until the update is done. In web based application, this won''t work since the user can open the patient for a long period of time. For web application, I think there are two ways to address the problem 1. Implement some application level lock so once a patient is marked as locked, another user can only read the data from the web server so no conflict will happen. 2. Allow two users to change the data at the same time but roll back the last change if the change conflicts according the time stamp as I have mentioned in my previous email Thanks chong -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Gavin Sinclair Sent: Thursday, December 09, 2004 3:14 PM To: John Wilger; rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: Re: [Rails] Concurrecy question with ActiveRecord On Friday, December 10, 2004, 7:59:00 AM, John wrote:> On Thu, 9 Dec 2004 14:40:08 -0600, ChongQing Xiao > <CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org> wrote: >> Since patient data has being changed before User B commit the change >> back, I want to roll back the commit. The normal way to handle it is >> when User B read the data, there is a column to indicate thetimestamp>> (or a version number) of the patient data and when it commits, itwill>> check the timestamp first, if the timestamp changed (say User A has >> commits a new change), then the commit will abort. >> >> Does ActiveRecord have build in function to handle it or I have tocheck>> by myself? >> >>From the source code, I didn''t see anything but want to make sure.> AFAIK, you would have to implement this check on your own. Shouldn''t > be to difficult with the validations framework, though. (Or, if you''re > using a database more powerful than MySQL, you could impliment it as a > database constraint which would probably be better.)Could you talk through the nature of this constraint? At a high level, what are you telling the database? How would ActiveRecord behave when the DB cries foul? Gavin _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
* ChongQing Xiao (CqXiao-VSMm719ZX/VmbZtjAW+qKA@public.gmane.org) [041209 16:33]:> For web application, I think there are two ways to address the problem > > 1. Implement some application level lock so once a patient is marked as > locked, another user can only read the data from the web server so no > conflict will happen. > > 2. Allow two users to change the data at the same time but roll back the > last change if the change conflicts according the time stamp as I have > mentioned in my previous emailThis topic is well-studied and often comes down to a choice between optimistic and pessimistic locking, but there are a lot of complicating factors (request length, ability to resolve conflicts automatically in the problem domain, the cost of a collision, etc.). Martin Fowler covers this general concurrency problem as it relates to enterprise web applications in his _Patterns of Enterprise Application Architecture_. [0] Clearly, the more you know about your particular application, its business logic, and the nature of your session transactions the more readily you can answer your own question and choose a suitable implementation. It''s very difficult for a framework to solve this type of concurrency problem generally and flexibly. I haven''t seen anything in the Rails technologies that gets in the way of handling long transaction concurrency, but you''re not going to find a quick solution either. [0] http://www.martinfowler.com/books.html Best, Rick -- http://www.rickbradley.com MUPRN: 879 | .sig? <p>-----Original random email haiku | Message----- <p>David- <p>Check out | www.distributed.net.
> This topic is well-studied and often comes down to a choice between > optimistic and pessimistic locking, but there are a lot of complicating > factors (request length, ability to resolve conflicts automatically in > the problem domain, the cost of a collision, etc.). Martin Fowler > covers this general concurrency problem as it relates to enterprise web > applications in his _Patterns of Enterprise Application Architecture_. [0] > > Clearly, the more you know about your particular application, its > business logic, and the nature of your session transactions the more > readily you can answer your own question and choose a suitable > implementation. It''s very difficult for a framework to solve this type > of concurrency problem generally and flexibly. I haven''t seen anything > in the Rails technologies that gets in the way of handling long > transaction concurrency, but you''re not going to find a quick solution > either.Point well made rick, any attempt to solve all the locking problems in the world with AR will fail. Having said that ... one thing that could be nice in active record is the ability to support optimistic locking with a timestamp column. Something like: class Patient < ActiveRecord::Base timestamp_column :last_modified end Assume you have a patient Bob, with id = 1. Take a hypothetical request: p = Patient.find(1) p.first_name = "some_name" p.save At present AR says: SELECT t.* FROM patients WHERE id=1 UPDATE patients SET first_name = "some_name" where id = 1; So if you have two concurrent requests, you have ''last in wins''. With optimistic locking, you have the last_modified column controlling requests. so for that *same code* AR will say: SELECT t.* FROM patients WHERE id=1 UPDATE patients SET first_name = "some_name" AND last_modified :current_time where id = 1 AND last_modified= :last_modified; Where :last_modified is the date when you read the row, and current_time is *now*. If Bob has had his name changed by another request, then the WHERE predicate fails and the update says ''no rows affected''. AR can throw a ''OptimisticLockingFailureException'' (oops java-ism). So, you know that the first-in wins, and the last-in has to try again. Optimistic locking doesn''t work for all situations, but it''s a huge value add for a fairly typical web application. Thoughts? Have I described something that''s there already? Or have I volunteered to implement it? -- Cheers Koz
> Thoughts? Have I described something that''s there already? Or have > I volunteered to implement it?You have volunteered to implement it as one of those fancy new mixins we just added support for a few days ago :). See: http://dev.rubyonrails.org/trac.cgi/file/trunk/activerecord/lib/ active_record/mixins/list.rb and http://dev.rubyonrails.org/trac.cgi/file/trunk/activerecord/lib/ active_record/mixins/touch.rb -- David Heinemeier Hansson, http://www.basecamphq.com/ -- Web-based Project Management http://www.rubyonrails.org/ -- Web-application framework for Ruby http://macromates.com/ -- TextMate: Code and markup editor (OS X) http://www.loudthinking.com/ -- Broadcasting Brain
On Dec 9, 2004, at 17:53, Michael Koziarski wrote:> Point well made rick, any attempt to solve all the locking problems > in the world with AR will fail. Having said that ... one thing that > could be nice in active record is the ability to support optimistic > locking with a timestamp column.I''d love to see both this, and locking based on modified fields (and where only modified fields are written back). Something like update people set name = ''David'' where id = 123 and name = ''Dave''; Ideally this could be declarative class Person < AR::B locking_style :optimistic, :type => :timestamp end Then you could also set it globally class AR::B locking_style ... end Cheers Dave
> I''d love to see both this, and locking based on modified fields (and > where only modified fields are written back). Something like > > update people > set name = ''David'' > where id = 123 > and name = ''Dave''; > > Ideally this could be declarative > > class Person < AR::B > locking_style :optimistic, :type => :timestamp > > endSounds good, I''ll try to implement the timestamp locking over the weekend (NZDT), then I''ll move on to modified fields locking. Hibernate has another option for a ''version'' column which is just an incrementing number, I''ll try that for completeness. -- Cheers Koz
Michael,
Which timestamp are you planning on using for optimistic locking? The time
it was pulled out to be viewed/edited or the time it was fetched from the
database in the submit request? Ideally, it should be the first timestamp
when it was viewed for editing.
Here is my lovely table showing the problem of using the value of the
timestamp from the record pulled out of the DB during the submit.
User 1 User 2
============= =============
Fetch customer where id = 1
(timstamp = 1)
Fetch customer where id = 1
(timestamp = 1)
Submit changes
Fetch customer where id 1 and
Update customer set ts = 2
Where id = 1 and ts = 1
SUCCESS!
Submit changes
Fetch customer where id = 1
(timestamp = 2 now)
Update customer set ts = 3 Where id = 1 and ts = 2
SUCCESS even though it should have failed
This is how some java appservers handle optimistic locking by default. We
would need to make sure to use the timestamp value in the where clause from
the object that was submitted and not the one the was pulled out of the
database to make the updates on.
I hope this makes sense. Let me know if it doesn''t.
-Lee
-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Michael Koziarski
Sent: Thursday, December 09, 2004 4:53 PM
To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: Re: [Rails] Concurrecy question with ActiveRecord
> This topic is well-studied and often comes down to a choice between
> optimistic and pessimistic locking, but there are a lot of
> complicating factors (request length, ability to resolve conflicts
> automatically in the problem domain, the cost of a collision, etc.).
> Martin Fowler covers this general concurrency problem as it relates to
> enterprise web applications in his _Patterns of Enterprise Application
> Architecture_. [0]
>
> Clearly, the more you know about your particular application, its
> business logic, and the nature of your session transactions the more
> readily you can answer your own question and choose a suitable
> implementation. It''s very difficult for a framework to solve this
> type of concurrency problem generally and flexibly. I haven''t
seen
> anything in the Rails technologies that gets in the way of handling
> long transaction concurrency, but you''re not going to find a quick
> solution either.
Point well made rick, any attempt to solve all the locking problems in the
world with AR will fail. Having said that ... one thing that could be nice
in active record is the ability to support optimistic locking with a
timestamp column.
Something like:
class Patient < ActiveRecord::Base
timestamp_column :last_modified
end
Assume you have a patient Bob, with id = 1. Take a hypothetical request:
p = Patient.find(1)
p.first_name = "some_name"
p.save
At present AR says:
SELECT t.* FROM patients WHERE id=1
UPDATE patients SET first_name = "some_name" where id = 1;
So if you have two concurrent requests, you have ''last in
wins''. With
optimistic locking, you have the last_modified column controlling requests.
so for that *same code* AR will say:
SELECT t.* FROM patients WHERE id=1
UPDATE patients SET first_name = "some_name" AND last_modified
:current_time where id = 1 AND last_modified= :last_modified;
Where :last_modified is the date when you read the row, and current_time is
*now*.
If Bob has had his name changed by another request, then the WHERE
predicate fails and the update says ''no rows affected''. AR
can throw a
''OptimisticLockingFailureException'' (oops java-ism).
So, you know that the first-in wins, and the last-in has to try again.
Optimistic locking doesn''t work for all situations, but it''s a
huge value
add for a fairly typical web application.
Thoughts? Have I described something that''s there already? Or have
I volunteered to implement it?
--
Cheers
Koz
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
>>>>> "Michael" == Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> writes:Michael> Point well made rick, any attempt to solve all the Michael> locking problems in the world with AR will fail. Having Michael> said that ... one thing that could be nice in active Michael> record is the ability to support optimistic locking with Michael> a timestamp column. <snip> Michael> SELECT t.* FROM patients WHERE id=1 UPDATE patients SET Michael> first_name = "some_name" AND last_modified Michael> :current_time where id = 1 AND last_modified Michael> :last_modified; Michael, Version number (incrementing integer) or version timestamp work well and are conceptually very simple (win-win). I''ve seen it done as a version number, but otherwise exactly as you describe. Gleb
> Michael, > > Version number (incrementing integer) or version timestamp work well > and are conceptually very simple (win-win). I''ve seen it done as a > version number, but otherwise exactly as you describe.Version numbers are useful, and are usually very fast too (easy indexing). However, there''s useful information in the ''last_modified'' column, you can see *when* things happened -- Cheers Koz
On Thu, 9 Dec 2004 18:25:08 -0700, Lee Marlow <lmarlow-/E1597aS9LQAvxtiuMwx3w@public.gmane.org> wrote:> Michael, > > Which timestamp are you planning on using for optimistic locking? The time > it was pulled out to be viewed/edited or the time it was fetched from the > database in the submit request? Ideally, it should be the first timestamp > when it was viewed for editing.I think I see what you''re getting at. I''ll be using the ''timestamp'' as it was when the ''current object'' was retrieved, this is how most people would expect active record to work. However it falls down a bit when doing things the ''rails way''. The following ''typical'' code will only fail if the two edit requests happen more or less simultaneously, whereas we want it to fail if an edit has happened since your ''details'' method was called ...: def details @user = User.find(@params["id"]) end def edit @user = User.find(@params["user"]["id"]) @user.attributes= @params["user"] @user.save end So, There are a couple of ways around this. 1) Require Hidden fields for the timestamp or version column, and have attributes= set them before the save is invoked. 2) Make find_* locking aware somehow, though the parameter would have to be passed to the finder. 3) encourage ''store in session, edit and save'' - Java style. Any thoughts? It strikes me that everything but 3, won''t work with ''old values'' locking. -- Cheers Koz
>>>>> "Michael" == Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> writes:>> Version number (incrementing integer) or version timestamp work >> well and are conceptually very simple (win-win). I''ve seen it >> done as a version number, but otherwise exactly as you >> describe. Michael> Version numbers are useful, and are usually very fast too Michael> (easy indexing). However, there''s useful information in Michael> the ''last_modified'' column, you can see *when* things Michael> happened I wasn''t suggesting not having last_modified, it is useful in itself. I was thinking of additional version number column (which is also interesting information, beyond its use for conflict resolution). Gleb
On Thu, 9 Dec 2004, ChongQing Xiao wrote:> If two end user accesses the same patient information at the same time, > Say > > User A User B > > Read Patient data > Read Patient Data > Made some changes > > Made some changes > > Commit change > Commit change. (Should abort)Right. Just use PostgreSQL and do both of these in a transaction. The database will abort B''s commit because it will detect that A has committed a change to the data B is working with since B asked for its transaction to start. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
On Fri, 10 Dec 2004, Michael Koziarski wrote:> so for that *same code* AR will say: > > SELECT t.* FROM patients WHERE id=1 > UPDATE patients SET first_name = "some_name" AND last_modified > :current_time where id = 1 AND last_modified= :last_modified;You don''t need a timestamp column for this: you can just add all of the columns with their old values to the WHERE clause and the row, if it''s been changed, will not be updated. You can look at the row count to see if your update succeeded or not. Or, as I mentioned before, just do it in PostgreSQL and hold the transaction open, since PostgreSQL already does internally that exact timestamp thing you described. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
On Fri, 10 Dec 2004 18:22:13 +0900 (JST), Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> wrote:> On Fri, 10 Dec 2004, Michael Koziarski wrote: > > > so for that *same code* AR will say: > > > > SELECT t.* FROM patients WHERE id=1 > > UPDATE patients SET first_name = "some_name" AND last_modified > > :current_time where id = 1 AND last_modified= :last_modified; > > You don''t need a timestamp column for this: you can just add all of the > columns with their old values to the WHERE clause and the row, if it''s > been changed, will not be updated. You can look at the row count to see > if your update succeeded or not.See my next message to the list. The ''values'' method can''t necessarily catch the all the ''retrieve'' then ''edit'' scenarios. I''m interested in hearing some alternatives here.> Or, as I mentioned before, just do it in PostgreSQL and hold the > transaction open, since PostgreSQL already does internally that exact > timestamp thing you described.A single transaction spanning multiple http requests is not something I typically use, if I was going to hold a database connection open, I''d use pessimistic locking (SELECT FOR UPDATE...)> cjs > -- > Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org > Make up enjoying your city life...produced by BIC CAMERA >-- Cheers Koz