Re: Multiple update problem

Posted and emailed.

Mungo Henning <mungoh_at_itacs.strath.ac.uk> wrote (in <3563DFD7.6C670AC8_at_itacs.strath.ac.uk>)...

| Thanks for the reply Thomas, but I'm still curious...
|
| Thomas Kyte wrote:
| [multiple update scenario snipped]
| >
| > >I'm thinking of the following (separate) scenarios: the past is to the
| > >left
| > >of the line, the future to the right:
| > >
| > > UserA:update UserB:insert UserA:commit UserB:commit
| >
| > If userA updates 10 to 90, UserB's insert will BLOCK on the unique index entry.

| When userA performs the update, the index will be modified. But I was
| under the impression that for read-consistency userB should not be
| aware that another user is amending the table until the amendments
| are committed?
This is true for *queries* not for statements which change values.

Queries see the database at the point when the *query* started. Nothing to do with any transactions which change the rows that the query may see after it (the query) has started. This is often where the 'snapshot too old' message comes from as Oracle stores original values of rows used by a query before any changed took place. It stores original values in the rollback segments.

Changing rows...
Be aware that when a transaction changes a row then that row is locked. No transaction from another session can change that row until...
1) The transaction is committed. At which point any awaiting transactions will see the new values.

2) The transaction does a rollback. At which point any awaiting transactions will see the original value before this transaction started.

There are *no* other options! (rolling back to save-points does not release locks - version 7.2 ).

To re-iterate about queries.
Any query which started before the following updates took place would *not* see the new values!  

| I'm quite willing to believe that it's my impression of
| Oracle that's wrong, but can you set me straight please?
|
| When you say "BLOCK" do you mean that the insert will be suspended
| pending the lock?
Yes

| What would happen if userA then altered the same
| record (whose value is now ninety) to eighty without committing - would
| the block be released?

See above about when a record is released. But to confirm. The record would be changed by User A and would *not* be released. An explicit commit or rollback is required by User A to free the record. An implicit commit would also free the record of course - such as a DDL statement (create table etc.)

It is fairly easy to try most of this out yourself. Just start a couple of SQL*Plus sessions in SCOTT/TIGER and play with it.

graham Received on Thu May 21 1998 - 15:39:46 CEST