| Groups | Blog | Home |
dotnet ado.net : Optimistic Concurrency - how? - net 2.0
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) ----------------------------------------------------------------------------------------------------------------------- [quoted text, click to view] "Rolf Welskes" <rolf@nospam.nospam> wrote in message news:%23Ih8a3QjHHA.4520@TK2MSFTNGP02.phx.gbl... > 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 > >
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
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 On Thu, 3 May 2007 01:01:41 +0200, "Rolf Welskes" <rolf@nospam.nospam> [quoted text, click to view] wrote:
>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
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 [quoted text, click to view] "Mary Chipman [MSFT]" wrote:
> 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 > > On Thu, 3 May 2007 01:01:41 +0200, "Rolf Welskes" <rolf@nospam.nospam> > wrote: > > >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 > >
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 "Rolf Welskes" <rolf@nospam.nospam> schreef in bericht news:%23Ih8a3QjHHA.4520@TK2MSFTNGP02.phx.gbl... [quoted text, click to view] > 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 > >
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]" <mchip@online.microsoft.com> schreef in bericht news:iq4k339p2r0ercpolksu0uu3tjejk2lue8@4ax.com... [quoted text, click to view] > 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 > > On Thu, 3 May 2007 01:01:41 +0200, "Rolf Welskes" <rolf@nospam.nospam> > wrote: > >>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 >>
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 On Thu, 3 May 2007 15:08:01 -0700, Kerry Moorman [quoted text, click to view] <KerryMoorman@discussions.microsoft.com> wrote:
>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 > >"Mary Chipman [MSFT]" wrote: > >> 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 >> >> On Thu, 3 May 2007 01:01:41 +0200, "Rolf Welskes" <rolf@nospam.nospam> >> wrote: >> >> >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 >> >
Yes, if I understand you correctly, that's about all you can do.
Ignore, resubmit, or let the user decide. -Mary On Fri, 4 May 2007 07:46:39 +0200, "Cor Ligthert [MVP]" [quoted text, click to view] <notmyfirstname@planet.nl> wrote:
>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]" <mchip@online.microsoft.com> schreef in bericht >news:iq4k339p2r0ercpolksu0uu3tjejk2lue8@4ax.com... >> 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 >> >> On Thu, 3 May 2007 01:01:41 +0200, "Rolf Welskes" <rolf@nospam.nospam> >> wrote: >> >>>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 >>>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |