optimistic concurrency

[Brad Williams]

In the .NET Data Access Architecture Guide, under Performing Database
Updates with DataSets | Managing Concurrency, it says there are four main
approaches to manage optimistic concurrency when updating through a DataSet:

1. Including only the primary key columns
2. Including all columns in the WHERE clause
3. Including unique key columns and the timestamp columns
4. Including unique key columns and the modified columns

Then it goes on to say that 1, 2, and 4 are not recommended -- even though 2
is the default behavior of the Data Adapter Configuration Wizard, and 3 is
NOT supported by the Data Adapter Configuration Wizard. I think "ironic" is
the applicable word here.

What is a good way to implement optimistic concurrency, then? Is everyone
putting a timestamp column in the tables, and leaving the Data Adapter
Configuration Wizard to RAD hackers? ;-)

Brad Williams

 

[Miha Markic]

Hi Brad,

In the .NET Data Access Architecture Guide, under Performing Database
Updates with DataSets | Managing Concurrency, it says there are four main
approaches to manage optimistic concurrency when updating through a DataSet:

1. Including only the primary key columns
2. Including all columns in the WHERE clause
3. Including unique key columns and the timestamp columns
4. Including unique key columns and the modified columns

Then it goes on to say that 1, 2, and 4 are not recommended -- even though 2
is the default behavior of the Data Adapter Configuration Wizard, and 3 is
NOT supported by the Data Adapter Configuration Wizard. I think "ironic" is
the applicable word here.

The guide is quite correct. I guess the wizard is not the ultimate tool.

What is a good way to implement optimistic concurrency, then? Is everyone
putting a timestamp column in the tables, and leaving the Data Adapter
Configuration Wizard to RAD hackers? ;-)

