Concurrency (recap and idea).....

With respect to my (now not so recent) thread on Concurrency, I would like
to run my idea past you gurus to see if its a runner. First, a brief recap:

I have a single user system (one user, one copy of the software, one copy of
MSDE, one machine) that I wish to convert into a multi-user/single database
networked system. The problem I had was that a lot of information is
fetched from the database and cached in the client program (the program
implements a tree structure, similar to a file system, and each of the nodes
in the system has properties). The concurrency issue revolved around having
multiple users updating these properties and possibly able to modify the
tree structure and there being no way to notify the other clients that they
need to refresh their data structures. Consider the system to be similar to
VSS to look at (and in VSS, people can make modifications to the tree
structure also!).

Ok, one of the suggestions was time stamping each record. So, when one user
modifies the record, a second user can detect whether their timestamp is
different and thus whether or not their update is invalid (and also whether
or not the client program needs to refresh the properties of the given
node). How about instead of a timestamp I simply use a reference counter.
ie. an integer that increments every time the record is modified (same
principle). I don't need to know when it was changed, just that the two
reference counters are different between when I fetched and when I am
updating the record.

Secondly, I think I have to distinguish between a change in properties and a
change in structure. For example, User A doesn't need to know about a
change in properties for a node he is not currently looking at. However,
that same user will want to be told about any change to the overall tree
structure. So, I was thinking that any operations involving modifications
to the tree structure should set a "structure changed" flag in the database
(increment a counter). After any operation is performed, the client
compares its "changed" flag to the database value to see if it needs to
reload the tree structure.

Do you think this is workable?

Thanks.

RobinRegarding your first question, this is known as optimistic concurrency and
is usually implemented with a timestamp data type, not to be confused with a
datetime column. The timestamp datatype contains a binary value unrelated
to date/time and is automatically updated by SQL Server whenever the row is
updated. Consequently, you can use a technique like:

UPDATE MyTable
SET MyData = @.NewMyDataValue
WHERE MyID = @.MyID AND
MyTimestamp = @.OldMyTimestamp
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR ('Data has been deleted or updated by another user', 16, 1)
END

You can use a similar technique to detect structure changes to your tree.
Save the current timestamp value in a program variable and check
periodically as desired.

SELECT LastUpdatedByUser, LastUpdateTime, TreeTimeStamp
FROM MyTrees
WHERE MyTreeID = 1

The above table can be updated as follows. You can also include this in
triggers or procs for convenience, depending on how you perform data
manipulation in your app.

UPDATE MyTrees
SET LastUpdatedByUser = SUSER_SNAME(),
LastUpdateTime = CURRENT_TIMESTAMP
WHERE MyTreeID = 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:cp6lcq$ns4$1$8302bc10@.news.demon.co.uk...
> With respect to my (now not so recent) thread on Concurrency, I would like
> to run my idea past you gurus to see if its a runner. First, a brief
> recap:
> I have a single user system (one user, one copy of the software, one copy
> of MSDE, one machine) that I wish to convert into a multi-user/single
> database networked system. The problem I had was that a lot of
> information is fetched from the database and cached in the client program
> (the program implements a tree structure, similar to a file system, and
> each of the nodes in the system has properties). The concurrency issue
> revolved around having multiple users updating these properties and
> possibly able to modify the tree structure and there being no way to
> notify the other clients that they need to refresh their data structures.
> Consider the system to be similar to VSS to look at (and in VSS, people
> can make modifications to the tree structure also!).
> Ok, one of the suggestions was time stamping each record. So, when one
> user modifies the record, a second user can detect whether their timestamp
> is different and thus whether or not their update is invalid (and also
> whether or not the client program needs to refresh the properties of the
> given node). How about instead of a timestamp I simply use a reference
> counter. ie. an integer that increments every time the record is modified
> (same principle). I don't need to know when it was changed, just that the
> two reference counters are different between when I fetched and when I am
> updating the record.
> Secondly, I think I have to distinguish between a change in properties and
> a change in structure. For example, User A doesn't need to know about a
> change in properties for a node he is not currently looking at. However,
> that same user will want to be told about any change to the overall tree
> structure. So, I was thinking that any operations involving modifications
> to the tree structure should set a "structure changed" flag in the
> database (increment a counter). After any operation is performed, the
> client compares its "changed" flag to the database value to see if it
> needs to reload the tree structure.
>
> Do you think this is workable?
>
> Thanks.
>
> Robin|||Thanks, this is useful stuff.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:rHCtd.38154$bP2.3402@.newssvr12.news.prodigy.c om...
> Regarding your first question, this is known as optimistic concurrency and
> is usually implemented with a timestamp data type, not to be confused with
> a datetime column. The timestamp datatype contains a binary value
> unrelated to date/time and is automatically updated by SQL Server whenever
> the row is updated. Consequently, you can use a technique like:
> UPDATE MyTable
> SET MyData = @.NewMyDataValue
> WHERE MyID = @.MyID AND
> MyTimestamp = @.OldMyTimestamp
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR ('Data has been deleted or updated by another user', 16, 1)
> END
> You can use a similar technique to detect structure changes to your tree.
> Save the current timestamp value in a program variable and check
> periodically as desired.
> SELECT LastUpdatedByUser, LastUpdateTime, TreeTimeStamp
> FROM MyTrees
> WHERE MyTreeID = 1
> The above table can be updated as follows. You can also include this in
> triggers or procs for convenience, depending on how you perform data
> manipulation in your app.
> UPDATE MyTrees
> SET LastUpdatedByUser = SUSER_SNAME(),
> LastUpdateTime = CURRENT_TIMESTAMP
> WHERE MyTreeID = 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:cp6lcq$ns4$1$8302bc10@.news.demon.co.uk...
>> With respect to my (now not so recent) thread on Concurrency, I would
>> like to run my idea past you gurus to see if its a runner. First, a
>> brief recap:
>>
>> I have a single user system (one user, one copy of the software, one copy
>> of MSDE, one machine) that I wish to convert into a multi-user/single
>> database networked system. The problem I had was that a lot of
>> information is fetched from the database and cached in the client program
>> (the program implements a tree structure, similar to a file system, and
>> each of the nodes in the system has properties). The concurrency issue
>> revolved around having multiple users updating these properties and
>> possibly able to modify the tree structure and there being no way to
>> notify the other clients that they need to refresh their data structures.
>> Consider the system to be similar to VSS to look at (and in VSS, people
>> can make modifications to the tree structure also!).
>>
>> Ok, one of the suggestions was time stamping each record. So, when one
>> user modifies the record, a second user can detect whether their
>> timestamp is different and thus whether or not their update is invalid
>> (and also whether or not the client program needs to refresh the
>> properties of the given node). How about instead of a timestamp I simply
>> use a reference counter. ie. an integer that increments every time the
>> record is modified (same principle). I don't need to know when it was
>> changed, just that the two reference counters are different between when
>> I fetched and when I am updating the record.
>>
>> Secondly, I think I have to distinguish between a change in properties
>> and a change in structure. For example, User A doesn't need to know
>> about a change in properties for a node he is not currently looking at.
>> However, that same user will want to be told about any change to the
>> overall tree structure. So, I was thinking that any operations involving
>> modifications to the tree structure should set a "structure changed" flag
>> in the database (increment a counter). After any operation is performed,
>> the client compares its "changed" flag to the database value to see if it
>> needs to reload the tree structure.
>>
>>
>> Do you think this is workable?
>>
>>
>> Thanks.
>>
>>
>>
>> Robin
>>