Rails - Jun 2008 - how to avoid concurency issue for this scenario?

Hi,

Any advice re how to avoid any clash from multiple users issueing the
same "bulk update" type request at the same time? or would this
already be covered by rails with it''s default transactions arrangement
?

Scenario:Update future transactions
* all occurs within one controller action request
* delete all records in projections table
• perform many read transactions from a few table and populate
projections table again

eg what happens here if Usea 2 starts their request just after User 1?
Q1: Is rails based on optimistic locking?
Q2: Does rails wrap a transaction around all database calls that
happen within the one request/action?

Thanks

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

PS.

Q3: Is it possible with Rails to do a table-lock on my projections
table?  This might be the best?


On 6/21/08, Greg Hauptmann
<greg.hauptmann.ruby-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Hi,
>
> Any advice re how to avoid any clash from multiple users issueing the
> same "bulk update" type request at the same time? or would this
> already be covered by rails with it''s default transactions
arrangement
> ?
>
> Scenario:Update future transactions
> * all occurs within one controller action request
> * delete all records in projections table
> • perform many read transactions from a few table and populate
> projections table again
>
> eg what happens here if Usea 2 starts their request just after User 1?
> Q1: Is rails based on optimistic locking?
> Q2: Does rails wrap a transaction around all database calls that
> happen within the one request/action?
>
> Thanks
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jun 21, 8:35 am, "Greg Hauptmann"
<greg.hauptmann.r...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> PS.
>
> Q3: Is it possible with Rails to do a table-lock on my projections
> table?  This might be the best?
>
1) rails provides both optimistic and pessimistic locking
2) no
3) you can execute any sql statement you want

in your case if 2 people started doing the same thing at the same time
you''ve probably end up with a mess.

Fred
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jun 20, 2008, at 11:23 PM, Greg Hauptmann wrote:
> Any advice re how to avoid any clash from multiple users issueing the
> same "bulk update" type request at the same time? or would this
> already be covered by rails with it''s default transactions
arrangement
> ?
>
> Scenario:Update future transactions
> * all occurs within one controller action request
> * delete all records in projections table
> • perform many read transactions from a few table and populate
> projections table again
>
> eg what happens here if Usea 2 starts their request just after User 1?
> Q1: Is rails based on optimistic locking?
> Q2: Does rails wrap a transaction around all database calls that
> happen within the one request/action?
> Q3: Is it possible with Rails to do a table-lock on my projections
> table?  This might be the best?

I have my own pessimistic system to handle situations like these and  
most day-to-day concurrent editing in admin-like UIs.

I''m not a fan of optimistic locking. At least, I''ve never seen
a case
where it make sense compared to pessimistic locking the way I do it.  
IMO dealing with collisions that happen under optimistic locking is a  
lot more hassle than just dealing with pessimistic locking right up  
front.

The theory is simple, but it takes some code to deal with and work  
around ActiveRecord.

Theory:

- add at least two, optionally three, fields to your models/tables:  
lock_id, locked_at, and locked_by (optional)

- lock_id = id of the lock owner''s user record (IOW current_user.id)

- locked_at = full time stamp of when a lock is claimed

- locked_by = friendly name of user -- handy for work group scenarios  
where you might want to literally display who has a current lock.

- add a method to your model (I have this abstracted as a mixin to  
ActiveRecord) called find_and_lock which is responsible for finding  
the records you want to lock and making sure they are available. If  
they are available, then lock them by populating lock_id and  
locked_at. One problem I had with applying this to Rails is  
ActiveRecord''s validation. I ended up using direct SQL to avoid AR.  
Not evil, but requires a layer to abstract queries for RDBMS engines,  
or you just live with db-specific code.

- next you''ll need a method for save_using_lock. This method is  
responsible for first checking the lock fields of a record about to  
be saved to see if our claim is still valid. The main cause for loss  
of lock is that it expires. If it has expired, but the lock_id still  
matches our own (no one else came along to claim a lock), I allow it  
to be saved anyway (that''s a little optimistic logic thrown in).

- I mentioned a lock expiring. My compromise for the stateless web  
environment is to treat a lock much like a session. It''s valid for X  
time in minutes. Depending on the app and data involved, I might have  
a very short one of 5 minutes where a field or two is being edited,  
and access requests might be common, to an hour to allow somone to  
write or approve an article where request freqency will be very low.  
By time stamping every lock, I can compare current time, locked_at  
time, and the allowed lock duration.