The number 3 is certainly the best one.
Of course, wizards are not for serious projects IMO.
This is what I do:
I generate all adapters from templates.
(I use CodeSmith template based code generator
http://www.ericjsmith.net/codesmith).
So, I have much more automated (one click for everything more or less) and
tweakeable solution (I can adjust template to my specific needs).

 

[Miha Markic]

Ah, yes, you might take a look at my web site
http://www.rthand.com/Default.aspx?Page=2&SubPage=1 - you'll find a template
for generating store adapters.

 

[Brad Williams]

Wow, this is great stuff, thanks Miha!

Brad Williams

 

[Rob Panosh]

Miha,

I am struggling with optimistic concurrency with our application and don't
think we can use it, let me give you a little bit of background about our
application. We have a Formula Management software package (fancy name for
a receipe program) which has very complex business objects. For example a
Formula business object has a header record and some of the following
supporting objects; composition (ingredients), properties (total fat,
calories, cholesterol, ..), costing, notes, attachments, and auditing.
Currently we are opening our business objects in three modes Add, Edit, and
View. When a user wants to Edit a Formula we are using semaphore locks for
concurrency which gets release explictly or via finalize/dispose. Now once
a user opens a formula in edit mode to make modifications let say they want
to change the amout of an ingredient which in turn will impact the Formula
properties, cost and possibly values in the header record such as the
density of the Formula. Currently we haven't had troubles because once the
user has an object open in edit mode and wants to save we essentially create
a SQL transaction for all the changes and execute againts the database.

If we tried to use optimistic concurency, with the above changes, and let
say another user opens the same formula and makes a change to a different
component thus updating property values, cost and saves before the first
user. Now when the original user tries to save the Formula they would be
rejected because of the second users changes. Now I am going to have users
that get pissed off becuase they have spent the last hour reformulating
their product and can't save because somebody made a change before they
saved.

Am I missing the boat here or does s Optimistic concurrency have it place?

Thanks,
Rob Panosh

 

[Miha Markic]

Hi Rob,

Miha,

I am struggling with optimistic concurrency with our application and don't
think we can use it, let me give you a little bit of background about our
application. We have a Formula Management software package (fancy name for
a receipe program) which has very complex business objects. For example a
Formula business object has a header record and some of the following
supporting objects; composition (ingredients), properties (total fat,
calories, cholesterol, ..), costing, notes, attachments, and auditing.
Currently we are opening our business objects in three modes Add, Edit, and
View. When a user wants to Edit a Formula we are using semaphore locks for
concurrency which gets release explictly or via finalize/dispose. Now once
a user opens a formula in edit mode to make modifications let say they want
to change the amout of an ingredient which in turn will impact the Formula
properties, cost and possibly values in the header record such as the
density of the Formula. Currently we haven't had troubles because once the
user has an object open in edit mode and wants to save we essentially create
a SQL transaction for all the changes and execute againts the database.

If we tried to use optimistic concurency, with the above changes, and let
say another user opens the same formula and makes a change to a different
component thus updating property values, cost and saves before the first
user. Now when the original user tries to save the Formula they would be
rejected because of the second users changes. Now I am going to have users
that get pissed off becuase they have spent the last hour reformulating
their product and can't save because somebody made a change before they
saved.

Am I missing the boat here or does s Optimistic concurrency have it place?

I tend to disagree. Note that this is a matter of philosophy.
Let's say you are doing pessimistic locking (your way).
Now, imagine that a user opens an object of your in edit mode, and remembers
that they serve free lunch in the coffeteria.
After lunch we goes straight home or to a bar . Now, your object is locked
for good. Of course, you can set a timeout but how long should it be?
Imagine optimistic locking (my proposal). In worst case, user is notified
that someone has changed the data in meantime (you can also show him what
data has changed) and you give him/her an option to force save regardless of
change. So you won't have bottlenecks and you won't have to take care of
synchronization.

 

[Rob Panosh]

Miha,

I tend to disagree. Note that this is a matter of philosophy.
Let's say you are doing pessimistic locking (your way).
Now, imagine that a user opens an object of your in edit mode, and remembers
that they serve free lunch in the coffeteria.
After lunch we goes straight home or to a bar . Now, your object is locked
for good.

Imagine optimistic locking (my proposal). In worst case, user is notified
that someone has changed the data in meantime (you can also show him what
data has changed) and you give him/her an option to force save regardless of
change. So you won't have bottlenecks and you won't have to take care of
synchronization.

You make a good point ... I am going to investigate further. Do you have any
suggestions on any good articles explianing
optimistic locking?

Thanks for taking the time...

Cheers,
Rob Panosh

 

[IbrahimMalluf]

Hello Brad

The "Good" weay to impliment optimistic concurrency is entirely
dependant upon your business needs.

The four methods are there becuase there are a myriad of situations that
require different situations. Item 1 is the best solution where the
volitility of the data does not matter, only the latest changes are valid.

Item 3 in the list is the one that will absolutely reject any changes that
colide with another user's changes. This means that you will have to handle
all concurrency collisions in some manner. It requires the least amount of
time and resources next to item 1, which provides no concurrency checking at
all.

Using number 2, All Collumns being checked is required where timestamps
are not an option and you must protect all columns from lost changes. It's
not as efficient as Items 1 & 3 but it works.

Item 4 allows you to automatically merge changes where the changes do not
coinflict with each other. If user A has changed the Size of an order item,
and User B has changed the Quanity of the order item, then both updates will
succeed without user intervention.

The best answer is "what do your requirements demand?"



--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx

 

[IbrahimMalluf]

Hello Rob



I agree with Miha, it is a matter of philosophy. But if you have bought
into NET as your development environment, understand that it strongly pushes
you towards Optimistic updating.



You said:

If we tried to use optimistic concurency, with the above changes, and let
say another user opens the same formula and makes a change to a different
component thus updating property values, cost and saves before the first
user. Now when the original user tries to save the Formula they would be
rejected because of the second users changes. Now I am going to have users
that get pissed off becuase they have spent the last hour reformulating
their product and can't save because somebody made a change before they
saved.

There are options available that will minimize data change collisions in the
above described scenario, and work does not have to be lost at all if it is
critical to your business model. User attitudes can also be assuaged with
some clever solutions to their perceived problems.



First, if the changes are to different components, then using Changed Fields
as your collision checking method will allow both changes to occur without
failure. If there is a collision failure, then Miha's option of presenting
the last user with a comparison of changes and the option to force the
submit the latest changes, or a merge of both changes alleviates the other
aspect of the problem.



These solutions might require a little more development time to work out in
a manner that meets your needs. But they are a one time development cost.
The use of locks in your Pessimistic approach on the other hand (illustrated
by Miha's situational description) leaves your production system open to
many inherent and recurring problems. Even if you are doing row locks you
are going to affect lookups, productivity, and the ultimate risk of that
errant user who is out to lunch (they are more common than most people
realize).




--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx

 

[Angel Saenz-Badillos[MS]]

Rob,
I agree with the advice that Miha has given, it is positively evil to lock a
database while somebody is editing a record for an hour. Optimistic locking
is a very good solution for the database, but it is more complicated to
implement since you have to deal with multiple update issues (notifying
user, showing them changed data, allowing merge/overwrite etc).

Another option that may work for you is to enforce the Multiple read Single
Write model in your data access layer ie. without using database locking.

 

[William \(Bill\) Vaughn]

I tend to agree with the others as I discuss locking (of all types) in my
books and articles. Yes, optimistic locking is not a good fit for all
situations and there are specific cases where pessimistic locking is the
best choice. However, consider that these situations are few and far
between. Most use timeouts that automatically release the locks after a
given time and permit other operations to go forward. There is little
support for pessimistic locks in .NET, but I show how to do it in a couple
of articles and conference sessions. But I emphasize that you really need to
deal with a whole set of issues with pessimistic locking. When working with
Windows forms apps (the only suitable client for pessimistic locks), you are
basically managing server state remotely. If the connection is dropped, the
server will cleanup for you, but if the user does not proceed to clear the
lock by using the application, the code itself must clear the lock. I don't
recommend this approach (ever) without automatic lock release code.

Frankly, I usually design systems that prevent access to shared rows by the
way the application itself is architected. For example, only permitting a
sales person to access (for update) the clients that this salesperson is
assigned.

hth.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Brad Williams]

Another option that may work for you is to enforce the Multiple read Single
Write model in your data access layer ie. without using database locking.

Thanks, Angel. Which article discusses this DAL model?

Brad Williams

 

[Brad Williams]

Frankly, I usually design systems that prevent access to shared rows by
the

way the application itself is architected. For example, only permitting a
sales person to access (for update) the clients that this salesperson is
assigned.

This is a good point Bill, whenever the architecture can be done this way it
evaporates the whole sticky issue. I have been lucky and always been able
to do similar in past projects, so optimistic concurrency in DAL is new to
me. I will get to Powell's when the snow clears and check out your book.

Brad Williams

 

[Rob Panosh]

Bill,

We are not using pessimistic locking. When a user wants to open one of our
objects, let say a Formula Object, the business object checks for the
presence of a row by objectId in our fmSemlock table, if the row exists the
user will not be able to open the Formula object for edit (only view mode).
This row will be remove from the fmSemlock table when the user closes
business object. In our application this business object is tied to a Form
and we have logic built in the automatically close the form if there is not
activitly for a specified amount of time ( which is user configurable ) thus
closing the business object which releases the lock.

The reason I don't think we can use optimistic locking in our application is
because of the complexity of our business objects. For example our formula
business objects have components (ingredients that make up the formula) and
properties (items you see on nutritional statement) which are items such as
total fat, cholesterol, ... you get the picture ... there could be 50+
properties on a formula. Now user A opens formula "Blueberry Muffin" and
makes a change to a component, let say Sugar, this change can/will affect
many properties (which the business object layer calculates using the
composition). Now while all this is taking place user B opens the formula
"Blueberry Muffin" for edit and changes component Milkfat which will affect
many of the same properties, which would calculate incorrectly because
doesn't know about the Sugar component change user A made. This is just one
issue at it simpliest form.

The only way I could see getting optimistic locking to work is to re-fetch
the data, only things we didn't change, to the business object from the
database before performing the save or any property calculations and we
still have a very small window for error.

Regards,
Rob Panosh

 

[Miha Markic]

Miha,

Can't happen we supply a ball and chaing with our software ... <grin>.

regardless
You make a good point ... I am going to investigate further. Do you have any
suggestions on any good articles explianing
optimistic locking?

Sorry, don't know. But the entire ado.net is build around optimistic
locking - there should be some info in help files.

 

[Angel Saenz-Badillos[MS]]

I don't have a good link sorry, you can get some info from:
http://www.cise.ufl.edu/research/ParallelPatterns/PatternLanguage/FindingConcurrency/DataSharing.htm .

I am trying to say exactly what Bill mentioned in parallel to this post,
instead of relaying on database locks you can build your application to
enforce the behavior that you want. IMO persistent locks on the database are
evil.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.