webserver read consistancy

From: Dennis Reed <djr3_at_pge.com>
Date: 1998/03/31
Message-ID: <6frts4$hr22_at_news02.comp.pge.com>#1/1


I have what may be a newbie question on how Oracle works internally that we're trying to understand in order to properly implement web-based access to our departmental database.

If I understand how Oracle implements read-consistancy correctly, when a client queries the database:
* server parses SQL statement, parsed statement stored in shared SGA

  • server opens cursor in private SGA, retrieves query result into PSGA. each row returned has an SCN (system change number) associated with it that date/time stamps it. all rows retrieved have the same SCN. this ensures all rows retrieved are read-consistant to the same point in time. SCN is stored in PSGA with each row.
  • server passes query result (but not SCN?) back to client.

Now, if 2 clients query the same rows, no problem. There are no read locks in oracle; both users get the data & see the same thing. If one client then starts updating the data, that client gets locks on the associated table (shared mode) and row(s) (exclusive mode). Locks are in place until transaction is either committed or rolled back, then released. NOW - if first client updates and commits, then second client tries to do the same thing, the second client will bomb out with an error indicating the data he's trying to update is no longer current; client is forced to refresh data to what is currently in database, THEN update and commit. I THINK this is because when the second client tries to commit, Oracle sees that the SCN for the row(s) he's changing is older than the current SCN for those rows, and forces the error/refresh. Can somebody tell me if this thinking is correct??? p.s. we have verified the above behavior by having 2 concurrent odbc connections to the database looking at the same rows, then trying the above. it does what I said.

NEXT - what happens when you go to a stateless mode of clientserver  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?
* in this type of connection, is the SCN transmitted to the client

   along with the row information?
* 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)?
* is resolution of this kind of problem built into OWS? Into ODBC?

   Into IIS? which component ensures data is still current before    allowing update?
* 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?

Thanks in advance for helping us out.
Dennis Reed
DBA, Procedure Services
Pacific Gas & Electric Co. Received on Tue Mar 31 1998 - 00:00:00 CEST

Original text of this message