Pausing execution of an ASP Script

[Dr. Know]

I am working on an ASP page that writes to several databases, ranging
from MDBs to x-base.

One of the tasks involves using an existing highest value from the DB
and incrementing it before inserting a new record.

I am using Application.Lock and .Unlock together with an application
variable to negotiate access to the DB routine to one session (user)
at a time. This is to ensure that the ID numbers are cleanly
incremented, and that no two records has the same ID. These databases
are also written to by a console user app (non-asp). The exclusive
access to the DB *generally* lasts WAY less than 1 second.

But because of the variable nature of things computer related, I have
error trapping in the code that detects a DB open or execute error,
and waits n period of time, and tries again - for a maximum of x
tries. (n is about 1 second, and x is 5 tries)

My problem is in pausing the script. I am a newbie at this, so be
gentle if this is a really stupid question.

You cannot use:

d - duration
x = time
do while time < (x + d)
.... (what, no doevents?)
loop

as it consumes vast amounts of CPU cycles.

There is no Sleep Function, I was amazed to learn.
There is no doevents, to release a dumb loop as above from hogging CPU
time.

I have had no luck in using the WScript.Sleep business....
I'm not quite sure how to reference it.

Is there a better approach to this?

Thanks,


Greg


Dr. Know

 

[Bob Barrows [MVP]]

I am working on an ASP page that writes to several databases, ranging
from MDBs to x-base.

One of the tasks involves using an existing highest value from the DB
and incrementing it before inserting a new record.

This is silly. Why not use an auto-increment field?
Or at least use a table with a single record to generate your new ID's (one
field: lastIssuedID). You can use pessimistic locking to prevent other users
from updating it during the nanosecond it takes for you to grab a new ID and
increment it.
as it consumes vast amounts of CPU cycles.

There is no Sleep Function, I was amazed to learn.
There is no doevents, to release a dumb loop as above from hogging CPU
time.

You don't want to tie up a server thread doing nothing. It's a nice way to
kill your server's scalability. You have to remember: you're not writing a
desktop application.

I have had no luck in using the WScript.Sleep business....
I'm not quite sure how to reference it.

Can't be done in ASP.
http://blogs.msdn.com/ericlippert/archive/2003/10/07/53155.aspx

Is there a better approach to this?

There is none in ASP. You would be better off sending a message to the
client to tell them to try again in a few seconds. But if you must do this
automatically, you can redirect to a page whose onload event uses setTimer()
to wait a second and resubmit the request. Follow up in a client-side coding
group such as .scripting.jscript if you want to pursue this further.

Bob Barrows

 

[Dr. Know]

Bob Barrows [MVP] said:

Dr. Know wrote:

This is silly. Why not use an auto-increment field?
Or at least use a table with a single record to generate your new ID's (one
field: lastIssuedID). You can use pessimistic locking to prevent other users
from updating it during the nanosecond it takes for you to grab a new ID and
increment it.

Well, Sure it's silly, but it's the way it has to be.
This is *legacy* work. Existing DB formats and tables. They cannot
be changed. The native apps would choke on it... and you have to
retrieve the field as is updated by the native app as well as the ASP
script.

Hey, don't blame me - I didn't write it! ;-)

You don't want to tie up a server thread doing nothing. It's a nice way to
kill your server's scalability. You have to remember: you're not writing a
desktop application.

Exactly my point. I was looking for a thread friendly way to pause an
ASP script.

Can't be done in ASP.
http://blogs.msdn.com/ericlippert/archive/2003/10/07/53155.aspx

That explains THAT! Rather odd omission, however, considering how
useful it might be if not misused.

There is none in ASP. You would be better off sending a message to the
client to tell them to try again in a few seconds. But if you must do this
automatically, you can redirect to a page whose onload event uses setTimer()
to wait a second and resubmit the request. Follow up in a client-side coding
group such as .scripting.jscript if you want to pursue this further.

OK, I'm just going to hammer it 5 times, and then return an error
message and suggest they repost the form. Darned... I only needed a
half second delay...

Thanks,

Greg


Dr. Know

 

[Bob Barrows [MVP]]

Bob Barrows [MVP] said:


Well, Sure it's silly, but it's the way it has to be.
This is *legacy* work. Existing DB formats and tables. They cannot
be changed. The native apps would choke on it... and you have to
retrieve the field as is updated by the native app as well as the ASP
script.

So use a separate database to generate your ID's - don't tie up your
database, application and web server for this purpose.

Hey, don't blame me - I didn't write it! ;-)


Exactly my point. I was looking for a thread friendly way to pause an
ASP script.

There isn't one.

