Re: Multiple update problem
Date: Thu, 21 May 1998 13:01:18 GMT
Message-ID: <3565235e.2458224_at_192.86.155.100>
A copy of this was sent to Mungo Henning <mungoh_at_itacs.strath.ac.uk> (if that email address didn't require changing) On Thu, 21 May 1998 09:03:35 +0100, you wrote:
>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? I'm quite willing to believe that it's my impression of
>Oracle that's wrong, but can you set me straight please?
>
[Quoted] [Quoted] Read consistency applies to Reads. Reads will not block in Oracle (unless of course they are select ... for update which is similar to updating the data).
In order to have consistent data in the database however, Writes may (and will at times) block other writes. UserA is writing, UserB is writing -- UserA and UserB may block eachother.
If either of UserA or UserB was just reading -- no blocking.
>When you say "BLOCK" do you mean that the insert will be suspended
Yes.
>pending the lock? What would happen if userA then altered the same
>record (whose value is now ninety) to eighty without committing - would
>the block be released?
>
[Quoted] No, the block would not be released until UserA commits. that is because UserA 'owns' the row with the value 10, the row with the value 90 (that doesn't exist anymore but does exist in reality -- it was there), and the row with the value 80 -- they are all the same row -- UserA just owns all of the index entries right now.
If userA updated 10 to 90 and 90 to 80 and committed, userb would successfully insert 90.
>TIA
>Mungo Henning
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 21 1998 - 15:01:18 CEST