# How to avoid concurency issue for this scenario? **URL:** \ **Category:** Rails\ **Created:** [June 21, 2008, 8:24am UTC](https://www.ruby-forum.com/t/how-to-avoid-concurency-issue-for-this-scenario/141046 "2008-06-21T08:24:06Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [June 21, 2008, 8:24am UTC](https://www.ruby-forum.com/t/how-to-avoid-concurency-issue-for-this-scenario/141046/1 "2008-06-21T08:24:06Z")
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 ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [June 21, 2008, 9:36am UTC](https://www.ruby-forum.com/t/how-to-avoid-concurency-issue-for-this-scenario/141046/2 "2008-06-21T09:36:10Z")
PS. Q3: Is it possible with Rails to do a table-lock on my projections table? This might be the best? ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [June 21, 2008, 6:30pm UTC](https://www.ruby-forum.com/t/how-to-avoid-concurency-issue-for-this-scenario/141046/3 "2008-06-21T18:30:08Z")
On Jun 21, 8:35 am, “Greg H.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [June 22, 2008, 11:06am UTC](https://www.ruby-forum.com/t/how-to-avoid-concurency-issue-for-this-scenario/141046/4 "2008-06-22T11:06:37Z")
On Jun 20, 2008, at 11:23 PM, Greg H. wrote: > 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 ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [June 22, 2008, 11:21am UTC](https://www.ruby-forum.com/t/how-to-avoid-concurency-issue-for-this-scenario/141046/5 "2008-06-22T11:21:15Z")
On 22 Jun 2008, at 10:06, Greg W. wrote: > it does require a little bit of care, but then dealing with race conditions is inherently tricky. > - 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 ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [June 22, 2008, 12:04pm UTC](https://www.ruby-forum.com/t/how-to-avoid-concurency-issue-for-this-scenario/141046/6 "2008-06-22T12:04:45Z")
On Jun 22, 2008, at 2:20 AM, Frederick C. wrote: > On 22 Jun 2008, at 10:06, Greg W. wrote: > we do have locked\_by and locked\_at columns but we don’t want 2 users > 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 ---
**Author:** ![greg](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/71c47a/32.png) [@greg](https://www.ruby-forum.com/u/greg)\ **Post date:** [June 22, 2008, 6:32pm UTC](https://www.ruby-forum.com/t/how-to-avoid-concurency-issue-for-this-scenario/141046/7 "2008-06-22T18:32:51Z")
On 22 Jun 2008, at 11:04, Greg W. wrote: > 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