People can abandon locked records. Start a form to edit a record and  
click a UI link to go who knows where in the web site, that record is  
still locked. The expiration handles the worst case. If nothing else  
the find_and_lock will ignore a lock that has expired. However, to  
make the web app more proactive in handling abandoned locks, I have a  
method called clear_pessimistic_locks. Every lock claimed adds to a  
session variable that stores the database and table of the record  
that was locked. clear_pessimistic_locks iterates through that list  
and clears the locks held by current_user. I pepper controller  
actions of likely entry pages in the site with that method (it''s a  
mixin to ActiveController). If the user editing a record jumps to the  
site home page, then that action will clean up the abandoned lock.

I''ve used this system on another platform for years. It''s not
fast
enough to use for highly competitive requests for updates like very  
heavy auctions, but it has worked very well in all my apps so far.  
For hyper applications, you''d need a high speed threaded entry-point.  
I had that in one of my first versions, but found it unnecessary for  
my apps, so I haven''t transported that to this Rails version.

I''m just getting started with this Rails version (written as a  
plugin), so I don''t know yet what quirks there might be. So far I  
discovered I need to use raw SQL to avoid AR''s autopilot actions  
everytime I just want to set or clear the lock fields. Seems to work  
just fine so far.

Seems like it should work well for your scenario too.

--
def gw
   writes_at ''www.railsdev.ws''
end





--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 22 Jun 2008, at 10:06, Greg Willits wrote:
>
>
> I have my own pessimistic system to handle situations like these and
> most day-to-day concurrent editing in admin-like UIs.
>
> I''m not a fan of optimistic locking. At least, I''ve never
seen a case
> where it make sense compared to pessimistic locking the way I do it.
> IMO dealing with collisions that happen under optimistic locking is a
> lot more hassle than just dealing with pessimistic locking right up
> front.
>
it does require a little bit of care, but then dealing with race  
conditions is inherently tricky.
> The theory is simple, but it takes some code to deal with and work
> around ActiveRecord.
>
> Theory:
>
> - add at least two, optionally three, fields to your models/tables:
> lock_id, locked_at, and locked_by (optional)
>
> - lock_id = id of the lock owner''s user record (IOW
current_user.id)
>
> - locked_at = full time stamp of when a lock is claimed
>
> - locked_by = friendly name of user -- handy for work group scenarios
> where you might want to literally display who has a current lock.
>
> - add a method to your model (I have this abstracted as a mixin to
> ActiveRecord) called find_and_lock which is responsible for finding
> the records you want to lock and making sure they are available. If
> they are available, then lock them by populating lock_id and
> locked_at. One problem I had with applying this to Rails is
> ActiveRecord''s validation. I ended up using direct SQL to avoid
AR.
> Not evil, but requires a layer to abstract queries for RDBMS engines,
> or you just live with db-specific code.
>
We use optimistic locking in a scenario that is very similar to this:  
we do have locked_by and locked_at columns but we don''t want 2 users  
clicking on the same thing at the same time to think they have both  
locked the same object (the way our app works this is is reasonably  
likely scenario): if what your find and lock does
1) get rows
2) update lock_id if lock_id is null

then you could have 2 users
1) get rows
1'') get rows
2) update lock_id if lock_id is null
2'') update lock_id if lock_id is null

