[thelist] updating a record?

Joshua Olson joshua at waetech.com
Sun Oct 13 15:41:01 CDT 2002


----- Original Message -----
From: "Jeremy Weiss" <jweiss03 at comcast.net>
Sent: Saturday, October 12, 2002 7:37 PM

Jeremy,

It may be possible to reduce the complexity of your code by performing one
update rather than a select and an update.  For example:

Dim SearchStr
Dim oConn
Dim cmacount

SearchStr = "UPDATE zip_table SET cmacount = cmacount + 1 WHERE zipcode Like
'%" &
Replace(zipcode, "'", "''") & "%' "

DBPath = Server.MapPath("../../Database/agentszips2.mdb")
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath & ";",
adOpenKeyset, adLockPessimistic, adCmdText

oConn.execute(SearchStr)

oConn.Close
Set oConn = Nothing

Like rudy pointed out, unless zipcode is the PK of zip_table, this may
update more than one record.  If it is the PK, then simply doing to update
is much better.  Using this code will also decrease the chances that you may
lose an increment if two threads run the same code at the same time.  To
illustrate look at the pseudo code of your old code:

1. Select the record
2. Record it's value
3. Update the record with the recorded value + 1

What happens when two threads run at the same time?  It is possible that the
value will only be incremented once when it really should be incremented
twice.  Use the follow steps as an example:

Thread 1: Select a record
Thread 1: Record it's value
Thread 2: Select a record
Thread 1: Update the record with the recorded value + 1
Thread 2: Record it's value
Thread 2: Update the record with the recorded value + 1

Even though two threads have executed, the ending value is only 1 greater
than what the second thread saw as the value.  Other people, I believe,
pointed out the different locking type.  That will help of course, but the
bottom line is that all this locking stuff can be avoided by moving most of
the actual functionality to the database.

-joshua




More information about the thelist mailing list