Technique for concurrent access?

[Robin Tucker]

I've been asked to turn our single-user database system into a multi-user
system. At present, each user has a copy of the MSDE on their desktop
machine and uses our program to access it. In future, we would like to
centralise our MSDE instance and allow multiple users to access it. In
order to facilitate this, we are going to only allow one user write access
to the system at a time (I know, its a kludge, but the system was never
designed for multiple users in the first place).

I have a single, simple question this being the case: can I update a single
"read-only" bit field in a table of the database in order to flag to other
users that the system is in read-only mode in a way that avoids concurrency
issues? ie. does an "UPDATE" query lock and unlock? ( I suspect the answer
is yes! ). If anyone else has experience of these things, I would also
appreciate some tips on how best to proceed.

Thanks
Robin

[John Bell]

Hi

You don't say how your application will not cope with multiple users. SQL
server is a muti-user environment therefore you may be better writing
transaction handling into your application than a single-user "cludge".

If you use the table based method you will almost invariably have to also
write a reset option.
Alternatively you may want to do this within the application for example
using as using a mutex in .NET or the application object in ASP.

John

"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:co*******************@news.demon.co.uk...

I've been asked to turn our single-user database system into a multi-user
system. At present, each user has a copy of the MSDE on their desktop
machine and uses our program to access it. In future, we would like to
centralise our MSDE instance and allow multiple users to access it. In
order to facilitate this, we are going to only allow one user write access
to the system at a time (I know, its a kludge, but the system was never
designed for multiple users in the first place).

I have a single, simple question this being the case: can I update a
single "read-only" bit field in a table of the database in order to flag
to other users that the system is in read-only mode in a way that avoids
concurrency issues? ie. does an "UPDATE" query lock and unlock? ( I
suspect the answer is yes! ). If anyone else has experience of these
things, I would also appreciate some tips on how best to proceed.

Thanks
Robin

[Erland Sommarskog]

Robin Tucker (id*************************@reallyidont.com) writes:

I've been asked to turn our single-user database system into a multi-user
system. At present, each user has a copy of the MSDE on their desktop
machine and uses our program to access it. In future, we would like to
centralise our MSDE instance and allow multiple users to access it. In
order to facilitate this, we are going to only allow one user write access
to the system at a time (I know, its a kludge, but the system was never
designed for multiple users in the first place).

I have a single, simple question this being the case: can I update a
single "read-only" bit field in a table of the database in order to flag
to other users that the system is in read-only mode in a way that avoids
concurrency issues? ie. does an "UPDATE" query lock and unlock? ( I
suspect the answer is yes! ). If anyone else has experience of these
things, I would also appreciate some tips on how best to proceed.

No, there is no built-in support for this, of the simple reason that this
is a funny thing to do. You can set a database in single-user mode, but
it means single-user. And that is when you do things like restoring the
database and the like.

You would have to roll your own to do this. Have a separate table where
you keep track of who is updating now. You could then have two different
application roles, and a user would be admitted into a reader role or
a writer role.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Robin Tucker]

Although I am using transaction processing at present and I understand that
anything in the brackets of the transaction is "unitary" in nature and
either will or will not be applied in its entirety, there still remains the
issue of updating the other clients when a change has taken place in the
database. I assume there is no "call back mechanism" I can use to keep the
clients all synchronised? ie. I won't know when another client has accessed
the database unless I perform some kind of poll and update myself if they
have.

"John Bell" <jb************@hotmail.com> wrote in message
news:41***********************@news.easynet.co.uk. ..

Hi

You don't say how your application will not cope with multiple users. SQL
server is a muti-user environment therefore you may be better writing
transaction handling into your application than a single-user "cludge".

If you use the table based method you will almost invariably have to also
write a reset option.
Alternatively you may want to do this within the application for example
using as using a mutex in .NET or the application object in ASP.

John

"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:co*******************@news.demon.co.uk...

I've been asked to turn our single-user database system into a multi-user
system. At present, each user has a copy of the MSDE on their desktop
machine and uses our program to access it. In future, we would like to
centralise our MSDE instance and allow multiple users to access it. In
order to facilitate this, we are going to only allow one user write
access to the system at a time (I know, its a kludge, but the system was
never designed for multiple users in the first place).

I have a single, simple question this being the case: can I update a
single "read-only" bit field in a table of the database in order to flag
to other users that the system is in read-only mode in a way that avoids
concurrency issues? ie. does an "UPDATE" query lock and unlock? ( I
suspect the answer is yes! ). If anyone else has experience of these
things, I would also appreciate some tips on how best to proceed.

