Record Locking

I created a simple web-app for the company I work at. It consists of several
'queues' (new, pending, completed, etc...) each containing records which are
customer cases.

Each user should only be able to open a case that is not being viewed/worked
by any other user. This is accomplished by checking if a field named
"AgentOwner" is empty. If it is empty, the app stamps the user's ID into the
field and displays the data.... if the field is already occupied with
another's ID, the app returns a message stating that agent 'ab12345' has the
case open.

This locking method works fine in a single processor environment. (No
problems.)

We recently moved the app to a quad-processor server. That's when the probs
began. Here's the issue: When two or more agents select the same record at
the same time, they're all able to pull the same case. (This is not good.)

It's as if, each processor is taking on the page requests simultaneously
...... or maybe the server it too fast for the database to keep up with the
record updates.

I've tried different cursors / locktypes when opening the recordset to no
avail.
Cursors:
Static
Dynamic
Forward Only

LockTypes:
adLockOptimistic
adLockPessimistic

I've also tried SQL UPDATE statements through the connection object.....
this doesn't work either. (in fact it's a slower update)

Has anyone come across a similar situation?
Is there anyway to specify/force IIS to utilize only one CPU for certain
..asp pages?

specs:
IIS 5.0
MS Access 2000 (I know, I know...... it's a small webapp)

any suggestions are appreciated. Thanks.

Re: Record Locking

Coder23 wrote:
> I created a simple web-app for the company I work at. It consists of
> several 'queues' (new, pending, completed, etc...) each containing
> records which are customer cases.
>
> Each user should only be able to open a case that is not being
> viewed/worked by any other user. This is accomplished by checking if
> a field named "AgentOwner" is empty. If it is empty, the app stamps
> the user's ID into the field and displays the data.... if the field
> is already occupied with another's ID, the app returns a message
> stating that agent 'ab12345' has the case open.
>
> This locking method works fine in a single processor environment. (No
> problems.)
>
> We recently moved the app to a quad-processor server. That's when
> the probs began. Here's the issue: When two or more agents select
> the same record at the same time, they're all able to pull the same
> case. (This is not good.)
>
> It's as if, each processor is taking on the page requests
> simultaneously ..... or maybe the server it too fast for the database
> to keep up with the record updates.
>
> I've tried different cursors / locktypes when opening the recordset
> to no avail.
> Cursors:
> Static
> Dynamic
> Forward Only
>
> LockTypes:
> adLockOptimistic
> adLockPessimistic
>
> I've also tried SQL UPDATE statements through the connection
> object..... this doesn't work either. (in fact it's a slower update)
>
> Has anyone come across a similar situation?
> Is there anyway to specify/force IIS to utilize only one CPU for
> certain .asp pages?
>
> specs:
> IIS 5.0
> MS Access 2000 (I know, I know...... it's a small webapp)
>
> any suggestions are appreciated. Thanks.

I suspect you are running into the Jet delayed-write "optimization". There
is a KB article about how to deal with it. Let me see if i can find it ....
yes, here it is:
http://support.microsoft.com/support/kb/articles/q200/3/00.a sp

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Record Locking

Thanks Bob,
this helped better understand the issue.
problem resolved. : )

"Bob Barrows [MVP]" wrote:

> Coder23 wrote:
> > I created a simple web-app for the company I work at. It consists of
> > several 'queues' (new, pending, completed, etc...) each containing
> > records which are customer cases.
> >
> > Each user should only be able to open a case that is not being
> > viewed/worked by any other user. This is accomplished by checking if
> > a field named "AgentOwner" is empty. If it is empty, the app stamps
> > the user's ID into the field and displays the data.... if the field
> > is already occupied with another's ID, the app returns a message
> > stating that agent 'ab12345' has the case open.
> >
> > This locking method works fine in a single processor environment. (No
> > problems.)
> >
> > We recently moved the app to a quad-processor server. That's when
> > the probs began. Here's the issue: When two or more agents select
> > the same record at the same time, they're all able to pull the same
> > case. (This is not good.)
> >
> > It's as if, each processor is taking on the page requests
> > simultaneously ..... or maybe the server it too fast for the database
> > to keep up with the record updates.
> >
> > I've tried different cursors / locktypes when opening the recordset
> > to no avail.
> > Cursors:
> > Static
> > Dynamic
> > Forward Only
> >
> > LockTypes:
> > adLockOptimistic
> > adLockPessimistic
> >
> > I've also tried SQL UPDATE statements through the connection
> > object..... this doesn't work either. (in fact it's a slower update)
> >
> > Has anyone come across a similar situation?
> > Is there anyway to specify/force IIS to utilize only one CPU for
> > certain .asp pages?
> >
> > specs:
> > IIS 5.0
> > MS Access 2000 (I know, I know...... it's a small webapp)
> >
> > any suggestions are appreciated. Thanks.
>
> I suspect you are running into the Jet delayed-write "optimization". There
> is a KB article about how to deal with it. Let me see if i can find it ....
> yes, here it is:
> http://support.microsoft.com/support/kb/articles/q200/3/00.a sp
>
> Bob Barrows
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>