Concurrency in Master/Detail Relationships

Level 400 DevForce Express		View Archived Tech Tips	Feb 2, 2007
	Concurrency in Master / Detail Relationships

What is the best way to detect concurrency violations in master / detail relationships?

An Example

Here's a sad tale of Orders and OrderDetails gone bad:

1. Abby creates an Order with three OrderDetails, (1a), (1b), and (1c).
2. Abby saves the Order and its details.
3. Abby goes to lunch.
4. Bob picks up the same Order and changes its details, adding (1d) and deleting (1b).
5. Bob saves his changes.
6. When Abby returns from lunch, she still has the original Order and details in her cache;
7. Abby continues working on details (1a) and (1c) but doesn't touch (1b) or the Order itself.
8. Abby saves her work.

Her save will succeed. She won't hear a peep from the database or DevForce. There is no concurrency violation because Abby's changes to (1a) and (1c) are independent of Bob's work on (1b) and (1d).

This is not good. Abby will not know that Bob has changed the Order. The order Abby thought she created is not the order stored in the database. Abby, Bob, or the customer are in for an unpleasant surprise.

Dependency Automation

Folks often wonder if DevForce can detect the problem and signal a concurrency violation. DevForce "knows" about the relationship between Order and its OrderDetails. Perhaps it should infer the implications of that relationship.

In fact, DevForce won't detect the problem and should not draw any conclusions about the relationships among the entities.

We begin to see why when we observe that few "master / detail" relationships are as strong as the one between an Order and its details. For example, Customer has a "master / detail" relationship to Order. We usually don't mind if Abby and Bob make changes to separate orders of the same customer. So how is that different from Abby and Bob changing separate details of the same order?

If you are a UML junky, you know to classify these two relationships differently. The Customer / Order relationship is an "Association"; we say that a customer "has orders". The Order / Order relationship is called a "Composition"; we say that an Order "is made up of" its OrderDetails". "Has a" is not as strong as "is made up of".

DevForce object mapping doesn't distinguish among the types of relationship. Some other mapping products allow you to make this distinction. Is DevForce deficient in this respect? Should we enable marking the "Order / OrderDetail" relationship as a "composition" and enforce the implications?

We've certainly thought about it. It isn't technically daunting; relationship marking is easy and cascading inserts, updates, and deletes isn't much harder. But experience teaches us that the "implications" are not as clear as they seem. For example, we generally don't worry when two users work on orders for the same customer; but sometimes we do. The customer could have a credit limit. There could be a business rule that requires senior management approval when the sum of all order totals exceeds a threshold.

Should we say that Customer / Order is a composition too and prevent simultaneous changes by multiple users to any of a customer's orders? That may not fly in the real world. And there is no telling where to stop once you start locking up the object graph with composition constraints. What if the order total threshold changes? Maybe we better lock up that one. How about the customer's headquarters address? There could be sales tax implications; I guess we should lock that up too. Soon no one can do anything for fear of some dependency somewhere.

We've chosen to stay out of your way. We think you should decide your own business rules and how to enforce them by writing code for the purpose.

Which is why we get that question at least once a month: how do I write the code so that orders and their details are treated as one thing?

"Root Entity Marker"

Suppose we're certain that any change to either the order or any of its details is a change to the entire order. We'll agree to deal with the nuances of customer credit limits another way. Meanwhile, we want the application to enforce the integrity of the order with respect to its details.

I suggest a simple expedient: whenever we save changes to an OrderDetail, we save its Order too. Of course we can only save entities that have been changed so we'll have to mark the Order "dirty".

We can generalize the problem and its solution. Whenever we detect a network (AKA, a graph) of objects which should be treated as a single unit, we should (a) identify the "Root Entity", (b) save the root entity when saving a change to any object in the graph , and (c) ensure that we save the combined order changes in a single transaction.

Order is the "Root Entity" in our example. Let's reexamine the story of Abby and Bob, picking up at the point when Bob saves his changes.

He didn't touch the Order object but the application marked it "dirty" anyway and saved it along with the addition of (1d) and the deletion of (1b). Abby's cached copy of the Order is no longer "current" with respect to the database. When she tries to save her order detail changes, the application marks her order dirty and tries to save it along with her changes to (1a) and (1c).

This time she gets a concurrency violation. Although Abby's and Bob's changes to order details did not collide - they worked on different OrderDetail objects - Abby's modified order no longer matches the order in the database - the one saved by Bob. DevForce will recognize a concurrency error on the order and the transaction will fail.

