Hi,

I am writing a web application with ASP and SQL Server 2000. The app basically enables the manipulation of data in the database. When I open a recordset, I do so with pessimistic locking.

Scenario:

Two people, A and B, are using my app at the same time. A opens a record, and therefore B is barred from opening it.

However, while A has the record open, B goes (in his browser) to the page that requests the opening of the record.

Question:

What happens to B's request? Is it discarded (and lost) by the server, or does the server queue it and execute it when A releases the record? If queueing takes place, how many requests can wait in a queue?

I am new to the way in which multi-user apps ought to be programmed, and this is an area I don't understand. Please can you help me see how this works, and if I need to apply special techniques for catering for multiple users?

Thanks.

James

//##

When you open a recordset in an ASP page the recordset is only open for the lifespan of that page. If user A loads up a page with row data in it, the record is opened and closed before the user ever even sees the data. Then user B loads up the same record, the same thing happens. They can both access the same record because they aren't really accessing it simultaneously. Subsequently, whoever saves the data LAST will win because the first one's changes will be overwritten.

There isn't really a concept of "queueing" in ASP. The server processes requests as it gets them. And when I mean request, I mean the browser sends an HTTP request, and waits for a response. This usually happens in fractions of a second.

ASP out of the box is not technically "multi-user". ASP is essentially a whole bunch of isolated and unrelated conversations between the client and server. You can use parts of ASP (sessions, which use cookies) to establish who the current user is. However, when you are interacting with data you need to program in how different users interact with the data. If two users were to try to edit the same database row, you would develop conflicts. You would need to build in the "locking" of the data into your particular application.

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

Thanks.

So - a VERY basic question here: Is the server responding to strictly ONE request at a time? If so, why is there a need for locking at all? As I understand it, the server could open the recordset as adLockPessimistic, so that a subsequent request for that recordset would be barred until it had been released. But that would only make sense if the server was processing requests simultaneously from different users of the application. I realise that everything is happening very fast on the server, but in principle the server COULD get two or more requests at EXACTLY the same time - what would it do in that case?

Still not quite sure I understand how the ASP server operates...

Can you dumb it down more for me?!

James

I'm really not all that knowledgable of underbelly workings of ADO. Ken Schaefer (http://www.adopenstatic.com) is really the guy who knows about that stuff.

What part of the ASP server process are you unclear about?

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

If the db server gets 2 or more request at the same time then it simply processes one of them and makes the other one(s) wait. (You cannot control which one gets processed and which one waits.) In SQL Server the SET LOCK_TIMEOUT setting controls how long it waits for the lock to be released (default is to wait forever).

In a web app you really shouldn't be opening recordsets with write locks if you have multi-user concerns - use adLockReadOnly and do your reads and updates via stored procedures. That way you let the database handle many of these issues rather than trying to code around it yourself - the db is much better at it than you (or I), its what it was designed to do.

hth
Phil

Thanks, Phil. That helps.

At the moment, my app uses SQL statements to open recordsets. I don't use stored procs (in fact, I have yet to learn how to!)

Please can you explain a bit more about how/why using stored procs would be a better method of programming the app?

I want to make sure that I am going about this in the best way. I am learning very much "on-the-fly", so your advice is much appreciated.

Thanks.

James, there are many reasons why stored procs are better than direct sql - in no particular order the main ones for me are:
1. more security against SQL injection attacks
2. the execution plan is compiled and re-used so its more efficient
3. it provides a framework where the code that reads/writes the database is much closer to where the data is stored so its much easier to make subsequent changes.

There are plenty of examples of writing and calling stored procs - give it a go, you may be surprised how simple it is.

hth
Phil

Phil,

OK - but in terms of providing the locking I need, why would stored procs be better? Do they allow me to do something I couldn't otherwise do in my ASP code?

Please let me know, in case I am missing a trick with regard to this locking isse.

Thanks.

I'm not aware of anything that can only be done in stored procedures, but if you were to use the timestamp method for checking updates it would be easier to implement with a stored proc based approach. Personally I can't see any reason to implement your own locking mechanism.

Phil,

I am now confused.

Surely if I create and check a timestamp field for my records, that IS an implementation of my own locking system...

I don't see how it is possible to have a locking system of the type I need (see my original post) without implementing my *own* locking system with ASP and SQL.

Can you clarify this for me?

Thanks.