Re: Concurrency Help

From: epigram (nospam_at_nospammy.com)
Date: 02/06/05 

Date: Sun, 6 Feb 2005 10:39:37 -0500

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!
> >
>
>

Relevant Pages
Loading