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

Ken Schaefer Ken at adOpenStatic.com
Fri Mar 18 00:13:29 CST 2005



: -----Original Message-----
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Matt Warden
: Subject: Re: [thelist] eliminating chance of double-usage (classic
: asp/mssql)
: 
: 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. 

You can set the transaction isolation level in SQL Server (e.g. repeatable
read) to prevent issues with updates and dirty reads.

Cheers
Ken


More information about the thelist mailing list