Re: Oracle, webserver, and read consistancy

On Thu, 19 Mar 1998 22:54:12 GMT, djr3_at_pge.com (Dennis Reed) wrote:

>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.

If I remember correctly, SCN are associated to database blocks, not rows. When a row in a table is updated, the entire block is copied to the rollback segment along with its SCN. The row in the table is updated and the block marked with a new SCN.

When any transaction performs a read on the table, Oracle retrieves rows which belong to blocks with a SCN lower than the current SCN when the query started. Thus, Oracle selects blocks from the table and blocks from the rollback segment, whenever necessary, even though the entire process is transparent for the client who requested the information.

>
>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.
>

I guess the reason why the 2nd client "bombs out" is that when trying to lock the row it wants to update, it doesn't wait for the lock performed by the 1st client to be released (either through a COMMIT or a ROLLBACK statement). If you test this situation using 2 SQL*Plus sessions, you'll see that when the 2nd session tries to update the same row, it will just block until the lock on that row is released.

I don't have much experience with ODBC, but it must be possible to indicate that you want your transactions to wait for locks to be released instead of popping out.

Also, if you're performing critical transactions in which read consistency is a crucial issue, be sure you read about the default way Oracle isolates transactions, since it is possible for a transaction which reads the "same data" several times to get different results if only the default transaction isolation level is used, which guarantees Statement Level Read Consistency and NOT Transaction Level Read Consistency.

Hope this helps,

Nuno Guerreiro Received on Fri Mar 20 1998 - 00:00:00 CET