Thanks
Robin

[John Bell]

Hi

There is not automatic call back mechanism for this, so you would run
the risk of overwriting someone else's changes. It sounds like you are
wanting a real-time system which is not really what a database is
designed for and it is tricky to do that in any client.

You could force a refresh periodically, although this may be annoying
to a user if they take a long time. Factors will be how volatile the
data is, how many users you have and if the changes made are likely to
be significant.

Check out Notification Services it may be useful.

John
"Robin Tucker" <id*************************@reallyidont.com> wrote in message news:<co*******************@news.demon.co.uk>...

Although I am using transaction processing at present and I understand that
anything in the brackets of the transaction is "unitary" in nature and
either will or will not be applied in its entirety, there still remains the
issue of updating the other clients when a change has taken place in the
database. I assume there is no "call back mechanism" I can use to keep the
clients all synchronised? ie. I won't know when another client has accessed
the database unless I perform some kind of poll and update myself if they
have.

"John Bell" <jb************@hotmail.com> wrote in message
news:41***********************@news.easynet.co.uk. ..

Hi

You don't say how your application will not cope with multiple users. SQL
server is a muti-user environment therefore you may be better writing
transaction handling into your application than a single-user "cludge".

If you use the table based method you will almost invariably have to also
write a reset option.
Alternatively you may want to do this within the application for example
using as using a mutex in .NET or the application object in ASP.

John

"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:co*******************@news.demon.co.uk...

I've been asked to turn our single-user database system into a multi-user
system. At present, each user has a copy of the MSDE on their desktop
machine and uses our program to access it. In future, we would like to
centralise our MSDE instance and allow multiple users to access it. In
order to facilitate this, we are going to only allow one user write
access to the system at a time (I know, its a kludge, but the system was
never designed for multiple users in the first place).

I have a single, simple question this being the case: can I update a
single "read-only" bit field in a table of the database in order to flag
to other users that the system is in read-only mode in a way that avoids
concurrency issues? ie. does an "UPDATE" query lock and unlock? ( I
suspect the answer is yes! ). If anyone else has experience of these
things, I would also appreciate some tips on how best to proceed.

Thanks
Robin

[Robin Tucker]

Ok, thanks for your help. Useful information for my manager at least :)

This isn't a real-time system. Actually, its a central repository for
images and associated data. I've implemented a tree structure, similar to a
file system inside the database. All access goes through a single module,
calling stored procedures, so I can put "locks" in no problem. I think my
solution will be to allow potential overwrites, coupled with warnings that
the database needs a refresh if, for example, nodes are deleted when another
user attempts to access.

Thanks again.
"John Bell" <jb************@hotmail.com> wrote in message
news:3b*************************@posting.google.co m...

Hi

There is not automatic call back mechanism for this, so you would run
the risk of overwriting someone else's changes. It sounds like you are
wanting a real-time system which is not really what a database is
designed for and it is tricky to do that in any client.

You could force a refresh periodically, although this may be annoying
to a user if they take a long time. Factors will be how volatile the
data is, how many users you have and if the changes made are likely to
be significant.

Check out Notification Services it may be useful.

John
"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:<co*******************@news.demon.co.uk>...

Although I am using transaction processing at present and I understand
that
anything in the brackets of the transaction is "unitary" in nature and
either will or will not be applied in its entirety, there still remains
the
issue of updating the other clients when a change has taken place in the
database. I assume there is no "call back mechanism" I can use to keep
the
clients all synchronised? ie. I won't know when another client has
accessed
the database unless I perform some kind of poll and update myself if they
have.

"John Bell" <jb************@hotmail.com> wrote in message
news:41***********************@news.easynet.co.uk. ..

> Hi
>
> You don't say how your application will not cope with multiple users.
> SQL
> server is a muti-user environment therefore you may be better writing
> transaction handling into your application than a single-user "cludge".
>
> If you use the table based method you will almost invariably have to
> also
> write a reset option.
> Alternatively you may want to do this within the application for
> example
> using as using a mutex in .NET or the application object in ASP.
>
> John
>
> "Robin Tucker" <id*************************@reallyidont.com> wrote in
> message news:co*******************@news.demon.co.uk...
>> I've been asked to turn our single-user database system into a
>> multi-user
>> system. At present, each user has a copy of the MSDE on their desktop
>> machine and uses our program to access it. In future, we would like
>> to
>> centralise our MSDE instance and allow multiple users to access it.
>> In
>> order to facilitate this, we are going to only allow one user write
>> access to the system at a time (I know, its a kludge, but the system
>> was
>> never designed for multiple users in the first place).
>>
>> I have a single, simple question this being the case: can I update a
>> single "read-only" bit field in a table of the database in order to
>> flag
>> to other users that the system is in read-only mode in a way that
>> avoids
>> concurrency issues? ie. does an "UPDATE" query lock and unlock? ( I
>> suspect the answer is yes! ). If anyone else has experience of these
>> things, I would also appreciate some tips on how best to proceed.
>>
>> Thanks
>>
>>
>> Robin
>>
>
>

