Concurrency Help

[epigram]

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!

 

[William \(Bill\) Vaughn]

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]

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]

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]

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]

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.
__________________________________

 

[Guest]

Hi,

I want to know whether it is possible to add callback functions when data is
updated.For eg: suppose a user has data in the dataset and some other user
modifies this data in the orginal database.Is there a mechanism through
which the database sends a notification to the application that this database
has been modified. I know that we can add event handlers for row
updation,deletion etc...but these event handlers can be added only to the
dataset or datatable in memory but not in the orginal database.If such
mechanism is present then i think concurrency control can be simplified to a
great extent. The application needs to just handle row updating and row
deleting events and take necesary action when these events are raised.

Thanks