obviously at the end of the day lock_id is only set to one think, but  
both actions will have assumed they had got the lock (and so a user  
might spend 3 minutes filling in the form you display even though they  
haven''t got the lock.

Fred

> - next you''ll need a method for save_using_lock. This method is
> responsible for first checking the lock fields of a record about to
> be saved to see if our claim is still valid. The main cause for loss
> of lock is that it expires. If it has expired, but the lock_id still
> matches our own (no one else came along to claim a lock), I allow it
> to be saved anyway (that''s a little optimistic logic thrown in).
>
> - I mentioned a lock expiring. My compromise for the stateless web
> environment is to treat a lock much like a session. It''s valid for
X
> time in minutes. Depending on the app and data involved, I might have
> a very short one of 5 minutes where a field or two is being edited,
> and access requests might be common, to an hour to allow somone to
> write or approve an article where request freqency will be very low.
> By time stamping every lock, I can compare current time, locked_at
> time, and the allowed lock duration.
>
> People can abandon locked records. Start a form to edit a record and
> click a UI link to go who knows where in the web site, that record is
> still locked. The expiration handles the worst case. If nothing else
> the find_and_lock will ignore a lock that has expired. However, to
> make the web app more proactive in handling abandoned locks, I have a
> method called clear_pessimistic_locks. Every lock claimed adds to a
> session variable that stores the database and table of the record
> that was locked. clear_pessimistic_locks iterates through that list
> and clears the locks held by current_user. I pepper controller
> actions of likely entry pages in the site with that method (it''s a
> mixin to ActiveController). If the user editing a record jumps to the
> site home page, then that action will clean up the abandoned lock.
>
> I''ve used this system on another platform for years. It''s
not fast
> enough to use for highly competitive requests for updates like very
> heavy auctions, but it has worked very well in all my apps so far.
> For hyper applications, you''d need a high speed threaded
entry-point.
> I had that in one of my first versions, but found it unnecessary for
> my apps, so I haven''t transported that to this Rails version.
>
> I''m just getting started with this Rails version (written as a
> plugin), so I don''t know yet what quirks there might be. So far I
> discovered I need to use raw SQL to avoid AR''s autopilot actions
> everytime I just want to set or clear the lock fields. Seems to work
> just fine so far.
>
> Seems like it should work well for your scenario too.
>
> --
> def gw
>   writes_at ''www.railsdev.ws''
> end
>
>
>
>
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Jun 22, 2008, at 2:20 AM, Frederick Cheung wrote:
> On 22 Jun 2008, at 10:06, Greg Willits wrote:
>> - add a method to your model (I have this abstracted as a mixin to
>> ActiveRecord) called find_and_lock which is responsible for finding
>> the records you want to lock and making sure they are available. If
>> they are available, then lock them by populating lock_id and
>> locked_at. One problem I had with applying this to Rails is
>> ActiveRecord''s validation. I ended up using direct SQL to
avoid AR.
>> Not evil, but requires a layer to abstract queries for RDBMS engines,
>> or you just live with db-specific code.
>>
> We use optimistic locking in a scenario that is very similar to this:
> we do have locked_by and locked_at columns but we don''t want 2
users
> clicking on the same thing at the same time to think they have both
> locked the same object (the way our app works this is is reasonably
> likely scenario): if what your find and lock does
> 1) get rows
> 2) update lock_id if lock_id is null
>
> then you could have 2 users
> 1) get rows
> 1'') get rows
> 2) update lock_id if lock_id is null
> 2'') update lock_id if lock_id is null
>
> obviously at the end of the day lock_id is only set to one think, but
> both actions will have assumed they had got the lock (and so a user
> might spend 3 minutes filling in the form you display even though they
> haven''t got the lock.
Granted...  but the way I figured it, there''s a much higher chance of  
that using optimistic than at least trying to read-write faster than  
the second read is likely to happen.

Using higher speed thread locking as the first step to gaining  
permission to do read-for-lock step shortens that interval where read  
read is possible, but at some level aren''t all approaches just faster  
versions of this process?

In my cases, dealing with trying to merge two submissions would be  
ugly. I''m not saying it''s not possible in my code (certainly
is
possible at the right scale), but a lot less likely -- which has  
worked so far for me.

What approach are you using that avoids this potential?

-- gw




--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 22 Jun 2008, at 11:04, Greg Willits wrote:
>>
>> obviously at the end of the day lock_id is only set to one think, but
>> both actions will have assumed they had got the lock (and so a user
>> might spend 3 minutes filling in the form you display even though  
>> they
>> haven''t got the lock.
>
> Granted...  but the way I figured it, there''s a much higher chance
of
> that using optimistic than at least trying to read-write faster than
> the second read is likely to happen.
>
> Using higher speed thread locking as the first step to gaining
> permission to do read-for-lock step shortens that interval where read
> read is possible, but at some level aren''t all approaches just
faster
> versions of this process?
>
> In my cases, dealing with trying to merge two submissions would be
> ugly. I''m not saying it''s not possible in my code
(certainly is
> possible at the right scale), but a lot less likely -- which has
> worked so far for me.
>
> What approach are you using that avoids this potential?
>
optimistic locking is enough for us. it ensures that the lock_id  
column doesn''t get messed around with. The only write where a  
StaleObjectError is likely is when someone is trying to acquire a lock  
on the object in question, and at that point they by definition  
haven''t made any changes (at least in our app) and so there is no  
problem. I''m not sure why you think that optimistic locking leaves you
worse off (in case I wasn''t clear I don''t advocate optimistic
locking
as a replacement for what you are doing, but to guard against race  
conditions when setting the lock_id column).

Fred
> -- gw
>
>
>
>
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---