"Last write wins" concurrency in ADO.NET

[Oenone]

I'm working on a VB.NET app which uses ADO.NET to interact with a database.

Currently I have a problem with concurrent updates due to the optimistic
locking used by ADO.NET. I wish to be able to modify the data updates so
that they only check the Primary Key columns when updating the database (as
described in option 1 here:
http://www.eggheadcafe.com/articles/20050719.asp) in order to get "last
write wins" functionality, instead of checking all of the data columns (as
ADO.NET appears to do by default).

Is there a property of the DataAdapter or Command objects that I can modify
to implement this? Or do I have to build the Command SQL myself?

My thanks in advance,

 

[Sahil Malik [MVP]]

In .NET 1.1 you have to write the commands yourself. If you are checking PK
only, you can easily write up logic to query metadata and cook up such
queries programatically so you don't have to keep rewriting them. I would
however recommend caching such commands for 2 reasons a) querying metadata
is expensive and b) query plans shouldn't keep changing.

In .NET 2.0 you can use CommandBuilder.ConflictResolution to specify PK only
updates.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[AMDRIT]

There are a few things you can do to enure concurrency ( This is not the
only way to achieve it, its just my way.)

First, add a LastModified (datetime) column to the table and update it
everytime you modify that row.

Now you would only need to check PKey + Lastprocessed

Second, use transactions and specify the level of isolation that you desire.
This will allow for queuing of data manipulation and everyone will have to
wait in line to update their data.

This doesn't help reduce "Hot Spots" (Massive data manipulation on the same
datapage and queuing may generate longer wait times for the users).

Take a look at the way IssueVision (www.windowsforms.com) managed
concurrency. I think it is a great approach, only there weren't any
transactions. One thing that it does is notify you when a record was
modified while you had it checked out and presents a working solution to
resolve the concurrency issue.

 

[William \(Bill\) Vaughn]

No there is no settable property as there is in ADO classic, (as I said in
an earlier post today) the DML commands that ADO.NET's CommandBuilder (that
can be called manually or called by the VS wizards) use a "check all
columns" concurrency approach.
You'll need to write these UPDATE commands yourself and set the
UpdateCommand CommandText to these hand-tuned SQL statements.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[Sahil Malik [MVP]]

Hey Bill,

Check out CommandBuilder.ConflictResolution in .NET 2.0.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

No there is no settable property as there is in ADO classic, (as I said in
an earlier post today) the DML commands that ADO.NET's CommandBuilder (that
can be called manually or called by the VS wizards) use a "check all
columns" concurrency approach.
You'll need to write these UPDATE commands yourself and set the
UpdateCommand CommandText to these hand-tuned SQL statements.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[William \(Bill\) Vaughn]

Yes... it's an improvement if it's actually working once they ship. They
have pulled and replaced this so many times I'm uncomfortable telling anyone
about it before I see the RTM code.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

Hey Bill,

Check out CommandBuilder.ConflictResolution in .NET 2.0.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Sahil Malik [MVP]]

I have seen it's name changing over and over again, but I think it should
ship. I mean, it works perfectly in Beta2 with Sql2k/2k5/Oracle/Access
(atleast these are the ones that I tested myself). For Oracle however the
timestamp flavor doesn't work (understandably so).

Have you seen specific information that may suggest that it might not be
included in RTM?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Yes... it's an improvement if it's actually working once they ship. They
have pulled and replaced this so many times I'm uncomfortable telling anyone
about it before I see the RTM code.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

Hey Bill,

Check out CommandBuilder.ConflictResolution in .NET 2.0.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------

--

 

[William \(Bill\) Vaughn]

I'll let you know once I get the new RC builds working.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
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.
__________________________________

 

[Oenone]

In .NET 1.1 you have to write the commands yourself. If you are
checking PK only, you can easily write up logic to query metadata and
cook up such queries programatically so you don't have to keep
rewriting them. I would however recommend caching such commands for 2
reasons a) querying metadata is expensive and b) query plans
shouldn't keep changing.

Many thanks to you and everyone else that replied -- your answers were
extremely useful even if they weren't quite what I wanted to hear.

I'd appreciate a little more advice on the best way to implement this. I
need to write this in a completely generic fashion so that it'll work with
any (single-table) SELECT statement that is passed, just like the
CommandBuilder itself. I can't use specific hand-coded updates for each
table, nor can I use timestamp columns, because the structure of our
database is flexible and differs entirely from one application to another.

I can see that at the time I create the DataAdapter I could easily extract
the name of the table in the SELECT statement and at that stage I could
build INSERT, UPDATE and DELETE commands for the adapter. However, for
UPDATE commands I'd like to be able to only modify the columns that have
actually changed, and this may differ for each row that is updated.

Can I therefore hook into the RowUpdating event of the adapter and change
the SQL statement in the Update command for each row that updates?

How can I identify which values have actually changed? I know that the
DataRow stores the Original and Current versions of the row and this is
enough for me to identify the changes, but how do I obtain the Original
value for a column within a DataRow?

And finally, are there any examples of this available to look at on the net?

In .NET 2.0 you can use CommandBuilder.ConflictResolution to specify
PK only updates.

Ah, if only... I need to wait until it goes into production before I can use
it unfortunately. Hopefully not too much longer now.

Thanks again,

 

[Sahil Malik [MVP]]

Oenone,

Well ... there are many things to consider here, many more than I can type
up in a 100-200 word answer.

First of all, how often does your table structure change? This is important
because querying the database for it's structure is not only an expensive
task, it is also a fail-prone task if the DBA restricts your access. These
two issues will not happen in SQL2k5, but I don't think you are using that
yet. I would try very hard to cache commands rather than keep regenerating
them everytime. But cache leads to a problem of "out of data cache" or
"stale data" - in your case out of data queries.

Secondly, you could generate command specific to only the data you are
changing - your intention I believe is better performance (why update if it
hasn't changed). But it may misfire against you because the query plans
won't be cached because you will truly be working with 100% dynamic SQL, and
that may lead to even lower performance.

Thirdly, this approach will work only for single table updates - multiple
table updates, especially with transactional and concurrency support needs a
more hands-on approach.

Regards your question of how to obtain the Original value, you can use
DataViewRowState to fetch the "ModifiedOriginal" version to acheive that.
DataRows will maintain data upto one version back, this behavior is
extensible by hooking into various events but it isn't a trivial task.

There must be examples of this available on the net, but most of them are
peicemeal - incomplete information in a multitude of articles. I can't think
of one good source at this time, and I doubt one may exist for exactly what
you need. What you really need is read a book cover to cover. My upcoming
book explains updating data specifically in chapters 9,10 & 11. After having
read through mutliple examples with various nuances you should be able to
come up with your own solution.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx