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

From: Dave Hau <davehau_nospam_123_at_nospam_netscape.net>
Date: Tue, 19 Aug 2003 19:08:40 GMT
Message-ID: <3F4275B7.80907@nospam_netscape.net>

Dave Hau wrote:
> "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.)

Yong, I was thinking for Case #2, you might be able to use "SELECT ... FOR UPDATE" which will hold exclusive locks on all the rows of the result set until you commit the transaction. But I believe "SELECT ... FOR UPDATE" will only guarantee repeatable read, not phantom-free read.   Correct me if I'm wrong. The documentation is not entirely clear on this issue.

This way you would be able to do a "repeatable-read" select statement within a "read-committed" transaction.

• Dave

>
> Cheers,
> Dave
>
>
Received on Tue Aug 19 2003 - 14:08:40 CDT