Concurrency (recap and idea).....

[Robin Tucker]

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

[Dan Guzman]

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 LastUpdatedByUs er, 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 LastUpdatedByUs er = SUSER_SNAME(),
LastUpdateTime = CURRENT_TIMESTA MP
WHERE MyTreeID = 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cp******** ***********@new s.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

[Robin Tucker]

Thanks, this is useful stuff.

"Dan Guzman" <gu******@nospa m-online.sbcgloba l.net> wrote in message
news:rH******** **********@news svr12.news.prod igy.com...

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 LastUpdatedByUs er, 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 LastUpdatedByUs er = SUSER_SNAME(),
LastUpdateTime = CURRENT_TIMESTA MP
WHERE MyTreeID = 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cp******** ***********@new s.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