Re: Control for "Isolation level" at SELECT statement level

"Yong Huang" <yong321_at_yahoo.com> wrote in message news:b3cb12d6.0308190645.7999320e_at_posting.google.com...
> "Dave Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message
news:<glU%a.1829$wk.1381_at_newssvr25.news.prodigy.com>...
> > If what you're asking is whether you can have a select statement within
a
> > transaction, that has an isolation level different than the isolation
level
> > of the transaction, no you cannot do that in Oracle.
> >
> > Cheers,
> > Dave
>
> Dave,
>
> A little addition. How about have an autonomous transaction inside the
> "parent" transaction? Then you should be able to use a different
> isolation level from that in the "parent".
>
> Yong Huang

That will work in one case, but the other. The two possible cases are:

1. You are in a transaction with serializable isolation level, and you want a select that's at read-committed level. In that case, putting the select into its own autonomous transaction would probably work.
2. You are in a transaction with read-committed isolation level, and you want a select that's at serializable level. In this case, putting the select into its own autonomous transaction would not work because you have to commit the autonomous transaction before the outer transaction can proceed, and committing the autonomous transaction means the select you did would not be repeatable and phantom-free all the way to the end of the outer transaction, which is what serializable requires. (It's only repeatable and phantom-free to the end of the autonomous transaction, not the outer transaction.)

Cheers,
Dave Received on Tue Aug 19 2003 - 10:38:48 CDT