[Dag]

Hi,

the complexity of providing an optimistic concurrency implementation
(ie. no locks, let serveral users get the same data for editing, but
detect and handle any conflicts that arise) depends on what
constitutes conflicting changes.

On the row level it is easy (ie. if conflicting change means two users
modify the same row in the database); just keep a copy of the original
row that was read. Then write an update proc that accepts both the new
values and the original values, uses all the original values in the
where clause so the update only matches a row if it has not been
changed, and return @@ROWCOUNT, thus signifying to the caller a
concurrency violation in the event the row was changed by another user
after it was read.

How to resolve the conflict is another matter. Options can be anything
from an error message explaining the concurrency violation and
requireing the user to start over with the new data to presenting the
user with the updated version of the data and let the user manually
resolve the conflict. Which is needed depends both on how frequently
violations are likely to occur and how much work the user would lose
had he to start over.

Hope this helps,

Dag

[John Bell]

Hi Robin

You can use a timestamp to check to see if the row has changed since
selecting it rather than checking each of the columns individually.

John

"Robin Tucker" <id*************************@reallyidont.com> wrote in message news:<co*******************@news.demon.co.uk>...

Ok, thanks for your help. Useful information for my manager at least :)

This isn't a real-time system. Actually, its a central repository for
images and associated data. I've implemented a tree structure, similar to a
file system inside the database. All access goes through a single module,
calling stored procedures, so I can put "locks" in no problem. I think my
solution will be to allow potential overwrites, coupled with warnings that
the database needs a refresh if, for example, nodes are deleted when another
user attempts to access.

Thanks again.
"John Bell" <jb************@hotmail.com> wrote in message
news:3b*************************@posting.google.co m...

Hi

There is not automatic call back mechanism for this, so you would run
the risk of overwriting someone else's changes. It sounds like you are
wanting a real-time system which is not really what a database is
designed for and it is tricky to do that in any client.

You could force a refresh periodically, although this may be annoying
to a user if they take a long time. Factors will be how volatile the
data is, how many users you have and if the changes made are likely to
be significant.

Check out Notification Services it may be useful.

John
"Robin Tucker" <id*************************@reallyidont.com> wrote in
message news:<co*******************@news.demon.co.uk>...

Although I am using transaction processing at present and I understand
that
anything in the brackets of the transaction is "unitary" in nature and
either will or will not be applied in its entirety, there still remains
the
issue of updating the other clients when a change has taken place in the
database. I assume there is no "call back mechanism" I can use to keep
the
clients all synchronised? ie. I won't know when another client has
accessed
the database unless I perform some kind of poll and update myself if they
have.

"John Bell" <jb************@hotmail.com> wrote in message
news:41***********************@news.easynet.co.uk. ..
> Hi
>
> You don't say how your application will not cope with multiple users.
> SQL
> server is a muti-user environment therefore you may be better writing
> transaction handling into your application than a single-user "cludge".
>
> If you use the table based method you will almost invariably have to
> also
> write a reset option.
> Alternatively you may want to do this within the application for
> example
> using as using a mutex in .NET or the application object in ASP.
>
> John
>
> "Robin Tucker" <id*************************@reallyidont.com> wrote in
> message news:co*******************@news.demon.co.uk...
>> I've been asked to turn our single-user database system into a
>> multi-user
>> system. At present, each user has a copy of the MSDE on their desktop
>> machine and uses our program to access it. In future, we would like
>> to
>> centralise our MSDE instance and allow multiple users to access it.
>> In
>> order to facilitate this, we are going to only allow one user write
>> access to the system at a time (I know, its a kludge, but the system
>> was
>> never designed for multiple users in the first place).
>>
>> I have a single, simple question this being the case: can I update a
>> single "read-only" bit field in a table of the database in order to
>> flag
>> to other users that the system is in read-only mode in a way that
>> avoids
>> concurrency issues? ie. does an "UPDATE" query lock and unlock? ( I
>> suspect the answer is yes! ). If anyone else has experience of these
>> things, I would also appreciate some tips on how best to proceed.
>>
>> Thanks
>>
>>
>> Robin
>>
>
>