Access Locking problem

I've been working on this problem for days and cannot seem to figure it out. I'm using the Microsoft Access Driver in an ASP web page to try to update a database. I'm using adLockPessimistic, but it is acting like I'm using adLockOptimistic.

Before you ask, I'm using MDAC 2.8 SP2 on my Windows 2003 Server.

If I open two web pages at the same time, both trying to increment a counter in a database 100 times, the end result should be a value of 200. But in most trials, I get a number less than 200, like 187. It almost seems like it is not locked at all, ecept for the fact that I sometimes get an error telling me I can't update the data because it is locked by another user.

So... I can read OK, but not save because sometimes its locked by another user and sometimes the increment doesn't really increment, therefore it seems like I have adLockOptimistic instead of adLockPessimistic. I do get adLockPessimistic when I read back the lock type.

Any help would be great!

Code Snippet

for i = 1 to 100
if ( len(Request.Form("zipcode")) > 0 ) then
call LogImpression (38)
end if
next

Function LogImpression (CampID)
'This increments the impression counter for a campaign in the vendors database
On Error Resume Next

Set adoCon = Server.CreateObject("ADODB.Connection")
'adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("accessdb/vendors.mdb")
adoCon.Open "DSN=vendorDB"
Set rsAddComments = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT vendors.impressions,vendors.ID FROM vendors WHERE ID=" & CampID & ";"
rsAddComments.CursorType = 2
rsAddComments.LockType = 2
rsAddComments.CursorLocation = 2

rsAddComments.Open strSQL, adoCon, 2, 2, adCmdTableDirect
if (NOT rsAddComments.EOF) then
Err.Clear

MyCount=CInt(rsAddComments.Fields("impressions")) + 1
rsAddComments.Fields("impressions") = MyCount
rsAddComments.Update

if Err.Number <> 0 then
'we had an error, row was locked
Err.Clear

MyCount=CInt(rsAddComments.Fields("impressions")) + 1
rsAddComments.Fields("impressions") = MyCount

rsAddComments.Update
end if

end if
rsAddComments.Close

Set rsAddComments = Nothing
Set adoCon = Nothing
end function

Thanks, Lee

Your code itself doesn't lock this critical section. You cannot consider these two statements to be atomic. You have two database operations going on, and you are trying to run them atomically, so your code needs to lock around that area or you need to wrap the commands in transactions.

Here is the scenario you have right now:

Assume that the database starts with "impressions" set to 1:

process A updates MyCount = impressions + 1 = 2 then context switches out

process B updates MyCount = impressions + 1 = 2 then context switches out

process A sets impressions = MyCount = 2 then context switches out

process B sets impressions = MyCount = 2 then context switches out

These concurrency issues do not happen all the time, and are hard to reproduce, but this is clearly an application level concurrency issue.

I suggest wrapping those two lines in a critical section to enforce serialization there.

Hope that helps,

John