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

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

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.

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

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

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