Re: webserver read consistancy

From: Steve mouatt <steve_at_bedrockcomputers.demon.co.uk>
Date: 1998/04/01
Message-ID: <35222AD2.655B_at_bedrockcomputers.demon.co.uk>#1/1


Dennis Reed wrote:
>

[snip]

> NEXT - what happens when you go to a stateless mode of client-
> server connection, such as a web browser? The connection
> between client (browser) and server (either OWS or IIS) is only open
> for the brief period of time it takes to exchange data. Then the
> connection is closed. Questions:
> * how long does the cursor stay open in the PSGA? does it close and
> the data get flushed as soon as the client connection closes? Does
> it stay open for a fixed amount of time and then get flushed? Does
> it age out on a least-frequently-used basis?
They close when the transaction ends or earlier if so specified.

> * in this type of connection, is the SCN transmitted to the client
> along with the row information?

Only if you obtain it and transmit it yourself.

> * if the PSGA cursor gets closed or aged out, how can the server
> ensure the data being changed is still current before allowing the
> update (as is done above by checking the SCN number)?
 See below
> * is resolution of this kind of problem built into OWS? Into ODBC?
> Into IIS? which component ensures data is still current before
> allowing update?

_Assistance_ is present in Oracles Webserver 3 as described below, I don't know about ODBC but I doubt it.

> * or (worst case), do connections like this update the data WITHOUT
> first ensuring the client data is current (as would happen with an
> UPDATE statement in sqlplus?
>

Yes

Firstly there is nothing in using an Oracle in the Web environment that is different to using it in any other. So the rules concerning the lifetime of any lock etc are the same.

There are two basic ways in which WWW access to an Oracle database can be achieved. Firstly the transient process( CGI,OWA etc) can access the database directly. Secondly the transient process can connect to a daemon which is its self connected to the database and all accesses are 'piped' through it.

The second method allows you to use the in built facilities of Oracle to perform the record locking but other complications and issues arise from this method including formulating a communication protocol between daemon and transient process, multi-threading, and multi-tasking. (Without the latter you will create a severe bottle neck within your structure). In effect you will have to mimic certain features of the Oracle daemon to gain the benefits of other features.

If no daemon is involved the the situation is reversed. Multi-tasking, the communication protocal and multi-threading are handled for you but you will need to implement a locking mechanism.

One way in which you can achieve this is to take a hybrid approach. By this I mean that you develop a daemon that will handle the locking for you and the transient processes will utilise this to accomplish there task. The important thing to this is to understand that you won't actually take out locks on the Oracle database but maintain a list of locked records and an identifier to indicate who has the lock. As a result the daemon can be refered to to ascertain whether a record selected is subject to one of our locks and therefore inhibit its display or whatever action you require.

Similarly when a lock is required the transient process must request a lock from the daemon and tell it when this lock can be released again.

It is important to remember that when a user selects a set of records for updating there is no guarentee within WWW that they will ever commit their changes and therefore any such lock daemon should incorporate a timeout facility.

In Oracle Webserver 3 a transaction _can_ span more that one conversation, ie it does end when the data is transmitted and the client connection closed. Prior to this the transaction ends when the client connection closes. This assumes that the PL/SQL cartridge is being used. In this context this new facility with OWS 3 means that the DBMS_LOCK package could be used to assist us without the need for a 'lock' daemon.

Steve

There are Received on Wed Apr 01 1998 - 00:00:00 CEST

Original text of this message