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

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 Hau" <davehau-no-spam-123_at_no-spam.netscape.net> wrote in message news:s2U%a.1826$3q.781_at_newssvr25.news.prodigy.com...
> "Hillel Eilat" <hillel_at_attunity.co.il> wrote in message
> news:bhnqgm$dei$1_at_news2.netvision.net.il...
> > Hi folks.
> >
> > Some RDBMS products support the following syntax format:
> >
> > SELECT .... <whatever...> WITH {READCOMMITTED | REPEATABLEREAD | ... |
> > SERIALIZABLE}
> >
> > The WITH clause provides the means for overriding the default isolation
> > level (or the one
> > specified upon SET TRANSACTION) at a specific SELECT statement.
> >
> > WITH clause is applicable for INSERT,UPDATE,DELETE as well.
> >
> > Is there an equvalent to the "WITH" clause in Oracle's SQL?
>
> No, but you can put the read in a transaction and get similar results:
>
> set transaction isolation level {serializable | read commited};
> select ... <whatever>;
> commit;
>
> The default isolation level for a transaction is read committed. If you
set
> it to serializable, what this does in Oracle is your select will be
> processed with the database "frozen" at the point in time (SCN) when the
> "set transaction isolation level serializable" is processed. You don't
see
> any data committed after that and before the commit. This is a bit
> different than in other databases where the isolation levels repeatable
read
> and serialization will cause a shared lock for the read to be held until
> commit. In Oracle, reads never block writes, and vice versa, even when
you
> are operating in the serializable isolation level.
>
> Another thing to note is that Oracle only provides two of the four
isolation
> levels in SQL92, i.e. read committed and serializable. Oracle does not
have
> read uncommitted because it does not need it. Read uncommitted is used to
> achieve non-blocking reads in many other databases (meaning you don't
apply
> a shared lock when reading a row). Oracle uses multi-version read
> concurrency and does not use shared locks in reads, therefore reads are
> always non-blocking. Regarding repeatable read, Oracle does not have an
> isolation level that provides repeatable reads within a transaction and
yet
> does not prevent phantom reads. However, Oracle does provide statement
> level read consistency even in the "read committed" isolation level. Many
> other databases (including DB2) does not provide statement level read
> consistency at the "read committed" isolation level.
>
> Cheers,
> Dave
>
>
>
> >
> >
> >
> > Your answers will be appreciated.
> >
> >
> >
> > Hillel.
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
Received on Sun Aug 17 2003 - 18:37:48 CDT