Problem recovering from multiple users updating same row simultaneously

[Ken Levy]

I have a problem with some code which is creating the following OLEDB error
message.

Run-time error: -2147217864 (80040e38)
The specified row could not be located for updating. Some values may have
been changed since it was last read.

The situation is as follows:

Field in a shared table is being updated using Microsoft Access 2003.

Table is also accessed in a query in the application which receives the
error message above.

I am using VB6 SP6 and ADO 2.6 SP2.

The code used to open the table in the VB app is as follows:

Public adoConn As ADODB.Connection
Public adoIngestHost As ADODB.Recordset

Set adoConn = New ADODB.Connection

adoConn.CursorLocation = adUseClient
adoConn.Open "File Name=" & gstrUDLFileName

' Note the Provider specified in the UDL file is: Microsoft Jet 4.0 OLE
DB Provider
' Access permissions are Share Deny None

' Set up SQL command to retrieve data
strSQL = ""
strSQL = strSQL & "SELECT * from IngestHost "
strSQL = strSQL & "WHERE (((IngestHost.Hostname)='" & HostIdStr & "'))"

Set adoIngestHost = New ADODB.Recordset
adoIngestHost.Open strSQL, adoConn, adOpenKeyset, adLockOptimistic

' other code

adoIngestHost!HostLocation = "prog"

adoIngestHost.Update

' The .Update throws the following error information into the
connections error collection:

Err.Number (hex) 80040E38
Err.Description Row cannot be located for updating. Some
values may have been changed since it was last read.
Err.NativeError 32
Err.Source Microsoft Cursor Engine

Now, I am not opposed to adding some code to handle the error and
select/warn about the conflicting values, but everything I tried to recover
from the error doesn't seem to work.

In the Error routine, when I look at the .Status of each of the fields in
the recordset, they show a code of zero (no error).
When I print out the .Value, .OriginalValue and .UnderlyingValue for the
field, they all show the same value ("prog"), although the value I entered
from Access is "test".

I've tried this with CursorType = adOpenDynamic and adOpenStatic with the
same results. I also tried it with a server side cursor, but that prevents
me from doing .Sort's of the recordset elsewhere in the code.

I also looked at the .Status field value both before and after the update in
the program, but it still always shows 0.

I tried inserting the following line before I set the value in the database:

adoIngestHost.Resync adAffectCurrent, adResyncAllValues

It throws the following error:

Err.Number (hex) 80004005
Err.Description Insufficient key column information for
updating or refreshing
Err.NativeError 1007
Err.Source Microsoft Cursor Engine

None of the hits I found on MSDN or google seem to address this problem.

Am I missing some limitiation of the Jet Engine as a OLEDB provider? Will I
have similar problems if I upgrade to MSDE or SQL Server? Any help would be
appreciated. Please send any responses to the newsgroup.

Thanks.

Ken

[Steven Cheng[MSFT]]

Hi Ken,

Thank you for the response. Regarding on the issue,we're
finding proper resource to assist you and we will update as soon as posible.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security(This posting is provided "AS IS",
with no warranties, and confers no rights.)

[Kevin Yu [MSFT]]

Hi Ken,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that when multiple users are updating same
row simultaneously, an error will be generated which says "The specified

row could not be located for updating. Some values may have been changed

since it was last read." If there is any misunderstanding, please feel free
to let me know.

This is a typical concurrency error. Since you have set the lock type to
Optimistic Lock, the data in the row will only be locked during update. So
if the cursor is at client side, the Recordset will not know that data has
been changed since it was get from the database.

In this case we can try to use the server side cursor and open a dynamic
cursor instead. When data has been changed by other users, the changes will
be visible to us. Or we can also try to use a Pessimistic lock. When other
users are making changes to certain data, that row will be locked until
updating is finished.

If a server side cursor prevents you from using the Sort property, could
you use the ORDER BY and WHERE clause in SQL instead?

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

[Ken Levy]

Kevin:

Thanks for the response.  Regarding your first question, yes, your
understanding is correct.

Perhaps a bit of explanation about the application will answer the other
questions.

