Re: How does unique index work?

jbradshaw777_at_yahoo.com (John) wrote:
> ctcgag_at_hotmail.com wrote in message
>
> > > 3. ORALCE enforces the uniqueness due to the index. How? By doing a
> > > full scaning on the table column and the index?
> >
> > No, a index look-up suffices, and of course it uses the index
> > mechanism, not a full scan.
> >
> Makes sense. Yes.
>
> > > 4. Insert another row into the table. How does ORACLE enforce the
> > > uniqueness? ORACLE does not need to look up the rollback segment to
> > > check for the uniqueness, right?
> >
> > Did the first insert commit? If not, was it in the same transaction or
> > a separate one?
> >
> > If the first isn't committed, and the two inserts are different
> > transactions, then I think it does go to rollback. From some
> > experiments I did a while ago (details forgotten), it seems like it
> > does two lookups. It does a read-consistent look-up and dirty-read
> > look-up. If both agree that the new row is unique, then it goes
> > through. If they both agree it's not unique, it throws an error. If
> > they disagree, then it blocks.
> >
> > Xho
> I did mean the first has not yet committed and a different transaction
> comes in and does an insert. Can you explain this further? You mean
> the 2nd transaction actually does a dirty read on the stuff not yet
> committed by the first?

Yes. This doesn't mean it present the results of the dirty read to you, but Oracle does internally do one. (It has no choice, if you are going to make changes to the data block, you have to have the actual data block, dirty as it may be. Some may say it is not a dirty read if it is only used internally, but that's just semantics to me.)

> You mean ORACLE actually compares 3 values,
> old data and new data from the 1st and the data being inserted by the
> 2nd?

Yes, that is what I meant. But apparently I was wrong, it doesn't happen that way.

> How do you determine ORACLE is doing a dirty read?

If T1 deletes a value, and then T2 tries to insert that value, T2 will block until T1 commits or rolls back. (T2 errors if T1 rollsback, completes if T1 commits). If T2 never did a dirty read, it would not know to block.

> Why is dirty
> read necessary?
>
> This is the kind of stuff that, like Ramsfeld said, 'you don't know
> that you don't know'. I have always been afraid of that. Please do
> comment further.

Say T1 deletes a value, then reinserts the same value, and then T2 tries to insert the same value. The way I had described it, T2 would immediately error, because both dirty and consistent reads find the value already there. But that isn't what happens, instead T2 blocks until T1 commits or rollsback. Then, T2 errors, regardless of which way T1 finished the transaction.

>
> John

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB