When a connection is made to the database, the recordset is essentially a "snapshot" of the elements of that databse that you have specified, i.e the data contained at the time of opening the connection. If user B subsequently logs onto the same page, and makes changes to the databse, user A will not see these changes until the page is reloaded, and a new "snapshot"(recordset) is taken. As stated earlier, if two requests are sent at the same time, the server will deal with it.

 Database apps are generally designed with multi user environments in mind, and thus are programmed how to deal with potential contention issues. To understand better, maybe it would be worth getting a book on database design to understand further how these contentions are managed. If you start trying to manually lock records, and thus try to deal with contentions yourself it soon becomes very tricky. For example what action would trigger the lock on a table to be released? when a transaction is commited to the database? but what if user a starts a transaction, and then gets side tracked and dosnt complete, and leaves it running while he goes and does soemthing else. The lock will still be in place and therefore user b,c,d...... cant do anything. If you release the lock after a set amount of time, how much time do you allow to allow a user to succesfully complete a transaction?

As for stored procs, they simply make your life as a programmer easier. once a procedure has been written and works, it can be called upon at any point in the program to perform the same job, therefore stops you having to retype the same bit of coding over and over again. It also makes it much easier to understand and manage what the program is doing, and subsequently much easier to update in the future.

The original question was, basically, how do you cater for multiple users in an ASP web application? What is the professionally recognised method?

A summary of the above posts so far is:

1. Don't do manual locking.
2. Don't do manual timestamping to create a lock.
3. It's a good idea to use stored procedures in a database.

All fair enough, but this does not really answer the question. No-one has so far told me how to implement a solution.

Has anyone here written a multi-user web application? If so, please can you tell me - in practical terms - how to address the issues in my original question.

Many thanks.

James

James,

When I build a multi-user application, usually I'm not building an application that has the scenario where two (or more) users are manipulating the same data simultaneously (key word being "manipulating"). Most often users are retrieving the same data (products, news, etc) but are very seldom updating the same data. Usually they are updating data that is only applicable to them (i.e. shopping basket, personal info). So in this regard there is little that has to really be considered when constructing the application. You basically are building it with a single user in mind so you need to make provisions to execute queries and such with that concept in mind (i.e. SELECT * FROM ShoppingBasket WHERE BasketUserID = x). In this regard, there is little that you need to do to make queries or the code "multi-user compatible". You just need to write queries that apply to a single user instance.

However, if you are going to be building an application that will have multiple users updating the same data simultaneously then you need to consider some of the previous responses. You must come up with some kind of locking scheme so you don't have users stepping on each other's toes. This is no trial task and can be difficult in a web application environment because of the stateless nature of it. Inevitably you'll need to make some sacrifices to the user experience in order to achieve data integrity.

Perhaps you should describe in more detail what your application is going to be doing so that we can help with some more refined recommendations.

Peter
------------------------------------------------------
Work smarter, not harder.

Peter,

Yep - thanks - that's helpful.

I see the distinction you are making.

Well - my application is one in which several users could potentially try to update the same records, because that data is "generic"; it does not belong specifically to one user. (You could consider it to be like a company address book - there are many entries, and several people authorised to update them). Hence, the possibility of clashes does arise here.

pgtips said, "Personally I can't see any reason to implement your own locking mechanism." I didn't understand that comment because surely that is exactly what I MUST do. And as I understand it, whether I do it in my ASP code or via stored procedures has no bearing on the general method used to create the locking.

So... I am curious to know if anyone has experience of this kind of application (surely it must be extremely common to want to do this kind of thing???) and if they can tell me what is the best, recognised way of tackling this issue.

Many thanks.

James

James it seems I have confused you. When I said "Personally I can't see any reason to implement your own locking mechanism" I didn't mean that you shouldn't use the timestamp method. I don't see the "timestamp method" as a locking mechanism - its just a way to tell if the data has been changed, it doesn't lock any oher users out of the table or record. That's why I like this timestamp method - it gives you the data integrity you need without having users blocked all the time by some custom lock flag in your tables.

Clearer?

Phil,

Thanks for clearing that up for me. I understand.

So... it looks like the best way is to create a timestamp. I suppose this means that occasionally, someone will make a bunch of edits (in their copy of a recordset) and try to submit them - only to find that the edits can't be submitted because someone else has submitted other edits since the recordset was opened.

I guess that would be annoying for the person concerned. Is that just an unavoidable "hazard" of two people using the same web application to edit a recordset?

James

I have several apps that you might consider multi-user. I open my recordsets as LockOptimistic without issue. I don't see a purpose for allowing two users to modify the same record with different data, however, I can see two users modifying two separate parts of the same record simultaneously.

Anyway a basic recordset for my work would look something like this...

set rs = server.createobject("adodb.recordset")
sql = "select * from TABLE where ID = " & ID
rs.open sql, cn, 3, 3
if not rs.eof then
  rs("FIELD1") = FIELD1
  rs("FIELD2") = FIELD2
  rs.update
end if
rs.close
set rs = nothing

The scenario in question here is that on one side of the office, person A may be trying to update an entry in our hypothetical "address book" web application, and on the other side of the office, person B may be simultaneously trying to update that same entry. Obviously, if person A submits his edits first, then person B will be editing an out-of-date copy of the entry. In certain circumstances, this is undesirable (e.g. person B might take a certain course of action because they are unaware of the changes that have already been submitted by person A).

This is a scenario which is likely, and must be catered for. That is what I am trying to do, and that is why I need to find out the best way of doing it.

Any other suggestions / techniques on this issue would be much appreciated.

Thanks.

James