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
7  2528 
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************ *************@r eallyidont.com> wrote in
message news:co******** ***********@new s.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 (id************ *************@r eallyidont.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****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
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.c o.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************ *************@r eallyidont.com> wrote in
message news:co******** ***********@new s.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
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************ *************@r eallyidont.com> wrote in message news:<co******* ************@ne ws.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.c o.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************ *************@r eallyidont.com> wrote in
message news:co******** ***********@new s.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
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.goog le.com... 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************ *************@r eallyidont.com> wrote in
message news:<co******* ************@ne ws.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.c o.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************ *************@r eallyidont.com> wrote in
> message news:co******** ***********@new s.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
>>
>
>
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
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************ *************@r eallyidont.com> wrote in message news:<co******* ************@ne ws.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.goog le.com... 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************ *************@r eallyidont.com> wrote in
message news:<co******* ************@ne ws.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.c o.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************ *************@r eallyidont.com> wrote in
> message news:co******** ***********@new s.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
>>
>
> This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: CJM |
last post by:
I'm setting up some web-based (ASP) reports that query an Access DB. I also
want certain people to be able to access and manipulate the database
directly.
However, if the database is open in Access, I cant access it via ASP:
Microsoft JET Database Engine error '80004005'
Could not use ''; file already in use.
|
by: focus |
last post by:
I have one doubt, I have defined a singleton class, that implements a
STL list, this singleton class, will be access at the same time from
diferent classes.
My doubt is if STL accepts concurrent accest, at the time to insert
another object in the list?. I supose that the answer is that STL
doesn't accept concurrent access when I have to insert an element to
the list, but I want to be sure. Thanks for your help.
|
by: Durai |
last post by:
Hi All,
I tested "concurrent testing" in MySQL. It works fine. But I couldn't do in PostgreSQL 7.3.4 on HPUX IPF. I got deadlock problem. I used the PHP script to update table( one script increment the column & another one decrement the column). Is the postgres support the concurrent access to update database?
I got the following errors:
test=# ERROR: deadlock detected
ERROR: deadlock detected
ERROR: deadlock detected
.....
|
by: bluedolphin |
last post by:
There seems to be a consensus that Access has a concurrent user limit
of 200 users. I am working on a system that currently stands at
approx. 1 gig and has a small number of users. However, there is a
project on the horizon that I have been asked to implement (within 2
months) where there will potentially be 1200 users accessing the
backend through a Cold Fusion Interface.
Whenever, I have been looking at this many users, I have always...
|
by: mgPA |
last post by:
Short: How can I limit the number of concurrent logins to Access
(2000) DB?
Long: I seem to be having the problem discussed in previous postings
of having more than 9 or 10 concurrent logins. If I can limit the
number of concurrent logins to 8 or 9, that would satisfy our needs.
Thanks
| | |
by: Adnan |
last post by:
Hey Ppl,
I'm developing an Online Auction Site using ASP.net and am
experiencing a problem with Transactions in ADO.Net.
When beginTrasaction() function is invoked from a specific connection,
and insertions are made into a table, no other connection can access
the table, until the transaction is committed. I've set the
Isoloation level to ReadUncommited.
|
by: roblugt |
last post by:
I have what I imagine is a well-known .Net networking problem, but even
though I've Googled for some time I've not yet come across a thread
where this has been fully explained...
There is a fairly common idiom (at least in the Java/UNIX/Win32 worlds)
where a socket connection is established and the program then utilises
two threads: one dedicated to reading from the socket and the other one
concurrently writing to the socket - both using...
|
by: goraya |
last post by:
This is design level discussion about web applications. How I design
application that support 1 million concurrent requests??
|
by: amitpatel66 |
last post by:
There is always a requirement that in Oracle Applications, the Concurrent Program need to be execute programatically based on certain conditions/validations:
Concurrent programs can be executed programatically either from UNIX or Oracle PLSQL. In this Section, I will be explaining about calling a Concurrent program from UNIX using the CONCSUB Command.
Pre-requisite:
1. Concurrent Program should be registered in oracle Applications...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
