A couple of questions on Concurrency

Yes, this is fundamentally harder than it seems. In this it is like multi-threaded programming - the simple and obvious answer works 99.99% ... but that rare - often undetected - failure causes enormous heartburn somewhere far from the source of the original injury.

I would like to be able to follow the thread of your reasoning. That would take time and I'd like to get back to you right away on your final two questions:

"If we want to do optimistic locking, we must have a “changed” field that you can track.  If we don’t have that today, we’ll need to add it to most of our tables.  Yes?

If we don’t make that change, all of our updates will be last one wins (because you won’t hold locks)? (oops, two questions)"

You don't have to have a concurrency column on every table - only on those tables that you use to detect and signal concurrency violations. DevForce will apply optimistic concurrency checking on those tables and "last one wins" on the others.

Let me illustrate: Suppose I have such a column on Order but not on OrderDetail.

Scenario #1: I change an order; you change the same order; I save, you save; DF detects the problem and tells you.

Scenario #2: I change an orderDetail; you change the same orderDetail; I save, you save; DF is oblivious and your save trumps mine.

Is this bad? Not necessarily!

Let me add another rule: "Everytime I change an orderDetail, I must also change its parent Order." This rule implies that a change to an order's detail items is tantamount to a change to the order itself. 

I happen to like that logic as I'll explain in a minute. But let's just run with this for now and replay Scenario #2:

Scenario #2b: I change an orderDetail which changes the parent order; you change the same orderDetail which changes the same parent order; I save, you save; DF detects the collision on order, fails your transaction, neither the order nor the detail are saved, and you hear all about it.

This is exactly what I want and, in fact, catches another slippery bug that neither of us considered in the discussion below (I left it out deliberately because there was enough to worry about).

Scenario #3:  I change an orderDetail ; you change its parent order; I save, you save; DF detects the collision on order, fails your transaction, your order change is rejected.

Observe that, in the absence of the rule, "changing an OrderDetail changes its parent Order", none of the mechanisms either of us described below would have caught that particular concurrency violation. Yet surely the Order is fundamentally changed if I modify/add/delete any of its OrderDetails. I should be wondering what you are doing changing RequiredDate on my order while I was changing the quantity of Acme Widgets?

We (you and I as sales reps working on the order) are clearly out-of-sync and the application should alert us to that fact.

In my solution, I've made Order the "master" object in a dependent object graph (there's a UML term for this but let's not be pedantic). Order is "soft locking" its OrderDetails. The master object provides effective concurrency control over objects in its dependent graph. Therefore, those dependent objects do not need their own concurrency column.

This works as long as all processes that can save data to the database play by the rules:

Always save member of the object graph within a transaction (i.e., can't save an orderDetail on its own).

Always "dirty" the parent when you modify/add/delete a child (or grandchild).

It follows that any object can play the "master" object role.

Suppose you couldn't add a concurrency column to Order. Fortunately, you can create a table to hold some kind of object to serve as "the master of the master".

Let's abstract this and dedicate a table dedicated to soft locking of any table in our database. Each of it's rows is a tuple: {ObjectType, ObjectPrimaryKey, RowVersion, UserId, ...}.

Every time I update an order, I try to acquire the SoftLock object for that order. If there isn't one, I create one. I now include this in my transaction with the Order and OrderDetail changes.

You see where this leads.

P.S.: This technique imposes a miniscule performance impact and is a small addition to the business logic that can be encapsulated in one place, either in a base entity class or via some kind of "mix-in" approach.

===

You may also see that there is an opportunity to use the SoftLock table as a non-blocking CheckOut mechanism.  Joe can check out the Order before working on it. Sam's client detects the checkout and tells Sam that Joe has it.  Sam can move on to something else, call Joe, or ... steal it from Joe by overwriting his SoftLock object.

This last alternative may seem sneaky. It may also be necessary:

Joe is at lunch; there's a crisis; Sam pitches in and gets it done; Joe returns ... tries to finish the order ... and discovers that Sam stole the order. He may be pissed but ... that's another story.

At least one of our customers uses this technique with considerable satisfaction. It provides safety and information ... and is non-blocking.

===

So that's my "quick" answer. To summarize:

optimistic concurrency only works for tables that have a suitable concurrency column

not every table has to be under concurrency control

if you can't modify a table, you can check it for concurrency by subordinating it in a transaction to another table

you can implement "master/detail" concurrency checking and "CheckOut" control via this mechanism

Hope this all makes sense.