[thelist] eliminating chance of double-usage (classic asp/mssql)
Brian Cummiskey
Brian at hondaswap.com
Thu Mar 17 14:39:10 CST 2005
Hi all,
I'm writing a script that first selects an available control number from
the table.
then, i am updating that selected number to UseStatus = "1" (which
denotes used; 0 = available/default value)
and then i'm doing a simple query to get the amount left for
documentation purposes for now.
This works fine, and produces the desired results.
Now, my problem is, there is a SMALL CHANCE that at any given moment,
there may be two people hitting the script at the exact same time, thus
the 2nd selecting the same control number as the first before the first
has a chance to update the UseStatus to "1".
I understand we're talking about mili seconds here, but there it still a
chance. If this were to happen, it will lead to sooo many problems, i
wouldn't even begin to type a list up.
Here's my current code:
<%
'------------------------------
' select an available control number
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open strCN
sSQL = "Select TOP 1 ControlNumber FROM tControlNumbers where UseStatus
= '0' AND STATE = '"& statelookup &"'"
Set rst = Server.CreateObject("ADODB.Recordset")
rst.ActiveConnection = conn
rst.open sSQL
if not rst.EOF then
do until rst.EOF
for x = 0 to rst.Fields.count - 1
Response.Write "<p>Your Control Number is: <em>"&
rst.Fields(x).Value &" </em></p>"
Response.cookies("OD")("control_number") = rst.Fields(x).Value
next
rst.MoveNext
loop
else
response.write "Error, Improper SearchState passed. Call over a
manager to get in touch with IT"
response.write "<script type=""text/javascript"">alert('Improper
SearchState passed. Call over a manager to get in touch with
IT.');</script>"
end if
'--------------------------------------------------------------
' 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
'---------------------------------------------------
' now, check for remaining numbers left...
Set Conn3 = Server.CreateObject("ADODB.Connection")
Conn3.open strCN
sSQL3 = "SELECT State AS 'State', count(*) AS 'Control Numbers Left'
FROM tControlNumbers where UseStatus = '0' group by State"
Set rst3 = Server.CreateObject("ADODB.Recordset")
rst3.ActiveConnection = conn3
rst3.open sSQL3
response.write "<table><tr>"
for x = 0 to 1
Response.Write "<th>"& rst3.Fields(x).Name & "</th>" & vbcrlf
next
response.write "</tr>"
if not rst3.EOF then
do until rst3.EOF
response.write "<tr>"
for x = 0 to 1
Response.Write "<td>"& rst3.Fields(x).Value &"</td>" & vbcrlf
next
response.write "</tr>"
rst3.MoveNext
loop
end if
rst3.close
conn3.close
response.write "</table>"
%>
this produces the desired results:
Your Control Number is: XXXXXXXXXXXX
State Control Numbers Left
CA 100
FL 100
MA 86
NJ 100
TX 100
But as stated, i'm concerned about that small time before the update
happens where someone else hits the app, and selects the same top 1 as
it hasn't been updated to used yet.
Anyone have any ideas on how to assure that it won't be used twice?
More information about the thelist
mailing list