Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Oracle, webserver, and read consistancy
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
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 Thu Mar 19 1998 - 00:00:00 CST