Optimistic Concurrency - how? - net 2.0

[Rolf Welskes]

Hello,
I have the situation that many users on the same db , table have access and
make changes.

Ok for this there is optimistic concurrency.

So I am able to check for cocurrency and get (in the moment) a messagebox,
which says
that there is a concurrency violation.

I have searched in MSDN and have many books about ado net 2.0,
but how to work REALY with optimistic concurrency no one says,
only in msdn is a small article with the absolute minimum to know.

Means, I can check and see that a concurrency violation is present,
but what now.

The user gets a messagebox: Data are not saved - Error.
No normal user for example a book keeper knows what to do.

I do no know how to hande such situations in details,
for example show the data or give a hint to do what ever.

It would be very helpful to have some full working examples for
a database which implements optimistic concurrency.

If there is anything, please send me links to it.

Thank you for any help.
Rolf Welskes

 

[William \(Bill\) Vaughn]

I don't know what books you've read but there are those that can supply
enough information to permit you to build your own concurrency handler--mine
is one of those. I don't know that anyone would be willing to just "give"
you a complete solution--not even a hungry author. That's because these are
the mother's milk of many custom applications. While my books discuss the
issues involved and illustrate a number of ways to address the issues, it
(like many other books) does not provide a canned solution. One of the
(many) problems has more to do with how you approach concurrency. I for one
don't recommend building applications whose data access schemes are designed
to "collide" and require code to pick up the pieces after the collision.
It's like building automobile intersections without traffic signals but with
strategically placed ambulances and wreckers to clean up the mess after the
expected collisions.

I suggest setting up a data access scheme where two users are not at all
likely to address the same row at the same time. If one looks at many
designs, it turns out that there are fairly easily managed ways to avoid
collisions.

In any case, the basics involve deciding what should happen when two (or
more) clients change the same row at the same time. Generally there are
several approaches (that I discuss in my book) that either let the last
change go through (the brute force method), the "timid" method that just
accepts any changes made by other users or the arbitration method that tries
to figure out which update to accept. The first two are easy to
implement--one leaves the concurrency check off the WHERE clause in the
UPDATE, the second does the same but reads back the row to determine the
current state. The last approach is very hard to implement with systems that
have to scale beyond a few users as it often requires the user to determine
how to proceed. This can lock up a system in no-time if you aren't
careful--and even if you are...

Once you've started to look at concurrency be sure to study replication and
synchronization that handles concurrency in an entirely different way.

hth



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
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.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Mary Chipman [MSFT]]

One reason you don't see msdn content on this subject is because most
databases, such as SQL Server, implement optimistic concurrency by
default. One way to avoid concurrency violations is to normalize your
database schema so that no two users ever edit the same row at the
same time. Combining a sound ER design with explicit transactions
pretty much eliminates the problem. Custom solutions to fix
concurrency after the fact get complicated and expensive, as Bill
pointed out.

-Mary

 

[Guest]

Mary,

I'm confused by your reply.

How does SQL Server implement optimistic concurrency in a way that helps
with the disconnected architecture of ADO.Net?

How does normalization prevent 2 users from editing the same row at the same
time?

As for explicit transactions, isn't that pessimistic concurrency?

Kerry Moorman

 

[Cor Ligthert [MVP]]

Rolf,

I have the same idea about this as you. That is not very helpfull, in the
next generations of ADONET will this have more attention. That is as well
not very helpfull, however, being busy with this you can have a look at the
rowstate as soon as there is a vialation. Another thing you can do is to use
the transaction

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx

or/and the

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.continueupdateonerror.aspx

just some simple information,

Cor

 

[Cor Ligthert [MVP]]

Mary,

In the line of Bill, optimistic concurency is standard implemented however
not the action that will be done, in a short piece of communication with
Pablo (as well sometimes active in this newsgroup) he told he will take his
attention to that.

In my idea are there 3 ways of actions that can be taken
Ignore, not so crazy when it are changes of by instance names, which would
be by another time of suplying have had no violence at all and the same
effect.
Give back with the violences and not changed data and ask the user to take
actions (this goes perfect with a datagrid).
Roll back and ask the user to take his actions on the completely not done
data change.

If there is more, I will be glad to see them.

Cor

 

[Mary Chipman [MSFT]]

Sorry for the confusion. SQL Server guarantees transactional
consistency (a la the ACID test). By default if a row has been changed
since it was fetched, then SQLS will not allow a blind update (which
is what the DataAdapter handles). In a disconnected architecture such
as ADO.NET, special care needs to be taken to ensure that concurrency
violations, blocking and deadlocking do not occur. The best medicine
is always preventive medicine. If your table architecture is such that
no two users edit the same record at the same time, then there is
never a problem. By the same token, if the E-R design is a mess, then
you have a lot more work to do. Implementing explicit transactions
does lock resources until all units of work are complete, but so do
implicit (autocommit) transactions. Explicit transactions also
guarantee that you (the developer) always control the outcome. They
are the most efficient way of updating data as long as you keep them
short - no user interaction, execute on the server, return
success/failure information to the client so that client code can
branch accordingly.

-Mary

 

[Mary Chipman [MSFT]]

Yes, if I understand you correctly, that's about all you can do.
Ignore, resubmit, or let the user decide.

-Mary