Access/Jet returning OLD data values immediately after update!

[Nick Gilbert]

Hi,

I have a website which updates a value in a database and then
immediately requests a dataset which contains that value. Sometimes I
the data returned contains the OLD value even though the database itself
shows the correct value. Ie JET was sending back an out of date value
for the data.

Similarly, when updating, I occasionally get the following exception:

"The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time. ".

It seems that if you immediately read back the data on the same
connection, it appears to be correct. But if, after updating the data,
you redirect to a webpage which should contain the updated data, then it
somehow can get OLD data. This happens even when the code is wrapped in
a transaction. I've proved that IIS isn't serving me an old *page* but
IS serving me old *data* by adding a sequential counter to the page (if
it was serving me an old page the counter would go backwards). So the
problem is definitely that Access (or it's drivers) is returning the old
values for some of the rows.

I presume what's happening is that if you redirect to a different page
after updating the database, you don't necessarily get the same thread
in IIS or the same connection from the connection pool, and somehow it's
managing to retrieve the old value from the database or perhaps some
caching is happening somewhere at IIS level.

Is there no way to force Access to properly flush all changes to the
database so this problem doesn't happen?

For information, connection pooling is enabled in my application for
performance reasons.

I really need to fix this problem ASAP. Any help would be greatly
appreciated.

Nick...

 

[Arvin Meyer]

If you are creating a lock on the page, or the table, (or occasionally even
the record) you can blame the problem on Access, and you'll need to fix the
condition that causes it (i.e. it is possible that you are locking the
record twice through different connections) More than likely though, its an
IIS lock, or a cached page.

Access will not update the table if there is a condition which prohibits it
like breaking a validation rule, creating a duplicate index on a primary
key, etc. It will not usually send an error back. When you see the error:

"The Microsoft Jet database engine stopped the process because you and
another user are attempting to change the same data at the same time. ".

it almost always is 2 users, or 2 different forms from the same user,
connecting to the page, or record. The problem can be avoided by changing
the conditions under which it occurs.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Nick Gilbert]

If you are creating a lock on the page, or the table, (or occasionally even
the record) you can blame the problem on Access, and you'll need to fix the
condition that causes it (i.e. it is possible that you are locking the
record twice through different connections) More than likely though, its an
IIS lock, or a cached page.

I've proved that it's not IIS caching the page with some other tests.
The page is definitely a new page which has somehow received some old
data via ADO/Jet. Not sure what you mean by "IIS lock". IIS itself
doesn't lock anything - maybe you meant ADO.

Access will not update the table if there is a condition which prohibits it
like breaking a validation rule, creating a duplicate index on a primary
key, etc. It will not usually send an error back.

True, but I'm not normally seeing an error - I'm just seeing out of date
data - even though the database DOES have the correct data in it.
(Refreshing the page fixes the problem).

it almost always is 2 users, or 2 different forms from the same user,
connecting to the page, or record. The problem can be avoided by changing
the conditions under which it occurs.

I agree that there are probably concurrent accesses going on, but my
question is, why does one user (connection) get to see old data if it
has been updated previously on a different connection? Why does it let
them read the table if Access hasn't flushed the changes yet? And is
there a way to prevent this happening?

Thanks,

Nick...

 

[Arvin Meyer]

I agree that there are probably concurrent accesses going on, but my
question is, why does one user (connection) get to see old data if it
has been updated previously on a different connection? Why does it let
them read the table if Access hasn't flushed the changes yet? And is
there a way to prevent this happening?

Remember that Access processing takes place at the workstation (or at the
IIS server, if that's the method used to get the data), not at the file
server. As a result, the server does not communicate with the client, the
client instigates communication with the server. As you mention, if you
refresh, you see current data. The only waay I know of avoiding seeing old
data, once the form has been opened, is to refresh the data. You can do this
in code at timed intervals, or at operator request.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access