Optimistic Concurrency

[Guest]

It could be me, but the check box for optimistic concurrency looks like it
should be called pessimistic concurrency and seems completely broken. To me
optimistic concurrency say's I'm never going to have a concurrency problem,
last save wins (which is the default). When you check the box, it say's I'm
only going to update the record if it hasn't changed on you. That being
said, it appears that checking the box will only work if you never allow a
null value in your tables?

It generates an update statement such as the following:

UPDATE [Test] SET [test] = @test WHERE [id] = @original_id AND [test] =
@original_test

The problem is if test or original_test are ever null, this statement will
never update data. If you have 40 fields, non of them can be null? Perhaps
what they mean by optimistic, is they're optimistic you'll never need a null
column?

Does anyone have a simple work around for this?
How do you tell if the update didn't work? (Seems it would be good to
indicate that?)

 

[Cor Ligthert [MVP]]

Larry,

Optimistic concurrency says that if a database is populated with a large
amount of information about an ententity, the change that two people are
updating (see updating not reading) is very low. Therefore is than the idea
that you can better check it afterwards if that has happen than lock all the
data.

However, you should almost forever check for optimistic concurrency errors
and handle those. There can be situations that you can overwrite the last,
however I will see that as the exception. Think that your process is base +
change = new, than it is absolute impossible. This is by the way the oldest
trick to swindle banks who don't check concurrency and therefore is that
example always taken.

There are many ways as a solution to concurrency problems. One of those is
making only one person owner of the data and that one is than the only one
that may change, but that is not forever possible.

Be aware that you can show concurrency errors direct record by record, but
as well as in this sample on our website for a complete datatable using the
errorstate in a datarow.

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this helps,

Cor

 

[Guest]

Thanks, that looks very useful. I'm still wondering if there's a work-around
for the NULL field problem. i.e. if you check optimistic concurrency you get
an update statement of the form
UPDATE table SET fields=newValues WHERE id=@id AND fields=@oldValues

The problem is that there will always be an "error" if any field is ever
allowed to be NULL.

I'm hoping your solution will show the error, the documentation says it will
throw a DBException error but it doesn't in this case, it simply updates 0
rows because the expression will always become:

UPDATE table SET fields=newValues WHERE NULL

Which always will match no records.