That explains THAT! Rather odd omission,

"Odd"? I thought he rather clearly explained why it was a deliberate
omission ...

however, considering how
useful it might be if not misused.

It can't NOT be misused. This is a server, not a desktop.

OK, I'm just going to hammer it 5 times, and then return an error
message and suggest they repost the form. Darned... I only needed a
half second delay...

No, don't hammer it. (by "hammer", I'm assuming you mean that you are going
to use the CPU-intensive code to "pause" it) Don't tie up your server that
way. There are other ways to generate unique ID's. I've already mentioned
the possibility of creating a separate database containing a lastID table.
There's nothing forcing you to use your legacy database for this.

Another possibility is to redirect the page to itself, using a querystring
variable to control the number of retries. Kludgy, but ...

But again, I strongly discourage serializing your database access in the way
you have designed it. if you can't modify the design of your main database,
create a separate mdb file containing a table with a single field. If you
make it two fields, one of which is an autonumber field, you don't even have
to worry about pessimistic locking - just connect to the database, insert a
record, and select @@Identity. You will have your unique ID. Close the
connection and connect to your legacy database.

See http://www.aspfaq.com/show.asp?id=2174

Bob Barrows

 

[Dr. Know]

Bob Barrows [MVP] said:

Dr. Know wrote:
So use a separate database to generate your ID's - don't tie up your
database, application and web server for this purpose.

First, thanks for the information!
You seem quite knowledgeable on the subject - I'm a newcomer...

What I apparently failed to make clear is that the native app
increments the ID code on it's own when a terminal user adds a record.
The IDs are sequential and have to be ordered, not just unique.

The ASP page also has to increment the same ID number while respecting
any changes made by the terminal app. I cannot rewrite the original
app to recognize a secondary ID database or to use auto number or
replication ID fields. Therefore some sort of sync must be maintained
between the two clients. I see no way of doing this. It barely
recognizes record locks...

The only saving grace to this is that it will see limited usage. (less
than 50 additions per day).

No, don't hammer it. (by "hammer", I'm assuming you mean that you are going
to use the CPU-intensive code to "pause" it) Don't tie up your server that
way. There are other ways to generate unique ID's. I've already mentioned
the possibility of creating a separate database containing a lastID table.
There's nothing forcing you to use your legacy database for this.

No, I meant that I would simply retry the command 5 times with no
(Sleep) delay before giving up, and returning an error that the record
could not be locked or appended. My perfectionist tendencies preclude
using any routine which consumes 100% CPU time... ;-)

I also don't want to simply repeat the command until it succeeds. I
was looking to allow a reasonable time limit for the other processes
to give up the record locks, then give up or proceed accordingly.

But again, I strongly discourage serializing your database access in the way
you have designed it. if you can't modify the design of your main database,
create a separate mdb file containing a table with a single field. If you
make it two fields, one of which is an autonumber field, you don't even have
to worry about pessimistic locking - just connect to the database, insert a
record, and select @@Identity. You will have your unique ID. Close the
connection and connect to your legacy database.

Believe me - I didn't design this thing. That's why it's giving me
such fits. But the customer wants this add-on - BAD! The
pre-existing app is a relic from the dark ages of DOS. All the
databases are pre-existing. It's still being sold - complete with
outrageous licensing and maintenance fees!


Thanks,
Greg






Dr. Know

 

[Mark Schupp]

It's still not completely clear what you are trying to do. Does the other
application control the id or is it a matter of avoiding 2 updates at the
same time? Is the counter in a separate table or is it part of the table
containing other data?

If the above are true you can roll your own optimistic locking mechanism
like this (adjust the names to match your database, fill in missing code for
executes, etc):

nRows = 0
do while nRow <> 1 'probably want a "too many tries" bailout

strSQL = "select count from counter"

nCount = CLng(rsdata(count))
nNewID = nCount + 1

strSQL = "update counter set count = " & nNewID & " where count = "
& nCount
dbconn.execute strsql, nrows

loop

You can get a delay in an ASP page by creating or purchasing a com component
that calls the winapi sleep function but we have the above code working with
no complaints (and far more than 50 calls per day). The above code is know
to work with Access, Oracle, and SQL Server. You would need to test it on
other DBMSs.

--
--Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com

 

[larrybud2002]

To demonstrate how bad this stuff is, all fields are character types,

including numbers and currency, and some data is referenced by it's
physical location within the field. i.e.
ID = Mid(fld,1,14)
Dept = Mid(fld,14,4)

I'd just retry it 10 or 20 times. With only 50 or so adds a day, I
don't see that as a problem.
Good luck... my job suddenly looks much better!