Re: Concurrency Help
From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 02/08/05
- Next message: David Browne: "Re: ORA-06550 error in .net with C#"
- Previous message: bgundas_at_hotmail.com: "Re: speed problems accessing large MS Access database"
- In reply to: epigram: "Re: Concurrency Help"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 8 Feb 2005 07:44:11 -0800
The timestamp column is changed whenever ANY data in the row is
changed--right.
-- ____________________________________ William (Bill) Vaughn Author, Mentor, Consultant Microsoft MVP 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. __________________________________ "epigram" <nospam@spammy.com> wrote in message news:1107777199.c854c664804c6543bdcdf2d6fa0957e3@bubbanews... > This failure is desired if we're taking the approach that if any value in > the row has been updated since our last read, we want the operation to > fail, correct? > > Thanks for the clarification! > > "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message > news:OnXboQODFHA.1932@TK2MSFTNGP14.phx.gbl... >> An UPDATE operation with a focused WHERE clause is an atomic operation. >> The DBMS engine will prevent two operations to be performed on the same >> row at the same time. However, the second operation will fail if you're >> comparing the original timestamp with the current timestamp in the WHERE >> clause. >> >> -- >> ____________________________________ >> William (Bill) Vaughn >> Author, Mentor, Consultant >> Microsoft MVP >> 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. >> __________________________________ >> >> "epigram" <nospam@nospammy.com> wrote in message >> news:1107703753.0ed7a6836b2af2a1cf7a2764a05f3afc@bubbanews... >>> At this point, I guess my question really boils down to a question about >>> databases. This may be one of the ACID properties of a DBMS (e.g. SQL >>> Server 2000) that I am just not clear on. I want to know if a database, >>> when executing a SQL UPDATE query, will not allow any data referenced in >>> the >>> WHERE clause to be read or manipulated before the UPDATE query finishes >>> executing and the data is committed. >>> >>> The reason I ask is because the type of UPDATE queries that are used in >>> the >>> ADO.NET concurrency examples are typically comparing one or more columns >>> in >>> the dataset (the data that was originally in the database) to what is >>> currently in the database. For example I have seen examples such as >>> this: >>> >>> UPDATE employees SET firstname = someStringVar1, lastname = >>> someStringVar2 >>> WHERE timedatecol = origTimeDateVar AND theprimarykey = thePrimaryKeyVar >>> >>> I am curious what happens if two clients execute this same query at the >>> same >>> time. I know the database probably doesn't carry a query out this way, >>> but >>> the following type of logical execution order is what concerns me: >>> >>> client 1: WHERE timedatecol = origTimeDateVar AND theprimarykey = >>> thePrimaryKeyVar >>> client 2: WHERE timedatecol = origTimeDateVar AND theprimarykey = >>> thePrimaryKeyVar >>> client 2: UPDATE employees SET firstname = someStringVar1, lastname = >>> someStringVar2 >>> client 1: UPDATE employees SET firstname = someStringVar1, lastname = >>> someStringVar2 >>> >>> Is it possible for this type of logical execution to happen? If so, >>> client >>> 1 would overwrite client 2's changes to the db. It seems for this type >>> of >>> concurrency model to work, the database would have to serialize similar >>> queries to protect references to columns in WHERE clauses. Any help >>> making >>> this clear to me would be greatly appreciated. >>> >>> Thanks. >>> >>> >>> "William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message >>> news:ux0yPdwCFHA.3740@TK2MSFTNGP09.phx.gbl... >>>> Update strategies are really dependant on a number of factors. I'm a >>>> fan >>> of >>>> using a TimeStamp column (which does not contain a time/date value but >>>> a >>>> number that's incremented when the row is changed). This technique is >>>> easy >>>> and foolproof. All you need to do to implement it is to add a TimeStamp >>>> datatype column to your table(s)--the system takes care of the rest. >>>> The >>>> problem is that TimeStamp concurrency is not supported by ADO.NET >>>> wizards--you'll have to create the UpdateCommand yourself. (Yes, it was >>>> an >>>> option in ADO classic using the Update Criteria property). >>>> No, all of these techniques we've been discussing use "optimistic" >>> locking. >>>> In this case we assume that the row read from the server will not be >>> changed >>>> by another user. In many database/application designs this can be >>>> assured >>> by >>>> the "ownership" of the data row. For example, only client/user X has >>> rights >>>> to change row Y. If this can't work for you, you'll have to be prepared >>>> to >>>> handle collisions when more than one client/user changes the row before >>>> another. These routines can be somewhat complex and trouble-prone but >>>> have >>>> been implemented many, many times over the years. >>>> Another approach is to use pessimistic locks. This assumes your client >>> opens >>>> and holds a connection on the server for the duration of the changes. >>>> This >>>> is not practical with ASP applications and can limit scalability on >>>> client/server applications.Even pessimistic locks don't prevent others >>> from >>>> reading the data row before you change it or prevent another user from >>>> trying to change it. As I discuss in my book(s), there are several >>>> other >>>> strategies to manage concurrency including trying to mark rows as >>>> "checked >>>> out", but these have problems of their own. >>>> Generally, I don't recommend using client/side transactions or locking >>>> one >>>> or more rows while the user decides what to change (or not). This >>>> approach >>>> causes a number of other issues that can cripple scalability and lock >>>> the >>>> entire table (or worse). >>>> >>>> hth >>>> >>>> -- >>>> ____________________________________ >>>> William (Bill) Vaughn >>>> Author, Mentor, Consultant >>>> Microsoft MVP >>>> 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. >>>> __________________________________ >>>> >>>> "epigram" <nospam@spammy.com> wrote in message >>>> news:1107553349.317b86fb43e93f47ba10d55b20076569@bubbanews... >>>> > I'm trying to decide on a concurrency strategy with my asp.net >>>> > application. I've seen examples where a dataset is used and the >>>> > update >>>> > query's WHERE clause basically checks to see if the original data >>>> > (from >>>> > the original read) equals the data in the database. I've also seen >>>> > mention of using a date stamp which I assume would be used via the >>>> > same >>>> > technique. Then the operation is wrapped inside a transaction. >>>> > >>>> > My question is even though these checks are being made in the >>>> > dataset's >>>> > update query WHERE clause, what would prevent a piece of data from >>>> > being >>>> > updated (by another thread/client) between the time the check is done >>>> > by >>>> > the WHERE clause and the transaction is complete? The transaction >>> doesn't >>>> > guarantee the update to be atomic does it? It just seems to me you'd >>> have >>>> > to lock a row to use this type of technique. Any pointers to good >>>> > articles on this would be much appreciated. >>>> > >>>> > Thanks! >>>> > >>>> >>>> >>> >>> >> >> > >
- Next message: David Browne: "Re: ORA-06550 error in .net with C#"
- Previous message: bgundas_at_hotmail.com: "Re: speed problems accessing large MS Access database"
- In reply to: epigram: "Re: Concurrency Help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
