[thelist] eliminating chance of double-usage (classic asp/mssql)

Matt Warden mwarden at gmail.com
Thu Mar 17 14:50:43 CST 2005

On Thu, 17 Mar 2005 15:39:10 -0500, Brian Cummiskey <Brian at hondaswap.com> wrote:
> '--------------------------------------------------------------
> ' update it to used...
>         sSQL2 = "SELECT * FROM tControlNumbers where ControlNumber = '"&
> request.cookies("OD")("control_number") &"'"
>         set objRS = server.CreateObject("ADODB.Recordset")
>         objRS.Open sSQL2, strCN, adOpenDynamic, adLockPessimistic
>         if objRS.EOF then
>                 response.write "<em>Error - no record found with that control number.
>    Contact IT</em>"
>         else
>                 objRS("UseStatus") = "1"
>                 objRS.UPDATE
>         end if
>         SET objRS=NOTHING

Use an UPDATE sql statement rather than SELECT'ing and then updating
the recordset.

UPDATE tControlNumbers SET UseStatus=1 WHERE ControlNumber=... AND UseStatus!=1

You can then test the number of affected rows. If it is 1, then you
have the control number. If it is 0, then another process has selected
the control number and reserved it between the time you last selected
the control number and are now updating it.

You could still have problems depending on how your database handles
concurrent updates and reads. This is more or less a solved problem,
but you might want to look into how it's solved to see if it gives
well with what yuo're trying to do. If not, you will need to use some
sort of locking system in your application.

Matt Warden
Miami University
Oxford, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list