How to Dirty the Root Entity

It is easy to "dirty" a DevForce entity even if it was previously unmodified:

C#:

public static Entity EnsureModified(Entity pEntity) {
  if ( pEntity.RowState == DataRowState.Unchanged ) {
    ( (DataRow) pEntity ).SetModified();
  }
return pEntity;
}

VB.NET:

Public Shared Function EnsureModified(ByVal pEntity As Entity ) As Entity
  If pEntity.RowState = DataRowState.Unchanged Then
    CType(pEntity, DataRow).SetModified()
  End If
  Return pEntity
End Function

When to do so takes a tad more programming. A full code sample wouldn't fit in this tip (contact us for it) but we can benefit right now from a sketch of the primary scenario.

1. The application tries to save Abby's OrderDetails.
2. DevForce raises its SavingEvent.
3. The SavingEventArgs provide a list of entities that are about to be saved.
4. Your SavingEventHandler calls each listed entity's Validate method.
5. The OrderDetail Validate method first validates the detail.
6. It next passes the parent Order to EnsureModified().
7. It adds the Order to the list of entities to save.
8. The Order itself gets validated.
9. The SavingEventHandler returns without validation errors.
10. DevForce saves the Order and the changed OrderDetails.

User Experience

Remember that Abby was at lunch when Bob saved his changes. Abby returned and spent fifteen more minutes working on the details before trying to save. Thanks to our new logic, the application detected a concurrency violation and told her "Sorry Abby, I can't save your work."

Abby is not happy. Actually, she is furious. If we're lucky, she turns her ire on Bob.

Of course it is really our fault. Bob didn't know that Abby was working on the order and Abby didn't know that Bob had taken over.

We might try to help her reconcile the differences - and perhaps merge her changes into Bob's. I think this is an exercise in futility. We'll burden our application with crushing complexity while doing little to improve Abby's mood. We should concentrate instead on preventing the problem in the first place.

I recommend that you combine the "Root Entity Marker" approach with a "Root Entity Reservation" system.

Root Entity Reservation

We could require a user to reserve (or "check out") the root entity before she begins work on it or any of its parts. Accordingly we begin by adding a "ReservedByUserId" column to the Order table.

[Aside: this is just one implementation. Maybe we can't change an existing table, in which case we could create a new "OrderReservation" table. "OrderReservation" becomes the "root entity" in our drama with Order and its OrderDetails in supporting roles.]

When Abby first saves her new Order, the UI stamps the ReservedByUserId with her user id. Then Bob looks at the Order in a list of pending work. He sees that Abby has reserved it. The application won't let him edit it.

Abby returns from lunch. She still owns the order. She makes her changes, saves her work, and releases her reservation. Now Bob can take over if he still wants to do so.

Let's suppose he does. The UI stamps the order with his user id and attempts to save it. Had the save succeeded, the UI would have opened the "order graph" for him to edit. As it happens, Charles from across the hall reserves the order a split second ahead of Bob, causing Bob's save to fail. Bob's UI refreshes its cached copy of the order so he can see that Charles has it now.

Reserve, Don't Lock

Observe that we updated the order. We didn't lock the table. Locking the table impairs performance. Worse, we would have no easy way of breaking the lock if we had to cancel the reservation.

Suppose the order turns critical while Abby was out for lunch. Bob steps in because there is a customer emergency. As it happens, Bob is a supervisor with the authority to override Abby's reservation. He does so and reserves the order for himself. The application queues up an alert for Abby; when she returns from lunch, she sees immediately that Bob has taken over. She calls Bob to discuss her pre-lunch changes so that he can enter them himself.

It's easy to enrich the reservation facility. We could add a timeout of, say, twenty minutes after which the reservation is cleared. The UI could display a countdown clock, sound a warning tone, and let Abby renew her reservation if she needed more time. If her reservation expired, she could try to re-reserve it; she'd be fine as long as Bob and Charlie left the order alone.

This scheme has a long history of success. The reservation system is transparent and familiar. Users "get it". They see what is going on and accept the consequences of expired or canceled reservations. It works for management too, stimulating "ownership" among the users and providing the flexibility necessary to cope with emergencies.

Consider the "Root Entity Marker" and "Root Entity Reservation" patterns the next time you face a graph of master / detail relationships.