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

[Brian Cummiskey]

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 &"&nbsp;</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?