Multi Computer Synchronization
Sean
I need to write a bunch of applications that all work on a database accross
a network. All run on winnt 4.0 and all the apps are in VC++ 5.0. Some of
these applications add or modify data to the database. Other read the data,
do something, and then change the data to reflect what they did. For
performance reasons, we would like to have several instances of each program
doing its thing all working together to distribute the load accross several
computers. Now, if these were threads, i could see using some of the
watiforxxx and critsections and such, but since these are apps on different
computers, what are my options? Basically, i want an application to be able
to seize a record in the database, do its thing, and then restore access to
it. (BTW We are using SQL Server 6.5) At the same time, the network
connection to the database might drop out. If that happens, i want access
restored to the record so some of the other apps in the pool can work on it.
I dont see much in SQL server to help me do this, so i was hoping for some
sort of network version of these contention API's.
Ideas?
-Sean!
--
___________________________
Sean M. Fulmer
Senior Software Engineer
Omnitech Corporate Solutions, Inc.
(formerly MediaRight)
Voice: (212) 966-7383
Fax: (212) 966-6488
Slava M. Usov
news:#gzJ$9DZ$GA....@cppssbbsa02.microsoft.com...
Sorry, I *really* don't understand why you need an extra level of
synchronization while a database such as MS SQL Server just has all you
need...
begin transaction
set transaction isolation level serializable
/* depending on your task, the isolation level may be less than that */
select data from your_table where condition = your_condition
/* process data ... don't do it too long */
update your_table set data = new_data where condition = your_condition
commit transaction
--
Slava
Please send any replies to this newsgroup.
microsoft.public.win32.programmer.kernel
Sean
I'm not sure i need it either. I'm not an expert on sql server, and that is
probably my problem.
The only problem i have with transactions is that i have 2 or more programs
running the same select statement designed to get the next available record
to work on. So if the 1st one gets the record in a transaction and the 2nd
app runs its select, will it block or will it get the next available record?
I want it to NOT block. I realize we are out of kernel programming now, but
you seem knowledgeable in this subject too...
-Sean!
--
___________________________
Sean M. Fulmer
Senior Software Engineer
Omnitech Corporate Solutions, Inc.
(formerly MediaRight)
Voice: (212) 966-7383
Fax: (212) 966-6488
"Slava M. Usov" <stripit...@usa.net> wrote in message
news:#fHM#ZEZ$GA....@cppssbbsa02.microsoft.com...
Tomas Restrepo
> I'm not sure i need it either. I'm not an expert on sql server, and that
is
> probably my problem.
> The only problem i have with transactions is that i have 2 or more
programs
> running the same select statement designed to get the next available
record
> to work on. So if the 1st one gets the record in a transaction and the
2nd
> app runs its select, will it block or will it get the next available
record?
Depends on the isolation level and other things. In Slava's example, the
Serializable level is pretty tough, as it guarantees that you won't read
"phantom" rows. But it also slows things down quite a bit since teh server
has to lock down all affected rows and even non-existan ows on the range
requested by the select statement. As the name implies, Serializable in
effect serializes transactions, which mean once teh first query executes,
the one run by the second app has to wait until the first transaction ends.
However, you don't necesarily have to used this isolation levels, unless you
are dealing with very critical data. The problem is, we don't know your
requirements, and evaluating teh specific needs would be very hard for us.
Only you, as the designer, can successfully balance consistency against
speed....
Finally, I think you'll get much better responses in the SQL server
newsgroups. Also, a good book on SQL server could help you out a lot (I'm
currently going though "Inside SQL Server 7.0" by Soukup and Delaney, and
it's pretty good).
--
Tomas Restrepo
http://members.xoom.com/trestrep/
Slava M. Usov
> Slava,
> I'm not sure i need it either. I'm not an expert on sql server, and that
is
> probably my problem.
> The only problem i have with transactions is that i have 2 or more
programs
> running the same select statement designed to get the next available
record
> to work on. So if the 1st one gets the record in a transaction and the
2nd
> app runs its select, will it block or will it get the next available
record?
This is a bit hard to suggest, because I don't quite understand what "next
available record" is. Please provide more details/background for your
problem.
Benjamin Kaufman
processing. Take a few days with a few good books to learn the
fundamentals.
Ben
On Fri, 21 Jan 2000 13:44:35 -0500, "Sean" <Se...@mediaright.com>
wrote:
>All,
>I need to write a bunch of applications that all work on a database accross
>a network. All run on winnt 4.0 and all the apps are in VC++ 5.0. Some of
>these applications add or modify data to the database. Other read the data,
>do something, and then change the data to reflect what they did. For
>performance reasons, we would like to have several instances of each program
>doing its thing all working together to distribute the load accross several
>computers. Now, if these were threads, i could see using some of the
>watiforxxx and critsections and such, but since these are apps on different
>computers, what are my options? Basically, i want an application to be able
>to seize a record in the database, do its thing, and then restore access to
>it. (BTW We are using SQL Server 6.5) At the same time, the network
>connection to the database might drop out. If that happens, i want access
>restored to the record so some of the other apps in the pool can work on it.
>I dont see much in SQL server to help me do this, so i was hoping for some
>sort of network version of these contention API's.
>Ideas?
>-Sean!
--
Ben Kaufman
Do not send SPAM. I will send it back to your de-hacked ISP
with a letter of complaint. Section 301 does not apply because
you have been forewarned!
For all others, change the domain name to pobox
Slava M. Usov
news:#0SOCnFZ$GA.260@cppssbbsa04...
> Sean,
> > I'm not sure i need it either. I'm not an expert on sql server, and that
> is
> > probably my problem.
> > The only problem i have with transactions is that i have 2 or more
> programs
> > running the same select statement designed to get the next available
> record
> > to work on. So if the 1st one gets the record in a transaction and the
> 2nd
> > app runs its select, will it block or will it get the next available
> record?
>
> Serializable level is pretty tough, as it guarantees that you won't read
> "phantom" rows. But it also slows things down quite a bit since teh server
> has to lock down all affected rows and even non-existan ows on the range
> requested by the select statement. As the name implies, Serializable in
> effect serializes transactions, which mean once teh first query executes,
> the one run by the second app has to wait until the first transaction
ends.
This is not exactly what I believe happens. Serialized will be the
transactions that try to select/modify the *same* records, extents, or
tables, depending on lock granularity. If the transactions deal with
different data, they'll run in concurrent, especially with row-level
locking, which MS SQL 7.0 kind of supports.
In general, I agree with your remark. I used the tightest isolation level
for maximum safety. In real application, the minimum possible level should
be used, as you note below.
> However, you don't necesarily have to used this isolation levels, unless
you
> are dealing with very critical data. The problem is, we don't know your
> requirements, and evaluating teh specific needs would be very hard for us.
> Only you, as the designer, can successfully balance consistency against
> speed....
>
> Finally, I think you'll get much better responses in the SQL server
> newsgroups. Also, a good book on SQL server could help you out a lot (I'm
> currently going though "Inside SQL Server 7.0" by Soukup and Delaney, and
> it's pretty good).
Is it out? How do you compare it with "Inside SQL Server 6.5", if you read
that?
Doug Reilly
It adds to the stuff in the older book, and makes some things clearer.
Generally worth the mony. In my case, I have the new book here at home, and
have placed the older book in an outside office that I sometimes use. Both
have been lifesavers.
Slava M. Usov <stripit...@usa.net> wrote in message
news:OJF5ZAnZ$GA.254@cppssbbsa04...
Slava M. Usov
news:s8rcab5...@corp.supernews.com...
> Excuse me for jumping in, but the new Inside SQL Server 7 is pretty good.
> It adds to the stuff in the older book, and makes some things clearer.
> Generally worth the mony. In my case, I have the new book here at home,
and
> have placed the older book in an outside office that I sometimes use.
Both
> have been lifesavers.
I'm primarily interested in details on the features that debuted in this
version, such as parallel query execution, the new locking mechanism, and
multiindex operations. Are these covered?
Oh, probably I should go asking that in relevant forums....