This application is basicly a server receiving and monitoring telemetry data in the form of UDP IP packets from a large number of stations.  To minimize loss of UDP packets, the processing overhead for each incoming packet must be kept to a minimum.  When the program receives a observation packet, it is dispatched (via MSMQ) to a back end database update process to minimize the database overhead within the monitor program..

We are, however, using Jet provider within the monitor program to checkpoint changes in the status (online, offline, missing data) of each telemetry station.  This information is used to automate recovery of the network (failover to a backup site).  To minimize overhead of requerying or recreating the recordset every time we need to update the status of a station, we leave the connection and "status tracking" recordset open for the duration of the program. To minimize the locking problem, updates are committed immediately after any fields are modified (status of the stations change).

The client side cursor is used for the .Sort capability which is used to fill an image combo box.  The operator is allowed to select the order in which stations are presented in the combo box.  Entries consist of 4-5 fields concatenated together.  The .Sort capability is used to change re-order (and then rebuild) the entries in the station selection combo box.  This method was a short and sweet answer to handling the complexity of a multi-column sort in ADO code.

With regard to locking conflicts, the problem I am seeing is not due to the external program holding a lock on the records (the external app has already completed the update and unlocked the record before my app tries to do its update).  The message I am getting indicates that the .UnderlyingValue (which should now be equal to the value set by the xternal program) no longer matches the .OriginalValue from my programs recordset.

 

I tried using the .Resync method prior to updating, but I get an error message when I use a server-side cursor (Run-time error '3251': Current provider does not support refreshing underlying values). 

 

To try to get a handle on this, I ran the following experiment:

 

CONN1 and RS1 from test app

CONN2 and RS2 from test app, pointing to same table as CONN1/RS1

 

EXT is manual update to same table using Access.

 

 

 

adUseClient, adOpenDynamic, adLockOptimistic

                   .status of field modified         .OriginalValue  .Value        .UnderlyingValue

 

Open both record sets (2 different connections) in test app
RS1 BEFORE RS2 UPDATE       0                           prog          prog          prog

Update field (to 'test') using RS2 in App and print field .values

RS2 AFTER RS2 UPDATE->test  0                           test          test          test
RS1 AFTER RS2 UPDATE        0                           prog          prog          prog

 

Update field (to 'Lenexa, KS') using Access and print field.values 
RS1 AFTER EXT UPDATE        0                           prog          prog          prog

 

Execute .Resync method on CONN1/RS1

RS1 AFTER RESYNC MTHD       0                           Lenexa, KS    Lenexa, KS    Lenexa, KS

 

Change field (to "prog") using RS1 in App and print field .values
RS1 BEFORE .UPDATE  ->prog  0                           Lenexa, KS    prog          Lenexa, KS

 

Commit changes to field with RS1.Update in App and print field .values
RS1 AFTER .UPDATE   ->prog  0                           prog          prog          prog

 

 

Note that is I don't issue the .Resync before my .Update, I get an ADO error (80040E38 - Row cannot be located for updating. Some values may have been changed since it was last read.) from the  Microsoft Cursor Engine (client side cursor provider).

 

My question goes back to the original question.  Is the use of the .OriginalValue and .UnderlyingValue properties not properly supported by the Microsoft Jet 4.0 OLE DB Provider?  The results of the test above certainly do not track with what the ADO documentation indicates we should see for the original and underlying values.

 

If I upgrade the database to MSDE or SQL/Server, will I be able to use the .OriginalValue and .UnderlyingValue properties to detect external changes (in order to programmatically resolve the conflict)?

"Kevin Yu [MSFT]" <v-k...@online.microsoft.com> wrote in message
news:6Skg8$9ZEHA...@cpmsftngxa06.phx.gbl...

[Kevin Yu [MSFT]]

Hi Ken,

Based on my research, Jet 4.0 OLE DB provider doesn't support the
OriginalValue and UnderlyingValue properties. However, SQLOLEDB provider
only supports these properties with a server side cursor. The locking issue
might be caused by other clients accessing data. Please check if there is
any .ldb file in the same folder as the .mdb file. The .ldb file might be
hidden, and if there is, it means that others are accessing data on the
.mdb file. Please try to delete that file and run you application again.