|
 |
asp_databases thread: RE: How to prevent 2 concurrent users from accessing the same records
Message #1 by "Yvan Caron" <yvancaron@s...> on Tue, 15 Aug 2000 18:55:54 -0400
|
|
Hi All,
I think I have found a strategy to limit the number of concurrent users to
an ACCESS database. I have read somewhere that ACCESS is limited to a
certain number of connections, I am not sure but I think the limit of
concurrent connections to an ACCESS Database is 10 but maybe I mix this with
Personnal Web Server's limit that is limited to a maximum of 10 concurrent
users. Anyway, my startegy is to define an Application variable (read
global) to hold at any one time the state of a connection object. This way I
may be sure to prevent 2 concurrent users from accessing the same records in
the same table.
Here is my strategy to accomplish this:
<%@ Language=VBScript %>
<!-- #include file = "adovbs.inc" -->
<%
' this function is used to prevent an infinite loop
' more on this below
dim MyTimeOut
Function IsTimeOut()
If Timer >= MyTimeOut Then
IsTimeOut = True
Else
IsTimeOut = False
End If
End Function
%>
<HTML>
<HEAD><TITLE>The Art of Blocking Concurrent Users</TITLE></HEAD>
<BODY>
<%
Dim AccessConnect
Dim oConn
' define the connection string for ACCESS
AccessConnect = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=MyAccessDatabase.mdb;" & _
"DefaultDir=D:\Documents\;" & _
"Uid=;Pwd=;"
' Here I use the ASP Application object to hold
' a persistent connection object.
' Note that this 'if' will execute only one time
' when the first user will request this page
if isEmpty(Application("ConnectionToAccess")) then
Application.Lock'
set Application("ConnectionToAccess") = _
Server.CreateObject("ADODB.Connection")
Application.UnLock
end if
' I keep an easy reference to the Application object
set oConn = Application("ConnectionToAccess")
' the tricky part is below ...
' When a user try to connect to my ACCESS db
' I verify if the connection is closed, if not
' it means that another user is connected to my ACCESS db
' Then I loop until the connection gets released or
' a predefined 5 seconds timeout has been reached
MyTimeOut = Timer + 5
Do
Loop Until ((oConn.State = adStateClosed) Or (IsTimeOut))
' When the loop ends, it may means 2 things:
' 1) the connection has been released by the other user
' 2) or my time out has expired
' If the time out has expired and the connection state is
' still pending then I end the script gracefully
' alerting the current user to come back later
if not (oConn.State = adStateClosed) then
Response.Write "Try to come back in a moment, sorry!"
Response.End
end if
' Otherwise I can process the rest of the script as intended
' Hence, I open the connection for the current user alone
oConn.Open AccessConnect
' I put my ASP stuff here
' and end my script with the close statement to release
' the connection for other users
oConn.Close
set oConn = Nothing
%>
</BODY>
</HTML>
What do you think ? I would appreciate having your comments
regarding this strategy.
Cheers ;-)
Yvan Caron
mailto:yvancaron@s...
Message #2 by "Yvan Caron" <yvancaron@s...> on Tue, 15 Aug 2000 14:26:07 -0400
|
|
Hi All,
I am trying to figure out how to prevent 2 concurrent
users from accessing the same records in the same table.
I have these datas in a table named 'tblHosts':
id host port connectedTo
233 100 10 235
234 200 20 236
235 300 30 0
236 400 40 0
237 500 50 240
238 600 60 241
239 700 70 0
240 800 80 0
241 900 90 0
A user request the top 2 records using this SQL command:
strSQL = "SELECT TOP 2 * FROM tblHosts WHERE connectedTo
<> 0"
and he gets the 2 first records matching the criteria
'connectedTo <> 0':
In this case he gets:
233 100 10 235
234 200 20 236
An ASP script process these 2 records but while the script
process these 2 records I would like to prevent other users
from getting or accessing these 2 same records.
How can I do this?
I tried different approaches in Visual Basic in order to
experiment. Once I find the exact strategy, I will use the
script in an ASP page.
BTW, I use an ACCESS database on the server.
Here is what I did so far but without success:
----------------------------------------------
Private Sub cmdReadTop2records_Click()
' I use two recordset objects to simulate 2 concurrent users
' accessing the same table
Dim oRS1 As New ADODB.Recordset
Dim oRS2 As New ADODB.Recordset
' My ACCESS connection string
Const AccessConnect As String = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=p2p.mdb;" & _
"DefaultDir=D:\Documents\Socket prog\p2p system\;" & _
"Uid=Admin;Pwd=;"
' local variable to hold my request
Dim strSQL As String
' Here it is, I open 2 recordsets with a locktype = adLockPessimistic
' using this approach, I thought I would block other users from
' accessing the same data but to no avail.
strSQL = "SELECT TOP 2 * FROM tblHosts WHERE connectedTo <> 0"
oRS1.Open strSQL, _
AccessConnect, _
adOpenStatic, _
adLockPessimistic, _
adCmdText
oRS2.Open strSQL, _
AccessConnect, _
adOpenStatic, _
adLockPessimistic, _
adCmdText
' here I output the datas in a text box named txtOut
While Not oRS1.EOF
txtOut.SelStart = Len(txtOut) + 1
txtOut.SelText = oRS1(0).Value & "|" & _
oRS1(1).Value & "|" & _
oRS1(2).Value & "|" & _
oRS1(3).Value & vbCrLf
oRS1.MoveNext
Wend
'
While Not oRS2.EOF
txtOut.SelStart = Len(txtOut) + 1
txtOut.SelText = oRS2(0).Value & "|" & _
oRS2(1).Value & "|" & _
oRS2(2).Value & "|" & _
oRS2(3).Value & vbCrLf
oRS2.MoveNext
Wend
'
oRS1.Close: Set oRS1 = Nothing
oRS2.Close: Set oRS2 = Nothing
End Sub
Using the above approach my 2 recordset objects (oRS1,
oRS2) always contain the same 2 records:
---------------------
233 100 10 235
234 200 20 236
The result that I intend to get is ...
oRS1 should get this:
---------------------
233 100 10 235
234 200 20 236
and
oRS2 should get this:
---------------------
237 500 50 240
238 600 60 241
Any clues about how to solve this dilemma?
----------
Yvan Caron
mailto:yvancaron@s...
|
|
|
View Cart
